Tips in Java database programming _ MySQL

1. basic java database operation procedures 2. several common important skills: rolling and updating record sets batch update transactions process java database operations basic procedures: obtain database connection 1. basic java database operations

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