Several common skills in Java database 1, Java database operation basic flow
2. Several commonly used important techniques:
Scroll, updated record set
Bulk update
Transaction processing
Java database operation Basic process: acquired database connection - execute SQL statement - Processing Execution Results - Release Database Connection
1, get the database connection
1) Take Database connection with DriverManager
example:
String ClassName, URL, UID, PWD; ClassName = "Oracle.jdbc.driver.Orcledriver"; 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) Way with JNDI (Java Names 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 ();
More for JSP
2, execute SQL statement
1) Use statement to execute SQL statements
String SQL; Statement SM = cn.createStatement (); sm.executeQuery (SQL); // Execute data query statement (SELECT) sm.executeUpdate (SQL); // Perform data update statement (delete, update, insert, drop, etc. );
2) Use preparedStatement to perform SQL statements
String SQL; SQL = "INSERT INTO USER (ID, NAME) VALUES (?,?)"; PreparedState ps = cn.prepareStatement (SQL); ps.setint (1, xxx); ps.setstring (2, xxx); ... resultset = ps.executeQuery (); // query int C = ps.executeUpdate (); // update
3. Processing execution results
Query the statement, return to the record set ResultSet.
Update the statement, returns the number, indicating the number of records affected by the update.
RESULTSET method:
1, Next (), move the cursor to the back, if you successfully return true; otherwise return false.
2, getint ("ID") or getSting ("name"), returns the value of a field under the current cursor.
3, release the connection.
Cn.close ();
General, turn off the ResultSet first, then turn off statement (or prepaaredStatement); finally close the Connection
Scroll, updated record set
1, create STATEMENT can be rolled, updated
Statement SM = cn.createStatement (ResultSet.Type_Scroll_ensitive, ResultSet.concur_read_only);
The STATEMENT acquisition is rolling 2, specifying parameters when creating preparedStatement
PreparedStateMet PS = cn.preparestatement (SQL, ResultSet.Type_Scroll_INSensitive, ResultSet.concur_read_only); ResultSet.absolute (9000);
Bulk update
1, Statement
STATEMENT SM = cn.createStatement (); sm.addbatch (SQL1); sm.addbatch (SQL2); ... sm.executebatch ()
A Statement object, after multiple SQL statements, batch updates. This multiple statements can be delete, update, insert, etc. or even
2, PreparedStatement
PreparedState ps = cn.preparedStatement (SQL); {ps.setxxx (1, xxx); ... ps.addbatch ();} ps.executebatch ();
A preparedStatement, you can execute a SQL statement, transform parameters multiple times, updated once.
Processing
1. Turn off the automatic submission of Connection
cn.setautocommit (false);
2. Execute a series of SQL statements
Important: Before performing each new SQL statement, the last execution of the SQL statement must be first clos
STATEMENT SM; SM = cn.createStatement (Insert INTO User ...); sm.executeUpdate (); sm.close (); SM = cn.createstatement ("INSERT INTO CORP ...); sm.executeUpdate (); Sm.close ();
3, submit
Cn.commit ();
4, if an exception occurs, then roll back
Cn.rollback ();
