Modified:
branches/branch_3_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
branches/branch_3_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java
branches/branch_3_1/connector-j/src/com/mysql/jdbc/Statement.java
branches/branch_3_1/connector-j/src/testsuite/simple/StatementsTest.java
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java
branches/branch_5_0/connector-j/src/com/mysql/jdbc/Statement.java
branches/branch_5_0/connector-j/src/testsuite/simple/StatementsTest.java
branches/branch_5_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
branches/branch_5_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java
branches/branch_5_1/connector-j/src/com/mysql/jdbc/Statement.java
branches/branch_5_1/connector-j/src/testsuite/simple/StatementsTest.java
Log:
Added performance feature, re-writing of batched executes for
Statement.executeBatch() (for all DML statements) and
PreparedStatement.executeBatch() (for INSERTs with VALUE clauses
only). Enable by using "rewriteBatchedStatements=true" in your JDBC URL.
Modified: branches/branch_3_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
===================================================================
---
branches/branch_3_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-03-09
20:53:06 UTC (rev 5033)
+++
branches/branch_3_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -1091,7 +1091,21 @@
"Should the driver retain the Statement reference in a ResultSet after
ResultSet.close()"
+ " has been called. This is not JDBC-compliant after JDBC-4.0.",
"3.1.11", MISC_CATEGORY, Integer.MIN_VALUE);
-
+
+ private BooleanConnectionProperty rewriteBatchedStatements = new
BooleanConnectionProperty(
+ "rewriteBatchedStatements",
+ false,
+ "Should the driver use multiqueries (irregardless of the setting of
\"allowMultiQueries\") as well as "
+ + "rewriting of prepared statements for INSERT into multi-value inserts when
executeBatch() is called? Notice that this has the potential "
+ + "for SQL injection if using plain java.sql.Statements and your code doesn't sanitize
input correctly.\n\n"
+ + "Notice that for prepared statements, server-side prepared statements can not
currently take advantage of "
+ + "this rewrite option, and that if you don't specify stream lengths when using
PreparedStatement.set*Stream(),"
+ + "the driver won't be able to determine the optimium number of parameters per batch
and you might receive an"
+ + "an error from the driver that the resultant packet is too large.\n\n"
+ + "Statement.getGeneratedKeys() for these rewritten statements only works when the
entire "
+ + "batch includes INSERT statements.",
+ "3.1.13", PERFORMANCE_CATEGORY, Integer.MIN_VALUE);
+
private BooleanConnectionProperty rollbackOnPooledClose = new BooleanConnectionProperty(
"rollbackOnPooledClose",
true,
@@ -1229,7 +1243,8 @@
"Should the driver refer to the internal values of autocommit and transaction
isolation that are set "
+ " by Connection.setAutoCommit() and Connection.setTransactionIsolation(), rather
than querying the database?",
"3.1.7", PERFORMANCE_CATEGORY, Integer.MIN_VALUE);
-
+
+
private BooleanConnectionProperty useNewIo = new BooleanConnectionProperty(
"useNewIO",
false,
@@ -3503,4 +3518,12 @@
protected void setDumpMetadataOnColumnNotFound(boolean flag) {
this.dumpMetadataOnColumnNotFound.setValue(flag);
}
+
+ protected boolean getRewriteBatchedStatements() {
+ return this.rewriteBatchedStatements.getValueAsBoolean();
+ }
+
+ protected void setRewriteBatchedStatements(boolean flag) {
+ this.rewriteBatchedStatements.setValue(flag);
+ }
}
Modified: branches/branch_3_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java
===================================================================
--- branches/branch_3_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-03-09
20:53:06 UTC (rev 5033)
+++ branches/branch_3_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -272,7 +272,7 @@
if (asCharArray == null) {
asCharArray = sql.toCharArray();
}
-
+
String temp = new String(asCharArray, begin, len);
this.staticSql[i] = temp.getBytes();
} else if (encoding == null) {
@@ -293,7 +293,7 @@
if (asCharArray == null) {
asCharArray = sql.toCharArray();
}
-
+
String temp = new String(asCharArray, begin, len);
this.staticSql[i] = StringUtils.getBytes(temp,
@@ -344,7 +344,13 @@
return numCharsRead;
}
- protected ArrayList batchedGeneratedKeys = null;
+ /**
+ * Does the batch (if any) contain "plain" statements added by
+ * Statement.addBatch(String)?
+ *
+ * If so, we can't re-write it to use multi-value or multi-queries.
+ */
+ protected boolean batchHasPlainStatements = false;
private java.sql.DatabaseMetaData dbmd = null;
@@ -382,8 +388,6 @@
private java.sql.ResultSetMetaData pstmtResultMetaData;
- protected boolean retrieveGeneratedKeys = false;
-
private byte[][] staticSqlStrings = null;
private byte[] streamConvertBuf = new byte[4096];
@@ -399,6 +403,8 @@
private boolean usingAnsiMode;
+ private String batchedValuesClause;
+
/**
* Constructor used by server-side prepared statements
*
@@ -504,6 +510,12 @@
this.isNull));
}
+ public synchronized void addBatch(String sql) throws SQLException {
+ this.batchHasPlainStatements = true;
+
+ super.addBatch(sql);
+ }
+
protected String asSql() throws SQLException {
return asSql(false);
}
@@ -575,6 +587,12 @@
return buf.toString();
}
+ public synchronized void clearBatch() throws SQLException {
+ this.batchHasPlainStatements = false;
+
+ super.clearBatch();
+ }
+
/**
* In general, parameter values remain in force for repeated used of a
* Statement. Setting a parameter value automatically clears its previous
@@ -741,7 +759,8 @@
rowLimit = this.maxRows;
} else {
if (this.maxRows <= 0) {
- this.connection.execSQL(this,
+ this.connection.execSQL(
+ this,
"SET OPTION SQL_SELECT_LIMIT=DEFAULT", -1, //$NON-NLS-1$
null, java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY, false,
@@ -815,96 +834,267 @@
+ Messages.getString("PreparedStatement.26"), //$NON-NLS-1$
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
-
+
synchronized (this.connection.getMutex()) {
try {
clearWarnings();
-
- int[] updateCounts = null;
-
- if (this.batchedArgs != null) {
- int nbrCommands = this.batchedArgs.size();
- updateCounts = new int[nbrCommands];
-
- for (int i = 0; i < nbrCommands; i++) {
- updateCounts[i] = -3;
+
+ if (!this.batchHasPlainStatements
+ && this.connection.getRewriteBatchedStatements()) {
+ if (StringUtils.startsWithIgnoreCaseAndWs(this.originalSql,
+ "INSERT")) {
+ return executeBatchedInserts();
}
-
- SQLException sqlEx = null;
-
- int commandIndex = 0;
-
- if (this.retrieveGeneratedKeys) {
- this.batchedGeneratedKeys = new ArrayList(nbrCommands);
+ }
+
+ return executeBatchSerially();
+ } finally {
+ clearBatch();
+ }
+ }
+ }
+
+ /**
+ * Rewrites the already prepared statement into a multi-value insert
+ * statement of 'statementsPerBatch' values and executes the entire batch
+ * using this new statement.
+ *
+ * @return update counts in the same fashion as executeBatch()
+ *
+ * @throws SQLException
+ */
+ private int[] executeBatchedInserts() throws SQLException {
+ String valuesClause = extractValuesClause();
+
+ if (valuesClause == null) {
+ return executeBatchSerially();
+ }
+
+ 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;
+
+ if (this.retrieveGeneratedKeys) {
+ batchedStatement = this.connection.prepareStatement(
+ generateBatchedInsertSQL(valuesClause, numValuesPerBatch),
+ RETURN_GENERATED_KEYS);
+ } else {
+ batchedStatement = this.connection
+ .prepareStatement(generateBatchedInsertSQL(valuesClause,
+ numValuesPerBatch));
+ }
+
+ int batchedParamIndex = 1;
+ int updateCountRunningTotal = 0;
+ int numberToExecuteAsMultiValue = 0;
+ int batchCounter = 0;
+
+ 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;
+
+ }
+
+ BatchParams paramArg = (BatchParams) this.batchedArgs
+ .get(batchCounter++);
+
+ batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
+ batchedParamIndex, paramArg);
+ }
+
+ updateCountRunningTotal += batchedStatement.executeUpdate();
+ getBatchedGeneratedKeys(batchedStatement);
+
+ numValuesPerBatch = numBatchedArgs - batchCounter;
+
+ if (numValuesPerBatch > 0) {
+
+ batchedStatement = this.connection.prepareStatement(
+ generateBatchedInsertSQL(valuesClause, numValuesPerBatch),
+ RETURN_GENERATED_KEYS);
+ batchedParamIndex = 1;
+
+ while (batchCounter < numBatchedArgs) {
+
+ BatchParams paramArg = (BatchParams) this.batchedArgs
+ .get(batchCounter++);
+ batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
+ batchedParamIndex, paramArg);
+ }
+
+ 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;
+ }
+
+ protected int computeBatchSize(int numBatchedArgs) {
+ long sizeOfEntireBatch = 0;
+ long maxSizeOfParameterSet = 0;
+
+ for (int i = 0; i < numBatchedArgs; i++) {
+ BatchParams paramArg = (BatchParams) this.batchedArgs
+ .get(i);
+
+ boolean[] isNullBatch = paramArg.isNull;
+ boolean[] isStreamBatch = paramArg.isStream;
+
+ long sizeOfParameterSet = 0;
+
+ for (int j = 0; j < isNullBatch.length; j++) {
+ if (!isNullBatch[j]) {
+
+ if (isStreamBatch[j]) {
+ int streamLength = paramArg.streamLengths[j];
+
+ if (streamLength != -1) {
+ sizeOfParameterSet += streamLength * 2; // for safety in escaping
+ } else {
+ int paramLength = paramArg.parameterStrings[j].length;
+ sizeOfParameterSet += paramLength;
+ }
+ } else {
+ sizeOfParameterSet += 4; // for NULL literal in SQL
}
-
- for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
- Object arg = this.batchedArgs.get(commandIndex);
-
- if (arg instanceof String) {
- updateCounts[commandIndex] = executeUpdate((String) arg);
- } else {
- BatchParams paramArg = (BatchParams) arg;
-
+ }
+ }
+
+ //
+ // Account for static part of values clause
+ // This is a little naiive, because the ?s will be replaced
+ // but it gives us some padding, and is less housekeeping
+ // to ignore them. We're looking for a "fuzzy" value here
+ // anyway
+ //
+
+ sizeOfParameterSet += this.batchedValuesClause.length() + 1;
+ sizeOfEntireBatch += sizeOfParameterSet;
+
+ if (sizeOfParameterSet > maxSizeOfParameterSet) {
+ maxSizeOfParameterSet = sizeOfParameterSet;
+ }
+ }
+
+ int maxAllowedPacket = this.connection.getMaxAllowedPacket();
+
+ if (sizeOfEntireBatch < maxAllowedPacket - this.originalSql.length()) {
+ return numBatchedArgs;
+ }
+
+ return (int)Math.max(1, (maxAllowedPacket - this.originalSql.length()) /
maxSizeOfParameterSet);
+ }
+
+ /**
+ * Executes the current batch of statements by executing them one-by-one.
+ *
+ * @return a list of update counts
+ * @throws SQLException
+ * if an error occurs
+ */
+ protected int[] executeBatchSerially() throws SQLException {
+
+ int[] updateCounts = null;
+
+ if (this.batchedArgs != null) {
+ int nbrCommands = this.batchedArgs.size();
+ updateCounts = new int[nbrCommands];
+
+ for (int i = 0; i < nbrCommands; i++) {
+ updateCounts[i] = -3;
+ }
+
+ SQLException sqlEx = null;
+
+ int commandIndex = 0;
+
+ if (this.retrieveGeneratedKeys) {
+ this.batchedGeneratedKeys = new ArrayList(nbrCommands);
+ }
+
+ for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
+ Object arg = this.batchedArgs.get(commandIndex);
+
+ if (arg instanceof String) {
+ updateCounts[commandIndex] = executeUpdate((String) arg);
+ } else {
+ BatchParams paramArg = (BatchParams) arg;
+
+ try {
+ updateCounts[commandIndex] = executeUpdate(
+ paramArg.parameterStrings,
+ paramArg.parameterStreams, paramArg.isStream,
+ paramArg.streamLengths, paramArg.isNull, true);
+
+ if (this.retrieveGeneratedKeys) {
+ java.sql.ResultSet rs = null;
+
try {
- updateCounts[commandIndex] = executeUpdate(
- paramArg.parameterStrings,
- paramArg.parameterStreams,
- paramArg.isStream, paramArg.streamLengths,
- paramArg.isNull,
- true);
-
- if (this.retrieveGeneratedKeys) {
- java.sql.ResultSet rs = null;
-
- try {
- // we don't want to use our version,
- // because we've altered the behavior of
- // ours to support batch updates
- // (catch-22)
- rs = super.getGeneratedKeys();
-
- while (rs.next()) {
- this.batchedGeneratedKeys
- .add(new byte[][] { rs
- .getBytes(1) });
- }
- } finally {
- if (rs != null) {
- rs.close();
- }
- }
+ rs = getGeneratedKeysInternal();
+
+ while (rs.next()) {
+ this.batchedGeneratedKeys
+ .add(new byte[][] { rs.getBytes(1) });
}
- } catch (SQLException ex) {
- updateCounts[commandIndex] = EXECUTE_FAILED;
-
- if (this.connection.getContinueBatchOnError()) {
- sqlEx = ex;
- } else {
- int[] newUpdateCounts = new int[commandIndex];
- System.arraycopy(updateCounts, 0,
- newUpdateCounts, 0, commandIndex);
-
- throw new java.sql.BatchUpdateException(ex
- .getMessage(), ex.getSQLState(), ex
- .getErrorCode(), newUpdateCounts);
+ } finally {
+ if (rs != null) {
+ rs.close();
}
}
}
+ } catch (SQLException ex) {
+ updateCounts[commandIndex] = EXECUTE_FAILED;
+
+ if (this.connection.getContinueBatchOnError()) {
+ sqlEx = ex;
+ } else {
+ int[] newUpdateCounts = new int[commandIndex];
+ System.arraycopy(updateCounts, 0, newUpdateCounts,
+ 0, commandIndex);
+
+ throw new java.sql.BatchUpdateException(ex
+ .getMessage(), ex.getSQLState(), ex
+ .getErrorCode(), newUpdateCounts);
+ }
}
-
- if (sqlEx != null) {
- throw new java.sql.BatchUpdateException(sqlEx.getMessage(),
- sqlEx.getSQLState(), sqlEx.getErrorCode(),
- updateCounts);
- }
}
-
- return (updateCounts != null) ? updateCounts : new int[0];
- } finally {
- clearBatch();
}
+
+ if (sqlEx != null) {
+ throw new java.sql.BatchUpdateException(sqlEx.getMessage(),
+ sqlEx.getSQLState(), sqlEx.getErrorCode(), updateCounts);
+ }
}
+
+ return (updateCounts != null) ? updateCounts : new int[0];
}
/**
@@ -1105,9 +1295,8 @@
*/
protected synchronized int executeUpdate(byte[][] batchedParameterStrings,
InputStream[] batchedParameterStreams, boolean[] batchedIsStream,
- int[] batchedStreamLengths, boolean[] batchedIsNull,
- boolean isReallyBatch)
- throws SQLException {
+ int[] batchedStreamLengths, boolean[] batchedIsNull,
+ boolean isReallyBatch) throws SQLException {
if (this.connection.isReadOnly()) {
throw new SQLException(Messages.getString("PreparedStatement.34") //$NON-NLS-1$
+ Messages.getString("PreparedStatement.35"), //$NON-NLS-1$
@@ -1164,7 +1353,8 @@
this.connection.setReadInfoMsgEnabled(true);
}
- rs = executeInternal(-1, sendPacket, false, false, true, isReallyBatch);
+ rs = executeInternal(-1, sendPacket, false, false, true,
+ isReallyBatch);
if (this.retrieveGeneratedKeys) {
this.connection.setReadInfoMsgEnabled(oldInfoMsgState);
@@ -1193,6 +1383,45 @@
return truncatedUpdateCount;
}
+ private synchronized String extractValuesClause() throws SQLException {
+ if (this.batchedValuesClause == null) {
+ String quoteCharStr = this.connection.getMetaData()
+ .getIdentifierQuoteString();
+
+ int indexOfValues = -1;
+
+ if (quoteCharStr.length() > 0) {
+ indexOfValues = StringUtils.indexOfIgnoreCaseRespectQuotes(0,
+ this.originalSql, "VALUES ", quoteCharStr.charAt(0), false);
+ } else {
+ indexOfValues = StringUtils.indexOfIgnoreCase(0, this.originalSql,
+ "VALUES ");
+ }
+
+ if (indexOfValues == -1) {
+ return null;
+ }
+
+ int indexOfFirstParen = this.originalSql
+ .indexOf('(', indexOfValues + 7);
+
+ if (indexOfFirstParen == -1) {
+ return null;
+ }
+
+ int indexOfLastParen = this.originalSql.lastIndexOf(')');
+
+ if (indexOfLastParen == -1) {
+ return null;
+ }
+
+ this.batchedValuesClause = this.originalSql.substring(indexOfFirstParen,
+ indexOfLastParen + 1);
+ }
+
+ return this.batchedValuesClause;
+ }
+
/**
* Creates the packet that contains the query to be sent to the server.
*
@@ -1276,6 +1505,21 @@
return sendPacket;
}
+ private String generateBatchedInsertSQL(String valuesClause, int numBatches) {
+ StringBuffer newStatementSql = new StringBuffer(this.originalSql
+ .length()
+ + (numBatches * (valuesClause.length() + 1)));
+
+ newStatementSql.append(this.originalSql, 0, this.originalSql.length());
+
+ for (int i = 0; i < numBatches - 1; i++) {
+ newStatementSql.append(',');
+ newStatementSql.append(valuesClause);
+ }
+
+ return newStatementSql.toString();
+ }
+
/**
* DOCUMENT ME!
*
@@ -1313,8 +1557,6 @@
return parameterVal;
}
- // --------------------------JDBC 2.0-----------------------------
-
private final String getDateTimePattern(String dt, boolean toTime)
throws Exception {
//
@@ -1480,26 +1722,6 @@
return format.toString();
}
- /*
- * (non-Javadoc)
- *
- * @see java.sql.Statement#getGeneratedKeys()
- */
- public synchronized java.sql.ResultSet getGeneratedKeys()
- throws SQLException {
- if (this.batchedGeneratedKeys == null) {
- return super.getGeneratedKeys();
- }
-
- Field[] fields = new Field[1];
- fields[0] = new Field("", "GENERATED_KEY", Types.BIGINT, 17); //$NON-NLS-1$
//$NON-NLS-2$
- fields[0].setConnection(this.connection);
-
- return new com.mysql.jdbc.ResultSet(this.currentCatalog, fields,
- new RowDataStatic(this.batchedGeneratedKeys), this.connection,
- this);
- }
-
/**
* The number, types and properties of a ResultSet's columns are provided by
* the getMetaData method.
@@ -1578,13 +1800,13 @@
/**
* @see PreparedStatement#getParameterMetaData()
*/
- public synchronized ParameterMetaData getParameterMetaData()
- throws SQLException {
+ public synchronized ParameterMetaData getParameterMetaData()
+ throws SQLException {
if (this.parameterMetaData == null) {
- this.parameterMetaData = new MysqlParameterMetadata(
- null, this.parameterCount);
+ this.parameterMetaData = new MysqlParameterMetadata(null,
+ this.parameterCount);
}
-
+
return this.parameterMetaData;
}
@@ -2336,13 +2558,14 @@
setNull(parameterIndex, sqlType);
}
- private void setNumericObject(int parameterIndex, Object parameterObj, int
targetSqlType, int scale) throws SQLException {
+ private void setNumericObject(int parameterIndex, Object parameterObj,
+ int targetSqlType, int scale) throws SQLException {
Number parameterAsNum;
if (parameterObj instanceof Boolean) {
- parameterAsNum = ((Boolean) parameterObj)
- .booleanValue() ? new Integer(1) : new Integer(
- 0);
+ parameterAsNum = ((Boolean) parameterObj).booleanValue() ? new Integer(
+ 1)
+ : new Integer(0);
} else if (parameterObj instanceof String) {
switch (targetSqlType) {
case Types.BIT:
@@ -2357,35 +2580,30 @@
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
- parameterAsNum = Integer
- .valueOf((String) parameterObj);
+ parameterAsNum = Integer.valueOf((String) parameterObj);
break;
case Types.BIGINT:
- parameterAsNum = Long
- .valueOf((String) parameterObj);
+ parameterAsNum = Long.valueOf((String) parameterObj);
break;
case Types.REAL:
- parameterAsNum = Float
- .valueOf((String) parameterObj);
+ parameterAsNum = Float.valueOf((String) parameterObj);
break;
case Types.FLOAT:
case Types.DOUBLE:
- parameterAsNum = Double
- .valueOf((String) parameterObj);
+ parameterAsNum = Double.valueOf((String) parameterObj);
break;
case Types.DECIMAL:
case Types.NUMERIC:
default:
- parameterAsNum = new java.math.BigDecimal(
- (String) parameterObj);
+ parameterAsNum = new java.math.BigDecimal((String) parameterObj);
}
} else {
parameterAsNum = (Number) parameterObj;
@@ -2428,29 +2646,21 @@
} catch (ArithmeticException ex) {
try {
scaledBigDecimal = ((java.math.BigDecimal) parameterAsNum)
- .setScale(scale,
- BigDecimal.ROUND_HALF_UP);
+ .setScale(scale, BigDecimal.ROUND_HALF_UP);
} catch (ArithmeticException arEx) {
- throw new SQLException(
- "Can't set scale of '"
- + scale
- + "' for DECIMAL argument '"
- + parameterAsNum + "'",
- SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
+ throw new SQLException("Can't set scale of '" + scale
+ + "' for DECIMAL argument '" + parameterAsNum
+ + "'", SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
}
setBigDecimal(parameterIndex, scaledBigDecimal);
} else if (parameterAsNum instanceof java.math.BigInteger) {
- setBigDecimal(
- parameterIndex,
- new java.math.BigDecimal(
- (java.math.BigInteger) parameterAsNum,
- scale));
+ setBigDecimal(parameterIndex, new java.math.BigDecimal(
+ (java.math.BigInteger) parameterAsNum, scale));
} else {
- setBigDecimal(parameterIndex,
- new java.math.BigDecimal(parameterAsNum
- .doubleValue()));
+ setBigDecimal(parameterIndex, new java.math.BigDecimal(
+ parameterAsNum.doubleValue()));
}
break;
@@ -2573,44 +2783,39 @@
switch (targetSqlType) {
case Types.BOOLEAN:
/*
- From Table-B5 in the JDBC-3.0 Spec
-
- T S I B R F D D N B B C V L
- I M N I E L O E U I O H A O
- N A T G A O U C M T O A R N
- Y L E I L A B I E L R C G
- I L G N T L M R E H V
- N I E T E A I A A A
- T N R L C N R R
- T C
- H
- A
- R
- -----------------------------------
- Boolean x x x x x x x x x x x x x x
- */
-
+ * From Table-B5 in the JDBC-3.0 Spec
+ *
+ * T S I B R F D D N B B C V L I M N I E L O E U I O H A O N
+ * A T G A O U C M T O A R N Y L E I L A B I E L R C G I L G
+ * N T L M R E H V N I E T E A I A A A T N R L C N R R T C H
+ * A R ----------------------------------- Boolean x x x x x
+ * x x x x x x x x x
+ */
+
if (parameterObj instanceof Boolean) {
- setBoolean(parameterIndex, ((Boolean)parameterObj).booleanValue());
-
+ setBoolean(parameterIndex, ((Boolean) parameterObj)
+ .booleanValue());
+
break;
} else if (parameterObj instanceof String) {
- setBoolean(parameterIndex, "true".equalsIgnoreCase((String)parameterObj) ||
- !"0".equalsIgnoreCase((String)parameterObj));
-
+ setBoolean(parameterIndex, "true"
+ .equalsIgnoreCase((String) parameterObj)
+ || !"0".equalsIgnoreCase((String) parameterObj));
+
break;
} else if (parameterObj instanceof Number) {
- int intValue = ((Number)parameterObj).intValue();
-
+ int intValue = ((Number) parameterObj).intValue();
+
setBoolean(parameterIndex, intValue != 0);
-
+
break;
} else {
- throw new SQLException("No conversion from " + parameterObj.getClass().getName() +
- " to Types.BOOLEAN possible.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
+ throw new SQLException("No conversion from "
+ + parameterObj.getClass().getName()
+ + " to Types.BOOLEAN possible.",
+ SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
-
-
+
case Types.BIT:
case Types.TINYINT:
case Types.SMALLINT:
@@ -2622,7 +2827,8 @@
case Types.DECIMAL:
case Types.NUMERIC:
- setNumericObject(parameterIndex, parameterObj, targetSqlType, scale);
+ setNumericObject(parameterIndex, parameterObj,
+ targetSqlType, scale);
break;
@@ -2678,7 +2884,8 @@
if (parameterObj instanceof String) {
ParsePosition pp = new ParsePosition(0);
java.text.DateFormat sdf = new java.text.SimpleDateFormat(
- getDateTimePattern((String) parameterObj, false), Locale.US);
+ getDateTimePattern((String) parameterObj, false),
+ Locale.US);
parameterAsDate = sdf.parse((String) parameterObj, pp);
} else {
parameterAsDate = (java.util.Date) parameterObj;
@@ -2717,7 +2924,8 @@
if (parameterObj instanceof String) {
java.text.DateFormat sdf = new java.text.SimpleDateFormat(
- getDateTimePattern((String) parameterObj, true), Locale.US);
+ getDateTimePattern((String) parameterObj, true),
+ Locale.US);
setTime(parameterIndex, new java.sql.Time(sdf.parse(
(String) parameterObj).getTime()));
} else if (parameterObj instanceof Timestamp) {
@@ -2755,6 +2963,31 @@
}
}
+ private int setOneBatchedParameterSet(
+ java.sql.PreparedStatement batchedStatement, int batchedParamIndex,
+ BatchParams paramArg) throws SQLException {
+ boolean[] isNullBatch = paramArg.isNull;
+ boolean[] isStreamBatch = paramArg.isStream;
+
+ for (int j = 0; j < isNullBatch.length; j++) {
+ if (isNullBatch[j]) {
+ batchedStatement.setNull(batchedParamIndex++, Types.NULL);
+ } else {
+ if (isStreamBatch[j]) {
+ batchedStatement.setBinaryStream(batchedParamIndex++,
+ paramArg.parameterStreams[j],
+ paramArg.streamLengths[j]);
+ } else {
+ ((com.mysql.jdbc.PreparedStatement) batchedStatement)
+ .setBytesNoEscapeNoQuotes(batchedParamIndex++,
+ paramArg.parameterStrings[j]);
+ }
+ }
+ }
+
+ return batchedParamIndex;
+ }
+
/**
* JDBC 2.0 Set a REF(<structured-type>) parameter.
*
@@ -3009,13 +3242,8 @@
if (x == null) {
setNull(parameterIndex, java.sql.Types.TIME);
} else {
- x = TimeUtil.changeTimezone(this.connection,
- x,
- tz,
- this.connection.getServerTimezoneTZ(),
- rollForward);
-
-
+ x = TimeUtil.changeTimezone(this.connection, x, tz, this.connection
+ .getServerTimezoneTZ(), rollForward);
setInternal(parameterIndex, "'" + x.toString() + "'"); //$NON-NLS-1$ //$NON-NLS-2$
}
@@ -3081,7 +3309,8 @@
.getServerTimezoneTZ(), rollForward);
if (this.tsdf == null) {
- this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$
+ this.tsdf = new SimpleDateFormat(
+ "''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$
}
timestampString = this.tsdf.format(x);
@@ -3273,7 +3502,7 @@
} catch (IOException ioEx) {
;
}
-
+
in = null;
}
}
Modified: branches/branch_3_1/connector-j/src/com/mysql/jdbc/Statement.java
===================================================================
--- branches/branch_3_1/connector-j/src/com/mysql/jdbc/Statement.java 2006-03-09 20:53:06
UTC (rev 5033)
+++ branches/branch_3_1/connector-j/src/com/mysql/jdbc/Statement.java 2006-03-09 20:56:13
UTC (rev 5034)
@@ -165,6 +165,10 @@
/** The warnings chain. */
protected SQLWarning warningChain = null;
+ protected ArrayList batchedGeneratedKeys = null;
+
+ protected boolean retrieveGeneratedKeys = false;
+
/**
* Constructor for a Statement.
*
@@ -469,6 +473,8 @@
// generated from the current Connection (saves
// a query, and network traffic).
synchronized (this.connection.getMutex()) {
+ this.batchedGeneratedKeys = null;
+
String oldCatalog = null;
if (!this.connection.getCatalog().equals(this.currentCatalog)) {
@@ -670,10 +676,24 @@
synchronized (this.connection.getMutex()) {
try {
+ this.retrieveGeneratedKeys = true;
+
int[] updateCounts = null;
if (this.batchedArgs != null) {
+
int nbrCommands = this.batchedArgs.size();
+
+ this.batchedGeneratedKeys = new ArrayList(this.batchedArgs.size());
+
+ boolean multiQueriesEnabled = this.connection.getAllowMultiQueries();
+
+ if (multiQueriesEnabled ||
+ (this.connection.getRewriteBatchedStatements() &&
+ nbrCommands > 4)) {
+ return executeBatchUsingMultiQueries(multiQueriesEnabled, nbrCommands);
+ }
+
updateCounts = new int[nbrCommands];
for (int i = 0; i < nbrCommands; i++) {
@@ -688,6 +708,7 @@
try {
updateCounts[commandIndex] = executeUpdate((String) this.batchedArgs
.get(commandIndex), true);
+ getBatchedGeneratedKeys();
} catch (SQLException ex) {
updateCounts[commandIndex] = EXECUTE_FAILED;
@@ -714,12 +735,120 @@
return (updateCounts != null) ? updateCounts : new int[0];
} finally {
+ this.retrieveGeneratedKeys = false;
+
clearBatch();
}
}
}
/**
+ * Rewrites batch into a single query to send to the server. This method
+ * will constrain each batch to be shorter than max_allowed_packet on the
+ * server.
+ *
+ * @return update counts in the same manner as executeBatch()
+ * @throws SQLException
+ */
+ private int[] executeBatchUsingMultiQueries(boolean multiQueriesEnabled,
+ int nbrCommands) throws SQLException {
+
+ if (!multiQueriesEnabled) {
+ this.connection.getIO().enableMultiQueries();
+ }
+
+ try {
+ int[] updateCounts = new int[nbrCommands];
+
+ for (int i = 0; i < nbrCommands; i++) {
+ updateCounts[i] = -3;
+ }
+
+ int commandIndex = 0;
+
+ StringBuffer queryBuf = new StringBuffer();
+
+ java.sql.Statement batchStmt = this.connection.createStatement();
+
+ int counter = 0;
+
+ int numberOfBytesPerChar = 1;
+
+ String connectionEncoding = this.connection.getEncoding();
+
+ if (StringUtils.startsWithIgnoreCase(connectionEncoding, "utf")) {
+ numberOfBytesPerChar = 3;
+ } else if (CharsetMapping.isMultibyteCharset(connectionEncoding)) {
+ numberOfBytesPerChar = 2;
+ }
+
+ int escapeAdjust = 1;
+
+ if (this.doEscapeProcessing) {
+ escapeAdjust = 2; /* We assume packet _could_ grow by this amount, as we're not
+ sure how big statement will end up after
+ escape processing */
+ }
+
+ for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
+ String nextQuery = (String) this.batchedArgs.get(commandIndex);
+
+ if (((((queryBuf.length() + nextQuery.length())
+ * numberOfBytesPerChar) + 1 /* for semicolon */
+ + MysqlIO.HEADER_LENGTH) * escapeAdjust) + 32 > this.connection
+ .getMaxAllowedPacket()) {
+ batchStmt.execute(queryBuf.toString());
+
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+ long generatedKeyStart = ((com.mysql.jdbc.Statement)batchStmt).getLastInsertID();
+ byte[][] row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+
+ while (batchStmt.getMoreResults()
+ || batchStmt.getUpdateCount() != -1) {
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+ row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+ }
+
+ queryBuf = new StringBuffer();
+ }
+
+ queryBuf.append(nextQuery);
+ queryBuf.append(";");
+ }
+
+ if (queryBuf.length() > 0) {
+ batchStmt.execute(queryBuf.toString());
+
+ long generatedKeyStart = ((com.mysql.jdbc.Statement)batchStmt).getLastInsertID();
+ byte[][] row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+
+
+ while (batchStmt.getMoreResults()
+ || batchStmt.getUpdateCount() != -1) {
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+ row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+ }
+ }
+
+ return (updateCounts != null) ? updateCounts : new int[0];
+ } finally {
+ if (!multiQueriesEnabled) {
+ this.connection.getIO().disableMultiQueries();
+ }
+ }
+ }
+
+ /**
* Execute a SQL statement that retruns a single ResultSet
*
* @param sql
@@ -766,6 +895,8 @@
// generated from the current Connection (saves
// a query, and network traffic).
synchronized (this.connection.getMutex()) {
+ this.batchedGeneratedKeys = null;
+
String oldCatalog = null;
if (!this.connection.getCatalog().equals(this.currentCatalog)) {
@@ -914,6 +1045,10 @@
ResultSet rs = null;
synchronized (this.connection.getMutex()) {
+ if (!isBatch) {
+ this.batchedGeneratedKeys = null;
+ }
+
String oldCatalog = null;
if (!this.connection.getCatalog().equals(this.currentCatalog)) {
@@ -1122,7 +1257,17 @@
*/
public synchronized java.sql.ResultSet getGeneratedKeys()
throws SQLException {
- return getGeneratedKeysInternal();
+ if (this.batchedGeneratedKeys == null) {
+ return getGeneratedKeysInternal();
+ }
+
+ Field[] fields = new Field[1];
+ fields[0] = new Field("", "GENERATED_KEY", Types.BIGINT, 17); //$NON-NLS-1$
//$NON-NLS-2$
+ fields[0].setConnection(this.connection);
+
+ return new com.mysql.jdbc.ResultSet(this.currentCatalog, fields,
+ new RowDataStatic(this.batchedGeneratedKeys), this.connection,
+ this);
}
/*
@@ -1628,6 +1773,7 @@
this.connection = null;
this.warningChain = null;
this.openResults = null;
+ this.batchedGeneratedKeys = null;
this.isClosed = true;
}
@@ -1822,4 +1968,42 @@
this.resultSetType = typeFlag;
}
+ protected void getBatchedGeneratedKeys(java.sql.Statement batchedStatement) throws
SQLException {
+ if (this.retrieveGeneratedKeys) {
+ java.sql.ResultSet rs = null;
+
+ try {
+ rs = batchedStatement.getGeneratedKeys();
+
+ while (rs.next()) {
+ this.batchedGeneratedKeys
+ .add(new byte[][] { rs.getBytes(1) });
+ }
+ } finally {
+ if (rs != null) {
+ rs.close();
+ }
+ }
+ }
+ }
+
+ protected void getBatchedGeneratedKeys() throws SQLException {
+ if (this.retrieveGeneratedKeys) {
+ java.sql.ResultSet rs = null;
+
+ try {
+ rs = getGeneratedKeysInternal();
+
+ while (rs.next()) {
+ this.batchedGeneratedKeys
+ .add(new byte[][] { rs.getBytes(1) });
+ }
+ } finally {
+ if (rs != null) {
+ rs.close();
+ }
+ }
+ }
+ }
+
}
Modified: branches/branch_3_1/connector-j/src/testsuite/simple/StatementsTest.java
===================================================================
--- branches/branch_3_1/connector-j/src/testsuite/simple/StatementsTest.java 2006-03-09
20:53:06 UTC (rev 5033)
+++ branches/branch_3_1/connector-j/src/testsuite/simple/StatementsTest.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -913,6 +913,108 @@
}
}
+ public void testStatementRewriteBatch() throws SQLException {
+ Properties props = new Properties();
+ props.setProperty("rewriteBatchedStatements", "true");
+ Connection multiConn = getConnectionWithProps(props);
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+ Statement multiStmt = multiConn.createStatement();
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (1)");
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (2)");
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (3)");
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (4)");
+ multiStmt.addBatch("UPDATE testStatementRewriteBatch SET field1=5 WHERE field1=1");
+ multiStmt.addBatch("UPDATE testStatementRewriteBatch SET field1=6 WHERE field1=2 OR
field1=3");
+
+ int[] counts = multiStmt.executeBatch();
+ ResultSet genKeys = multiStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 5; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+
+ assertEquals(counts.length, 6);
+ assertEquals(counts[0], 1);
+ assertEquals(counts[1], 1);
+ assertEquals(counts[2], 1);
+ assertEquals(counts[3], 1);
+ assertEquals(counts[4], 1);
+ assertEquals(counts[5], 2);
+
+ this.rs = multiStmt.executeQuery("SELECT field1 FROM testStatementRewriteBatch ORDER BY
field1");
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 4);
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 5);
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 6);
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 6);
+
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+ props.clear();
+ props.setProperty("rewriteBatchedStatements", "true");
+ props.setProperty("sessionVariables", "max_allowed_packet=1024");
+ multiConn = getConnectionWithProps(props);
+ multiStmt = multiConn.createStatement();
+
+ for (int i = 0; i < 1000; i++) {
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (" + i +
")");
+ }
+
+ multiStmt.executeBatch();
+ genKeys = multiStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 1000; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+
+ props.clear();
+ props.setProperty("useServerPrepStmts", "false");
+ props.setProperty("rewriteBatchedStatements", "true");
+ multiConn = getConnectionWithProps(props);
+ PreparedStatement pStmt = multiConn.prepareStatement("INSERT INTO
testStatementRewriteBatch(field1) VALUES (?)",
+ Statement.RETURN_GENERATED_KEYS);
+
+ for (int i = 0; i < 1000; i++) {
+ pStmt.setInt(1, i);
+ pStmt.addBatch();
+ }
+
+ pStmt.executeBatch();
+ genKeys = pStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 1000; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+ props.setProperty("useServerPrepStmts", "false");
+ props.setProperty("rewriteBatchedStatements", "true");
+ props.setProperty("sessionVariables", "max_allowed_packet=1024");
+ multiConn = getConnectionWithProps(props);
+ pStmt = multiConn.prepareStatement("INSERT INTO testStatementRewriteBatch(field1)
VALUES (?)",
+ Statement.RETURN_GENERATED_KEYS);
+
+ for (int i = 0; i < 1000; i++) {
+ pStmt.setInt(1, i);
+ pStmt.addBatch();
+ }
+
+ pStmt.executeBatch();
+ genKeys = pStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 1000; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+ }
+
/**
* DOCUMENT ME!
*
Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
===================================================================
---
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-03-09
20:53:06 UTC (rev 5033)
+++
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -1108,6 +1108,20 @@
"Should the driver retain the Statement reference in a ResultSet after
ResultSet.close()"
+ " has been called. This is not JDBC-compliant after JDBC-4.0.",
"3.1.11", MISC_CATEGORY, Integer.MIN_VALUE);
+
+ private BooleanConnectionProperty rewriteBatchedStatements = new
BooleanConnectionProperty(
+ "rewriteBatchedStatements",
+ false,
+ "Should the driver use multiqueries (irregardless of the setting of
\"allowMultiQueries\") as well as "
+ + "rewriting of prepared statements for INSERT into multi-value inserts when
executeBatch() is called? Notice that this has the potential "
+ + "for SQL injection if using plain java.sql.Statements and your code doesn't sanitize
input correctly.\n\n"
+ + "Notice that for prepared statements, server-side prepared statements can not
currently take advantage of "
+ + "this rewrite option, and that if you don't specify stream lengths when using
PreparedStatement.set*Stream(),"
+ + "the driver won't be able to determine the optimium number of parameters per batch
and you might receive an"
+ + "an error from the driver that the resultant packet is too large.\n\n"
+ + "Statement.getGeneratedKeys() for these rewritten statements only works when the
entire "
+ + "batch includes INSERT statements.",
+ "3.1.13", PERFORMANCE_CATEGORY, Integer.MIN_VALUE);
private BooleanConnectionProperty rollbackOnPooledClose = new BooleanConnectionProperty(
"rollbackOnPooledClose",
@@ -3558,7 +3572,7 @@
protected boolean getProcessEscapeCodesForPrepStmts() {
return this.processEscapeCodesForPrepStmts.getValueAsBoolean();
-}
+ }
protected void setProcessEscapeCodesForPrepStmts(boolean flag) {
this.processEscapeCodesForPrepStmts.setValue(flag);
@@ -3587,4 +3601,12 @@
protected void setResourceId(String resourceId) {
this.resourceId.setValue(resourceId);
}
+
+ protected boolean getRewriteBatchedStatements() {
+ return this.rewriteBatchedStatements.getValueAsBoolean();
+ }
+
+ protected void setRewriteBatchedStatements(boolean flag) {
+ this.rewriteBatchedStatements.setValue(flag);
+ }
}
Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java
===================================================================
--- branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-03-09
20:53:06 UTC (rev 5033)
+++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -338,7 +338,13 @@
return numCharsRead;
}
- protected ArrayList batchedGeneratedKeys = null;
+ /**
+ * Does the batch (if any) contain "plain" statements added by
+ * Statement.addBatch(String)?
+ *
+ * If so, we can't re-write it to use multi-value or multi-queries.
+ */
+ protected boolean batchHasPlainStatements = false;
private java.sql.DatabaseMetaData dbmd = null;
@@ -376,8 +382,6 @@
private java.sql.ResultSetMetaData pstmtResultMetaData;
- protected boolean retrieveGeneratedKeys = false;
-
private byte[][] staticSqlStrings = null;
private byte[] streamConvertBuf = new byte[4096];
@@ -393,6 +397,8 @@
private boolean usingAnsiMode;
+ private String batchedValuesClause;
+
/**
* Constructor used by server-side prepared statements
*
@@ -498,6 +504,12 @@
this.isNull));
}
+ public synchronized void addBatch(String sql) throws SQLException {
+ this.batchHasPlainStatements = true;
+
+ super.addBatch(sql);
+ }
+
protected String asSql() throws SQLException {
return asSql(false);
}
@@ -569,6 +581,12 @@
return buf.toString();
}
+ public synchronized void clearBatch() throws SQLException {
+ this.batchHasPlainStatements = false;
+
+ super.clearBatch();
+ }
+
/**
* In general, parameter values remain in force for repeated used of a
* Statement. Setting a parameter value automatically clears its previous
@@ -805,7 +823,7 @@
*/
public int[] executeBatch() throws SQLException {
if (this.connection.isReadOnly()) {
- throw SQLError.createSQLException(Messages.getString("PreparedStatement.25")
//$NON-NLS-1$
+ throw new SQLException(Messages.getString("PreparedStatement.25") //$NON-NLS-1$
+ Messages.getString("PreparedStatement.26"), //$NON-NLS-1$
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
@@ -813,92 +831,263 @@
synchronized (this.connection.getMutex()) {
try {
clearWarnings();
-
- int[] updateCounts = null;
-
- if (this.batchedArgs != null) {
- int nbrCommands = this.batchedArgs.size();
- updateCounts = new int[nbrCommands];
-
- for (int i = 0; i < nbrCommands; i++) {
- updateCounts[i] = -3;
+
+ if (!this.batchHasPlainStatements
+ && this.connection.getRewriteBatchedStatements()) {
+ if (StringUtils.startsWithIgnoreCaseAndWs(this.originalSql,
+ "INSERT")) {
+ return executeBatchedInserts();
}
-
- SQLException sqlEx = null;
-
- int commandIndex = 0;
-
- if (this.retrieveGeneratedKeys) {
- this.batchedGeneratedKeys = new ArrayList(nbrCommands);
+ }
+
+ return executeBatchSerially();
+ } finally {
+ clearBatch();
+ }
+ }
+ }
+
+ /**
+ * Rewrites the already prepared statement into a multi-value insert
+ * statement of 'statementsPerBatch' values and executes the entire batch
+ * using this new statement.
+ *
+ * @return update counts in the same fashion as executeBatch()
+ *
+ * @throws SQLException
+ */
+ private int[] executeBatchedInserts() throws SQLException {
+ String valuesClause = extractValuesClause();
+
+ if (valuesClause == null) {
+ return executeBatchSerially();
+ }
+
+ 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;
+
+ if (this.retrieveGeneratedKeys) {
+ batchedStatement = this.connection.prepareStatement(
+ generateBatchedInsertSQL(valuesClause, numValuesPerBatch),
+ RETURN_GENERATED_KEYS);
+ } else {
+ batchedStatement = this.connection
+ .prepareStatement(generateBatchedInsertSQL(valuesClause,
+ numValuesPerBatch));
+ }
+
+ int batchedParamIndex = 1;
+ int updateCountRunningTotal = 0;
+ int numberToExecuteAsMultiValue = 0;
+ int batchCounter = 0;
+
+ 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;
+
+ }
+
+ BatchParams paramArg = (BatchParams) this.batchedArgs
+ .get(batchCounter++);
+
+ batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
+ batchedParamIndex, paramArg);
+ }
+
+ updateCountRunningTotal += batchedStatement.executeUpdate();
+ getBatchedGeneratedKeys(batchedStatement);
+
+ numValuesPerBatch = numBatchedArgs - batchCounter;
+
+ if (numValuesPerBatch > 0) {
+
+ batchedStatement = this.connection.prepareStatement(
+ generateBatchedInsertSQL(valuesClause, numValuesPerBatch),
+ RETURN_GENERATED_KEYS);
+ batchedParamIndex = 1;
+
+ while (batchCounter < numBatchedArgs) {
+
+ BatchParams paramArg = (BatchParams) this.batchedArgs
+ .get(batchCounter++);
+ batchedParamIndex = setOneBatchedParameterSet(batchedStatement,
+ batchedParamIndex, paramArg);
+ }
+
+ 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;
+ }
+
+ protected int computeBatchSize(int numBatchedArgs) {
+ long sizeOfEntireBatch = 0;
+ long maxSizeOfParameterSet = 0;
+
+ for (int i = 0; i < numBatchedArgs; i++) {
+ BatchParams paramArg = (BatchParams) this.batchedArgs
+ .get(i);
+
+ boolean[] isNullBatch = paramArg.isNull;
+ boolean[] isStreamBatch = paramArg.isStream;
+
+ long sizeOfParameterSet = 0;
+
+ for (int j = 0; j < isNullBatch.length; j++) {
+ if (!isNullBatch[j]) {
+
+ if (isStreamBatch[j]) {
+ int streamLength = paramArg.streamLengths[j];
+
+ if (streamLength != -1) {
+ sizeOfParameterSet += streamLength * 2; // for safety in escaping
+ } else {
+ int paramLength = paramArg.parameterStrings[j].length;
+ sizeOfParameterSet += paramLength;
+ }
+ } else {
+ sizeOfParameterSet += 4; // for NULL literal in SQL
}
-
- for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
- Object arg = this.batchedArgs.get(commandIndex);
-
- if (arg instanceof String) {
- updateCounts[commandIndex] = executeUpdate((String) arg);
- } else {
- BatchParams paramArg = (BatchParams) arg;
-
+ }
+ }
+
+ //
+ // Account for static part of values clause
+ // This is a little naiive, because the ?s will be replaced
+ // but it gives us some padding, and is less housekeeping
+ // to ignore them. We're looking for a "fuzzy" value here
+ // anyway
+ //
+
+ sizeOfParameterSet += this.batchedValuesClause.length() + 1;
+ sizeOfEntireBatch += sizeOfParameterSet;
+
+ if (sizeOfParameterSet > maxSizeOfParameterSet) {
+ maxSizeOfParameterSet = sizeOfParameterSet;
+ }
+ }
+
+ int maxAllowedPacket = this.connection.getMaxAllowedPacket();
+
+ if (sizeOfEntireBatch < maxAllowedPacket - this.originalSql.length()) {
+ return numBatchedArgs;
+ }
+
+ return (int)Math.max(1, (maxAllowedPacket - this.originalSql.length()) /
maxSizeOfParameterSet);
+ }
+
+ /**
+ * Executes the current batch of statements by executing them one-by-one.
+ *
+ * @return a list of update counts
+ * @throws SQLException
+ * if an error occurs
+ */
+ protected int[] executeBatchSerially() throws SQLException {
+
+ int[] updateCounts = null;
+
+ if (this.batchedArgs != null) {
+ int nbrCommands = this.batchedArgs.size();
+ updateCounts = new int[nbrCommands];
+
+ for (int i = 0; i < nbrCommands; i++) {
+ updateCounts[i] = -3;
+ }
+
+ SQLException sqlEx = null;
+
+ int commandIndex = 0;
+
+ if (this.retrieveGeneratedKeys) {
+ this.batchedGeneratedKeys = new ArrayList(nbrCommands);
+ }
+
+ for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
+ Object arg = this.batchedArgs.get(commandIndex);
+
+ if (arg instanceof String) {
+ updateCounts[commandIndex] = executeUpdate((String) arg);
+ } else {
+ BatchParams paramArg = (BatchParams) arg;
+
+ try {
+ updateCounts[commandIndex] = executeUpdate(
+ paramArg.parameterStrings,
+ paramArg.parameterStreams, paramArg.isStream,
+ paramArg.streamLengths, paramArg.isNull, true);
+
+ if (this.retrieveGeneratedKeys) {
+ java.sql.ResultSet rs = null;
+
try {
- updateCounts[commandIndex] = executeUpdate(
- paramArg.parameterStrings,
- paramArg.parameterStreams,
- paramArg.isStream, paramArg.streamLengths,
- paramArg.isNull,
- true);
-
- if (this.retrieveGeneratedKeys) {
- java.sql.ResultSet rs = null;
-
- try {
- // we don't want to use our version,
- // because we've altered the behavior of
- // ours to support batch updates
- // (catch-22)
- rs = super.getGeneratedKeys();
-
- while (rs.next()) {
- this.batchedGeneratedKeys
- .add(new byte[][] { rs
- .getBytes(1) });
- }
- } finally {
- if (rs != null) {
- rs.close();
- }
- }
+ rs = getGeneratedKeysInternal();
+
+ while (rs.next()) {
+ this.batchedGeneratedKeys
+ .add(new byte[][] { rs.getBytes(1) });
}
- } catch (SQLException ex) {
- updateCounts[commandIndex] = EXECUTE_FAILED;
-
- if (this.connection.getContinueBatchOnError()) {
- sqlEx = ex;
- } else {
- int[] newUpdateCounts = new int[commandIndex];
- System.arraycopy(updateCounts, 0,
- newUpdateCounts, 0, commandIndex);
-
- throw new java.sql.BatchUpdateException(ex
- .getMessage(), ex.getSQLState(), ex
- .getErrorCode(), newUpdateCounts);
+ } finally {
+ if (rs != null) {
+ rs.close();
}
}
}
+ } catch (SQLException ex) {
+ updateCounts[commandIndex] = EXECUTE_FAILED;
+
+ if (this.connection.getContinueBatchOnError()) {
+ sqlEx = ex;
+ } else {
+ int[] newUpdateCounts = new int[commandIndex];
+ System.arraycopy(updateCounts, 0, newUpdateCounts,
+ 0, commandIndex);
+
+ throw new java.sql.BatchUpdateException(ex
+ .getMessage(), ex.getSQLState(), ex
+ .getErrorCode(), newUpdateCounts);
+ }
}
-
- if (sqlEx != null) {
- throw new java.sql.BatchUpdateException(sqlEx.getMessage(),
- sqlEx.getSQLState(), sqlEx.getErrorCode(),
- updateCounts);
- }
}
-
- return (updateCounts != null) ? updateCounts : new int[0];
- } finally {
- clearBatch();
}
+
+ if (sqlEx != null) {
+ throw new java.sql.BatchUpdateException(sqlEx.getMessage(),
+ sqlEx.getSQLState(), sqlEx.getErrorCode(), updateCounts);
+ }
}
+
+ return (updateCounts != null) ? updateCounts : new int[0];
}
/**
@@ -1210,6 +1399,45 @@
return truncatedUpdateCount;
}
+ private synchronized String extractValuesClause() throws SQLException {
+ if (this.batchedValuesClause == null) {
+ String quoteCharStr = this.connection.getMetaData()
+ .getIdentifierQuoteString();
+
+ int indexOfValues = -1;
+
+ if (quoteCharStr.length() > 0) {
+ indexOfValues = StringUtils.indexOfIgnoreCaseRespectQuotes(0,
+ this.originalSql, "VALUES ", quoteCharStr.charAt(0), false);
+ } else {
+ indexOfValues = StringUtils.indexOfIgnoreCase(0, this.originalSql,
+ "VALUES ");
+ }
+
+ if (indexOfValues == -1) {
+ return null;
+ }
+
+ int indexOfFirstParen = this.originalSql
+ .indexOf('(', indexOfValues + 7);
+
+ if (indexOfFirstParen == -1) {
+ return null;
+ }
+
+ int indexOfLastParen = this.originalSql.lastIndexOf(')');
+
+ if (indexOfLastParen == -1) {
+ return null;
+ }
+
+ this.batchedValuesClause = this.originalSql.substring(indexOfFirstParen,
+ indexOfLastParen + 1);
+ }
+
+ return this.batchedValuesClause;
+ }
+
/**
* Creates the packet that contains the query to be sent to the server.
*
@@ -1293,6 +1521,21 @@
return sendPacket;
}
+ private String generateBatchedInsertSQL(String valuesClause, int numBatches) {
+ StringBuffer newStatementSql = new StringBuffer(this.originalSql
+ .length()
+ + (numBatches * (valuesClause.length() + 1)));
+
+ newStatementSql.append(this.originalSql, 0, this.originalSql.length());
+
+ for (int i = 0; i < numBatches - 1; i++) {
+ newStatementSql.append(',');
+ newStatementSql.append(valuesClause);
+ }
+
+ return newStatementSql.toString();
+ }
+
/**
* DOCUMENT ME!
*
@@ -1497,26 +1740,6 @@
return format.toString();
}
- /*
- * (non-Javadoc)
- *
- * @see java.sql.Statement#getGeneratedKeys()
- */
- public synchronized java.sql.ResultSet getGeneratedKeys()
- throws SQLException {
- if (this.batchedGeneratedKeys == null) {
- return super.getGeneratedKeys();
- }
-
- Field[] fields = new Field[1];
- fields[0] = new Field("", "GENERATED_KEY", Types.BIGINT, 17); //$NON-NLS-1$
//$NON-NLS-2$
- fields[0].setConnection(this.connection);
-
- return new com.mysql.jdbc.ResultSet(this.currentCatalog, fields,
- new RowDataStatic(this.batchedGeneratedKeys), this.connection,
- this);
- }
-
/**
* The number, types and properties of a ResultSet's columns are provided by
* the getMetaData method.
@@ -2811,6 +3034,31 @@
}
}
+ private int setOneBatchedParameterSet(
+ java.sql.PreparedStatement batchedStatement, int batchedParamIndex,
+ BatchParams paramArg) throws SQLException {
+ boolean[] isNullBatch = paramArg.isNull;
+ boolean[] isStreamBatch = paramArg.isStream;
+
+ for (int j = 0; j < isNullBatch.length; j++) {
+ if (isNullBatch[j]) {
+ batchedStatement.setNull(batchedParamIndex++, Types.NULL);
+ } else {
+ if (isStreamBatch[j]) {
+ batchedStatement.setBinaryStream(batchedParamIndex++,
+ paramArg.parameterStreams[j],
+ paramArg.streamLengths[j]);
+ } else {
+ ((com.mysql.jdbc.PreparedStatement) batchedStatement)
+ .setBytesNoEscapeNoQuotes(batchedParamIndex++,
+ paramArg.parameterStrings[j]);
+ }
+ }
+ }
+
+ return batchedParamIndex;
+ }
+
/**
* JDBC 2.0 Set a REF(<structured-type>) parameter.
*
Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/Statement.java
===================================================================
--- branches/branch_5_0/connector-j/src/com/mysql/jdbc/Statement.java 2006-03-09 20:53:06
UTC (rev 5033)
+++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/Statement.java 2006-03-09 20:56:13
UTC (rev 5034)
@@ -240,6 +240,10 @@
*/
protected boolean holdResultsOpenOverClose = false;
+protected ArrayList batchedGeneratedKeys = null;
+
+ protected boolean retrieveGeneratedKeys = false;
+
/**
* Constructor for a Statement.
*
@@ -605,6 +609,8 @@
// generated from the current Connection (saves
// a query, and network traffic).
synchronized (this.connection.getMutex()) {
+ this.batchedGeneratedKeys = null;
+
if (useServerFetch()) {
rs = createResultSetUsingServerFetch(sql);
} else {
@@ -838,10 +844,23 @@
synchronized (this.connection.getMutex()) {
try {
+ this.retrieveGeneratedKeys = true;
+
int[] updateCounts = null;
if (this.batchedArgs != null) {
int nbrCommands = this.batchedArgs.size();
+
+ this.batchedGeneratedKeys = new ArrayList(this.batchedArgs.size());
+
+ boolean multiQueriesEnabled = this.connection.getAllowMultiQueries();
+
+ if (multiQueriesEnabled ||
+ (this.connection.getRewriteBatchedStatements() &&
+ nbrCommands > 4)) {
+ return executeBatchUsingMultiQueries(multiQueriesEnabled, nbrCommands);
+ }
+
updateCounts = new int[nbrCommands];
for (int i = 0; i < nbrCommands; i++) {
@@ -854,9 +873,9 @@
for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
try {
- updateCounts[commandIndex] = executeUpdate(
- (String) this.batchedArgs.get(commandIndex),
- true);
+ updateCounts[commandIndex] = executeUpdate((String) this.batchedArgs
+ .get(commandIndex), true);
+ getBatchedGeneratedKeys();
} catch (SQLException ex) {
updateCounts[commandIndex] = EXECUTE_FAILED;
@@ -883,12 +902,119 @@
return (updateCounts != null) ? updateCounts : new int[0];
} finally {
+ this.retrieveGeneratedKeys = false;
+
clearBatch();
}
}
}
/**
+ * Rewrites batch into a single query to send to the server. This method
+ * will constrain each batch to be shorter than max_allowed_packet on the
+ * server.
+ *
+ * @return update counts in the same manner as executeBatch()
+ * @throws SQLException
+ */
+ private int[] executeBatchUsingMultiQueries(boolean multiQueriesEnabled,
+ int nbrCommands) throws SQLException {
+
+ if (!multiQueriesEnabled) {
+ this.connection.getIO().enableMultiQueries();
+ }
+
+ try {
+ int[] updateCounts = new int[nbrCommands];
+
+ for (int i = 0; i < nbrCommands; i++) {
+ updateCounts[i] = -3;
+ }
+
+ int commandIndex = 0;
+
+ StringBuffer queryBuf = new StringBuffer();
+
+ java.sql.Statement batchStmt = this.connection.createStatement();
+
+ int counter = 0;
+
+ int numberOfBytesPerChar = 1;
+
+ String connectionEncoding = this.connection.getEncoding();
+
+ if (StringUtils.startsWithIgnoreCase(connectionEncoding, "utf")) {
+ numberOfBytesPerChar = 3;
+ } else if (CharsetMapping.isMultibyteCharset(connectionEncoding)) {
+ numberOfBytesPerChar = 2;
+ }
+
+ int escapeAdjust = 1;
+
+ if (this.doEscapeProcessing) {
+ escapeAdjust = 2; /* We assume packet _could_ grow by this amount, as we're not
+ sure how big statement will end up after
+ escape processing */
+ }
+
+ for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
+ String nextQuery = (String) this.batchedArgs.get(commandIndex);
+
+ if (((((queryBuf.length() + nextQuery.length())
+ * numberOfBytesPerChar) + 1 /* for semicolon */
+ + MysqlIO.HEADER_LENGTH) * escapeAdjust) + 32 > this.connection
+ .getMaxAllowedPacket()) {
+ batchStmt.execute(queryBuf.toString());
+
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+ long generatedKeyStart = ((com.mysql.jdbc.Statement)batchStmt).getLastInsertID();
+ byte[][] row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+
+ while (batchStmt.getMoreResults()
+ || batchStmt.getUpdateCount() != -1) {
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+ row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+ }
+
+ queryBuf = new StringBuffer();
+ }
+
+ queryBuf.append(nextQuery);
+ queryBuf.append(";");
+ }
+
+ if (queryBuf.length() > 0) {
+ batchStmt.execute(queryBuf.toString());
+
+ long generatedKeyStart = ((com.mysql.jdbc.Statement)batchStmt).getLastInsertID();
+ byte[][] row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+
+ while (batchStmt.getMoreResults()
+ || batchStmt.getUpdateCount() != -1) {
+ updateCounts[counter++] = batchStmt.getUpdateCount();
+ row = new byte[1][];
+ row[0] = Long.toString(generatedKeyStart++).getBytes();
+ this.batchedGeneratedKeys.add(row);
+ }
+ }
+
+ return (updateCounts != null) ? updateCounts : new int[0];
+ } finally {
+ if (!multiQueriesEnabled) {
+ this.connection.getIO().disableMultiQueries();
+ }
+ }
+ }
+
+ /**
* Execute a SQL statement that retruns a single ResultSet
*
* @param sql
@@ -1357,9 +1483,19 @@
*/
public synchronized java.sql.ResultSet getGeneratedKeys()
throws SQLException {
- return getGeneratedKeysInternal();
+ if (this.batchedGeneratedKeys == null) {
+ return getGeneratedKeysInternal();
+ }
+
+ Field[] fields = new Field[1];
+ fields[0] = new Field("", "GENERATED_KEY", Types.BIGINT, 17); //$NON-NLS-1$
//$NON-NLS-2$
+ fields[0].setConnection(this.connection);
+
+ return new com.mysql.jdbc.ResultSet(this.currentCatalog, fields,
+ new RowDataStatic(this.batchedGeneratedKeys), this.connection,
+ this);
}
-
+
/*
* Needed because there's no concept of super.super to get to this
* implementation from ServerPreparedStatement when dealing with batched
@@ -1874,6 +2010,7 @@
this.connection = null;
this.warningChain = null;
this.openResults = null;
+ this.batchedGeneratedKeys = null;
this.isClosed = true;
}
@@ -2078,6 +2215,44 @@
this.resultSetType = typeFlag;
}
+ protected void getBatchedGeneratedKeys(java.sql.Statement batchedStatement) throws
SQLException {
+ if (this.retrieveGeneratedKeys) {
+ java.sql.ResultSet rs = null;
+
+ try {
+ rs = batchedStatement.getGeneratedKeys();
+
+ while (rs.next()) {
+ this.batchedGeneratedKeys
+ .add(new byte[][] { rs.getBytes(1) });
+ }
+ } finally {
+ if (rs != null) {
+ rs.close();
+ }
+ }
+ }
+ }
+
+ protected void getBatchedGeneratedKeys() throws SQLException {
+ if (this.retrieveGeneratedKeys) {
+ java.sql.ResultSet rs = null;
+
+ try {
+ rs = getGeneratedKeysInternal();
+
+ while (rs.next()) {
+ this.batchedGeneratedKeys
+ .add(new byte[][] { rs.getBytes(1) });
+ }
+ } finally {
+ if (rs != null) {
+ rs.close();
+ }
+ }
+ }
+ }
+
/**
* @return
*/
Modified: branches/branch_5_0/connector-j/src/testsuite/simple/StatementsTest.java
===================================================================
--- branches/branch_5_0/connector-j/src/testsuite/simple/StatementsTest.java 2006-03-09
20:53:06 UTC (rev 5033)
+++ branches/branch_5_0/connector-j/src/testsuite/simple/StatementsTest.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -1241,6 +1241,108 @@
// Server-side prepared statements can only reset streamed data
// in-toto, not piecemiel.
+ public void testStatementRewriteBatch() throws SQLException {
+ Properties props = new Properties();
+ props.setProperty("rewriteBatchedStatements", "true");
+ Connection multiConn = getConnectionWithProps(props);
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+ Statement multiStmt = multiConn.createStatement();
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (1)");
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (2)");
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (3)");
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (4)");
+ multiStmt.addBatch("UPDATE testStatementRewriteBatch SET field1=5 WHERE field1=1");
+ multiStmt.addBatch("UPDATE testStatementRewriteBatch SET field1=6 WHERE field1=2 OR
field1=3");
+
+ int[] counts = multiStmt.executeBatch();
+ ResultSet genKeys = multiStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 5; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+
+ assertEquals(counts.length, 6);
+ assertEquals(counts[0], 1);
+ assertEquals(counts[1], 1);
+ assertEquals(counts[2], 1);
+ assertEquals(counts[3], 1);
+ assertEquals(counts[4], 1);
+ assertEquals(counts[5], 2);
+
+ this.rs = multiStmt.executeQuery("SELECT field1 FROM testStatementRewriteBatch ORDER BY
field1");
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 4);
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 5);
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 6);
+ assertTrue(this.rs.next());
+ assertEquals(this.rs.getInt(1), 6);
+
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+ props.clear();
+ props.setProperty("rewriteBatchedStatements", "true");
+ props.setProperty("sessionVariables", "max_allowed_packet=1024");
+ multiConn = getConnectionWithProps(props);
+ multiStmt = multiConn.createStatement();
+
+ for (int i = 0; i < 1000; i++) {
+ multiStmt.addBatch("INSERT INTO testStatementRewriteBatch(field1) VALUES (" + i +
")");
+ }
+
+ multiStmt.executeBatch();
+ genKeys = multiStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 1000; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+
+ props.clear();
+ props.setProperty("useServerPrepStmts", "false");
+ props.setProperty("rewriteBatchedStatements", "true");
+ multiConn = getConnectionWithProps(props);
+ PreparedStatement pStmt = multiConn.prepareStatement("INSERT INTO
testStatementRewriteBatch(field1) VALUES (?)",
+ Statement.RETURN_GENERATED_KEYS);
+
+ for (int i = 0; i < 1000; i++) {
+ pStmt.setInt(1, i);
+ pStmt.addBatch();
+ }
+
+ pStmt.executeBatch();
+ genKeys = pStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 1000; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+
+ createTable("testStatementRewriteBatch", "(pk_field INT PRIMARY KEY NOT NULL
AUTO_INCREMENT, field1 INT)");
+ props.setProperty("useServerPrepStmts", "false");
+ props.setProperty("rewriteBatchedStatements", "true");
+ props.setProperty("sessionVariables", "max_allowed_packet=1024");
+ multiConn = getConnectionWithProps(props);
+ pStmt = multiConn.prepareStatement("INSERT INTO testStatementRewriteBatch(field1)
VALUES (?)",
+ Statement.RETURN_GENERATED_KEYS);
+
+ for (int i = 0; i < 1000; i++) {
+ pStmt.setInt(1, i);
+ pStmt.addBatch();
+ }
+
+ pStmt.executeBatch();
+ genKeys = pStmt.getGeneratedKeys();
+
+ for (int i = 1; i < 1000; i++) {
+ genKeys.next();
+ assertEquals(i, genKeys.getInt(1));
+ }
+ }
+
public void testStreamChange() throws Exception {
createTable("testStreamChange",
"(field1 varchar(32), field2 int, field3 TEXT, field4 BLOB)");
@@ -1308,7 +1410,7 @@
}
}
}
-
+
public void testTruncationOnRead() throws Exception {
this.rs = this.stmt.executeQuery("SELECT '" + Long.MAX_VALUE + "'");
this.rs.next();
Modified: branches/branch_5_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
===================================================================
---
branches/branch_5_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-03-09
20:53:06 UTC (rev 5033)
+++
branches/branch_5_1/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -1093,6 +1093,15 @@
"Require SSL connection if useSSL=true? (defaults to 'false').",
"3.1.0", SECURITY_CATEGORY, 3);
+ private StringConnectionProperty resourceId = new StringConnectionProperty(
+ "resourceId",
+ null, "A globally unique name that identifies the resource that this datasource or
connection is " +
+ "connected to, used for XAResource.isSameRM() when the driver can't determine this
value based on " +
+ "hostnames used in the URL",
+ "5.0.1",
+ HA_CATEGORY,
+ Integer.MIN_VALUE);
+
private BooleanConnectionProperty retainStatementAfterResultSetClose = new
BooleanConnectionProperty(
"retainStatementAfterResultSetClose",
false,
@@ -1100,6 +1109,20 @@
+ " has been called. This is not JDBC-compliant after JDBC-4.0.",
"3.1.11", MISC_CATEGORY, Integer.MIN_VALUE);
+ private BooleanConnectionProperty rewriteBatchedStatements = new
BooleanConnectionProperty(
+ "rewriteBatchedStatements",
+ false,
+ "Should the driver use multiqueries (irregardless of the setting of
\"allowMultiQueries\") as well as "
+ + "rewriting of prepared statements for INSERT into multi-value inserts when
executeBatch() is called? Notice that this has the potential "
+ + "for SQL injection if using plain java.sql.Statements and your code doesn't sanitize
input correctly.\n\n"
+ + "Notice that for prepared statements, server-side prepared statements can not
currently take advantage of "
+ + "this rewrite option, and that if you don't specify stream lengths when using
PreparedStatement.set*Stream(),"
+ + "the driver won't be able to determine the optimium number of parameters per batch
and you might receive an"
+ + "an error from the driver that the resultant packet is too large.\n\n"
+ + "Statement.getGeneratedKeys() for these rewritten statements only works when the
entire "
+ + "batch includes INSERT statements.",
+ "3.1.13", PERFORMANCE_CATEGORY, Integer.MIN_VALUE);
+
private BooleanConnectionProperty rollbackOnPooledClose = new BooleanConnectionProperty(
"rollbackOnPooledClose",
true,
@@ -1589,10 +1612,6 @@
return this.alwaysSendSetIsolation.getValueAsBoolean();
}
- protected boolean getAutoClosePStmtStreams() {
- return this.autoClosePStmtStreams.getValueAsBoolean();
- }
-
/**
* @return Returns the autoDeserialize.
*/
@@ -2637,6 +2656,10 @@
protected void setAutoClosePStmtStreams(boolean flag) {
this.autoClosePStmtStreams.setValue(flag);
}
+
+ protected boolean getAutoClosePStmtStreams() {
+ return this.autoClosePStmtStreams.getValueAsBoolean();
+ }
/**
* @param autoDeserialize
@@ -3571,4 +3594,20 @@
protected boolean useUnbufferedInput() {
return this.useUnbufferedInput.getValueAsBoolean();
}
+
+ protected String getResourceId() {
+ return this.resourceId.getValueAsString();
+ }
+
+ protected void setResourceId(String resourceId) {
+ this.resourceId.setValue(resourceId);
+ }
+
+ protected boolean getRewriteBatchedStatements() {
+ return this.rewriteBatchedStatements.getValueAsBoolean();
+ }
+
+ protected void setRewriteBatchedStatements(boolean flag) {
+ this.rewriteBatchedStatements.setValue(flag);
+ }
}
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 2006-03-09
20:53:06 UTC (rev 5033)
+++ branches/branch_5_1/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-03-09
20:56:13 UTC (rev 5034)
@@ -341,7 +341,13 @@
return numCharsRead;
}
- protected ArrayList batchedGeneratedKeys = null;
+ /**
+ * Does the batch (if any) contain "plain" statements added by
+ * Statement.addBatch(String)?
+ *
+ * If so, we can't re-write it to use multi-value or multi-queries.
+ */
+ protected boolean batchHasPlainStatements = false;
private java.sql.DatabaseMetaData dbmd = null;
@@ -379,8 +385,6 @@
private java.sql.ResultSetMetaData pstmtResultMetaData;
- protected boolean retrieveGeneratedKeys = false;
-
private byte[][] staticSqlStrings = null;
private byte[] streamConvertBuf = new byte[4096];
@@ -396,6 +400,8 @@
private boolean usingAnsiMode;
+ private String batchedValuesClause;
+
/**
* Constructor used by server-side prepared statements
*
@@ -501,6 +507,12 @@
this.isNull));
}
+ public synchronized void addBatch(String sql) throws SQLException {
+ this.batchHasPlainStatements = true;
+
+ super.addBatch(sql);
+ }
+
protected String asSql() throws SQLException {
return asSql(false);
}
@@ -572,6 +584,12 @@
return buf.toString();
}
+ public synchronized void clearBatch() throws SQLException {
+ this.batchHasPlainStatements = false;
+
+ super.clearBatch();
+ }
+
/**
* In general, parameter values remain in force for repeated used of a
* Statement. Setting a parameter value automatically clears its previous
@@ -808,7 +826,7 @@
*/
public int[] executeBatch() throws SQLException {
if (this.connection.isReadOnly()) {
- throw SQLError.createSQLException(Messages.getString("PreparedStatement.25")
//$NON-NLS-1$
+ throw new SQLException(Messages.getString("PreparedStatement.25") //$NON-NLS-1$
+ Messages.getString("PreparedStatement.26"), //$NON-NLS-1$
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
@@ -817,91 +835,261 @@
try {
clearWarnings();
- int[] updateCounts = null;
-
- if (this.batchedArgs != null) {
- int nbrCommands = this.batchedArgs.size();
- updateCounts = new int[nbrCommands];
-
- for (int i = 0; i < nbrCommands; i++) {
- updateCounts[i] = -3;
+ if (!this.batchHasPlainStatements
+ && this.connection.getRewriteBatchedStatements()) {
+ if (StringUtils.startsWithIgnoreCaseAndWs(this.originalSql,
+ "INSERT")) {
+ return executeBatchedInserts();
}
-
- SQLException sqlEx = null;
-
- int commandIndex = 0;
-
- if (this.retrieveGeneratedKeys) {
- this.batchedGeneratedKeys = new ArrayList(nbrCommands);
+ }
+
+ return executeBatchSerially();
+ } finally {
+ clearBatch();
+ }
+ }
+ }
+
+ /**
+ * Rewrites the already prepared statement into a multi-value insert
+ * statement of 'statementsPerBatch' values and executes the entire batch
+ * using this new statement.
+ *
+ * @return update counts in the same fashion as executeBatch()
+ *
+ * @throws SQLException
+ */
+ private int[] executeBatchedInserts() throws SQLException {
+ String valuesClause = extractValuesClause();
+
+ if (valuesClause == null) {
+ return executeBatchSerially();
+ }
+
+ 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;
+
