Basic process of Java database operation: Get database connection-Execute SQL statement-Process Execution Result-free database connection. I. Get a database connection 1. Use DriverManager to Access 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 services) way 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 in JSP. Second, execute the SQL statement 1. Execute SQL statements with statement
String SQL; Statement sm = cn.createstatement (); Sm.executequery (SQL); Execute Data query statement (SELECT) Sm.executeupdate (SQL); Execute Data UPDATE statement (delete, update, insert, DROP, etc.) Statement.close (); |
2. Execute SQL statements with PreparedStatement
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.executequery (); Inquire int c = Ps.executeupdate (); Update |
Iii. processing the results of the implementation A query statement that returns the recordset resultset. UPDATE statement, which returns a number indicating the number of records affected by the update ResultSet the method (1) Next () Moves the cursor back one line, or False if it returns true; (2) getInt ("id") or getsting ("name"), which returns the value of a field under the current cursor Iv. Release the connection
In general, close resultset first, then close statement (or PreparedStatement), and finally close connection. (i) A scrollable, updated set of records 1. Create scrollable, updated statement
Statement sm = cn.createstatement (resultset.type_scroll_ensitive, RESULTSET.CONCUR_READ_ONLY); |
The resultset obtained by the statement is a scrollable 2. Specify parameters when creating PreparedStatement
Preparedstatemet PS = cn.preparestatement (sql,resultset.type_scroll_insensitive, RESULTSET.CONCUR_READ_ONLY); Resultset.absolute (9000); |
#p # (b) Batch Update 1, Statement
Statement sm = cn.createstatement (); Sm.addbatch (SQL1); Sm.addbatch (SQL2); ... Sm.executebatch () |
A statement object that can execute multiple SQL statements after a batch update. The multiple statements can be delete, update, INSERT, etc. or both 2, PreparedStatement
PreparedStatement PS = cn.preparedstatement (SQL); { Ps.setxxx (1,XXX); ... Ps.addbatch (); } Ps.executebatch (); |
A PreparedStatement, you can put an SQL statement, transform parameters to execute multiple times, one update. (iii) Processing of transactions 1. Turn off automatic submission of connection
Cn.setautocommit (FALSE); |
2. Execute a series of SQL statements Important: Before executing each new SQL statement, the last statement (or Preparedstatemet) that executed the SQL statement must first close
Statement SM; SM = cn.createstatement (insert into user ...); Sm.executeupdate (); Sm.close (); SM = cn.createstatement ("INSERT INTO corp ..."); Sm.executeupdate (); Sm.close (); |
3. Submit
4, if an exception occurs, then rollback
|