Java Code Examples for java.sql.Statement.executeBatch()

The following are Jave code examples for showing how to use executeBatch() of the java.sql.Statement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
Example 1
Project: ProyectoPacientes   File: StatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Tests fix for Bug#51704, rewritten batched statements don't honor escape
 * processing flag of Statement that they are created for
 */
public void testBug51704() throws Exception {
    createTable("testBug51704", "(field1 TIMESTAMP)");
    Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
    Statement rewriteStmt = rewriteConn.createStatement();

    try {
        rewriteStmt.setEscapeProcessing(false);

        for (int i = 0; i < 20; i++) {
            rewriteStmt.addBatch("INSERT INTO testBug51704 VALUES ({tsp '2002-11-12 10:00:00'})");
        }

        rewriteStmt.executeBatch(); // this should pass, because mysqld doesn't validate any escape sequences, 
                                   // it just strips them, where our escape processor validates them

        Statement batchStmt = this.conn.createStatement();
        batchStmt.setEscapeProcessing(false);
        batchStmt.addBatch("INSERT INTO testBug51704 VALUES ({tsp '2002-11-12 10:00:00'})");
        batchStmt.executeBatch(); // same here
    } finally {
        rewriteConn.close();
    }
}
 
Example 2
Project: tqdev-metrics   File: InstrumentedDataSourceTest.java   Source Code and License Vote up 6 votes
/**
 * Should measure statement.
 *
 * @throws SQLException
 *             the SQL exception
 */
@Test
public void shouldMeasureStatement() throws SQLException {
	Statement statements[] = { dataSource.getConnection().createStatement(),
			dataSource.getConnection().createStatement(1, 1), dataSource.getConnection().createStatement(1, 1, 1) };
	for (Statement statement : statements) {
		statement.execute("select");
		statement.execute("select", 1);
		statement.execute("select", new int[] {});
		statement.execute("select", new String[] {});
		statement.executeQuery("select");
		statement.executeUpdate("select");
		statement.executeUpdate("select", 1);
		statement.executeUpdate("select", new int[] {});
		statement.executeUpdate("select", new String[] {});
		statement.executeLargeUpdate("select");
		statement.executeLargeUpdate("select", 1);
		statement.executeLargeUpdate("select", new int[] {});
		statement.executeLargeUpdate("select", new String[] {});
		statement.addBatch("select");
		statement.executeBatch();
		statement.executeLargeBatch();
	}
	assertThat(registry.get("jdbc.Statement.Invocations", "select")).isEqualTo(1L * 15 * statements.length);
	assertThat(registry.get("jdbc.Statement.Durations", "select")).isEqualTo(123456789L * 15 * statements.length);
}
 
Example 3
Project: maxwell-sink   File: MySqlDbWriter.java   Source Code and License Vote up 6 votes
public void flush(final List<String> sqlBatch) throws SQLException {
    if (sqlBatch == null || sqlBatch.isEmpty()) {
        return;
    }
    Connection connection = connectionHolder.getValidConnection();
    connection.setAutoCommit(false);

    Statement statement = connection.createStatement();
    for (String sql : sqlBatch) {
        log.info("===>>>statement addBatch sql:{}", sql);
        statement.addBatch(sql);
    }
    int[] updateCountArr = statement.executeBatch();
    if (updateCountArr.length != sqlBatch.size()) {
        throw new ConnectException(String.format("updateCountArr size:(%d) not equals to sqlBatch size:(%d)", updateCountArr.length, sqlBatch.size()));
    }
    connection.commit();
    statement.close();
}
 
Example 4
Project: KITE   File: ResultHandler.java   Source Code and License Vote up 6 votes
/**
 * Updates the status for configuration & test when they're done.
 *
 * @param connection a JDBC connection to the database.
 * @param configName name of the configuration.
 * @param testName name of the test.
 * @param resultTableName name of the appropriate result table.
 * @param timeStamp start time of the configuration.
 * @param endTime end time of the configuration.
 */
private void updateStatus(Connection connection, String configName, String testName,
    String resultTableName, long timeStamp, long endTime) throws SQLException {
  String query1 = "UPDATE TESTS SET STATUS='DONE', END_TIME=" + endTime + " WHERE TEST_NAME='"
      + testName + "' AND RESULT_TABLE='" + resultTableName + "';";

  String query2 = "UPDATE CONFIG_EXECUTION SET STATUS='DONE', END_TIME=" + endTime
      + " WHERE CONFIG_NAME='" + configName + "' AND START_TIME=" + timeStamp + " ;";
  Statement statement = null;
  try {
    statement = connection.createStatement();
    statement.addBatch(query1);
    statement.addBatch(query2);
    if (log.isDebugEnabled())
      log.debug("Executing Status Update: " + query1);
    if (log.isDebugEnabled())
      log.debug("Executing Status Update: " + query2);
    statement.executeBatch();
  } finally {
    Utility.closeDBResources(statement, null);
  }
}
 
