Java Connection.prepareCall方法代码示例

import java.sql.Connection; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#12417 - stored procedure catalog name is case-sensitive
 * on Windows (this is actually a server bug, but we have a workaround in
 * place for it now).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug12417() throws Exception {
    if (serverSupportsStoredProcedures() && isServerRunningOnWindows()) {

        createProcedure("testBug12417", "()\nBEGIN\nSELECT 1;end\n");

        Connection ucCatalogConn = null;

        try {
            ucCatalogConn = getConnectionWithProps((Properties) null);
            ucCatalogConn.setCatalog(this.conn.getCatalog().toUpperCase());
            ucCatalogConn.prepareCall("{call testBug12417()}");
        } finally {
            if (ucCatalogConn != null) {
                ucCatalogConn.close();
            }
        }
    }
}
 

import java.sql.Connection; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#78961 - Can't call MySQL procedure with InOut parameters in Fabric environment.
 * 
 * Although this is a Fabric related bug we are able reproduce it using a couple of multi-host connections.
 */
public void testBug78961() throws Exception {
    createProcedure("testBug78961", "(IN c1 FLOAT, IN c2 FLOAT, OUT h FLOAT, INOUT t FLOAT) BEGIN SET h = SQRT(c1 * c1 + c2 * c2); SET t = t + h; END;");

    Connection highLevelConn = getLoadBalancedConnection(null);
    assertTrue(highLevelConn.getClass().getName().startsWith("com.sun.proxy") || highLevelConn.getClass().getName().startsWith("$Proxy"));

    Connection lowLevelConn = getMasterSlaveReplicationConnection(null);
    // This simulates the behavior from Fabric connections that are causing the problem.
    ((ReplicationConnection) lowLevelConn).setProxy((MySQLConnection) highLevelConn);

    CallableStatement cstmt = lowLevelConn.prepareCall("{CALL testBug78961 (?, ?, ?, ?)}");
    cstmt.setFloat(1, 3.0f);
    cstmt.setFloat(2, 4.0f);
    cstmt.setFloat(4, 5.0f);
    cstmt.registerOutParameter(3, Types.FLOAT);
    cstmt.registerOutParameter(4, Types.FLOAT);
    cstmt.execute();

    assertEquals(5.0f, cstmt.getFloat(3));
    assertEquals(10.0f, cstmt.getFloat(4));
}
 

import java.sql.Connection; //导入方法依赖的package包/类
public void deleteTestcase(
                            List<Object> objectsToDelete ) throws DatabaseAccessException {

    StringBuilder testcaseIds = new StringBuilder();
    for (Object obj : objectsToDelete) {
        testcaseIds.append( ((Testcase) obj).testcaseId);
        testcaseIds.append(",");
    }
    testcaseIds.delete(testcaseIds.length() - 1, testcaseIds.length());

    final String errMsg = "Unable to delete testcase(s) with id " + testcaseIds;

    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_delete_testcase(?) }");
        callableStatement.setString(1, testcaseIds.toString());
        callableStatement.execute();
    } catch (SQLException e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        DbUtils.close(connection, callableStatement);
    }
}
 

import java.sql.Connection; //导入方法依赖的package包/类
public int getSuiteMessagesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_suite_messages_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int messagesCount = 0;
            if (rs.next()) {
                messagesCount = rs.getInt("messagesCount");
            }
            logQuerySuccess(sqlLog, "suite messages count", messagesCount);

            return messagesCount;
        } catch (Exception e) {
            throw new DatabaseAccessException("Error when " + sqlLog, e);
        } finally {
            DbUtils.closeResultSet(rs);
            DbUtils.close(connection, callableStatement);
        }
    }
 

import java.sql.Connection; //导入方法依赖的package包/类
protected void sendRequest(Document request) throws IOException {
    try {
        StringWriter writer = new StringWriter();
        (new XMLWriter(writer,OutputFormat.createPrettyPrint())).write(request);
        writer.flush(); writer.close();
        SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
        Connection connection = session.getJdbcConnectionAccess().obtainConnection();
        try {
            CallableStatement call = connection.prepareCall(iRequestSql);
            call.setString(1, writer.getBuffer().toString());
            call.execute();
            call.close();
        } finally {
        	session.getJdbcConnectionAccess().releaseConnection(connection);
        }
    } catch (Exception e) {
        sLog.error("Unable to send request: "+e.getMessage(),e);
    } finally {
        _RootDAO.closeCurrentThreadSessions();
    }
}
 

