Java CallableStatement.getResultSet方法代码示例

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testThree() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result (inout intp int) "
            + " language java reads sql data external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultOne'");

        CallableStatement cs =
            conn.prepareCall("call proc_inout_result(varone)");
        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Serve para executar um Stantment
 * @param quereSQL O sql de sera executado na base de dados
 * @param parans Os parametros que seram setados para as interogacoes
 * @return 
 */
                                            @SuppressWarnings("CallToPrintStackTrace")
public static ResultCall executeQuere (String quereSQL, Object ... parans)
{
    if(!EstadoConnexao.isValid) return null;
    if (quereSQL != null)
    {
        try 
        {
            Connection con = new  Conexao().getCon();
            CallableStatement call = mapParamsType(con, quereSQL, 1, parans);
            
            call.execute();
            return new ResultCall(call, call.getResultSet());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return null;
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Essa funcao serve para invocar a funcao da base de dados que retorna uma tabela
 * @param functionName O nome da funcao a ser invocado
 * @param campos Os campos que se quer obter da funcao {NULL ou String valida = *}
 * @param param Os parametros que serao usados para invocar a funcao {Nao é obrigatorio ter os parametros}
 * @return 
 */
@SuppressWarnings("CallToPrintStackTrace")
public static ResultSet callTableFunction  (String functionName, String campos, Object ... param)
{
    if (!EstadoConnexao.isValid) return null;
    try 
    {
        Connection con = new Conexao().getCon();
        
        campos = (campos == null||campos.length() == 0)? "*":campos;
        String interogations = (param != null && param.length>0)? createInterogation(param.length): "";
        String sql = "SELECT "+campos+" FROM TABLE("+functionName+interogations+")";
        
        CallableStatement call  = mapParamsType(con, sql, 1, param);
      
        if(call==null)
            return null;
        call.execute();
        return call.getResultSet();
    } catch (Exception e)
    {
        e.printStackTrace();
    }
    return null;
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testFive() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute(
            "create function func_table (in namep varchar(128)) returns table(cola varchar(128), colb varchar(128)) "
            + "return table(select schema_name, schema_owner from information_schema.schemata where schema_owner=namep);");

        CallableStatement cs = conn.prepareCall("call func_table('_SYSTEM')");
        boolean           isResult = cs.execute();

        assertTrue(isResult);

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "INFORMATION_SCHEMA");
        assertEquals(rs.getString(2), "_SYSTEM");
        rs.close();

        //
        isResult = st.execute("call func_table('_SYSTEM')");

        assertTrue(isResult);

        rs = st.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "INFORMATION_SCHEMA");
        assertEquals(rs.getString(2), "_SYSTEM");
        rs.close();
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	while ( !isResultSet && ps.getUpdateCount() != -1 ) {
		isResultSet = ps.getMoreResults();
	}
	return ps.getResultSet();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	// This assumes you will want to ignore any update counts
	while (!isResultSet && ps.getUpdateCount() != -1) { 
		isResultSet = ps.getMoreResults();
	}

	return ps.getResultSet();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	// This assumes you will want to ignore any update counts 
	while ( !isResultSet && ps.getUpdateCount() != -1 ) {
		isResultSet = ps.getMoreResults();
	}

	return ps.getResultSet();
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testFour() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result_two (inout intp int) "
            + " language java reads sql data dynamic result sets 2 external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultTwo'");

        CallableStatement cs =
            conn.prepareCall("call proc_inout_result_two(varone)");
        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();

        if (cs.getMoreResults()) {
            rs = cs.getResultSet();

            rs.next();
            assertEquals(rs.getString(1), "SYSTEM_LOBS");
            assertEquals(rs.getString(2), "LOBS");
            rs.close();
        }
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Populates instance of SqlResults using rows from ResultSet.
 *
 * @param cstmt
 *            Statement whose rows are to be read.
 */
public static StoreResults newInstance(CallableStatement cstmt) {
    StoreResults storeResults = new StoreResults();
    try {
        do {
            ResultSet rs = cstmt.getResultSet();
            if (rs == null) {
                return storeResults;
            }
            if (!rs.next()) { // move to first row.
                continue;
            }
            SqlResultType resultType = resultFromColumnName.get(rs.getMetaData().getColumnLabel(2));
            switch (resultType) {
                case ShardMap:
                    do {
                        storeResults.getStoreShardMaps().add(readShardMap(rs, 2));
                    }
                    while (rs.next());
                    break;
                case Shard:
                    do {
                        storeResults.getStoreShards().add(readShard(rs, 2));
                    }
                    while (rs.next());
                    break;
                case ShardMapping:
                    do {
                        storeResults.getStoreMappings().add(readMapping(rs, 2));
                    }
                    while (rs.next());
                    break;
                case ShardLocation:
                    do {
                        storeResults.getStoreLocations().add(readLocation(rs, 2));
                    }
                    while (rs.next());
                    break;
                case SchemaInfo:
                    do {
                        storeResults.getStoreSchemaInfoCollection().add(readSchemaInfo(rs, 2));
                    }
                    while (rs.next());
                    break;
                case StoreVersion:
                    do {
                        storeResults.setStoreVersion(readVersion(rs, 2));
                    }
                    while (rs.next());
                    break;
                case Operation:
                    do {
                        storeResults.getLogEntries().add(readLogEntry(rs, 2));
                    }
                    while (rs.next());
                    break;
                default:
                    break;
            }
        }
        while (cstmt.getMoreResults());

    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    return storeResults;
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testFourParams() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute(
            "create procedure proc_inout_result_two_params (inout intp int) "
            + " language java reads sql data dynamic result sets 2 external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultTwo'");

        CallableStatement cs =
            conn.prepareCall("{call proc_inout_result_two_params(?)}");

        cs.setInt(1, 0);

        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();

        if (cs.getMoreResults()) {
            rs = cs.getResultSet();

            rs.next();
            assertEquals(rs.getString(1), "SYSTEM_LOBS");
            assertEquals(rs.getString(2), "LOBS");
            rs.close();
        }

        rs = cs.executeQuery();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void testB() throws SQLException, ClassNotFoundException {

        Statement statement = con.createStatement();

        statement.execute(
            "CREATE TABLE IF NOT EXISTS users (id INTEGER, name VARCHAR(25), PRIMARY KEY(id))");
        statement.executeUpdate("INSERT INTO users VALUES(1, 'Ramiro')");
        statement.executeUpdate("INSERT INTO users VALUES(2, 'Chanukya')");

        String storedProcedure1 =
            "CREATE PROCEDURE sp_say_hi(IN greeting_p VARCHAR(10)) "
            + "READS SQL DATA DYNAMIC RESULT SETS 2 " + "BEGIN ATOMIC "
            + "DECLARE result CURSOR WITH RETURN FOR SELECT COALESCE(greeting_p, 'Hi')+' '+name as greeting FROM users FOR READ ONLY; "
            + "DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM users FOR READ ONLY; "
            + "OPEN result; " + "OPEN result1; " + "END";

        statement.execute(storedProcedure1);

        String            sqlCall           = "CALL sp_say_hi(?)";
        CallableStatement callableStatement = con.prepareCall(sqlCall);

        callableStatement.setObject("GREETING_P", "Hola");

        boolean result = callableStatement.execute();

        if (!result) {
            int value = callableStatement.getUpdateCount();

            assertTrue(value == 0);
            result = callableStatement.getMoreResults();
            assertTrue(result);
            ResultSet result1 = callableStatement.getResultSet();
            result = callableStatement.getMoreResults();
            assertTrue(result);
            ResultSet result2 = callableStatement.getResultSet();
            result = callableStatement.getMoreResults();
            assertFalse(result);
            value = callableStatement.getUpdateCount();
            assertTrue(value == -1);

        }
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#33678 - Multiple result sets not supported in
 * "streaming" mode. This fix covers both normal statements, and stored
 * procedures, with the exception of stored procedures with registered
 * OUTPUT parameters, which can't be used at all with "streaming" result
 * sets.
 * 
 * @throws Exception
 */
public void testBug33678() throws Exception {
    if (!versionMeetsMinimum(4, 1)) {
        return;
    }

    createTable("testBug33678", "(field1 INT)");

    Connection multiConn = getConnectionWithProps("allowMultiQueries=true");
    Statement multiStmt = multiConn.createStatement();

    try {
        multiStmt.setFetchSize(Integer.MIN_VALUE);

        multiStmt.execute("SELECT 1 UNION SELECT 2; INSERT INTO testBug33678 VALUES (1); UPDATE testBug33678 set field1=2; "
                + "INSERT INTO testBug33678 VALUES(3); UPDATE testBug33678 set field1=2 WHERE field1=3; UPDATE testBug33678 set field1=2; SELECT 1");
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertTrue(multiStmt.getMoreResults());
        this.rs = multiStmt.getResultSet();
        this.rs.next();
        assertEquals("1", this.rs.getString(1));

        this.rs.close();

        multiStmt.execute("INSERT INTO testBug33678 VALUES (1); INSERT INTO testBug33678 VALUES (1), (2); INSERT INTO testBug33678 VALUES (1), (2), (3)");

        assertEquals(1, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(2, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults());
        assertEquals(3, multiStmt.getUpdateCount());
        assertFalse(multiStmt.getMoreResults() && multiStmt.getUpdateCount() == -1);

        this.rs.close();

        if (versionMeetsMinimum(5, 0)) {
            createProcedure("spBug33678", "() BEGIN SELECT 1; SELECT 2; SELECT 3; END");

            CallableStatement cStmt = multiConn.prepareCall("{CALL spBug33678()}");
            cStmt.setFetchSize(Integer.MIN_VALUE);
            cStmt.execute();

            for (int i = 0; i < 2; i++) {
                if (i != 0) {
                    assertTrue(cStmt.getMoreResults());
                }

                this.rs = cStmt.getResultSet();
                assertTrue(this.rs.next());
                assertEquals(i + 1, this.rs.getInt(1));
            }
        }
    } finally {
        multiStmt.close();
        multiConn.close();
    }
}
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests functioning of output parameters.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testResultSet() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        createTable("testSpResultTbl1", "(field1 INT)");
        this.stmt.executeUpdate("INSERT INTO testSpResultTbl1 VALUES (1), (2)");
        createTable("testSpResultTbl2", "(field2 varchar(255))");
        this.stmt.executeUpdate("INSERT INTO testSpResultTbl2 VALUES ('abc'), ('def')");

        createProcedure("testSpResult", "()\nBEGIN\nSELECT field2 FROM testSpResultTbl2 WHERE field2='abc';\n"
                + "UPDATE testSpResultTbl1 SET field1=2;\nSELECT field2 FROM testSpResultTbl2 WHERE field2='def';\nend\n");

        storedProc = this.conn.prepareCall("{call testSpResult()}");

        storedProc.execute();

        this.rs = storedProc.getResultSet();

        ResultSetMetaData rsmd = this.rs.getMetaData();

        assertTrue(rsmd.getColumnCount() == 1);
        assertTrue("field2".equals(rsmd.getColumnName(1)));
        assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

        assertTrue(this.rs.next());

        assertTrue("abc".equals(this.rs.getString(1)));

        // TODO: This does not yet work in MySQL 5.0
        // assertTrue(!storedProc.getMoreResults());
        // assertTrue(storedProc.getUpdateCount() == 2);
        assertTrue(storedProc.getMoreResults());

        ResultSet nextResultSet = storedProc.getResultSet();

        rsmd = nextResultSet.getMetaData();

        assertTrue(rsmd.getColumnCount() == 1);
        assertTrue("field2".equals(rsmd.getColumnName(1)));
        assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);

        assertTrue(nextResultSet.next());

        assertTrue("def".equals(nextResultSet.getString(1)));

        nextResultSet.close();

        this.rs.close();

        storedProc.execute();
    }
}