Example 5
Project: KITE   File: ResultHandler.java   Source Code and License Vote up 6 votes
/**
 * Updates the status for configuration & test when they're done.
 *
 * @param connection a JDBC connection to the database.
 * @param browserList list of browsers to put in the BROWSERS Table if not already exist
 */
private void putInBrowserTable(Connection connection, List<Browser> browserList)
    throws SQLException {
  List<String> queryList = new ArrayList<>();
  for (Browser browser : browserList) {
    queryList.add(
        "INSERT INTO BROWSERS(NAME, VERSION, PLATFORM) " + "SELECT '" + browser.getName() + "','"
            + browser.getVersion() + "','" + browser.getPlatform() + "' " + "WHERE NOT EXISTS( "
            + "SELECT 1 FROM BROWSERS " + "WHERE NAME='" + browser.getName() + "' AND VERSION='"
            + browser.getVersion() + "' AND PLATFORM='" + browser.getPlatform() + "');");
  }
  Statement statement = null;
  try {
    statement = connection.createStatement();
    for (String query : queryList) {
      statement.addBatch(query);
      if (log.isDebugEnabled())
        log.debug("Executing browser entry Update: " + query);
      System.out.println("Executing browser entry Update: " + query);
    }
    statement.executeBatch();
  } finally {
    Utility.closeDBResources(statement, null);
  }
}
 
Example 6
Project: OpenVertretung   File: StatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Tests fix for Bug#51704, rewritten batched statements don't honor escape
 * processing flag of Statement that they are created for
 */
public void testBug51704() throws Exception {
    createTable("testBug51704", "(field1 TIMESTAMP)");
    Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
    Statement rewriteStmt = rewriteConn.createStatement();

    try {
        rewriteStmt.setEscapeProcessing(false);

        for (int i = 0; i < 20; i++) {
            rewriteStmt.addBatch("INSERT INTO testBug51704 VALUES ({tsp '2002-11-12 10:00:00'})");
        }

        rewriteStmt.executeBatch(); // this should pass, because mysqld doesn't validate any escape sequences, 
                                   // it just strips them, where our escape processor validates them

        Statement batchStmt = this.conn.createStatement();
        batchStmt.setEscapeProcessing(false);
        batchStmt.addBatch("INSERT INTO testBug51704 VALUES ({tsp '2002-11-12 10:00:00'})");
        batchStmt.executeBatch(); // same here
    } finally {
        rewriteConn.close();
    }
}
 
Example 7
Project: s-store   File: TestJDBCQueries.java   Source Code and License Vote up 6 votes
@Test
public void testQueryBatch() throws SQLException
{
    Statement batch = conn.createStatement();
    for (Data d : data) {
        String q = String.format("update %s set value='%s'", d.tablename, "whatever");
        batch.addBatch(q);
    }
    try {
        int[] resultCodes = batch.executeBatch();
        assertEquals(data.length, resultCodes.length);
        int total_cnt = 0;
        for (int i = 0; i < data.length; ++i) {
            assertEquals(data[i].good.length, resultCodes[i]);
            total_cnt += data[i].good.length;
        }
        //Test update count
        assertEquals(total_cnt, batch.getUpdateCount());
    }
    catch(SQLException e) {
        System.err.printf("ERROR: %s\n", e.getMessage());
        fail();
    }
}
 
Example 8
Project: maxwell-sink   File: MySqlDbWriter.java   Source Code and License Vote up 6 votes
public void flush(final List<String> sqlBatch) throws SQLException {
	if (sqlBatch == null || sqlBatch.isEmpty()) {
		return;
	}
	Connection connection = connectionHolder.getValidConnection();
	connection.setAutoCommit(false);

	Statement statement = connection.createStatement();
	for (String sql : sqlBatch) {
		statement.addBatch(sql);
	}
	int[] updateCountArr = statement.executeBatch();
	if (updateCountArr.length != sqlBatch.size()) {
		throw new ConnectException(String.format("updateCountArr size:(%d) not equals to sqlBatch size:(%d)",
				updateCountArr.length, sqlBatch.size()));
	}
	connection.commit();
	statement.close();
}
 
