[Go] using JDBC for batch processing

http://mousepc.iteye.com/blog/1131462

• Business Scenario: When you need to send a batch of SQL statements to the database execution, you should avoid sending execution to the database, and use the JDBC batch mechanism to improve execution efficiency.

One
• There are two ways to implement batch processing, the first way:
statement.addbatch (SQL)
• Execute Batch SQL statements
ExecuteBatch () Method: Execute Batch Command
Clearbatch () Method: Clear 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 ', ' [email protected] ', ' 1978-08-08 ');
String sql2 = "Update user set password= ' 123456 ' where id=3";
st = Conn.createstatement ();
St.addbatch (SQL1); Add SQL statements to the batch command
St.addbatch (SQL2); Add SQL statements to the batch command
St.executebatch ();
} finally{
Jdbcutil.free (Conn, St, RS);
}

• Batch processing with statement.addbatch (SQL): • Pros: You can send multiple different SQL statements to the database.　　• Cons: SQL statements are not precompiled. • When you send multiple statements to the database, but only different SQL statements, 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 ');

Second, 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 ();

• Batch processing with preparedstatement.addbatch () Advantages: A pre-compiled SQL statement is sent, and the execution is highly efficient. • Cons: Only apply in batches with the same SQL statement but with 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.

[Go] using JDBC for batch processing