Java Code Examples for java.sql.CallableStatement#addBatch()

@Test
public void testBatch() throws SQLException, MalformedURLException {
    CallableStatement preparedStatement = multiHostConnection.prepareCall("UPDATE test SET a = ? WHERE b = ?");
    preparedStatement.setShort(1, (short) 12);
    preparedStatement.setTime(2, new Time(System.currentTimeMillis()));
    preparedStatement.addBatch();
    int[] resultSet = preparedStatement.executeBatch();
    preparedStatement.clearBatch();

    verify(mysqlCallableStatement).executeBatch();
    verify(mysqlCallableStatement).addBatch();
    verify(mysqlCallableStatement).clearBatch();

    assertThat(segmentStorage.getTraceSegments().size(), is(1));
    TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
    List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
    assertThat(spans.size(), is(1));
    assertDBSpan(spans.get(0), "Mysql/JDBI/CallableStatement/executeBatch", "");
}
 

public void testChangeIsoLevelProcedureSqlCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_SQL(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setString(1, isoLevels[i].getSqlName());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 

public void testChangeIsoLevelProcedureJdbcCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_JDBC(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setInt(1, isoLevels[i].getIsoLevel());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 

public CallableStatement createCallableStatement(Connection conn, String sql, StatementParameters[] parametersList, DalHints hints) throws Exception {
	CallableStatement statement = conn.prepareCall(sql);

	applyHints(statement, hints);

	Map<Integer, String> paramsInSql = null;
	boolean needReorderParameters = needReorderParameters(sql, parametersList);
	if(needReorderParameters)
		paramsInSql = extractParamsFromCallString(sql);

	for (StatementParameters parameters : parametersList) {
		if (needReorderParameters) {
			reorderParameters(parameters, paramsInSql);
		}
		setParameter(statement, parameters);
		statement.addBatch();
	}

	return statement;
}
 

public void testChangeIsoLevelProcedureSqlCallableBatch() throws SQLException {
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_SQL(?)");
    for (int i = 0; i < isoLevels.length; ++i) {
        cs.setString(1, isoLevels[i].getSqlName());
        cs.addBatch();
    }
    try {
        cs.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(cs.getConnection());
    cs.close();
}
 

public CallableStatement createCallableStatement(Connection conn,  String sql, List<Parameter>[] parametersList, Hints hints) throws Exception {
	CallableStatement statement = conn.prepareCall(sql);
	
	applyHints(statement, hints);
	
	for(List<Parameter> parameters: parametersList) {
		setParameter(statement, parameters);
		statement.addBatch();
	}

	return statement;
}
 

private void executeBatchedStoredProc(Connection c) throws Exception {
    this.stmt.executeUpdate("TRUNCATE TABLE testBatchTable");

    CallableStatement storedProc = c.prepareCall("{call testBatch(?)}");

    try {
        int numBatches = 300;

        for (int i = 0; i < numBatches; i++) {
            storedProc.setInt(1, i + 1);
            storedProc.addBatch();
        }

        int[] counts = storedProc.executeBatch();

        assertEquals(numBatches, counts.length);

        for (int i = 0; i < numBatches; i++) {
            assertEquals(1, counts[i]);
        }

        this.rs = this.stmt.executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");

        for (int i = 0; i < numBatches; i++) {
            assertTrue(this.rs.next());
            assertEquals(i + 1, this.rs.getInt(1));
        }
    } finally {

        if (storedProc != null) {
            storedProc.close();
        }
    }
}
 

/**
 * Tests fix for BUG#28689 - CallableStatement.executeBatch() doesn't work
 * when connection property "noAccessToProcedureBodies" has been set to
 * "true".
 * 
 * The fix involves changing the behavior of "noAccessToProcedureBodies", in
 * that the driver will now report all paramters as "IN" paramters but allow
 * callers to call registerOutParameter() on them.
 * 
 * @throws Exception
 */
public void testBug28689() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return; // no stored procedures
    }

    createTable("testBug28689", "(" +

            "`id` int(11) NOT NULL auto_increment,`usuario` varchar(255) default NULL,PRIMARY KEY  (`id`))");

    this.stmt.executeUpdate("INSERT INTO testBug28689 (usuario) VALUES ('AAAAAA')");

    createProcedure("sp_testBug28689", "(tid INT)\nBEGIN\nUPDATE testBug28689 SET usuario = 'BBBBBB' WHERE id = tid;\nEND");

    Connection noProcedureBodiesConn = getConnectionWithProps("noAccessToProcedureBodies=true");
    CallableStatement cStmt = null;

    try {
        cStmt = noProcedureBodiesConn.prepareCall("{CALL sp_testBug28689(?)}");
        cStmt.setInt(1, 1);
        cStmt.addBatch();
        cStmt.executeBatch();

        assertEquals("BBBBBB", getSingleIndexedValueWithQuery(noProcedureBodiesConn, 1, "SELECT `usuario` FROM testBug28689 WHERE id=1"));
    } finally {
        if (cStmt != null) {
            cStmt.close();
        }

        if (noProcedureBodiesConn != null) {
            noProcedureBodiesConn.close();
        }
    }
}
 

private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 

private void executeBatchedStoredProc(Connection c) throws Exception {
    this.stmt.executeUpdate("TRUNCATE TABLE testBatchTable");

    CallableStatement storedProc = c.prepareCall("{call testBatch(?)}");

    try {
        int numBatches = 300;

        for (int i = 0; i < numBatches; i++) {
            storedProc.setInt(1, i + 1);
            storedProc.addBatch();
        }

        int[] counts = storedProc.executeBatch();

        assertEquals(numBatches, counts.length);

        for (int i = 0; i < numBatches; i++) {
            assertEquals(1, counts[i]);
        }

        this.rs = this.stmt.executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");

        for (int i = 0; i < numBatches; i++) {
            assertTrue(this.rs.next());
            assertEquals(i + 1, this.rs.getInt(1));
        }
    } finally {

        if (storedProc != null) {
            storedProc.close();
        }
    }
}
 

private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 

public void testCallableStatementWithOutputParamBatch() 
throws SQLException {

    println("Negative Callable Statement: " +
        "callable statement with output parameters in the batch");
    Statement s = createStatement();

    s.execute("CREATE PROCEDURE " +
        "takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
        "EXTERNAL NAME '" + this.getClass().getName() + ".takesString'" +
    " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement cs = prepareCall("call takesString(?,?)");
    cs.registerOutParameter(1, Types.CHAR);
    cs.setInt(2, Types.INTEGER);
    try
    {
        cs.addBatch();
        if (usingEmbedded())
            fail("Expected to see error XJ04C");
        else if (usingDerbyNetClient()) {
            executeBatchCallableStatement(cs);       
        }
    }
    catch (SQLException sqle)
    {
        // Check to be sure the exception is callback related
        assertSQLState("XJ04C", sqle);
    }

    cs.close();
    s.execute("drop procedure takesString");
    s.close();
    rollback();
}
 

private void executeBatchedStoredProc(Connection c) throws Exception {
    this.stmt.executeUpdate("TRUNCATE TABLE testBatchTable");

    CallableStatement storedProc = c.prepareCall("{call testBatch(?)}");

    try {
        int numBatches = 300;

        for (int i = 0; i < numBatches; i++) {
            storedProc.setInt(1, i + 1);
            storedProc.addBatch();
        }

        int[] counts = storedProc.executeBatch();

        assertEquals(numBatches, counts.length);

        for (int i = 0; i < numBatches; i++) {
            assertEquals(1, counts[i]);
        }

        this.rs = this.stmt.executeQuery("SELECT field1 FROM testBatchTable ORDER BY field1 ASC");

        for (int i = 0; i < numBatches; i++) {
            assertTrue(this.rs.next());
            assertEquals(i + 1, this.rs.getInt(1));
        }
    } finally {

        if (storedProc != null) {
            storedProc.close();
        }
    }
}
 

/**
 * Tests fix for BUG#28689 - CallableStatement.executeBatch() doesn't work
 * when connection property "noAccessToProcedureBodies" has been set to
 * "true".
 * 
 * The fix involves changing the behavior of "noAccessToProcedureBodies", in
 * that the driver will now report all paramters as "IN" paramters but allow
 * callers to call registerOutParameter() on them.
 * 
 * @throws Exception
 */
public void testBug28689() throws Exception {
    createTable("testBug28689", "(" +

            "`id` int(11) NOT NULL auto_increment,`usuario` varchar(255) default NULL,PRIMARY KEY  (`id`))");

    this.stmt.executeUpdate("INSERT INTO testBug28689 (usuario) VALUES ('AAAAAA')");

    createProcedure("sp_testBug28689", "(tid INT)\nBEGIN\nUPDATE testBug28689 SET usuario = 'BBBBBB' WHERE id = tid;\nEND");

    Connection noProcedureBodiesConn = getConnectionWithProps("noAccessToProcedureBodies=true");
    CallableStatement cStmt = null;

    try {
        cStmt = noProcedureBodiesConn.prepareCall("{CALL sp_testBug28689(?)}");
        cStmt.setInt(1, 1);
        cStmt.addBatch();
        cStmt.executeBatch();

        assertEquals("BBBBBB", getSingleIndexedValueWithQuery(noProcedureBodiesConn, 1, "SELECT `usuario` FROM testBug28689 WHERE id=1"));
    } finally {
        if (cStmt != null) {
            cStmt.close();
        }

        if (noProcedureBodiesConn != null) {
            noProcedureBodiesConn.close();
        }
    }
}
 

private void execProcBug49831(Connection c) throws Exception {
    CallableStatement cstmt = c.prepareCall("{call pTestBug49831(?)}");
    cstmt.setObject(1, "abc", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.setObject(1, "def", Types.VARCHAR, 32);
    cstmt.addBatch();
    cstmt.executeBatch();
    assertEquals(2, getRowCount("testBug49831"));
    this.rs = this.stmt.executeQuery("SELECT * from testBug49831 ORDER BY VAL ASC");
    this.rs.next();
    assertEquals("abc", this.rs.getString(1));
    this.rs.next();
    assertEquals("def", this.rs.getString(1));
}
 

@Override
public void batch(Statement statement) throws SQLException {
  CallableStatement cs = (CallableStatement) statement;
  cs.addBatch();
}
 

/**
 * Batches up calls to a SQL procedure that updates a value in a table.
 * Uses DriverManager and Batch calls, so requires JDBC 2 support.
 * @throws SQLException 
 */
public void xtestBatchUpdate() throws SQLException
{
    // Setup table data
    Statement stmt = createStatement();
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");

    // Setup batch to modify value to 10 * the id (makes verification easy).
    CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
    cstmt.setInt(1,2);  // Id 2's value will be updated to 20.
    cstmt.setInt(2,20);
    cstmt.addBatch();
    cstmt.setInt(1,3);  // Id 3's value will be updated to 30.
    cstmt.setInt(2,30);
    cstmt.addBatch();
    cstmt.setInt(1,4);  // Two rows will be updated to 40 for id 4.
    cstmt.setInt(2,40);
    cstmt.addBatch();
    cstmt.setInt(1,5);  // No rows updated (no id 5).
    cstmt.setInt(2,50);
    cstmt.addBatch();

    int[] updateCount=null;
    try {
        updateCount = cstmt.executeBatch();
        assertEquals("updateCount length", 4, updateCount.length);

        for(int i=0; i< updateCount.length; i++){
            if (usingEmbedded()) {
                assertEquals("Batch updateCount", 0, updateCount[0]);
            }
            else if (usingDerbyNetClient()) {
                assertEquals("Batch updateCount", -1, updateCount[0]);
            }
        }
    } catch (BatchUpdateException b) {
        assertSQLState("Unexpected SQL State", b.getSQLState(), b);
    }

    // Retrieve the updated values and verify they are correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
    assertNotNull("SELECT from BATCH_TABLE", rs);

    while (rs.next())
    {
        assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
    }
}
 

@Override
public void batch(Statement statement) throws SQLException {
  CallableStatement cs = (CallableStatement) statement;
  cs.addBatch();
}
 

/**
 * Batches up calls to a SQL procedure that updates a value in a table.
 * Uses DriverManager and Batch calls, so requires JDBC 2 support.
 * @throws SQLException 
 */
public void xtestBatchUpdate() throws SQLException
{
    // Setup table data
    Statement stmt = createStatement();
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");

    // Setup batch to modify value to 10 * the id (makes verification easy).
    CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
    cstmt.setInt(1,2);  // Id 2's value will be updated to 20.
    cstmt.setInt(2,20);
    cstmt.addBatch();
    cstmt.setInt(1,3);  // Id 3's value will be updated to 30.
    cstmt.setInt(2,30);
    cstmt.addBatch();
    cstmt.setInt(1,4);  // Two rows will be updated to 40 for id 4.
    cstmt.setInt(2,40);
    cstmt.addBatch();
    cstmt.setInt(1,5);  // No rows updated (no id 5).
    cstmt.setInt(2,50);
    cstmt.addBatch();

    int[] updateCount=null;
    try {
        updateCount = cstmt.executeBatch();
        assertEquals("updateCount length", 4, updateCount.length);

        for(int i=0; i< updateCount.length; i++){
            if (usingEmbedded()) {
                assertEquals("Batch updateCount", 0, updateCount[0]);
            }
            else if (usingDerbyNetClient()) {
                assertEquals("Batch updateCount", -1, updateCount[0]);
            }
        }
    } catch (BatchUpdateException b) {
        assertSQLState("Unexpected SQL State", b.getSQLState(), b);
    }

    // Retrieve the updated values and verify they are correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
    assertNotNull("SELECT from BATCH_TABLE", rs);

    while (rs.next())
    {
        assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
    }
}
 

/**
 * Batches up calls to a SQL procedure that updates a value in a table.
 * Uses DriverManager and Batch calls, so requires JDBC 2 support.
 * @throws SQLException 
 */
public void xtestBatchUpdate() throws SQLException
{
    // Setup table data
    Statement stmt = createStatement();
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
    stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");

    // Setup batch to modify value to 10 * the id (makes verification easy).
    CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
    cstmt.setInt(1,2);  // Id 2's value will be updated to 20.
    cstmt.setInt(2,20);
    cstmt.addBatch();
    cstmt.setInt(1,3);  // Id 3's value will be updated to 30.
    cstmt.setInt(2,30);
    cstmt.addBatch();
    cstmt.setInt(1,4);  // Two rows will be updated to 40 for id 4.
    cstmt.setInt(2,40);
    cstmt.addBatch();
    cstmt.setInt(1,5);  // No rows updated (no id 5).
    cstmt.setInt(2,50);
    cstmt.addBatch();

    int[] updateCount=null;
    try {
        updateCount = cstmt.executeBatch();
        assertEquals("updateCount length", 4, updateCount.length);

        for(int i=0; i< updateCount.length; i++){
            if (usingEmbedded()) {
                assertEquals("Batch updateCount", 0, updateCount[0]);
            }
            else if (usingDerbyNetClient()) {
                assertEquals("Batch updateCount", -1, updateCount[0]);
            }
        }
    } catch (BatchUpdateException b) {
        assertSQLState("Unexpected SQL State", b.getSQLState(), b);
    }

    // Retrieve the updated values and verify they are correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
    assertNotNull("SELECT from BATCH_TABLE", rs);

    while (rs.next())
    {
        assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
    }
}