Database Programming in jsp

I. SQL Review
1. SQL statements are divided into two types: DDL (Data Definition Language) and DML (Dat Manipulation Languge, Data operation Language ). The former mainly defines the logical structure of data, including the definition of tables, views and indexes. DML mainly queries and updates databases.
2. Create Table (DDL ):
Create Table tabName (
ColName1 colType1 [else],
ColName2 colType2 [else],
...,
ColNamen colTypen [else]
);
Example: Cteate Table pJoiner (
Pno char (6) not null,
Eno char (6) nut null
);
Char int varchar and so on are reserved words used to define the column data type. varchar indicates a variable character type.
3, Select <col1>, <col2>,..., <coln>
From <tab1>, <tab2>,..., <tabm>
[Where <condition>]

Subquery in the condition:
Where Not Exists (
Select * From tab2 Where col1 = col2
) // When the query result is null, the condition is true.

4. insert into <tab1> VALUES (<col1>,... <coln>)
5. delete from <tab1> [WHERE <condition>]
6. UPDATE <tab1>
SET <tab1 >=< vlu1>
...
<Tabn >=< vlun>
[WHERE <condition>]
For example:
Update exployee
Set age = 27
Where name = Zhao Yi
2. Main JDBC interfaces:
The java. SQL. DriverManager class is used to process driver calls and support new database connections.
Java. SQL. Connection refers to the Connection between an application and a specific database.
Java. SQL. Statement, used for the execution of General SQL statements (it can be the execution process of queries, updates, or even database creation)
Java. SQL. ResultSet, The results returned by the query are saved in this object, which allows you to browse and access records in the database.

1. Use the odbc database through the jdbc-odbc bridge (jdbc Drivers is not required)

Set pubs sysDSN in odbc DSN (Data Source Name), sa as username, and password as null.
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver"); // load the driver
Con = DriverManager. getConnection ("jdbc: odbc: pubs", "sa", ""); // jdbc: odbc: pubs
Con. close ();
// Catch ClassNotFoundException and SQLException

The getWarning method of Connection returns a SQLWarning object. Check Before Connection.
The biggest benefit of using jdbc-odbc is: free. However, the performance is limited by odbc, and odbc drivers are generally expensive.
2. Use a dedicated jdbc driver. // Mm jdbc Driver
Put the jar file in ClassPath first.
Class. forName ("org. gjt. mm. MySQL (the best combination with PHP). Driver ");
Con = DriverManager. getConnection ("jdbc: MySQL (best combination with PHP): // localhost: 3306/dbname", "root ","");
Con. close ();

It can be seen that the method used to connect to which database is irrelevant to database operations and database connection.
Iii. query Databases
Statement stmt = con. createStatement ();
Stmt. setMaxRows () can control the maximum number of output records;
ResultSet rs1_stmt.exe cuteQuery ("select .....");

ResultSet points to the current record:
Int userId = rs. getInt ("userid ");
String userName = rs. getString ("username ");
... Or use the serial number (starting from 1)
Int userId = rs. getInt (1 );
Stirng userName = rs. getString (2 );

ClassNotFoundException is triggered by the failure of Class. forName () to load the jdbc driver.
SQLException is generated when a problem occurs during jdbc execution. There is an additional method getNextException ()
Catch (SQLException e ){
Out. println (e. getMessage ());
While (e = e. getNextException ()){
Out. println (e. getMessage ());
}
}

Generally, it is not recommended to write database access programs in jsp (the preferred choice for SUN enterprise-level applications) to encapsulate database access in a javabean.
Iv. In-depth ResultSet
1. ResultSetMetaData
ResultSet rs1_stmt.exe cuteQuery ("select ....");
ResultSetMetaData rsmd = rs. getMetaData (); // gets the ResultSetMateData object
Int numberOfColumns = rsmd. getColumnCount (); // number of returned Columns
Boolean B = rsmd. isSearchable (int I); // returns whether column I can be used in the where clause
String c = rsmd. getColumnLabel (int I); // obtain the column ID of column I
Objcet obj = rs. getObject ();
If (obj! = Null) out. println (obj. toString ());
Else println ("");
2. the SQL type and the getObject return type of the ResultSet and the corresponding XXX getXXX () method
GetXXX () method for SQL type jsp (preferred for SUN Enterprise applications)
--------------------------------------------
CHAR String getString ()
VARCHAR String getString ()
LONGVARCHAR String InputStream getAsciiStream ()/getUnicodeStream ()
NUMERIC java. math. BigDecimal java. math. BigDecimal getBigDecimal ()
DECIMAL same as above
BIT Boolean boolean getBoolean ()
TINYINT Integer byte getByte ()
SMALLINT Integer short getShort ()
INTEGER Integer int getInt ()
BIGINT Long long getLong ()
REAL Float float getFloat ()
FLOAT Double double getDouble ()
DOUBLE Double double getDouble ()
BINARY byte [] byte [] getBytes ()
VARBINARY byte [] byte [] getBytes ()
LONGVARBINARY byte [] InputStream getBinaryStream ()
DATE java. SQL. Date java. SQL. Date getDate ()
TIME java. SQL. Time java. SQL. Time getTime ()
TIMESTAMP java. SQL. Timestamp java. SQL. Timestamp getTimestamp ()