Java 类java.sql.CallableStatement 实例源码

/**
 * Tests fix for BUG#25379 - INOUT parameters in CallableStatements get
 * doubly-escaped.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug25379() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createTable("testBug25379", "(col char(40))");

    createProcedure("sp_testBug25379", "(INOUT invalue char(255))\nBEGIN" + "\ninsert into testBug25379(col) values(invalue);\nEND");

    CallableStatement cstmt = this.conn.prepareCall("{call sp_testBug25379(?)}");
    cstmt.setString(1, "'john'");
    cstmt.executeUpdate();
    assertEquals("'john'", cstmt.getString(1));
    assertEquals("'john'", getSingleValue("testBug25379", "col", "").toString());
}

/**
 * Given a delegate, retrieves the interface that must be implemented by a
 * surrogate dynamic proxy to ensure pooling sensitive methods
 * of the delegate are not exposed directly to clients.
 *
 * @param delegate the target delegate of interest
 * @return the interface that must be implemented by a surrogate dynamic
 *         proxy to ensure pooling sensitive methods of the delegate are
 *         not exposed directly to clients
 */
protected static Class[] _computeProxiedInterface(Object delegate) {

    // NOTE:  Order is important for XXXStatement.
    if (delegate instanceof Array) {
        return arrayInterface;
    } else if (delegate instanceof Connection) {
        return connectionInterface;
    } else if (delegate instanceof CallableStatement) {
        return callableStatementInterface;
    } else if (delegate instanceof DatabaseMetaData) {
        return databaseMetaDataInterface;
    } else if (delegate instanceof PreparedStatement) {
        return preparedStatementInterface;
    } else if (delegate instanceof ResultSet) {
        return resultSetInterface;
    } else if (delegate instanceof Statement) {
        return statementInterface;
    } else {
        return null;
    }
}

/**
 * Given a delegate, retrieves the interface that must be implemented by a
 * surrogate dynamic proxy to ensure pooling sensitive methods
 * of the delegate are not exposed directly to clients.
 *
 * @param delegate the target delegate of interest
 * @return the interface that must be implemented by a surrogate dynamic
 *         proxy to ensure pooling sensitive methods of the delegate are
 *         not exposed directly to clients
 */
protected static Class[] _computeProxiedInterface(Object delegate) {

    // NOTE:  Order is important for XXXStatement.
    if (delegate instanceof Array) {
        return arrayInterface;
    } else if (delegate instanceof Connection) {
        return connectionInterface;
    } else if (delegate instanceof CallableStatement) {
        return callableStatementInterface;
    } else if (delegate instanceof DatabaseMetaData) {
        return databaseMetaDataInterface;
    } else if (delegate instanceof PreparedStatement) {
        return preparedStatementInterface;
    } else if (delegate instanceof ResultSet) {
        return resultSetInterface;
    } else if (delegate instanceof Statement) {
        return statementInterface;
    } else {
        return null;
    }
}

private void lookupDefaultSchema(DatabaseMetaData databaseMetaData) {
    try {
        CallableStatement cstmt = null;
        try {
            cstmt = databaseMetaData.getConnection().prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}");
            cstmt.registerOutParameter(1, Types.VARCHAR);
            cstmt.execute();
            this.defaultSchema = cstmt.getString(1);
        }
        finally {
            if (cstmt != null) {
                cstmt.close();
            }
        }
    }
    catch (Exception ignore) {
    }
}

/**
 * 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();
        }
    }

}

/**
 * Tests fix for BUG#21462 - JDBC (and ODBC) specifications allow
 * no-parenthesis CALL statements for procedures with no arguments, MySQL
 * server does not.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug21462() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug21462", "() BEGIN SELECT 1; END");

    CallableStatement cstmt = null;

    try {
        cstmt = this.conn.prepareCall("{CALL testBug21462}");
        cstmt.execute();
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }

}

public void testBug35199() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    createFunction("test_function", "(a varchar(40), b bigint(20), c varchar(80)) RETURNS bigint(20) LANGUAGE SQL DETERMINISTIC "
            + "MODIFIES SQL DATA COMMENT 'bbb' BEGIN RETURN 1; END; ");

    CallableStatement callable = null;
    try {
        callable = this.conn.prepareCall("{? = call test_function(?,101,?)}");
        callable.registerOutParameter(1, Types.BIGINT);

        callable.setString(2, "FOO");
        callable.setString(3, "BAR");
        callable.executeUpdate();
    } finally {
        if (callable != null) {
            callable.close();
        }
    }
}

public void setSQLXML(String parameterName, SQLXML xmlObject) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setSQLXML(parameterName, xmlObject);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setClob(String parameterName, Reader reader) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setClob(parameterName, reader);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException
{
    try
    {
        CallableStatement statement = realConnection.prepareCall(sql, resultSetType, resultSetConcurrency);
        return (CallableStatement) new CallableStatementSpy(sql, this, statement);
    }
    catch(SQLException s)
    {
        String methodCall = "prepareCall(" + sql + ", " + resultSetType + ", " + resultSetConcurrency + ")";
        reportException(methodCall, s, sql);
        throw s;
    }
}

/**
 * Support for java.sql.JDBCType/java.sql.SQLType.
 * 
 * @param parameterIndex
 * @param x
 * @param targetSqlType
 * @param scaleOrLength
 * @throws SQLException
 */