Example 9
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License Vote up 5 votes
/**
 * Tests fix for BUG#30550 - executeBatch() on an empty batch when there are
 * no elements in the batch causes a divide-by-zero error when rewriting is
 * enabled.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug30550() throws Exception {
    createTable("testBug30550", "(field1 int)");

    Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
    PreparedStatement batchPStmt = null;
    Statement batchStmt = null;

    try {
        batchStmt = rewriteConn.createStatement();
        assertEquals(0, batchStmt.executeBatch().length);

        batchStmt.addBatch("INSERT INTO testBug30550 VALUES (1)");
        int[] counts = batchStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchStmt.executeBatch().length);

        batchPStmt = rewriteConn.prepareStatement("INSERT INTO testBug30550 VALUES (?)");
        batchPStmt.setInt(1, 1);
        assertEquals(0, batchPStmt.executeBatch().length);
        batchPStmt.addBatch();
        counts = batchPStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchPStmt.executeBatch().length);
    } finally {
        if (batchPStmt != null) {
            batchPStmt.close();
        }

        if (batchStmt != null) {
            batchStmt.close();
        }
        if (rewriteConn != null) {
            rewriteConn.close();
        }
    }
}
 
Example 10
Project: mycat-src-1.6.1-RELEASE   File: StandBatchInsertTest.java   Source Code and License Vote up 5 votes
public static void testJDBCBatchInsert(Connection theCon)
		throws SQLException {
	theCon.setAutoCommit(false);
	Statement stmt = theCon.createStatement();
	int batchSize = 10;
	for (int i = 0; i < batchSize; i++) {
		String sql = "insert into travelrecord (id,user_id,traveldate,fee,days) values("
				+ i + ",'wang','2014-01-05',510.5,3)";
		stmt.addBatch(sql);
	}
	stmt.executeBatch();
	theCon.commit();
     System.out.println("succees");
}
 
Example 11
Project: bdf2   File: DbJdbcUtils.java   Source Code and License Vote up 5 votes
public static int[] executeUpdate(Connection conn, String[] sqls) throws Exception {
	boolean isShowSql = false;
	List<String> sqlList = new ArrayList<String>();
	for (String sql : sqls) {
		if (StringUtils.hasText(sql)) {
			sqlList.add(sql);
		}
	}
	String[] sqlArray = sqlList.toArray(new String[] {});
	if (isShowSql) {
		for (String s : sqlArray) {
			System.out.println(s);
		}
	}
	Statement st = null;
	int[] rowsAffected = new int[] { sqlArray.length };
	try {
		conn.setAutoCommit(true);
		st = conn.createStatement();
		if (DbJdbcUtils.supportsBatchUpdates(conn)) {
			for (String currentSql : sqlArray) {
				st.addBatch(currentSql);
			}
			rowsAffected = st.executeBatch();
		} else {
			for (int i = 0; i < sqlArray.length; i++) {
				if (!st.execute(sqlArray[i])) {
					rowsAffected[i] = st.getUpdateCount();
				}
			}
		}
	} catch (SQLException e) {
		throw new SQLException(e);
	} finally {
		DbJdbcUtils.closeStatement(st);
	}
	return rowsAffected;
}
 
Example 12
Project: jdbc-middleware   File: HsqlUtil.java   Source Code and License Vote up 5 votes
public static JDBCDataSource setupDataSource() throws SQLException {
    final JDBCDataSource jdbcDataSource = new JDBCDataSource();
    jdbcDataSource.setDatabase("jdbc:hsqldb:mem:db");
    Statement statement = jdbcDataSource.getConnection().createStatement();
    statement.addBatch("DROP SCHEMA public CASCADE");
    statement.executeBatch();
    return jdbcDataSource;
}
 
Example 13
Project: dble   File: StandBatchInsertTest.java   Source Code and License Vote up 5 votes
public static void testJDBCBatchInsert(Connection theCon)
        throws SQLException {
    theCon.setAutoCommit(false);
    Statement stmt = theCon.createStatement();
    int batchSize = 10;
    for (int i = 0; i < batchSize; i++) {
        String sql = "insert into travelrecord (id,user_id,traveldate,fee,days) values("
                + i + ",'wang','2014-01-05',510.5,3)";
        stmt.addBatch(sql);
    }
    stmt.executeBatch();
    theCon.commit();
    System.out.println("succees");
}
 
Example 14
Project: jkami   File: DataMapper.java   Source Code and License Vote up 5 votes
/**
 * 批量执行
 * 
 * @param sqls
 *            sql集合
 * @param params
 *            参数
 * @return 变更集合
 */