import java.sql.Connection; //导入方法依赖的package包/类
public Map<String, Integer>
        getNumberOfCheckpointsPerQueue( String testcaseIds ) throws DatabaseAccessException {

    Map<String, Integer> allStatistics = new HashMap<String, Integer>();

    String sqlLog = new SqlRequestFormatter().add("testcase ids", testcaseIds).format();

    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_number_of_checkpoints_per_queue(?) }");
        callableStatement.setString(1, testcaseIds);

        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            String name = rs.getString("name");
            int queueNumbers = rs.getInt("numberOfQueue");
            allStatistics.put(name, queueNumbers);
        }

        logQuerySuccess(sqlLog, "system statistics", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return allStatistics;
}
 

import java.sql.Connection; //导入方法依赖的package包/类
private void testInterfaceImplementation(Connection connToCheck) throws Exception {
    Method[] dbmdMethods = java.sql.DatabaseMetaData.class.getMethods();

    // can't do this statically, as we return different
    // implementations depending on JDBC version
    DatabaseMetaData dbmd = connToCheck.getMetaData();

    checkInterfaceImplemented(dbmdMethods, dbmd.getClass(), dbmd);

    Statement stmtToCheck = connToCheck.createStatement();

    checkInterfaceImplemented(java.sql.Statement.class.getMethods(), stmtToCheck.getClass(), stmtToCheck);

    PreparedStatement pStmtToCheck = connToCheck.prepareStatement("SELECT 1");
    ParameterMetaData paramMd = pStmtToCheck.getParameterMetaData();

    checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck);
    checkInterfaceImplemented(java.sql.ParameterMetaData.class.getMethods(), paramMd.getClass(), paramMd);

    pStmtToCheck = ((com.mysql.jdbc.Connection) connToCheck).serverPrepareStatement("SELECT 1");

    checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck);
    ResultSet toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1");
    checkInterfaceImplemented(java.sql.ResultSet.class.getMethods(), toCheckRs.getClass(), toCheckRs);
    toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1");
    checkInterfaceImplemented(java.sql.ResultSetMetaData.class.getMethods(), toCheckRs.getMetaData().getClass(), toCheckRs.getMetaData());

    if (versionMeetsMinimum(5, 0, 0)) {
        createProcedure("interfaceImpl", "(IN p1 INT)\nBEGIN\nSELECT 1;\nEND");

        CallableStatement cstmt = connToCheck.prepareCall("{CALL interfaceImpl(?)}");

        checkInterfaceImplemented(java.sql.CallableStatement.class.getMethods(), cstmt.getClass(), cstmt);
    }
    checkInterfaceImplemented(java.sql.Connection.class.getMethods(), connToCheck.getClass(), connToCheck);
}
 

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

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

import java.sql.Connection; //导入方法依赖的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.Connection; //导入方法依赖的package包/类
public int getTestcasesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_testcases_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int testcasesCount = 0;
            while (rs.next()) {
                testcasesCount = rs.getInt("testcasesCount");
                logQuerySuccess(sqlLog, "test cases", testcasesCount);
                break;
            }

            return testcasesCount;
        } catch (Exception e) {
            throw new DatabaseAccessException("Error when " + sqlLog, e);
        } finally {
            DbUtils.closeResultSet(rs);
            DbUtils.close(connection, callableStatement);
        }
    }
 

import java.sql.Connection; //导入方法依赖的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;
}
 

