From: Date: March 9 2006 9:56pm Subject: Connector/J commit: r5034 - in branches: branch_3_1/connector-j/src/com/mysql/jdbc branch_3_1/connector-j/src/testsuite/simple branch_5_0/connector-j/src/com/mysql/jdbc branch_5_0/connector-j/src/testsuite/simple branch_5_1/connector-j/src/com/mysql/jdbc branch_5_1/connector-j/src/testsuite/simple List-Archive: http://lists.mysql.com/commits/3666 Message-Id: <200603092056.k29KuJws004852@bk-internal.mysql.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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; + + 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]; } /** @@ -1213,6 +1401,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. * @@ -1296,6 +1523,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! * @@ -1500,26 +1742,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. @@ -3055,8 +3277,29 @@ } } - public void setPoolable(boolean poolable) throws SQLException { - throw new ToBeImplementedException(); + 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; } /** @@ -3719,4 +3962,8 @@ public Object unwrap(Class arg0) throws SQLException { throw new ToBeImplementedException(); } + + public void setPoolable(boolean poolable) throws SQLException { + throw new ToBeImplementedException(); + } } Modified: branches/branch_5_1/connector-j/src/com/mysql/jdbc/Statement.java =================================================================== --- branches/branch_5_1/connector-j/src/com/mysql/jdbc/Statement.java 2006-03-09 20:53:06 UTC (rev 5033) +++ branches/branch_5_1/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 { + 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 @@ -1376,23 +1512,25 @@ long beginAt = getLastInsertID(); int numKeys = getUpdateCount(); - String serverInfo = this.results.getServerInfo(); - - // - // Only parse server info messages for 'REPLACE' - // queries - // - if ((numKeys > 0) && (this.results.getFirstCharOfQuery() == 'R') - && (serverInfo != null) && (serverInfo.length() > 0)) { - numKeys = getRecordCountFromInfo(serverInfo); - } - - if ((beginAt > 0) && (numKeys > 0)) { - for (int i = 0; i < numKeys; i++) { - byte[][] row = new byte[1][]; - row[0] = Long.toString(beginAt++).getBytes(); - rowSet.add(row); + if (this.results != null) { + String serverInfo = this.results.getServerInfo(); + + // + // Only parse server info messages for 'REPLACE' + // queries + // + if ((numKeys > 0) && (this.results.getFirstCharOfQuery() == 'R') + && (serverInfo != null) && (serverInfo.length() > 0)) { + numKeys = getRecordCountFromInfo(serverInfo); } + + if ((beginAt > 0) && (numKeys > 0)) { + for (int i = 0; i < numKeys; i++) { + byte[][] row = new byte[1][]; + row[0] = Long.toString(beginAt++).getBytes(); + rowSet.add(row); + } + } } return new com.mysql.jdbc.ResultSet(this.currentCatalog, fields, @@ -1880,6 +2018,7 @@ this.connection = null; this.warningChain = null; this.openResults = null; + this.batchedGeneratedKeys = null; this.isClosed = true; } @@ -2088,6 +2227,44 @@ throw new ToBeImplementedException(); } + 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_1/connector-j/src/testsuite/simple/StatementsTest.java =================================================================== --- branches/branch_5_1/connector-j/src/testsuite/simple/StatementsTest.java 2006-03-09 20:53:06 UTC (rev 5033) +++ branches/branch_5_1/connector-j/src/testsuite/simple/StatementsTest.java 2006-03-09 20:56:13 UTC (rev 5034) @@ -1813,6 +1813,112 @@ assertEquals("bbb", new String(c2)); this.rs.close(); } + + // 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)); + } + } + /** * Tests for ResultSet.updateNString()