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.
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 ();