Java Database Operations (zz)

Basic Java Database Operations
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.

4. Release the connection
CN. Close ();
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
CN. Commit ();
4. if an exception occurs, roll back
CN. rollback ();