public void setObject(int parameterIndex, Object x, SQLType targetSqlType, int scaleOrLength) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setObject(parameterIndex, x, targetSqlType, scaleOrLength);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {

            Object bigdec = cs.getBigDecimal(columnIndex);
            if (cs.wasNull()) {
                return null;
            }
            else {
                return bigdec;
            }
        }

public String getNString(int parameterIndex) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getNString(parameterIndex);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return null;
}

public Time getTime(int parameterIndex) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getTime(parameterIndex);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return null;
}

public CallableStatement prepareCall(String sql, int resultSetType,
        int resultSetConcurrency) throws SQLException {

    validate();

    return this.getConnection().prepareCall(sql, resultSetType,
            resultSetConcurrency);
}

private CallableStatement createCallableStatement(int numberOfParameters) throws Exception {
    CallableStatement cStmt = null;

    String callString = "{call " + procedure + "( ";
    for (int i = 0; i < numberOfParameters - 1; i++) {
        callString = callString + "?, ";
    }
    if (numberOfParameters > 0) {
        callString = callString + "? ";
    }
    callString = callString + " )}";
    cStmt = con.prepareCall(callString);

    return cStmt;
}

public Timestamp getTimestamp(String parameterName, Calendar cal) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getTimestamp(parameterName, cal);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
    return null;
}

public void setObject(String parameterName, Object x, int targetSqlType, int scale) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setObject(parameterName, x, targetSqlType, scale);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

/**
 * Support for java.sql.JDBCType/java.sql.SQLType.
 * 
 * @param parameterIndex
 * @param x
 * @param targetSqlType
 * @param scaleOrLength
 * @throws SQLException
 */
public void setObject(int parameterIndex, Object x, SQLType targetSqlType, int scaleOrLength) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setObject(parameterIndex, x, targetSqlType, scaleOrLength);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setTime(String parameterName, Time x, Calendar cal) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setTime(parameterName, x, cal);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setBytes(String parameterName, byte[] x) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setBytes(parameterName, x);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setBytes(String parameterName, byte[] x) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setBytes(parameterName, x);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

@Test
public void testBadProcedureName() throws SQLException {
    CallableStatement cs = conn.prepareCall("{call Oopsy(?)}");
    cs.setLong(1, 99);
    try {
        cs.execute();
    } catch (SQLException e) {
        assertEquals(e.getSQLState(), SQLError.GENERAL_ERROR);
    }
}

/**
 * @see java.sql.CallableStatement#getNCharacterStream(java.lang.String)
 */
public Reader getNCharacterStream(String parameterName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getNCharacterStream(parameterName);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return null;
}

/**
 * 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();
        }
    }
}

/**
 * Tests fix for Bug#71131 - Poor error message in CallableStatement.java.
 */
public void testBug71131() throws Exception {
    createProcedure("testBug71131", "(IN r DOUBLE, OUT p DOUBLE) BEGIN SET p = 2 * r * PI(); END");
    final CallableStatement cstmt = this.conn.prepareCall("{ CALL testBug71131 (?, 5) }");
    assertThrows(SQLException.class, "Parameter p is not registered as an output parameter", new Callable<Void>() {
        public Void call() throws Exception {
            cstmt.execute();
            return null;
        }
    });
    cstmt.close();
}

public float getFloat(int parameterIndex) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getFloat(parameterIndex);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return 0;
}

public Timestamp getTimestamp(int parameterIndex, Calendar cal) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getTimestamp(parameterIndex, cal);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
    return null;
}

public void registerOutParameter(int paramIndex, int sqlType, String typeName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).registerOutParameter(paramIndex, sqlType, typeName);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

@Override
public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    Timestamp sqlTimestamp = cs.getTimestamp(columnIndex);
    if (sqlTimestamp != null) {
        return new Date(sqlTimestamp.getTime());
    }
    return null;
}

/**
 * @see java.sql.CallableStatement#getCharacterStream(int)
 */
public Reader getCharacterStream(int parameterIndex) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getCharacterStream(parameterIndex);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return null;
}

public int getSuitesCount( String whereClause ) throws DatabaseAccessException {

        Connection connection = getConnection();

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

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

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

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

public long getLong(String parameterName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getLong(parameterName);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return 0;
}

public void setRowId(String parameterName, RowId x) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setRowId(parameterName, x);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setBoolean(String parameterName, boolean x) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setBoolean(parameterName, x);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setNull(String parameterName, int sqlType) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setNull(parameterName, sqlType);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

/**
 * Support for java.sql.JDBCType/java.sql.SQLType.
 * 
 * @param parameterName
 * @param sqlType
 * @param scale
 * @throws SQLException
 */
public void registerOutParameter(String parameterName, SQLType sqlType, int scale) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).registerOutParameter(parameterName, sqlType, scale);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public void setNull(String parameterName, int sqlType, String typeName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setNull(parameterName, sqlType, typeName);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public int getInt(String parameterName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getInt(parameterName);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return 0;
}

public void setDate(String parameterName, Date x, Calendar cal) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setDate(parameterName, x, cal);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}