JDBC Learning notes-JDBC performance optimization

Notes | performance | Optimizing the JDBC program's performance is determined primarily by two factors, one is the nature of the database itself, the other is the use of the database's relatively independent JDBC application interface (API). Here's how to use the JDBC programming interface correctly for better performance.
JDBC Main optimizations are:
1. Choose the correct JDBC driver
2.Connention optimization uses connection pooling to manage connection objects
3.Statement optimization using batch update, etc.
4.Result optimization right from the database get data etc.

(1) Select the correct JDBC driver:
1 Jdbc-odbc Bridge
2 Local api-partial Java driver
3 JDBC Network protocol-Pure Java driver
4 JDBC Local protocol
It is best to choose JDBC Network protocol-Pure Java driver efficiency is high but requires Third-party software support such as CORBA WebLogic belong to this type

(2) Optimizing connection objects:
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);
objects can optimize connections by setting Setdefaultrowprefetch (int) and setdefaultbatchvalue (int) Two parameter classes

2. Use the connection pool to write a connection pool. This program is flexible and easy to transplant.
The Apache project developed a very versatile and very stable object pool http://jakarta.apache.org/commons/pool.htm
Create an object on the client call after you design your own connection pool
Public Object Makeobject () throws exception{
Class.forName ("Oracle.jdbc.driver.OracalDriver");
return drivermanager.getconnection ("url", "username", "password");
}
Use when destroying objects
public void Destroyobject (Object obj) throws exception{
((Connection) obj.close ());
}
Note that there is no recycling mechanism in the object pool, the object pool has a capacity limit, the object pool how many idle objects (can be released)

3. The submission of control transactions is best to manually commit transactions, not only to ensure data atomicity, but also to leave room for new improvements.
try{
Connection.setautocommint (FALSE);
Code is better than Statementh for PreparedStatement performance.

Connection.commit ();
Connection.setautocommit (TRUE);
}
catch (SQLException e) {
}
finally{
Code
if (connection!=null) {
Connection.close ();
}
}

4. Appropriate choice of transaction isolation level transaction_read_uncommited performance is highest
Transaction_read_commited, come on.
Transaction_refeatable_read Medium
Ransaction_serializable Slow

(3) Statement optimization
JDBC3 interfaces are used to handle SQL execution, statement PreparedStatement CallableStatement
Provide the appropriate statement interface
Bulk Execute SQL
Bulk fetching data from a database
PreparedStatement is better than statement performance is mainly reflected in the case of repeated executions of a SQL statement
PREPAREDSTATEMNT only compile the parse once and statement compile it once at a time.

Batch Modify Database
Statement provides methods Addbatch (String) and ExecuteBatch ()
The calling method is Stmt.addbatch ("Isnert ..."); Stmt.addbatch ("Update ...")
Stmt.executebatch ();
You 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 ();

Take data from the database in bulk.
This parameter is set and viewed through the setfetchsize () and Getfectchsize () methods. This parameter has a greater impact on the performance of the system.
This parameter is too small to degrade program performance significantly.
Connection Statement resultset all have this parameter, their effect on the performance of the order is:
ResultSet---------Statement---------Connection
(4) Optimizing resultset.
Embodied in the following several aspects
Bulk read data. To reasonably set the parameters in the Getfetchsize () and Setfetchsize () method of ResultSet
Using the correct get and set methods
Using integers instead of field names is a relatively high performance for parameters,
For example Setint (1,100);
SetString (2, "AAAA");
than Setint ("id", "100");
SetString ("name", "AAAA");
Good performance
Set the appropriate scrolling direction. There are 3 directions Fetch_forword,fetch_reverse Fetch_unknown
The one-way rolling performance is relatively high.
Other aspects of performance optimization
In time to show off connection Statement ResultSet
Where connection can be treated with connetion pool.
Use the powerful query function of the database system to organize the data. This program runs with less interaction with the database service, and the database returns to the
The number of records in the program is much less, so the performance is greatly improved.