Java CallableStatement.setString方法代码示例

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

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

import java.sql.CallableStatement; //导入方法依赖的package包/类
public void updateMachineInfo(
                               String machineName,
                               String machineInfo,
                               boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update the info about machine with name " + machineName;

    // then start the run
    final int indexRowsInserted = 3;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_machine_info(?, ?, ?) }");
        callableStatement.setString(1, machineName);
        callableStatement.setString(2, machineInfo);
        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包/类
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.CallableStatement; //导入方法依赖的package包/类
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);
        }
    }
 

import java.sql.CallableStatement; //导入方法依赖的package包/类
public int getRunsCount( 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_runs_count(?) }");
            callableStatement.setString(1, whereClause);

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

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

import java.sql.CallableStatement; //导入方法依赖的package包/类
public static void importXml(String baseFileName){
    	Debug.info("filename = " + baseFileName);
   	try {
   		String fileReceiveSql =
   	        ApplicationProperties.getProperty("tmtbl.data.exchange.receive.file","{?= call timetable.receive_xml_file.receive_file(?, ?)}");
   		String exchangeDir = 
   	    	ApplicationProperties.getProperty("tmtbl.data.exchange.directory", "LOAD_SMASDEV");
           SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
           Connection connection = session.getJdbcConnectionAccess().obtainConnection();
           CallableStatement call = connection.prepareCall(fileReceiveSql);
           call.registerOutParameter(1, java.sql.Types.CLOB);
	    call.setString(2, exchangeDir);
           call.setString(3, baseFileName);
           call.execute();
           String response = call.getString(1);
           call.close();
           session.getJdbcConnectionAccess().releaseConnection(connection);
           if (response==null || response.length()==0) return;
           StringReader reader = new StringReader(response);
           Document document = (new SAXReader()).read(reader);
           reader.close();
           DataExchangeHelper.importDocument(document, null, null);           
	} catch (Exception e) {
		e.printStackTrace();
	} 
	
}
 

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包/类
@Override
public void insertUserActivityStatistics( 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_user_activity_statistic_by_ids(?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testCaseId);
        callableStatement.setString(2, machine);
        callableStatement.setString(3, statisticIds);
        callableStatement.setString(4, statisticValues);
        callableStatement.setTimestamp(5, new Timestamp(timestamp));

        callableStatement.execute();

    } catch (Exception e) {
        String errMsg = "Unable to insert user activity 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包/类
/**
 * Update the static information about an existing run
 *
 * @param runId
 * @param runName
 * @param osName
 * @param productName
 * @param versionName
 * @param buildName
 * @param userNote
 * @param hostName
 * @param closeConnection
 * @throws DatabaseAccessException
 */
public void updateRun(
                       int runId,
                       String runName,
                       String osName,
                       String productName,
                       String versionName,
                       String buildName,
                       String userNote,
                       String hostName,
                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update run with name '" + runName + "' and id " + runId;

    // then start the run
    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_run(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, runId);
        callableStatement.setString(2, productName);
        callableStatement.setString(3, versionName);
        callableStatement.setString(4, buildName);
        callableStatement.setString(5, runName);
        callableStatement.setString(6, osName);
        callableStatement.setString(7, userNote);
        callableStatement.setString(8, hostName);
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsUpdate) != 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包/类
public List<Statistic> getSystemStatistics( float timeOffset,
                                            String testcaseIds,
                                            String machineIds,
                                            String statsTypeIds,
                                            int utcTimeOffset,
                                            boolean dayLightSavingOn )
                                                                       throws DatabaseAccessException {

    List<Statistic> allStatistics = new ArrayList<Statistic>();

    String sqlLog = new SqlRequestFormatter().add("fdate", formatDateFromEpoch(timeOffset))
                                             .add("testcase ids", testcaseIds)
                                             .add("machine ids", machineIds)
                                             .add("stats type ids", statsTypeIds)
                                             .format();

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

        callableStatement = connection.prepareCall("{ call sp_get_system_statistics(?, ?, ?, ?) }");

        callableStatement.setString(1, formatDateFromEpoch(timeOffset));
        callableStatement.setString(2, testcaseIds);
        callableStatement.setString(3, machineIds);
        callableStatement.setString(4, statsTypeIds);

        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            Statistic statistic = new Statistic();
            statistic.statisticTypeId = rs.getInt("statsTypeId");
            statistic.name = rs.getString("statsName");
            statistic.parentName = rs.getString("statsParent");
            statistic.unit = rs.getString("statsUnit");
            statistic.value = rs.getFloat("value");
            statistic.setDate(rs.getString("statsAxis"));

            long startTimestamp = rs.getInt("statsAxisTimestamp");
            if (dayLightSavingOn) {
                startTimestamp += 3600; // add 1h to time stamp
            }
            statistic.setStartTimestamp(startTimestamp);
            statistic.setTimeOffset(utcTimeOffset);

            statistic.machineId = rs.getInt("machineId");
            statistic.testcaseId = rs.getInt("testcaseId");

            numberRecords++;
            // add the combined statistics to the others
            allStatistics.add(statistic);
        }

        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.CallableStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
 * 
 * @throws Exception
 *             if the test fails
 */

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

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

    CallableStatement storedProc = null;

    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包/类
public void updateTestcase(
                            String suiteFullName,
                            String scenarioName,
                            String scenarioDescription,
                            String testcaseName,
                            String userNote,
                            int testcaseResult,
                            int testcaseId,
                            long timestamp,
                            boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update testcase with name '" + testcaseName + "' and id " + testcaseId;

    timestamp = inUTC(timestamp);

    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_testcase(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testcaseId);
        callableStatement.setString(2, suiteFullName);
        callableStatement.setString(3, scenarioName);
        callableStatement.setString(4, scenarioDescription);
        callableStatement.setString(5, testcaseName);
        callableStatement.setString(6, userNote);
        callableStatement.setInt(7, testcaseResult);
        callableStatement.setTimestamp(8, new Timestamp(timestamp));
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsUpdate) != 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 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));

    }
}