The performance of the JDBC program is mainly determined by two factors. First, the database itself is in the nature of the database itself, the other is the use of the Database relative independent JDBC application interface (API). What is said is how to use the JDBC programming interface correctly to achieve better performance. The main optimization of JDBC is: 1. Select the correct JDBC driver the optimization of the Connection: Use the connection pool to manage the connection object 3.Statement optimization: use bulk update, etc. 4.Result Optimization: Correctly from Get data in the database, etc.
(1) Select the correct JDBC driver: It is best to choose the JDBC network protocol - pure Java drive efficiency is relatively high, but requires third-party software, such as Corba WebLogic belongs to this type.
(2) Optimize Connection objects: 1, set appropriate parameters DRIVERMANAGER.GETCONNECTION (STRING URL, Properties PROPS); prOPerties Props = new property (); 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) and setDefaultBatchValue (int ) Two parameter class optimization connectivity 2, use the connection pool to write a connection pool, so the flexibility of the program is strong, easy to transplant. The Apache project has developed a very common and very stable object pool http://jakarta.apache.org/commons/pool.htm designed its own connection pool, settled in the client: public object MakeObject () throwoBject ("Oracle.jdbc.driver.OracalDriver); Return Drivermanager.getConnection (" URL "," Username "," password "); when the object with the destruction}: public void destroyObject (Object obj) throws Exception { ((Connection) obj.close ()); } Note that: there is no target pool Recycling mechanism, the organic capacity limit in the object pool, how many idle objects (can be released) in the object pool (can be released) New can improve the rest. Try {CONNECTION.SetAutoCommint (false); // code PREPAREDSTATEMENT performance than Statementh is good Connection.commit () ;Connection.SetautoCommit (TRUE); } Catch (sqlexception e) {} Finally { 代 (connection! = Null) {Connection.Close ();
(3) Statement Optimization: The JDBC3 interface is used to process SQL execution, which is STATEMENT PREPAREDSTATEMENT CALLLESTATEMENT to provide appropriate STATEMENT interface Batch to get data from the database batch. PreparedStatement is better than Statement performance, which is mainly reflected in a SQL statement multiple times. PreparedStatemnt only compiles the analysis once and Statement is compiled once. Batch modified database Statement provides the way addbatch (string) and executebatch () calling method is stmt.addbatch ("isner .....") ;stmt.addbatch ("Update ... .. ") ;stimt.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 ();
Batch data from the database: This parameter will seriously reduce the performance of the program. CONNECTION STATEMENT RESULTSET has this parameter, and they affect the order of performance: ResultSet --- Statement --- Connection
(4) Optimization ResultSet: Reflecting on the following aspects: Batch reading data. Reasonable settings RESUETCHSIZE () and setFetchSize () methods parameters in the setFetchSize () method Using the correct GET and SET methods Use an integer rather than a field name as the parameter performance is relatively high. For example: setstring (2, "aaaa"); TString ("Name", "AAAA"); There are 3 directions fetch_forword, fetch_reverse fetch_unknown, one-way scrolling performance is relatively high.
Other performance optimization Timely display closedness Statement ResultSet, where Connection can be processed with the Connection POOL. Use the powerful query function of the database system to organize the data. This way to run is less interaction with the database service, and the database returns to the program has fewer records, so performance has great improvement.
