Multiple UPDATE or INSERT statements can be grouped into a batch, and the entire batch is sent to the database for processing. This process reduces the number of round trips to the database and improves the application performance.
The JDBC driver uses PrepareStatement and Statements on the server as standards for communicating with the database for batch processing and manages the standards by setting the allowMultiQueries and rewriteBatchedStatements parameters. If allowMultiQueries or rewriteBatchedStatements is set to true, the JDBC driver uses only the text protocol. PrepareStatement(parameter replacement) is handled by the driver on the client.
In an INSERT query, rewriteBatchedStatements is used to rewrite batchedStatement for execution in single executeQuery. If you set this parameter set to true, useServerPrepStmts is set to false. When allowMultiQueries is set to true, multi-value queries are allowed.
The JDBC driver provides different batch processing settings in Oracle and MySQL modes. Batch processing in Oracle mode
PrepareStatement
| useServerPrepStmts | rewriteBatchedStatements | allowMultiQueries | INSERT operation | UPDATE operation |
|---|---|---|---|---|
| true | true | true | The prepared SQL string changes to the VALUES(),(),()...format. |
The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
| true | true | false | The prepared SQL string changes to the VALUES(),(),()...format. |
The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
| true | false | true | The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
| true | false | false | The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
| false | true | true | Multiple statements are combined into a complete string in the VALUES(),(),()... format. |
Multiple statements are combined into a complete string in the VALUES(),(),()... format. |
| false | true | false | Multiple statements are combined into a complete string in the VALUES(),(),()... format. |
Multiple UPDATE statements are executed. |
| false | false | true | Multiple INSERT statements are executed. |
Multiple UPDATE statements are executed. |
| false | false | false | Multiple INSERT statements are executed. |
Multiple UPDATE statements are executed. |
Statement
| useServerPrepStmts | rewriteBatchedStatements | allowMultiQueries | INSERT operation | UPDATE operation |
|---|---|---|---|---|
| true | true | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| true | true | false | The SQL statements are executed one by one. | The SQL statements are executed one by one. |
| true | false | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| true | false | false | The INSERT statements are executed one by one. |
The UPDATE statements are executed one by one. |
| false | true | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| false | true | false | The SQL statements are executed one by one. | The SQL statements are executed one by one. |
| false | false | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| false | false | false | The INSERT statements are executed one by one. |
The UPDATE statements are executed one by one. |
Batch processing in MySQL mode
PrepareStatement
| useServerPrepStmts | rewriteBatchedStatements | allowMultiQueries | INSERT operation | UPDATE operation |
|---|---|---|---|---|
| true | true | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL strings prepared one by one are in the normal format, and then parameters are set with multiple EXECUTE operations. |
| true | true | false | The SQL string changes to the VALUES(),(),()... format. |
The SQL strings prepared one by one are in the normal format, and then parameters are set with multiple EXECUTE operations. |
| true | false | true | The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
| true | false | false | The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
The prepared SQL string is in the normal format, and then parameters are set with multiple EXECUTE operations. |
| false | true | true | Multiple statements are combined into a complete string in the VALUES(),(),()... format. |
Multiple statements are combined into a complete string in the VALUES(),(),()... format. |
| false | true | false | Multiple statements are combined into a complete string in the VALUES(),(),()... format. |
Multiple UPDATE statements are executed. |
| false | false | true | Multiple INSERT statements are executed. |
Multiple UPDATE statements are executed. |
| false | false | false | Multiple INSERT statements are executed. |
Multiple UPDATE statements are executed. |
Statement
| useServerPrepStmts | rewriteBatchedStatements | allowMultiQueries | INSERT operation | UPDATE operation |
|---|---|---|---|---|
| true | true | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| true | true | false | The SQL statements are executed one by one. | The SQL statements are executed one by one. |
| true | false | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| true | false | false | The INSERT statements are executed one by one. |
The UPDATE statements are executed one by one. |
| false | true | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| false | true | false | The SQL statements are executed one by one. | The SQL statements are executed one by one. |
| false | false | true | The SQL string changes to the VALUES(),(),()... format. |
The SQL string changes to the VALUES(),(),()... format. |
| false | false | false | The INSERT statements are executed one by one. |
The UPDATE statements are executed one by one. |
Example
public void test_execute_batch() {
Connection conn = null;
try {
for (int q = 0; q < 2; q++) {
for (int w = 0; w < 2; w++) {
for (int e = 0; e < 2; e++) {
/**
* useServerPrepStmts = true / false
* rewriteBatchedStatements = true / false
* allowMultiQueries = true / false
*/
boolean uSPS = q == 0 ? false : true;
String rBS = w == 0 ? "false" : "true";
String aMQ = e == 0 ? "false" : "true";
String other_condition = "&rewriteBatchedStatements=" + rBS
+ "&allowMultiQueries=" + aMQ;
conn = ConnectionUtils.getDefObOracleConnection(uSPS, true, other_condition);
Statement statement = conn.createStatement();
try {
statement.execute("DROP TABLE test_batch_t");
} catch (SQLException exp) {
//e.printStackTrace();
}
String sql = "create table test_batch_t(id int primary key, c1 varchar2(10))";
statement.execute(sql);
PreparedStatement ps = conn
.prepareStatement("insert into test_batch_t(id, c1) values (?, ?)");
for (int j = 0; j < 10; j++) {
ps.setInt(1, j);
ps.setString(2, j + "_test");
ps.addBatch();
}
ps.executeBatch();
ps = conn.prepareStatement("update test_batch_t set id = ? where c1 = ?");
for (int j = 0; j < 10; j++) {
ps.setInt(1, j);
ps.setString(2, j + "_test");
ps.addBatch();
}
ps.executeBatch();
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}