JDBC entry (4) --- batch processing, jdbc entry

JDBC entry (4) --- batch processing, jdbc entry

1. Statement Batch Processing

When you have 10 SQL statements to execute, one SQL statement is sent to the server at a time, which is very inefficient. The solution is to use batch processing, that is, multiple SQL statements are sent to the service at a time and then processed by the server at a time.

Batch Processing is only applicable to update (add, delete, modify) statements. batch processing is irrelevant to queries.

You can call the Statement class addBatch (String SQL) method multiple times, add all the SQL statements to be executed to a batch, and then call the Statement class excuteBatch () method to execute the statement in the current batch ".

• Void addBatch (String SQL): Add a statement to the batch.
• Int [] excuteBatch (): execute all the statements in the batch. The returned value indicates the row data affected by each statement;
• Void clearBatch (): clears all statements in the batch.

2. PreparedStatement Batch Processing

The batch processing of PreparedStatement is different because each PreparedStatement object is bound to an SQL template. Therefore, what is added to the PreparedStatement is not an SQL statement, but "?". Assign values.

1 public class Demo5 {2/* 3 * pstmt object contains a set of 4*1. Add SQL parameters to pstmt in a loop, and it has its own template, 5 * use a set of parameters and the template to match an SQL statement 6*2. Call the execution batch method of the SQL statement to complete sending to the database. 7 **/8 @ Test 9 public void fun1 () throws Exception {10/* 11 * pstmt12 **/13 Connection con = JdbcUtils. getConnection (); 14 String SQL = "INSERT INTO t_user VALUES (?,?) "; 15 PreparedStatement pstmt = con. prepareStatement (SQL); 16 for (int I = 0; I <10000; I ++) {17 pstmt. setInt (1, I + 1); 18 pstmt. setInt (2, I); 19 pstmt. addBatch (); // Add batch, this set of parameters is saved to the Set; 20} 21 long start = System. currentTimeMillis (); 22 pstmt.exe cuteBatch (); // execute batch processing; 23 long end = System. currentTimeMillis (); 24 System. out. println (end-start); 25} 26}

Open Batch Processing

MySQL batch processing also needs to be enabled through parameters: rewriteBatchedStatements = true, as shown in

url = jdbc:mysql://localhost:3306/mydb1?rewriteBatchedStatements=true

Effect:

• Time consumed before enabling batch processing: 140794 ms
• Time consumed after batch processing is Enabled: 174 ms