1. Basic Java database operation process
2. Several Common important skills:
Record Sets that can be rolled and updated
Batch update
Transaction Processing
Basic Java database operation process: Get database connection-Execute SQL statements-process execution results-Release Database Connection
1. Obtain database connection
1) Use drivermanager to retrieve the database connection
Example:
String classname, URL, uid, PWD;
Classname = "oracle. JDBC. Driver. oracledriver ";
Url = "JDBC: oracle: thin: @ 127.0.0.1: 1521: orasvr;
Uid = "system ";
Pwd = "manager ";
Class. forname (classname );
Connection Cn = drivermanager. getconnection (URL, uid, PWD ); |
2) using JNDI (Java Naming and Directory Service)
Example
String JNDI = "JDBC/DB ";
Context CTX = (context) New initialcontext (). Lookup ("Java: COMP/ENV ");
Datasource DS = (datasource) CTX. Lookup (JNDI );
Connection Cn = Ds. getconnection (); |
Mostly used in JSP
2. Execute SQL statements
1) Use statement to execute SQL statements
String SQL;
Statement Sm = cn. createstatement ();
Sm.exe cutequery (SQL); // execute the data query statement (select)
Sm.exe cuteupdate (SQL); // execute the data update Statement (delete, update, insert, drop, etc.) statement. Close (); |
2) use preparedstatement to execute SQL statements
String SQL;
SQL = "insert into user (ID, name) values (?,?) ";
Preparedstatement PS = cn. preparestatement (SQL );
PS. setint (1, XXX );
PS. setstring (2, XXX );
...
Resultset rs = ps.exe cutequery (); // query
Int c = ps.exe cuteupdate (); // update |
3. Process execution results
Query statement, returns the record set resultset.
Update statement. A number is returned, indicating the number of records affected by the update.
Resultset method:
1. Next (): move the cursor back to a row. If the cursor is successful, true is returned. Otherwise, false is returned.
2. getint ("ID") or getsting ("name") returns the value of a field under the current cursor.
3. Release the connection.
Generally, disable resultset first, then close statement (or preparedstatement), and finally close connection.
Record Sets that can be rolled and updated
1. Create a rolling and updated statement
Statement Sm = cn. createstatement (resultset. type_scroll_ensitive, resultset. concur_read_only ); |
The resultset obtained by the statement is rolling.
2. Specify parameters when creating preparedstatement
Preparedstatemet PS = cn. preparestatement (SQL, resultset. type_scroll_insensitive, resultset. concur_read_only ); Resultset. Absolute (9000 ); |
Batch update
1. Statement
Statement Sm = cn. createstatement ();
SM. addbatch (sql1 );
SM. addbatch (sql2 );
...
Sm.exe cutebatch () |
A statement object that can be updated in batches after multiple SQL statements are executed. These statements can be delete, update, insert, or both.
2. preparedstatement
Preparedstatement PS = cn. preparedstatement (SQL );
{
PS. setxxx (1, XXX );
...
PS. addbatch ();
}
Ps.exe cutebatch (); |
A preparedstatement can be used to execute an SQL statement, change parameters multiple times, and update each time.
Transaction Processing
1. Disable Automatic Connection submission
CN. setautocommit (false ); |
2. Execute a series of SQL statements
Key Point: Before executing each new SQL statement, the statement (or preparedstatemet) of the last SQL statement must be closed first.
Statement Sm;
Sm = cn. createstatement (insert into user ...);
Sm.exe cuteupdate ();
SM. Close ();Sm = cn. createstatement ("insert into corp ...);
Sm.exe cuteupdate ();
SM. Close (); |
3. Submit
4. if an exception occurs, roll back