JDBC learning notes ---- JDBC performance optimized JDBC program performance is mainly determined by two factors, one is the nature of the database itself, the other is the use of the database relative independent JDBC application interface (API). How to use the JDBC programming interface correctly to get better performance. JDBC mainly optimized: 1. Select the correct JDBC driver 2. Connection Optimization Use the connection pool to manage the connection pool to manage the optimization of the Connection object 3.Statement uses batch update 4 .Result's optimization correctly from the database Get data, etc. (1) Select the correct JDBC driver: 1 JDBC-ODBC Bridge 2 Local API-Part Java Driver 3 JDBC Network Protocol - Pure Java Drive 4 JDBC Local Agreement best choice JDBC Network Protocol - Pure Java drive efficiency is high but requires third-party software support, such as CORBA WebLogic belongs to this type (2) Optimization Connection object: 1. Set the appropriate parameter DriverManager.getConnection (String Url, Properties PROPS); for example : Properties Props = New Properties (); Props.Put ("User", "Wuwei"); Props.Put ("Password", "wuwei"); Props.Put ("DefaultrowPrefectCH", "30"); PrOPS. put ( "dufaultBatchValue", "5"); Connection con = DriverManager.getConnection ( "jdbc: oracle: thin: @hostsString", props); object can be provided by setDefaultRowPrefetch (int) setDefaultBatchValue (int) and the two parameters based optimization connection
2. Using the connection pool to write a flexibility to connect to a connection pool so that it is easy to transplant. Apache project has developed a very common and very stable object pool http://jakarta.apache.org/commons/pool. HTM designs its own connection pool after the client calls the establishment of the Object makeObject () throws exception {class.forname ("Oracle.jdbc.driver.OracalDriver); Return DriverManager.getConnection (" URL "," Username ", "Password");} When destroying objects, use public void destroyObject (object obj) throws exception {((Connection) Obj.close ()); How many idle objects (can be released) in the object pool 3. The submission of the control transaction is best to manually submit the transaction, which can guarantee data atomicity and leave the new energy. Try {connection.setautoCommint (false) ; // Code preparedStatement performance is better than StatementH.Connection.commit (); connection.setautocommit (TRUE);} catch (sqlexception e) {} finally {// code IF (connection! = Null) {connection.close () }}
4. Select the appropriate transaction isolation level TRANSACTION_READ_UNCOMMITED highest performance TRANSACTION_READ_COMMITED fast TRANSACTION_REFEATABLE_READ medium RANSACTION_SERIALIZABLE slow (. 3) Statement optimization jdbc3 execution processing for an interface sql is Statement PreparedStatement CallableStatement Statement interface to provide appropriate batch to batch sql data fetched from the database PreparedStatement is better than Statement, which is mainly reflected in a SQL statement. PREPAREDSTATEMNT is only compiled. ("Isners ....."); stmt.addbatch ("Update .....") stmt.executebatch (); can also use preparedStatement to better improve performance. PSTMT = conn.preparedStatement ("INSERT INTO Test_table (...) VALUES (....?) "); PSTMT.SetString (1," AAA "); PSTMT.Addbatch (); PSTMT.SetString (1," BBB "); PSTMT. Addbatch (); ..... pstmt.executebatch (); Data from the database is quantified. This parameter is set and viewed through the setFectChSize () method. This parameter is relatively large for the performance of the impact. This parameter will seriously reduce the program performance. Both Connection Statement ResultSet has this parameter, and they affect the performance of performance: resultSet --------- Statement -------- CONNECTION (4) Optimize ResultSet. Reflected in the following aspects Read the data. Reasonable Setting ResultSet's getFetchSize () and setFetchSize () methods use the correct GET and SET methods to use integers instead of field names as parameter performance, such as Setint (1,100); setString (2, "AAAA "); Than setint (" ID "," 100 "); setString (" Name "," AAAA ");
