Implementing batch processing Addbatch,executebatch

L Business Scenario: When you need to send a batch of SQL statements to the database execution, you should avoid sending to the database, but should adopt JDBC batch processing mechanism to improve execution efficiency.
There are two ways to implement batch processing, the first way:
statement.addbatch (SQL)
L Execute Batch SQL statements
ExecuteBatch () Method: Execute Batch Command
Clearbatch () Method: Purge Batch Command

Connection conn = null;

Statement st = null;

ResultSet rs = null;

Try {

conn = Jdbcutil.getconnection ();

String SQL1 = "INSERT into user (Name,password,email,birthday)

VALUES (' KKK ', ' 123 ', ' abc@sina.com ', ' 1978-08-08 ');

String sql2 = "Update user set password= ' 123456 ' where id=3";

st = Conn.createstatement ();

St.addbatch (SQL1); Adding SQL statements to the batch command

St.addbatch (SQL2); Adding SQL statements to the batch command

St.executebatch ();

finally{

Jdbcutil.free (Conn, St, RS);

}

L implement batch processing using Statement.addbatch (SQL): • Advantages: You can send multiple different SQL statements to the database. • Disadvantage: The SQL statement is not precompiled. • When you send multiple statements to the database, but only the SQL statements with different parameters, you need to write a number of SQL statements repeatedly. For example:

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 ');

The second way to implement batch processing: Preparedstatement.addbatch ()

conn = Jdbcutil.getconnection ();

String sql = "INSERT into user (Name,password,email,birthday) VALUES (?,?,?,?)";

st = conn.preparestatement (SQL);

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

St.setstring (1, "AAA" + i);

St.setstring (2, "123" + i);

St.setstring (3, "AAA" + i + "@sina. com");

St.setdate (4,new Date (1980, 10, 10));

St.addbatch ();

if (i%1000==0) {

St.executebatch ();

St.clearbatch ();

}

}

St.executebatch (); l implement batch processing with Preparedstatement.addbatch (): Send the precompiled SQL statement, the execution is high efficiency. • Disadvantages: 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.