Getting Started with JDBC (4)---Batch

1, Statement batch processing

When you have 10 SQL statements to execute, sending one SQL statement to the server at a time is extremely inefficient, and the solution is to use batching, which is to send multiple SQL statements to the service at once, which is then processed by the server at once.

Batches are only for update (redaction) statements, and batches are not related to queries.

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 Excutebatch () method of the statement class to execute the statements in the current batch.

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

2, PreparedStatement batch processing

PreparedStatement batches differ because each PreparedStatement object is bound to an SQL template. So instead of adding SQL statements to PreparedStatement, instead of "? "Assign value.

1 public class Demo5 {2     /* 3     * Pstmt object has a set of 4     * 1, with a loop crazy to add SQL parameters to pstmt, it has a template, 5     * Use a set of parameters and template can match a sq L Statement 6     * 2, call its execution batch method, complete to send to the database. 7         @Test 9 public     void Fun1 () throws Exception {         /*11         * pstmt12         * */13         Connection con = jdbcutils.getconnection ();         String sql = "INSERT into T_user VALUES (?,?)" ;         PreparedStatement pstmt = con.preparestatement (SQL), + for (int i = 0; i < 10000; i++) {pstmt.setint (1,i+1 ) Pstmt.setint (2 }21 long start = system.currenttimemillis (); Pstmt.executebatch ();//execute batch; End = System.currenttimemillis (); System.out.println (end- }26}      

Open Batch Processing

MySQL batch processing also needs to be opened by parameters: Rewritebatchedstatements=true, such as

URL = jdbc:mysql://localhost:3306/mydb1?rewritebatchedstatements=true

Effect:

• Elapsed time before batch start: 140794ms
• Elapsed time after batch processing: 174ms

Getting Started with JDBC (4)---Batch