Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-83

getGeneratedKeys() does not return generated keys in case of a batch SQL INSERT

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.5, 1.1.6
    • Fix Version/s: 1.1.8
    • Component/s: None
    • Labels:
      None
    • Environment:
      JDK 1.7, (QueryDSL-SQL 3.3.1)

      Description

      It is currently not possible to execute a batch insert and retrieve the generated keys.

      Expected outcome:
      After calling the MySQLPreparedStatement.executeBatch(), the MySQLPreparedStatement.getGeneratedKeys() method should return a ResultSet from which the keys generated in SQL INSERTs can be obtained.

      Actual outcome:
      The MySQLPreparedStatement.getGeneratedKeys() returns MySQLResultSet.EMPTY after calling MySQLPreparedStatement.executeBatch().

      More details:

      • The problem is reproducible e.g. by using QueryDSL-SQL. Its com.mysema.query.sql.dml.SQLInsertClause performs following operation:

        // Code simplified, see full implementation in https://github.com/mysema/querydsl/blob/master/querydsl-sql/src/main/java/com/mysema/query/sql/dml/SQLInsertClause.java:
         
        public ResultSet executeWithKeys() {
            final PreparedStatement stmt = createBatchStatement();
            stmt.executeBatch();
            return stmt.getGeneratedKeys();
        }
         
        private PreparedStatement createStatement(boolean withKeys) throws SQLException {
        	SQLSerializer serializer = new SQLSerializer(configuration, true);
        	serializer.serializeInsert(metadata, entity, batches.get(0).getColumns(), batches.get(0).getValues(), batches.get(0).getSubQuery());
         
        	PreparedStatement stmt = prepareStatementAndSetParameters(serializer, withKeys);
         
        	// add first batch
        	stmt.addBatch();
         
        	// add other batches
        	for (int i = 1; i < batches.size(); i++) {
        		SQLInsertBatch batch = batches.get(i);
        		serializer = new SQLSerializer(configuration, true);
        		serializer.serializeInsert(metadata, entity, batch.getColumns(), batch.getValues(), batch.getSubQuery());
        		setParameters(stmt, serializer.getConstants(), serializer.getConstantPaths(), metadata.getParams());
        		stmt.addBatch();
        	}
        	return stmt;
        }

      • The above example code from QueryDSL-SQL works with mysql:mysql-connector-java:5.1.29. I.e. it returns the generated keys after batch INSERT.

        Attachments

          Activity

            People

            Assignee:
            massimo.siani Massimo Siani (Inactive)
            Reporter:
            terohe Tero Heittola
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.