Java CallableStatement.setInt方法代码示例

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Helper method for *SetObject* tests.
 * Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
 * 1 - `id` INT
 * 2 - `ot1` VARCHAR
 * 3 - `ot2` BLOB
 * 4 - `odt1` VARCHAR
 * 5 - `odt2` BLOB
 * 
 * @param pstmt
 * @return the row count of inserted records.
 * @throws Exception
 */
private int insertTestDataOffsetDTTypes(PreparedStatement pstmt) throws Exception {
    pstmt.setInt(1, 1);
    pstmt.setObject(2, testOffsetTime, JDBCType.VARCHAR);
    pstmt.setObject(3, testOffsetTime);
    pstmt.setObject(4, testOffsetDateTime, JDBCType.VARCHAR);
    pstmt.setObject(5, testOffsetDateTime);
    assertEquals(1, pstmt.executeUpdate());

    if (pstmt instanceof CallableStatement) {
        CallableStatement cstmt = (CallableStatement) pstmt;

        cstmt.setInt("id", 2);
        cstmt.setObject("ot1", testOffsetTime, JDBCType.VARCHAR);
        cstmt.setObject("ot2", testOffsetTime);
        cstmt.setObject("odt1", testOffsetDateTime, JDBCType.VARCHAR);
        cstmt.setObject("odt2", testOffsetDateTime);
        assertEquals(1, cstmt.executeUpdate());

        return 2;
    }

    return 1;
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#22024 - Newlines causing whitespace to span confuse
 * procedure parser when getting parameter metadata for stored procedures.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug22024() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug22024_1", "(\r\n)\r\n BEGIN SELECT 1; END");
    createProcedure("testBug22024_2", "(\r\na INT)\r\n BEGIN SELECT 1; END");

    CallableStatement cstmt = null;

    try {
        cstmt = this.conn.prepareCall("{CALL testBug22024_1()}");
        cstmt.execute();

        cstmt = this.conn.prepareCall("{CALL testBug22024_2(?)}");
        cstmt.setInt(1, 1);
        cstmt.execute();
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }

}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * End a run in the database
 *
 * @param timestamp
 * @param runId
 */
public void endRun(
                    long timestamp,
                    int runId,
                    boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to end run with id " + runId;

    final int indexRowsInserted = 3;

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_run(?, ?, ?) }");
        callableStatement.setInt(1, runId);
        callableStatement.setTimestamp(2, new Timestamp(timestamp));
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        }

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
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();
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Update meta info about an existing test scenario. This data is expected
 * to come from java method annotations
 *
 * @param testcaseId
 * @param metaKey
 * @param metaValue
 * @param closeConnection
 * @throws DatabaseAccessException
 */