public Object executeBatchUpdate(String sqls, Object... params) {
    Connection con = getCon();
    try {
        if (sqls.indexOf(SEMICOLON) == -1) {
            return executeUpdate(sqls, params);
        }
        String[] sqlss = sqls.replaceAll("\r|\n", EMPTY).split(SEMICOLON);
        int length = sqlss.length;
        Statement st = con.createStatement();
        for (int i = 0; i < length; i++) {
            String sql = sqlss[i];
            if (logger.isDebugEnabled()) {
                logger.debug("addBatch: " + sql);
            }
            st.addBatch(sql);
        }
        if (logger.isDebugEnabled()) {
            logger.debug("executeBatchUpdate size: " + length);
        }
        int[] batch = st.executeBatch();
        st.close();
        return batch;
    } catch (Exception e) {
        throw new JkException(e);
    } finally {
        doReleaseConnection(con);
    }
}
 
Example 15
Project: OpenVertretung   File: StatementRegressionTest.java   Source Code and License Vote up 5 votes
/**
 * Tests fix for BUG#30550 - executeBatch() on an empty batch when there are
 * no elements in the batch causes a divide-by-zero error when rewriting is
 * enabled.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug30550() throws Exception {
    createTable("testBug30550", "(field1 int)");

    Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
    PreparedStatement batchPStmt = null;
    Statement batchStmt = null;

    try {
        batchStmt = rewriteConn.createStatement();
        assertEquals(0, batchStmt.executeBatch().length);

        batchStmt.addBatch("INSERT INTO testBug30550 VALUES (1)");
        int[] counts = batchStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchStmt.executeBatch().length);

        batchPStmt = rewriteConn.prepareStatement("INSERT INTO testBug30550 VALUES (?)");
        batchPStmt.setInt(1, 1);
        assertEquals(0, batchPStmt.executeBatch().length);
        batchPStmt.addBatch();
        counts = batchPStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchPStmt.executeBatch().length);
    } finally {
        if (batchPStmt != null) {
            batchPStmt.close();
        }

        if (batchStmt != null) {
            batchStmt.close();
        }
        if (rewriteConn != null) {
            rewriteConn.close();
        }
    }
}
 
Example 16
Project: ts-benchmark   File: TsfileDB.java   Source Code and License Vote up 4 votes
@Override
	public Status updatePoints(List<TsPoint> points) {
		//timestamp->device->sensor
		long costTime=0;
//		int count=0;
		Connection connection = null;
		Statement statement = null;
		try {
			connection=getConnection();
			statement=connection.createStatement();
			if(points!=null){
				StringBuilder sc=new StringBuilder();
				for(TsPoint point:points){
					sc.append("update ");
					sc.append(ROOT_SERIES_NAME);
					sc.append(".");
					sc.append(point.getDeviceCode());
					sc.append(".");
					sc.append(point.getSensorCode());
					sc.append(" set value=");
					sc.append(point.getValue());
					sc.append(" where");
					sc.append(" time=");
					sc.append(point.getTimestamp());
					statement.addBatch(sc.toString());
					sc.setLength(0);
				}
			}
			long startTime=System.nanoTime();
			statement.executeBatch();//批量更新
			statement.clearBatch();
			long endTime=System.nanoTime();
			costTime=endTime-startTime;
		}catch(Exception e){
			return Status.FAILED(-1);
		}finally{
			closeStatement(statement);
			closeConnection(connection);
		}
		return Status.OK(costTime);
	}
 
Example 17
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for BUG#77681 - rewrite replace sql like insert when rewriteBatchedStatements=true (contribution)
 * 
 * When using 'rewriteBatchedStatements=true' we rewrite several batched statements into one single query by extending its VALUES clause. Although INSERT
 * REPLACE have the same syntax, this wasn't happening for REPLACE statements.
 * 
 * This tests the number of queries actually sent to server when rewriteBatchedStatements is used and not by using a StatementInterceptor. The test is
 * repeated for server side prepared statements. Without the fix, this test fails while checking the number of expected REPLACE queries.
 */
