Javaweb Study Summary (11)--JDBC Batch Processing

First, the introduction of batch processing

In the actual project development, sometimes need to send a batch of SQL statement execution to the database, should avoid to send execution to the database, and should adopt the batch processing mechanism of JDBC, in order to improve the execution efficiency. Batch processing only for update (add, delete, change) statements, batch processing did not query what happened!

There are two ways in which JDBC implements batching: statement and PreparedStatement

You can call the Addbatch (String sql) method of the statement class multiple times, add all the SQL statements that need to be executed to a batch, and then call the ExecuteBatch () method of the statement class to execute the statements in the current batch.

• void Addbatch (String sql): Adds a statement to the "batch";
• Int[] ExecuteBatch (): Executes all the statements in the batch. The return value represents the row data affected by each statement;
• void Clearbatch (): Empties all statements in the batch.

Note: MySQL is not enabled by default batch processing, need to add parameters: jdbc:mysql://localhost:3306/testdb?rewritebatchedstatements=true

Second, Statement batch processing

1. Create a test table

CREATE TABLE Testbatch (    int  primary key,    name varchar)

2. Execute code

 Packagecn.zy.test;Importjava.sql.Connection;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;Importjava.sql.Statement;Importorg.junit.Test;Importcn.zy.utils.JdbcUtils; Public classBatchhandle {/** Use statement to implement batch processing*/@Test Public voidbatchbystatement () {Connection conn=NULL; Statement St=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); String SQL1= "INSERT into Testbatch (id,name) VALUES (1, ' AAA ')"; String SQL2= "INSERT into Testbatch (Id,name) VALUES (2, ' BBB ')"; String Sql3= "INSERT into Testbatch (Id,name) VALUES (3, ' CCC ')"; St=conn.createstatement (); //add SQL to be executed in bulkSt.addbatch (SQL1);            St.addbatch (SQL2);            St.addbatch (SQL3); //perform batch processingSt.executebatch (); //Clear Batch CommandSt.clearbatch (); } Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, St, RS); }    }    }

Pros and cons of batching with Statement.addbatch (SQL)

Batch processing using Statement.addbatch (SQL):
Pros: You can send multiple different SQL statements to the database.
Disadvantage: SQL statements are not precompiled.
When you send more than one statement to a database, but only a different SQL statement, you need to write a number of SQL statements repeatedly.

Third, the use of PreparedStatement to achieve batch processing

The code is as follows:

 Packagecn.zy.test;Importjava.sql.Connection;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;Importjava.sql.Statement;Importorg.junit.Test;Importcn.zy.utils.JdbcUtils; Public classBatchhandle {/** Use PreparedStatement to implement batch processing*/@Test Public voidbatchbypreparedstatement () {LongStartTime =System.currenttimemillis (); Connection Conn=NULL; PreparedStatement St=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); String SQL= "INSERT into Testbatch (id,name) VALUES (?,?)"; St=conn.preparestatement (SQL);  for(inti=1;i<100000;i++) {St.setint (1, i); St.setstring (2, "dog" +i);            St.addbatch (); if(i%1000==0) {st.executebatch ();            St.clearbatch ();        }} st.executebatch (); } Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, St, RS); }        LongEndtime =System.currenttimemillis (); System.out.println ("Program takes time:" + (Endtime-starttime)/1000 + "seconds!! "); }}

You can see the time required to insert millions data here is:

Pros and cons of batch processing with Preparedstatement.addbatch ()

Using Preparedstatement.addbatch () to implement batch processing
Advantage: The post-compilation SQL statement is sent with high execution efficiency.
Disadvantage: You can only apply in batches that have the same SQL statement but different parameters. So this form of batching is often used to bulk insert data in the same table, or to bulk update the data for a table.

Javaweb Study Summary (11)--JDBC Batch Processing