public void addScenarioMetainfo(
                                 int testcaseId,
                                 String metaKey,
                                 String metaValue,
                                 boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to add scenario meta info '" + metaKey + "=" + metaValue
                          + "' to scenario for testcase with id " + testcaseId;

    final int indexRowsInserted = 4;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_add_scenario_metainfo(?, ?, ?, ?) }");
        callableStatement.setInt(1, testcaseId);
        callableStatement.setString(2, metaKey);
        callableStatement.setString(3, metaValue);
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        }

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {

    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Test for CallableStatement.executeLargeUpdate().
 * Validate update count returned and generated keys.
 */
public void testCallStmtExecuteLargeUpdate() throws Exception {
    createTable("testExecuteLargeUpdate", "(id BIGINT AUTO_INCREMENT PRIMARY KEY, n INT)");
    createProcedure("testExecuteLargeUpdateProc", "(IN n1 INT, IN n2 INT, IN n3 INT, IN n4 INT, IN n5 INT) BEGIN "
            + "INSERT INTO testExecuteLargeUpdate (n) VALUES (n1), (n2), (n3), (n4), (n5); END");

    CallableStatement testCstmt = this.conn.prepareCall("{CALL testExecuteLargeUpdateProc(?, ?, ?, ?, ?)}");
    testCstmt.setInt(1, 1);
    testCstmt.setInt(2, 2);
    testCstmt.setInt(3, 3);
    testCstmt.setInt(4, 4);
    testCstmt.setInt(5, 5);

    long count = testCstmt.executeLargeUpdate();
    assertEquals(5, count);
    assertEquals(5, testCstmt.getLargeUpdateCount());

    this.rs = testCstmt.getGeneratedKeys();

    // Although not requested, CallableStatements makes gerenated keys always available.
    ResultSetMetaData rsmd = this.rs.getMetaData();
    assertEquals(1, rsmd.getColumnCount());
    assertEquals(JDBCType.BIGINT.getVendorTypeNumber().intValue(), rsmd.getColumnType(1));
    assertEquals(20, rsmd.getColumnDisplaySize(1));

    // We can't check the generated keys as they are not returned correctly in this case (last_insert_id is missing from OK_PACKET when executing inserts
    // within a stored procedure - Bug#21792359).
    //        long generatedKey = 0;
    //        while (this.rs.next()) {
    //            assertEquals(++generatedKey, this.rs.getLong(1));
    //        }
    //        assertEquals(5, generatedKey);
    this.rs.close();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
private void callProcedure(CallableStatement cStmt, Connection c) throws SQLException {
    cStmt = c.prepareCall("{CALL testbug61203pr(?,?,?)}");
    cStmt.setFloat(1, 2);
    cStmt.setInt(2, 1);
    cStmt.setInt(3, 1);
    cStmt.registerOutParameter(1, Types.INTEGER);
    cStmt.execute();
    assertEquals(2f, cStmt.getInt(1), .001);
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void insertSystemStatistics(
                                    int testCaseId,
                                    String machine,
                                    String statisticIds,
                                    String statisticValues,
                                    long timestamp,
                                    boolean closeConnection ) throws DatabaseAccessException {

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_insert_system_statistic_by_ids(?, ?, ?, ?, ?) }");
        callableStatement.setString(3, statisticIds);
        callableStatement.setInt(1, testCaseId);
        callableStatement.setString(2, machine);
        callableStatement.setString(4, statisticValues);
        callableStatement.setTimestamp(5, new Timestamp(timestamp));

        callableStatement.execute();

    } catch (Exception e) {
        String errMsg = "Unable to insert system statistics, statistic IDs '" + statisticIds
                        + "', statistic values '" + statisticValues + "', timestamp " + timestamp;
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public int populateCheckpointSummary( int loadQueueId, String name, String transferRateUnit,
                                      boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to populate checkpoint summary '" + name + "' in load queue " + loadQueueId;

    final int indexCheckpointSummaryId = 4;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_populate_checkpoint_summary(?, ?, ?, ?) }");
        callableStatement.setInt(1, loadQueueId);
        callableStatement.setString(2, name);
        callableStatement.setString(3, transferRateUnit);
        callableStatement.registerOutParameter(indexCheckpointSummaryId, Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt(indexCheckpointSummaryId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint summary ID returned was 0");
        }

        return callableStatement.getInt(indexCheckpointSummaryId);

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#84324 - CallableStatement.extractProcedureName() not work when catalog name with dash.
 */
public void testBug84324() throws Exception {
    createDatabase("`testBug84324-db`");

    /*
     * Test procedure.
     */
    createProcedure("`testBug84324-db`.`testBug84324-proc`", "(IN a INT, INOUT b VARCHAR(100)) BEGIN SELECT a, b; END");

    final CallableStatement cstmtP = this.conn.prepareCall("CALL testBug84324-db.testBug84324-proc(?, ?)");
    ParameterMetaData pmd = cstmtP.getParameterMetaData();

    assertEquals(2, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(2));
    assertEquals(Types.VARCHAR, pmd.getParameterType(2));
    assertEquals(String.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeInOut, pmd.getParameterMode(2));

    cstmtP.setInt(1, 1);
    cstmtP.setString(2, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtP.execute();
            return null;
        }
    }); // Although the procedure metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();

    /*
     * Test function.
     */
    createFunction("`testBug84324-db`.`testBug84324-func`", "(a INT, b VARCHAR(123)) RETURNS INT DETERMINISTIC BEGIN RETURN a + LENGTH(b); END");

    final CallableStatement cstmtF = this.conn.prepareCall("{? = CALL testBug84324-db.testBug84324-func(?, ?)}");
    pmd = cstmtF.getParameterMetaData();

    assertEquals(3, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeOut, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("INT", pmd.getParameterTypeName(2));
    assertEquals(Types.INTEGER, pmd.getParameterType(2));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(2));
    // 3rd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(3));
    assertEquals(Types.VARCHAR, pmd.getParameterType(3));
    assertEquals(String.class.getName(), pmd.getParameterClassName(3));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(3));

    cstmtF.registerOutParameter(1, Types.INTEGER);
    cstmtF.setInt(2, 1);
    cstmtF.setString(3, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtF.execute();
            return null;
        }
    }); // Although the function metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();
    cstmtF.close();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests functioning of inout parameters
 * 
 * @throws Exception
 *             if the test fails
 */

