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

The following examples show how to use java.sql.CallableStatement#addBatch() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: CacheSessionDataTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
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();
}
 
Example 2
Source File: SWCallableStatementTest.java    From skywalking with Apache License 2.0 6 votes vote down vote up
@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", "");
}
 
Example 3
Source File: DalStatementCreator.java    From dal with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 4
Source File: CacheSessionDataTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example 5
Source File: CacheSessionDataTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example 6
Source File: CallableStatementTest.java    From r-course with MIT License 5 votes vote down vote up
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();
        }
    }
}
 
Example 7
Source File: CallableStatementRegressionTest.java    From FoxTelem with GNU General Public License v3.0 5 votes vote down vote up
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));
}
 
Example 8
Source File: CallableStatementRegressionTest.java    From FoxTelem with GNU General Public License v3.0 5 votes vote down vote up
/**
 * 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();
        }
    }
}
 
Example 9
Source File: CallableStatementTest.java    From FoxTelem with GNU General Public License v3.0 5 votes vote down vote up
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();
        }
    }
}
 
Example 10
Source File: BatchUpdateTest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
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();
}
 
Example 11
Source File: CallableStatementRegressionTest.java    From Komondor with GNU General Public License v3.0 5 votes vote down vote up
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));
}
 
Example 12
Source File: CallableStatementTest.java    From Komondor with GNU General Public License v3.0 5 votes vote down vote up
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();
        }
    }
}
 
Example 13
Source File: CallableStatementRegressionTest.java    From r-course with MIT License 5 votes vote down vote up
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));
}
 
Example 14
Source File: CallableStatementRegressionTest.java    From r-course with MIT License 5 votes vote down vote up
/**
 * 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();
        }
    }
}
 
Example 15
Source File: DalStatementCreator.java    From das with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 16
Source File: CallableTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * 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));
    }
}
 
Example 17
Source File: CallableStatementHandler.java    From mybatis with Apache License 2.0 4 votes vote down vote up
@Override
public void batch(Statement statement) throws SQLException {
  CallableStatement cs = (CallableStatement) statement;
  cs.addBatch();
}
 
Example 18
Source File: CallableStatementHandler.java    From mybaties with Apache License 2.0 4 votes vote down vote up
@Override
public void batch(Statement statement) throws SQLException {
  CallableStatement cs = (CallableStatement) statement;
  cs.addBatch();
}
 
Example 19
Source File: CallableTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
/**
 * 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));
    }
}
 
Example 20
Source File: CallableTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * 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));
    }
}