MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mmatthews Date:June 28 2007 8:01pm
Subject:Connector/J commit: r6472 - in branches/branch_5_1/connector-j: . src/com/mysql/jdbc src/testsuite/simple
View as plain text  
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/simplemmatthews28 Jun