public void testInOutParams() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        createProcedure("testInOutParam", "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\nSET z = p2 + 1;\nSET p2 = z;\n"
                + "SELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

        storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

        storedProc.setString(1, "abcd");
        storedProc.setInt(2, 4);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        assertEquals(5, storedProc.getInt(2));

    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#57022 - cannot execute a store procedure with output
 * parameters Problem was in CallableStatement.java, private void
 * determineParameterTypes() throws SQLException if (procName.indexOf(".")
 * == -1) { useCatalog = true; } The fix will be to "sanitize" db.sp call
 * just like in noAccessToProcedureBodies.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug57022() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    String originalCatalog = this.conn.getCatalog();

    createDatabase("bug57022");

    createProcedure("bug57022.procbug57022", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    CallableStatement cStmt = null;
    try {
        cStmt = this.conn.prepareCall("{call `bug57022`.`procbug57022`(?, ?)}");
        cStmt.setInt(1, 5);
        cStmt.registerOutParameter(2, Types.INTEGER);

        cStmt.execute();
        assertEquals(6, cStmt.getInt(2));
        cStmt.clearParameters();
        cStmt.close();

        this.conn.setCatalog("bug57022");
        cStmt = this.conn.prepareCall("{call bug57022.procbug57022(?, ?)}");
        cStmt.setInt(1, 5);
        cStmt.registerOutParameter(2, Types.INTEGER);

        cStmt.execute();
        assertEquals(6, cStmt.getInt(2));
        cStmt.clearParameters();
        cStmt.close();

        this.conn.setCatalog("mysql");
        cStmt = this.conn.prepareCall("{call `bug57022`.`procbug57022`(?, ?)}");
        cStmt.setInt(1, 5);
        cStmt.registerOutParameter(2, Types.INTEGER);

        cStmt.execute();
        assertEquals(6, cStmt.getInt(2));
    } finally {
        if (cStmt != null) {
            cStmt.clearParameters();
            cStmt.close();
        }
        this.conn.setCatalog(originalCatalog);
    }

}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void endCheckpoint(
                           CheckpointInfo runningCheckpointInfo,
                           long endTimestamp,
                           long transferSize,
                           int result,
                           boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to end checkpoint with name '" + runningCheckpointInfo.getName()
                          + "', checkpoint summary id " + runningCheckpointInfo.getCheckpointSummaryId()
                          + ", id " + runningCheckpointInfo.getCheckpointId();

    endTimestamp = inUTC(endTimestamp);

    final int indexRowsInserted = 8;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_checkpoint(?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, runningCheckpointInfo.getCheckpointSummaryId());
        callableStatement.setInt(2, runningCheckpointInfo.getCheckpointId());
        callableStatement.setInt(3,
                                 (int) (endTimestamp - runningCheckpointInfo.getStartTimestamp()));
        callableStatement.setLong(4, transferSize);
        callableStatement.setInt(5, result);
        callableStatement.setInt(6, checkpointLogLevel.toInt());
        callableStatement.setTimestamp(7, new Timestamp(endTimestamp));
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#61150 - First call to SP
 * fails with "No Database Selected"
 * The workaround introduced in DatabaseMetaData.getCallStmtParameterTypes
 * to fix the bug in server where SHOW CREATE PROCEDURE was not respecting
 * lower-case table names is misbehaving when connection is not attached to
 * database and on non-casesensitive OS.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug61150() throws Exception {
    NonRegisteringDriver driver = new NonRegisteringDriver();
    Properties oldProps = driver.parseURL(BaseTestCase.dbUrl, null);

    String host = driver.host(oldProps);
    int port = driver.port(oldProps);
    StringBuilder newUrlToTestNoDB = new StringBuilder("jdbc:mysql://");
    if (host != null) {
        newUrlToTestNoDB.append(host);
    }
    newUrlToTestNoDB.append(":").append(port).append("/");

    Statement savedSt = this.stmt;

    Properties props = getHostFreePropertiesFromTestsuiteUrl();
    props.remove(NonRegisteringDriver.DBNAME_PROPERTY_KEY);
    Connection conn1 = DriverManager.getConnection(newUrlToTestNoDB.toString(), props);

    this.stmt = conn1.createStatement();
    createDatabase("TST1");
    createProcedure("TST1.PROC", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    CallableStatement cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("TST1");
    cStmt = null;
    cStmt = conn1.prepareCall("{call TST1.PROC(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("mysql");
    cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    this.stmt = savedSt;
}