import java.sql.Connection; //导入方法依赖的package包/类
public int populateSystemStatisticDefinition(
                                              String name,
                                              String parentName,
                                              String internalName,
                                              String unit,
                                              String params ) throws DatabaseAccessException {

    if (parentName == null) {
        parentName = "";
    }
    if (internalName == null) {
        internalName = "";
    }

    CallableStatement callableStatement = null;
    Connection con = null;
    boolean useLocalConnection = false;
    try {
        if (connection == null || connection.isClosed()) {
            // connection not set externally so use new connection only for
            // this method invocation
            useLocalConnection = true;
            con = getConnection();
        } else {
            useLocalConnection = false;
            con = connection;
        }
        final int statisticId = 6;
        callableStatement = con.prepareCall("{ call sp_populate_system_statistic_definition(?, ?, ?, ?, ?, ?) }");
        callableStatement.setString(1, parentName);
        callableStatement.setString(2, internalName);
        callableStatement.setString(3, name);
        callableStatement.setString(4, unit);
        callableStatement.setString(5, params);
        callableStatement.registerOutParameter(statisticId, Types.INTEGER);

        callableStatement.execute();

        return callableStatement.getInt(statisticId);

    } catch (Exception e) {
        String errMsg = "Unable to populate statistic '" + name + "' with unit '" + unit
                        + "' and params '" + params + "'";
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        DbUtils.closeStatement(callableStatement);
        if (useLocalConnection) {
            DbUtils.closeConnection(con);
        }
    }
}
 

import java.sql.Connection; //导入方法依赖的package包/类
@BeforeClass
public static void setUpClosedObjects() throws Exception {
  // (Note: Can't use JdbcTest's connect(...) for this test class.)

  final Connection connToClose =
      new Driver().connect("jdbc:dremio:zk=local",
                           JdbcAssert.getDefaultProperties());
  final Connection connToKeep =
      new Driver().connect("jdbc:dremio:zk=local",
                           JdbcAssert.getDefaultProperties());

  final Statement plainStmtToClose = connToKeep.createStatement();
  final Statement plainStmtToKeep = connToKeep.createStatement();
  final PreparedStatement preparedStmtToClose =
      connToKeep.prepareStatement("VALUES 'PreparedStatement query'");
  try {
    connToKeep.prepareCall("VALUES 'CallableStatement query'");
    fail("Test seems to be out of date.  Was prepareCall(...) implemented?");
  }
  catch (SQLException | UnsupportedOperationException e) {
    // Expected.
  }

  final ResultSet resultSetToCloseOnStmtToClose =
      plainStmtToClose.executeQuery("VALUES 'plain Statement query'");
  resultSetToCloseOnStmtToClose.next();
  final ResultSet resultSetToCloseOnStmtToKeep =
      plainStmtToKeep.executeQuery("VALUES 'plain Statement query'");
  resultSetToCloseOnStmtToKeep.next();

  final ResultSetMetaData rsmdForClosedStmt =
      resultSetToCloseOnStmtToKeep.getMetaData();
  final ResultSetMetaData rsmdForOpenStmt =
      resultSetToCloseOnStmtToClose.getMetaData();

  final DatabaseMetaData dbmd = connToClose.getMetaData();

  connToClose.close();
  plainStmtToClose.close();
  preparedStmtToClose.close();
  resultSetToCloseOnStmtToClose.close();
  resultSetToCloseOnStmtToKeep.close();

  closedConn = connToClose;
  openConn = connToKeep;
  closedPlainStmtOfOpenConn = plainStmtToClose;
  closedPreparedStmtOfOpenConn = preparedStmtToClose;
  closedResultSetOfClosedStmt = resultSetToCloseOnStmtToClose;
  closedResultSetOfOpenStmt = resultSetToCloseOnStmtToKeep;
  resultSetMetaDataOfClosedResultSet = rsmdForOpenStmt;
  resultSetMetaDataOfClosedStmt = rsmdForClosedStmt;
  databaseMetaDataOfClosedConn = dbmd;

  // Self-check that member variables are set (and objects are in right open
  // or closed state):
  assertTrue("Test setup error", closedConn.isClosed());
  assertFalse("Test setup error", openConn.isClosed());
  assertTrue("Test setup error", closedPlainStmtOfOpenConn.isClosed());
  assertTrue("Test setup error", closedPreparedStmtOfOpenConn.isClosed());
  assertTrue("Test setup error", closedResultSetOfClosedStmt.isClosed());
  assertTrue("Test setup error", closedResultSetOfOpenStmt.isClosed());
  // (No ResultSetMetaData.isClosed() or DatabaseMetaData.isClosed():)
  assertNotNull("Test setup error", resultSetMetaDataOfClosedResultSet);
  assertNotNull("Test setup error", resultSetMetaDataOfClosedStmt);
  assertNotNull("Test setup error", databaseMetaDataOfClosedConn);
}
 

import java.sql.Connection; //导入方法依赖的package包/类
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
	return con.prepareCall(this.callString);
}