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

Batch Statement Rewrite: Support for ON DUPLICATE KEY ...

Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 1.1.8
    • 1.2.2
    • Other
    • None
    • Long network delay between client and server.
    • Sprint connector/j 1.3.0

    Description

      CONJ-99 introduced support for the rewriteBatchedStatements=true JDBC URL parameter. This rewrites batched prepared statements into a single statement, when using PreparedStatement.addBatch() & executeBatch().

      This works fine for INSERT statements which do not specify an ON DUPLICATE KEY UPDATE clause. However for statements which do, the generated SQL cannot be parsed because the ON DUPLICATE ... clause is repeated for each set of values. E.g. for a prepared statement such as:

      prep.sql

      		 
      INSERT INTO my_table (pkey, col1) VALUES (?, ?) ON DUPLICATE KEY UPDATE col1 = VALUES(col1)

      after setting the parameters multiple times and calling addBatch() then executeBatch(), the generated SQL looks like this:

      gen.sql

      		 
      INSERT INTO my_table (pkey, col1) VALUES (1, 'a') ON DUPLICATE KEY UPDATE col1 = VALUES(col1),(2,'b') ON DUPLICATE KEY UPDATE col1 = VALUES(col1), ...

      I've written a small patch with a test case to duplicate the issue, along with a suggested fix. The test will fail against 1.1.8 but should pass with the suggested fix.

      Attachments

        Activity

          diego dupin Diego Dupin added a comment -

          adding test case that handle different possibilities : 

          /**
          		 
               * CONJ-141 : Batch Statement Rewrite: Support for ON DUPLICATE KEY
          		 
               * @throws SQLException
          		 
               */
          		 
              @Test
          		 
              public void rewriteBatchedStatementsWithQueryFirstAndLAst() throws SQLException {
          		 
                  Properties props = new Properties();
          		 
                  props.setProperty("rewriteBatchedStatements", "true");
          		 
                  Connection tmpConnection = null;
          		 
                  try {
          		 
                      tmpConnection = openNewConnection(connURI, props);
          		 
                      Statement st = tmpConnection.createStatement();
          		 
                      st.executeUpdate("drop table if exists t3_dupp");
          		 
                      st.executeUpdate("create table t3_dupp(col1 int, pkey int NOT NULL, col2 int, col3 int, col4 int, PRIMARY KEY (`pkey`))");
          		 
           
          		 
                      PreparedStatement sqlInsert = connection.prepareStatement("INSERT INTO t3_dupp(col1, pkey,col2,col3,col4) VALUES (9, ?, 5, ?, 8) ON DUPLICATE KEY UPDATE pkey=pkey+10");
          		 
                      sqlInsert.setInt(1, 1);
          		 
                      sqlInsert.setInt(2, 2);
          		 
                      sqlInsert.addBatch();
          		 
           
          		 
                      sqlInsert.setInt(1, 2);
          		 
                      sqlInsert.setInt(2, 5);
          		 
                      sqlInsert.addBatch();
          		 
           
          		 
                      sqlInsert.setInt(1, 7);
          		 
                      sqlInsert.setInt(2, 6);
          		 
                      sqlInsert.addBatch();
          		 
                      sqlInsert.executeBatch();
          		 
                  } finally {
          		 
                      if (tmpConnection != null) tmpConnection.close();
          		 
                  }
          		 
              }

          Resulting query send to database :
          INSERT INTO t3_dupp(col1, pkey,col2,col3,col4) VALUES (9, 1, 5, 2, 8),(9, 2, 5, 5, 8),(9, 7, 5, 6, 8) ON DUPLICATE KEY UPDATE pkey=pkey+10

          diego dupin Diego Dupin added a comment - adding test case that handle different possibilities : /** * CONJ-141 : Batch Statement Rewrite: Support for ON DUPLICATE KEY * @throws SQLException */ @Test public void rewriteBatchedStatementsWithQueryFirstAndLAst() throws SQLException { Properties props = new Properties(); props.setProperty( "rewriteBatchedStatements" , "true" ); Connection tmpConnection = null ; try { tmpConnection = openNewConnection(connURI, props); Statement st = tmpConnection.createStatement(); st.executeUpdate( "drop table if exists t3_dupp" ); st.executeUpdate( "create table t3_dupp(col1 int, pkey int NOT NULL, col2 int, col3 int, col4 int, PRIMARY KEY (`pkey`))" );   PreparedStatement sqlInsert = connection.prepareStatement( "INSERT INTO t3_dupp(col1, pkey,col2,col3,col4) VALUES (9, ?, 5, ?, 8) ON DUPLICATE KEY UPDATE pkey=pkey+10" ); sqlInsert.setInt( 1 , 1 ); sqlInsert.setInt( 2 , 2 ); sqlInsert.addBatch();   sqlInsert.setInt( 1 , 2 ); sqlInsert.setInt( 2 , 5 ); sqlInsert.addBatch();   sqlInsert.setInt( 1 , 7 ); sqlInsert.setInt( 2 , 6 ); sqlInsert.addBatch(); sqlInsert.executeBatch(); } finally { if (tmpConnection != null ) tmpConnection.close(); } } Resulting query send to database : INSERT INTO t3_dupp(col1, pkey,col2,col3,col4) VALUES (9, 1, 5, 2, 8),(9, 2, 5, 5, 8),(9, 7, 5, 6, 8) ON DUPLICATE KEY UPDATE pkey=pkey+10

          People

            diego dupin Diego Dupin
            benrowland Ben Rowland
            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.