Statement and PreparedStatement usage in depth batch processing

We can execute the corresponding SQL statement by using statement or PreparedStatement objects, but sometimes when we need to send a batch of SQL statements to the database to avoid sending execution to the database, We should adopt the JDBC batch mechanism to improve execution efficiency.

There are two ways to implement batching, the first way: statement objects

Statement.addbatch (SQL)

For example, we can use batch processing when we are going to send a batch of data to a table named me.

Connection con =null;

Statement st=null;

ResultSet rs=null;

PreparedStatement ps=null;

publicvoid statementbanch () {

con = Dbcmanager.getconnect ();//Get Connection object

Try {

Defining SQL statements

String sql = "Insertinto Me (Id,name,age) VALUES (1, ' Lisi ', 13)";

String SQL1 = "Update me set id =2,name = ' zhangsan ', age =14 Whereid = 1";

To create a statement object

St =con.createstatement ();

Add in Batch

St.addbatch (SQL);

St.addbatch (SQL1);

Execution Batch

St.executebatch ();

Catch (SQLException e) {

TODO Auto-generatedcatch block

E.printstacktrace ();

}

Dbcmanager.release (null, null, con);

}

This completes the insertion of data for a batch of different SQL sentences.

However, there are times when we need to send the same number of statements to the database, but there are a number of SQL statements that need to be written repeatedly when the parameters are different, such as:

Insert into User (Name,password) VALUES (' AA ', ' 111 ');

Insert into User (Name,password) VALUES (' BB ', ' 222 ');

Insert into User (Name,password) VALUES (' cc ', ' 333 ');

Insert into User (Name,password) VALUES (' dd ', ' 444 ');

This greatly reduces the efficiency of the program running, and all SQL statements are not precompiled, which is very bad, so we need to use the second way to the same characteristics of a batch of data insertion work. That is, the use of Preparestatement objects to achieve this whole batch of data entry.

Cases:

publicvoid preparestatementbanch () {

con = Dbcmanager.getconnect ();//Get Connection object

Try {

Defining SQL statements

String sql = INSERT INTO me (id,name,age) values (?,?,?);

To create a statement object

PS =con.preparestatement (SQL);

for (int i=0;i<=10;i++) {

Assign a value to a field

Ps.setint (1, i+1);

Ps.setstring (2, "name" +i);

Ps.setint (3, 10+i);

Add in Batch

Ps.addbatch ();

Set a condition when I to 10 takes the remainder of 0 o'clock, executes the batch statement first, and then clears the batch

if (i%10==0) {

Ps.executebatch ();

Ps.clearbatch ();

}

}

Complete the remaining statements in the batch

Ps.executebatch ();

Catch (SQLException e) {

TODO Auto-generatedcatch block

E.printstacktrace ();

}

Dbcmanager.release (null, PS, con);

}

In this way, by using Preparedstatement.addbatch () To implement batch processing, sent out through the precompiled SQL statements, the execution is more efficient. This, of course, has its drawbacks: it can only be applied in batches with the same SQL statement but with different parameters. Therefore, this form of batch processing is often used to bulk insert data in the same table, or to bulk Update table data.

This way we can then implement the insertion of different types and quantities of data in several different ways.