public void testBug77681() throws Exception {
    createTable("testBug77681", "(id INT, txt VARCHAR(50), PRIMARY KEY (id))");

    Properties props = new Properties();
    props.setProperty("statementInterceptors", TestBug77681StatementInterceptor.class.getName());

    for (int tst = 0; tst < 4; tst++) {
        props.setProperty("useServerPrepStmts", Boolean.toString((tst & 0x1) != 0));
        props.setProperty("rewriteBatchedStatements", Boolean.toString((tst & 0x2) != 0));
        Connection testConn = getConnectionWithProps(props);

        PreparedStatement testPstmt = testConn.prepareStatement("INSERT INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 1);
        testPstmt.setString(2, "one");
        testPstmt.addBatch();
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "two");
        testPstmt.addBatch();
        testPstmt.setInt(1, 3);
        testPstmt.setString(2, "three");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "four");
        testPstmt.addBatch();
        testPstmt.setInt(1, 5);
        testPstmt.setString(2, "five");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        testPstmt = testConn.prepareStatement("REPLACE INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "TWO");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "FOUR");
        testPstmt.addBatch();
        testPstmt.setInt(1, 6);
        testPstmt.setString(2, "SIX");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        Statement testStmt = testConn.createStatement();
        testStmt.clearBatch();
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (7, 'seven')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (8, 'eight')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (9, 'nine')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (10, 'ten')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (11, 'eleven')");
        testStmt.executeBatch();

        testStmt.clearBatch();
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (8, 'EIGHT')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (10, 'TEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (12, 'TWELVE')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (14, 'FOURTEEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (16, 'SIXTEEN')");
        testStmt.executeBatch();

        this.stmt.executeUpdate("DELETE FROM testBug77681");
    }
}
 
Example 18
Project: OpenVertretung   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for BUG#77681 - rewrite replace sql like insert when rewriteBatchedStatements=true (contribution)
 * 
 * When using 'rewriteBatchedStatements=true' we rewrite several batched statements into one single query by extending its VALUES clause. Although INSERT
 * REPLACE have the same syntax, this wasn't happening for REPLACE statements.
 * 
 * This tests the number of queries actually sent to server when rewriteBatchedStatements is used and not by using a StatementInterceptor. The test is
 * repeated for server side prepared statements. Without the fix, this test fails while checking the number of expected REPLACE queries.
 */
public void testBug77681() throws Exception {
    createTable("testBug77681", "(id INT, txt VARCHAR(50), PRIMARY KEY (id))");

    Properties props = new Properties();
    props.setProperty("statementInterceptors", TestBug77681StatementInterceptor.class.getName());

    for (int tst = 0; tst < 4; tst++) {
        props.setProperty("useServerPrepStmts", Boolean.toString((tst & 0x1) != 0));
        props.setProperty("rewriteBatchedStatements", Boolean.toString((tst & 0x2) != 0));
        Connection testConn = getConnectionWithProps(props);

        PreparedStatement testPstmt = testConn.prepareStatement("INSERT INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 1);
        testPstmt.setString(2, "one");
        testPstmt.addBatch();
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "two");
        testPstmt.addBatch();
        testPstmt.setInt(1, 3);
        testPstmt.setString(2, "three");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "four");
        testPstmt.addBatch();
        testPstmt.setInt(1, 5);
        testPstmt.setString(2, "five");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        testPstmt = testConn.prepareStatement("REPLACE INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "TWO");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "FOUR");
        testPstmt.addBatch();
        testPstmt.setInt(1, 6);
        testPstmt.setString(2, "SIX");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        Statement testStmt = testConn.createStatement();
        testStmt.clearBatch();
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (7, 'seven')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (8, 'eight')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (9, 'nine')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (10, 'ten')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (11, 'eleven')");
        testStmt.executeBatch();

        testStmt.clearBatch();
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (8, 'EIGHT')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (10, 'TEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (12, 'TWELVE')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (14, 'FOURTEEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (16, 'SIXTEEN')");
        testStmt.executeBatch();

        this.stmt.executeUpdate("DELETE FROM testBug77681");
    }
}
 
Example 19
Project: the-vigilantes   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
public void testBug39956() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    ResultSet enginesRs = this.conn.createStatement().executeQuery("SHOW ENGINES");

    while (enginesRs.next()) {
        if ("YES".equalsIgnoreCase(enginesRs.getString("Support")) || "DEFAULT".equalsIgnoreCase(enginesRs.getString("Support"))) {

            String engineName = enginesRs.getString("Engine");

            if ("CSV".equalsIgnoreCase(engineName) || "BLACKHOLE".equalsIgnoreCase(engineName) || "FEDERATED".equalsIgnoreCase(engineName)
                    || "MRG_MYISAM".equalsIgnoreCase(engineName) || "PARTITION".equalsIgnoreCase(engineName) || "EXAMPLE".equalsIgnoreCase(engineName)
                    || "PERFORMANCE_SCHEMA".equalsIgnoreCase(engineName) || engineName.endsWith("_SCHEMA")) {
                continue; // not supported
            }

            if ("ARCHIVE".equalsIgnoreCase(engineName) && !versionMeetsMinimum(5, 1, 6)) {
                continue;
            }

            String tableName = "testBug39956_" + engineName;

            Connection twoConn = getConnectionWithProps("sessionVariables=auto_increment_increment=2");

            try {
                for (int i = 0; i < 2; i++) {
                    createTable(tableName, "(k int primary key auto_increment, p varchar(4)) ENGINE=" + engineName);

                    ((com.mysql.jdbc.Connection) twoConn).setRewriteBatchedStatements(i == 1);

                    this.pstmt = twoConn.prepareStatement("INSERT INTO " + tableName + " (p) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
                    this.pstmt.setString(1, "a");
                    this.pstmt.addBatch();
                    this.pstmt.setString(1, "b");
                    this.pstmt.addBatch();
                    this.pstmt.executeBatch();

                    this.rs = this.pstmt.getGeneratedKeys();

                    this.rs.next();
                    assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), 1, this.rs.getInt(1));
                    this.rs.next();
                    assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), 3, this.rs.getInt(1));

                    createTable(tableName, "(k int primary key auto_increment, p varchar(4)) ENGINE=" + engineName);
                    Statement twoStmt = twoConn.createStatement();
                    for (int j = 0; j < 10; j++) {
                        twoStmt.addBatch("INSERT INTO " + tableName + " (p) VALUES ('" + j + "')");
                    }

                    twoStmt.executeBatch(); // No getGeneratedKeys() support in JDBC spec, but we allow it...might have to rewrite test if/when we don't
                    this.rs = twoStmt.getGeneratedKeys();

                    int key = 1;

                    for (int j = 0; j < 10; j++) {
                        this.rs.next();
                        assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), key, this.rs.getInt(1));
                        key += 2;
                    }
                }
            } finally {
                if (twoConn != null) {
                    twoConn.close();
                }
            }
        }
    }
}
 
