Modified:
branches/branch_5_1/connector-j/CHANGES
branches/branch_5_1/connector-j/src/com/mysql/jdbc/CallableStatement.java
branches/branch_5_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java
branches/branch_5_1/connector-j/src/testsuite/simple/CallableStatementTest.java
Log:
Setting the configuration property "rewriteBatchedStatements"
to "true" will now cause the driver to rewrite batched prepared
statements with more than 3 parameter sets in a batch into
multi-statements (separated by ";") if they are not plain
(i.e. without SELECT or ON DUPLICATE KEY UPDATE clauses) INSERT
or REPLACE statements.
Modified: branches/branch_5_1/connector-j/CHANGES
===================================================================
--- branches/branch_5_1/connector-j/CHANGES 2007-06-28 16:18:10 UTC (rev 6471)
+++ branches/branch_5_1/connector-j/CHANGES 2007-06-28 20:01:19 UTC (rev 6472)
@@ -1,5 +1,15 @@
# Changelog
# $Id$
+
+06-29-07 - Version 5.1.2 Beta
+
+ - Setting the configuration property "rewriteBatchedStatements"
+ to "true" will now cause the driver to rewrite batched prepared
+ statements with more than 3 parameter sets in a batch into
+ multi-statements (separated by ";") if they are not plain
+ (i.e. without SELECT or ON DUPLICATE KEY UPDATE clauses) INSERT
+ or REPLACE statements.
+
06-22-07 - Version 5.1.1 Alpha
- Pulled vendor-extension methods of Connection implementation out
Modified: branches/branch_5_1/connector-j/src/com/mysql/jdbc/CallableStatement.java
===================================================================
--- branches/branch_5_1/connector-j/src/com/mysql/jdbc/CallableStatement.java 2007-06-28 16:18:10 UTC (rev 6471)
+++ branches/branch_5_1/connector-j/src/com/mysql/jdbc/CallableStatement.java 2007-06-28 20:01:19 UTC (rev 6472)
@@ -2260,155 +2260,9 @@
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
- if (!this.batchHasPlainStatements
- && this.connection.getRewriteBatchedStatements()
- && this.batchedArgs != null
- && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
- return executeBatchedCalls();
- }
-
return super.executeBatch();
}
-
- private int[] executeBatchedCalls() throws SQLException {
- synchronized (this.connection.getMutex()) {
- // This is kind of an abuse, but it gets the job done
- if (this.batchedValuesClause == null) {
- this.batchedValuesClause = this.originalSql + ";";
- }
-
- ConnectionImpl locallyScopedConn = this.connection;
-
- boolean multiQueriesEnabled = locallyScopedConn.getAllowMultiQueries();
-
- try {
- clearWarnings();
-
- int numBatchedArgs = this.batchedArgs.size();
-
- if (this.retrieveGeneratedKeys) {
- this.batchedGeneratedKeys = new ArrayList(numBatchedArgs);
- }
- int numValuesPerBatch = computeBatchSize(numBatchedArgs);
-
- if (numBatchedArgs < numValuesPerBatch) {
- numValuesPerBatch = numBatchedArgs;
- }
-
- java.sql.PreparedStatement batchedStatement = null;
-
- int batchedParamIndex = 1;
- int updateCountRunningTotal = 0;
- int numberToExecuteAsMultiValue = 0;
- int batchCounter = 0;
-
- try {
- if (!multiQueriesEnabled) {
- locallyScopedConn.getIO().enableMultiQueries();
- }
-
- if (this.retrieveGeneratedKeys) {
- batchedStatement = locallyScopedConn.prepareStatement(
- generateBatchedCallSQL(numValuesPerBatch),
- RETURN_GENERATED_KEYS);
- } else {
- batchedStatement = locallyScopedConn
- .prepareStatement(generateBatchedCallSQL(numValuesPerBatch));
- }
-
- if (numBatchedArgs < numValuesPerBatch) {
- numberToExecuteAsMultiValue = numBatchedArgs;
- } else {
- numberToExecuteAsMultiValue = numBatchedArgs / numValuesPerBatch;
- }
-
- int numberArgsToExecute = numberToExecuteAsMultiValue * numValuesPerBatch;
-
- for (int i = 0; i < numberArgsToExecute; i++) {
- if (i != 0 && i % numValuesPerBatch == 0) {
- updateCountRunningTotal += batchedStatement.executeUpdate();
-
- getBatchedGeneratedKeys(batchedStatement);
- batchedStatement.clearParameters();
- batchedParamIndex = 1;
- }
-
- batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
- batchedParamIndex, this.batchedArgs
- .get(batchCounter++));
- }
-
- updateCountRunningTotal += batchedStatement.executeUpdate();
- getBatchedGeneratedKeys(batchedStatement);
-
- numValuesPerBatch = numBatchedArgs - batchCounter;
- } finally {
- if (batchedStatement != null) {
- batchedStatement.close();
- }
- }
-
- try {
- if (numValuesPerBatch > 0) {
-
- if (this.retrieveGeneratedKeys) {
- batchedStatement = locallyScopedConn.prepareStatement(
- generateBatchedCallSQL(numValuesPerBatch),
- RETURN_GENERATED_KEYS);
- } else {
- batchedStatement = locallyScopedConn.prepareStatement(
- generateBatchedCallSQL(numValuesPerBatch));
- }
-
- batchedParamIndex = 1;
-
- while (batchCounter < numBatchedArgs) {
- batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
- batchedParamIndex, this.batchedArgs
- .get(batchCounter++));
- }
-
- updateCountRunningTotal += batchedStatement.executeUpdate();
- getBatchedGeneratedKeys(batchedStatement);
- }
-
- int[] updateCounts = new int[this.batchedArgs.size()];
-
- for (int i = 0; i < this.batchedArgs.size(); i++) {
- updateCounts[i] = 1;
- }
-
- return updateCounts;
- } finally {
- if (batchedStatement != null) {
- batchedStatement.close();
- }
- }
- } finally {
- if (!multiQueriesEnabled) {
- locallyScopedConn.getIO().disableMultiQueries();
- }
-
- clearBatch();
- }
- }
- }
-
- private String generateBatchedCallSQL(int numBatches) {
- StringBuffer newStatementSql = new StringBuffer((this.originalSql
- .length() + 1) * numBatches);
-
- newStatementSql.append(this.originalSql);
-
- for (int i = 0; i < numBatches - 1; i++) {
- newStatementSql.append(';');
- newStatementSql.append(this.originalSql);
- }
-
- return newStatementSql.toString();
- }
-
protected int getParameterIndexOffset() {
if (this.callingStoredFunction) {
return -1;
Modified: branches/branch_5_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java
===================================================================
--- branches/branch_5_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2007-06-28 16:18:10 UTC (rev 6471)
+++ branches/branch_5_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2007-06-28 20:01:19 UTC (rev 6472)
@@ -1053,6 +1053,13 @@
if (canRewriteAsMultivalueInsertStatement()) {
return executeBatchedInserts();
}
+
+ if (this.connection.versionMeetsMinimum(4, 1, 0)
+ && !this.batchHasPlainStatements
+ && this.batchedArgs != null
+ && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
+ return executePreparedBatchAsMultiStatement();
+ }
}
return executeBatchSerially();
@@ -1064,10 +1071,21 @@
public synchronized boolean canRewriteAsMultivalueInsertStatement() {
if (!this.hasCheckedForRewrite) {
+ // Needs to be INSERT, can't have INSERT ... SELECT or
+ // INSERT ... ON DUPLICATE KEY UPDATE
+ //
+ // We're not smart enough to re-write to
+ //
+ // INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
+ // ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
+ //
+ // (yet)
+
this.canRewrite = StringUtils.startsWithIgnoreCaseAndWs(
- this.originalSql, "INSERT", this.statementAfterCommentsPos)
- || StringUtils.startsWithIgnoreCaseAndWs(this.originalSql,
- "REPLACE", this.statementAfterCommentsPos);
+ this.originalSql, "INSERT", this.statementAfterCommentsPos)
+ && StringUtils.indexOfIgnoreCaseRespectMarker(this.statementAfterCommentsPos, this.originalSql, "SELECT", "\"'`", "\"'`", false) == -1
+ && StringUtils.indexOfIgnoreCaseRespectMarker(this.statementAfterCommentsPos, this.originalSql, "UPDATE", "\"'`", "\"'`", false) == -1;
+
this.hasCheckedForRewrite = true;
}
@@ -1075,6 +1093,155 @@
}
/**
+ * Rewrites the already prepared statement into a multi-statement
+ * query of 'statementsPerBatch' values and executes the entire batch
+ * using this new statement.
+ *
+ * @return update counts in the same fashion as executeBatch()
+ *
+ * @throws SQLException
+ */
+
+ protected int[] executePreparedBatchAsMultiStatement() throws SQLException {
+ synchronized (this.connection.getMutex()) {
+ // This is kind of an abuse, but it gets the job done
+ if (this.batchedValuesClause == null) {
+ this.batchedValuesClause = this.originalSql + ";";
+ }
+
+ ConnectionImpl locallyScopedConn = this.connection;
+
+ boolean multiQueriesEnabled = locallyScopedConn.getAllowMultiQueries();
+
+ try {
+ clearWarnings();
+
+ int numBatchedArgs = this.batchedArgs.size();
+
+ if (this.retrieveGeneratedKeys) {
+ this.batchedGeneratedKeys = new ArrayList(numBatchedArgs);
+ }
+
+ int numValuesPerBatch = computeBatchSize(numBatchedArgs);
+
+ if (numBatchedArgs < numValuesPerBatch) {
+ numValuesPerBatch = numBatchedArgs;
+ }
+
+ java.sql.PreparedStatement batchedStatement = null;
+
+ int batchedParamIndex = 1;
+ int updateCountRunningTotal = 0;
+ int numberToExecuteAsMultiValue = 0;
+ int batchCounter = 0;
+
+ try {
+ if (!multiQueriesEnabled) {
+ locallyScopedConn.getIO().enableMultiQueries();
+ }
+
+ if (this.retrieveGeneratedKeys) {
+ batchedStatement = locallyScopedConn.prepareStatement(
+ generateMultiStatementForBatch(numValuesPerBatch),
+ RETURN_GENERATED_KEYS);
+ } else {
+ batchedStatement = locallyScopedConn
+ .prepareStatement(generateMultiStatementForBatch(numValuesPerBatch));
+ }
+
+ if (numBatchedArgs < numValuesPerBatch) {
+ numberToExecuteAsMultiValue = numBatchedArgs;
+ } else {
+ numberToExecuteAsMultiValue = numBatchedArgs / numValuesPerBatch;
+ }
+
+ int numberArgsToExecute = numberToExecuteAsMultiValue * numValuesPerBatch;
+
+ for (int i = 0; i < numberArgsToExecute; i++) {
+ if (i != 0 && i % numValuesPerBatch == 0) {
+ updateCountRunningTotal += batchedStatement.executeUpdate();
+
+ getBatchedGeneratedKeys(batchedStatement);
+ batchedStatement.clearParameters();
+ batchedParamIndex = 1;
+ }
+
+ batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
+ batchedParamIndex, this.batchedArgs
+ .get(batchCounter++));
+ }
+
+ updateCountRunningTotal += batchedStatement.executeUpdate();
+ getBatchedGeneratedKeys(batchedStatement);
+
+ numValuesPerBatch = numBatchedArgs - batchCounter;
+ } finally {
+ if (batchedStatement != null) {
+ batchedStatement.close();
+ }
+ }
+
+ try {
+ if (numValuesPerBatch > 0) {
+
+ if (this.retrieveGeneratedKeys) {
+ batchedStatement = locallyScopedConn.prepareStatement(
+ generateMultiStatementForBatch(numValuesPerBatch),
+ RETURN_GENERATED_KEYS);
+ } else {
+ batchedStatement = locallyScopedConn.prepareStatement(
+ generateMultiStatementForBatch(numValuesPerBatch));
+ }
+
+ batchedParamIndex = 1;
+
+ while (batchCounter < numBatchedArgs) {
+ batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
+ batchedParamIndex, this.batchedArgs
+ .get(batchCounter++));
+ }
+
+ updateCountRunningTotal += batchedStatement.executeUpdate();
+ getBatchedGeneratedKeys(batchedStatement);
+ }
+
+ int[] updateCounts = new int[this.batchedArgs.size()];
+
+ for (int i = 0; i < this.batchedArgs.size(); i++) {
+ updateCounts[i] = 1;
+ }
+
+ return updateCounts;
+ } finally {
+ if (batchedStatement != null) {
+ batchedStatement.close();
+ }
+ }
+ } finally {
+ if (!multiQueriesEnabled) {
+ locallyScopedConn.getIO().disableMultiQueries();
+ }
+
+ clearBatch();
+ }
+ }
+ }
+
+ private String generateMultiStatementForBatch(int numBatches) {
+ StringBuffer newStatementSql = new StringBuffer((this.originalSql
+ .length() + 1) * numBatches);
+
+ newStatementSql.append(this.originalSql);
+
+ for (int i = 0; i < numBatches - 1; i++) {
+ newStatementSql.append(';');
+ newStatementSql.append(this.originalSql);
+ }
+
+ return newStatementSql.toString();
+ }
+
+ /**
* Rewrites the already prepared statement into a multi-value insert
* statement of 'statementsPerBatch' values and executes the entire batch
* using this new statement.
Modified: branches/branch_5_1/connector-j/src/testsuite/simple/CallableStatementTest.java
===================================================================
--- branches/branch_5_1/connector-j/src/testsuite/simple/CallableStatementTest.java 2007-06-28 16:18:10 UTC (rev 6471)
+++ branches/branch_5_1/connector-j/src/testsuite/simple/CallableStatementTest.java 2007-06-28 20:01:19 UTC (rev 6472)
@@ -25,6 +25,7 @@
package testsuite.simple;
import com.mysql.jdbc.SQLError;
+import com.mysql.jdbc.log.StandardLogger;
import testsuite.BaseTestCase;
@@ -98,55 +99,72 @@
public void testBatch() throws Exception {
if (versionMeetsMinimum(5, 0)) {
- CallableStatement storedProc = null;
-
+ Connection batchedConn = null;
+
try {
- this.stmt
- .executeUpdate("DROP PROCEDURE IF EXISTS testBatch");
createTable("testBatchTable", "(field1 INT)");
-
- this.stmt
- .executeUpdate("create procedure testBatch(IN foo VARCHAR(15))\n"
+ createProcedure("testBatch", "(IN foo VARCHAR(15))\n"
+ "begin\n"
+ "INSERT INTO testBatchTable VALUES (foo);\n"
+ "end\n");
- storedProc = this.conn.prepareCall("{call testBatch(?)}");
-
- int numBatches = 300;
+ executeBatchedStoredProc(this.conn);
- for (int i = 0; i < numBatches; i++) {
- storedProc.setInt(1, i + 1);
- storedProc.addBatch();
- }
+ batchedConn = getConnectionWithProps("rewriteBatchedStatements=true,profileSQL=true");
- int[] counts = storedProc.executeBatch();
+ StringBuffer outBuf = new StringBuffer();
+ StandardLogger.bufferedLog = outBuf;
+ executeBatchedStoredProc(batchedConn);
+ String[] log = outBuf.toString().split(";");
+ assertTrue(log.length > 20);
+ } finally {
+ StandardLogger.bufferedLog = null;
- assertEquals(numBatches, counts.length);
+ closeMemberJDBCResources();
- for (int i = 0; i < numBatches; i++) {
- assertEquals(1, counts[i]);
+ if (batchedConn != null) {
+ batchedConn.close();
}
-
- /*
- this.rs = this.stmt.executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");
-
- for (int i = 0; i < numBatches; i++) {
- assertTrue(this.rs.next());
- assertEquals(i + 1, this.rs.getInt(1));
- }
- */
- } finally {
- if (this.rs != null) {
- this.rs.close();
- this.rs = null;
- }
-
- this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBatch");
}
}
}
+
+ private void executeBatchedStoredProc(Connection c) throws Exception {
+ this.stmt.executeUpdate("TRUNCATE TABLE testBatchTable");
+
+ CallableStatement storedProc = c.prepareCall("{call testBatch(?)}");
+ try {
+ int numBatches = 300;
+
+ for (int i = 0; i < numBatches; i++) {
+ storedProc.setInt(1, i + 1);
+ storedProc.addBatch();
+ }
+
+ int[] counts = storedProc.executeBatch();
+
+ assertEquals(numBatches, counts.length);
+
+ for (int i = 0; i < numBatches; i++) {
+ assertEquals(1, counts[i]);
+ }
+
+ this.rs = this.stmt.executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");
+
+ for (int i = 0; i < numBatches; i++) {
+ assertTrue(this.rs.next());
+ assertEquals(i + 1, this.rs.getInt(1));
+ }
+ } finally {
+ closeMemberJDBCResources();
+
+ if (storedProc != null) {
+ storedProc.close();
+ }
+ }
+ }
+
/**
* Tests functioning of output parameters.
*
| Thread |
|---|
| • Connector/J commit: r6472 - in branches/branch_5_1/connector-j: . src/com/mysql/jdbc src/testsuite/simple | mmatthews | 28 Jun |
