Using batch processing to speed up mysql--large database operations with JDBC operations

Before all the operations due to the small amount of data, so there is no batch optimization, performance has not been significantly deteriorated, but as I use Java to handle the large amount of data, the frequent use of static SQL statements method seriously reduce the processing efficiency, here summarizes the JDBC batch process method, To increase the throughput of the database.

First, in the formation of SQL statements, we can use StringBuilder to synthesize SQL, or we can use the JDBC set method to bring the parameters into.

The batch method of JDBC is known to have 2 major classes: The statement object-based and the PreparedStatement object.

1. Batch processing of statement objects

Advantages and Disadvantages :

Statement the batch of objects, by calling the Addbatch method to add each SQL separately to batch, the call Stmt.executebatch is typically used to process different kinds of SQL statements in bulk . In this example, a batch processing example using INSERT, modify, delete is given.

The statement object sends an SQL statement that is not compiled and performs more efficiently preparedstatement

Connection conn=getconnection (); String SQL1 = "INSERT into Multisql (Id,name,class) VALUES (3,a great news,see a lot)"; String sql2 = "Update multisql set id=3, name=\ ' modifiedname\ ', class=\ ' mengnew\ '"; String sql3 = "Delete from Multisql where id>0 and name=\ ' modifiedname\ '"; try{    Statement stmt = conn.createstatemen T ();    Stmt.addbatch (SQL1);    Stmt.addbatch (SQL2);    Stmt.addbatch (SQL3);    /** Execute Batch SQL *    /Stmt.executebatch ();    /** clears the executed SQL *    /Stmt.clearbatch ();} catch (Exception e) {    System.out.println ("Error in bulk processing of SQL!");    E.printstacktrace ();}

2. Batch processing of PreparedStatement objects

PreparedStatement class inherits sub-statement class, usually we can directly use the statement class reference to the PreparedStatement class object, but here for polymorphic, I still do not understand, not BB.

Advantages and Disadvantages :

The batch method of the PreparedStatement class wants the database to send the compiled SQL statements, which is more efficient than the method of calling the statement class above.

The batch method of the PreparedStatement class, which is used to send a set of SQL operation statements of the same kind, for example: A group is insert, or a group is update, delete, so often used for BULK INSERT, bulk Delete, batch update field values and other operations. The above method can send a different set of actions

Because it is a set of identical operations, it is often used in JDBC to bring parameters such as field values "into" into the SQL statement using the set method that comes with it

There is no catch statement, in fact the focus is not on the catch ...

try{    Statement stmt = conn.preparestatement ("INSERT into Multisql (Id,name,class) VALUES (?,?,?)");    PreparedStatement PS = conn.preparestatement ("INSERT into Multisql (Id,name,class) VALUES (?,?,?)");    Ps.setint (1,par_int);    Ps.setstring (2, "thename");    Ps.setstring (3, "phone");    Ps.addbatch ();    cnt++;    if (cnt%1000==0) {        ps.executebatch ();        Ps.clearbatch ();/** clean up the SQL instructions to avoid out of        memory */}/** final release of RAM *    /Ps.close ();

　　

Using batch processing to speed up mysql--large database operations with JDBC operations