Example 20
Project: the-vigilantes   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for BUG#77681 - rewrite replace sql like insert when rewriteBatchedStatements=true (contribution)
 * 
 * When using 'rewriteBatchedStatements=true' we rewrite several batched statements into one single query by extending its VALUES clause. Although INSERT
 * REPLACE have the same syntax, this wasn't happening for REPLACE statements.
 * 
 * This tests the number of queries actually sent to server when rewriteBatchedStatements is used and not by using a StatementInterceptor. The test is
 * repeated for server side prepared statements. Without the fix, this test fails while checking the number of expected REPLACE queries.
 */
public void testBug77681() throws Exception {
    createTable("testBug77681", "(id INT, txt VARCHAR(50), PRIMARY KEY (id))");

    Properties props = new Properties();
    props.setProperty("statementInterceptors", TestBug77681StatementInterceptor.class.getName());

    for (int tst = 0; tst < 4; tst++) {
        props.setProperty("useServerPrepStmts", Boolean.toString((tst & 0x1) != 0));
        props.setProperty("rewriteBatchedStatements", Boolean.toString((tst & 0x2) != 0));
        Connection testConn = getConnectionWithProps(props);

        PreparedStatement testPstmt = testConn.prepareStatement("INSERT INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 1);
        testPstmt.setString(2, "one");
        testPstmt.addBatch();
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "two");
        testPstmt.addBatch();
        testPstmt.setInt(1, 3);
        testPstmt.setString(2, "three");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "four");
        testPstmt.addBatch();
        testPstmt.setInt(1, 5);
        testPstmt.setString(2, "five");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        testPstmt = testConn.prepareStatement("REPLACE INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "TWO");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "FOUR");
        testPstmt.addBatch();
        testPstmt.setInt(1, 6);
        testPstmt.setString(2, "SIX");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        Statement testStmt = testConn.createStatement();
        testStmt.clearBatch();
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (7, 'seven')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (8, 'eight')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (9, 'nine')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (10, 'ten')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (11, 'eleven')");
        testStmt.executeBatch();

        testStmt.clearBatch();
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (8, 'EIGHT')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (10, 'TEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (12, 'TWELVE')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (14, 'FOURTEEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (16, 'SIXTEEN')");
        testStmt.executeBatch();

        this.stmt.executeUpdate("DELETE FROM testBug77681");
    }
}