Example usage for java.sql CallableStatement getResultSet

List of usage examples for java.sql CallableStatement getResultSet

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. getResultSet

Introduction

In this page you can find the example usage for java.sql CallableStatement getResultSet.

Prototype

ResultSet getResultSet() throws SQLException;

Source Link

Document

Retrieves the current result as a ResultSet object.

Usage

From source file:org.s23m.cell.repository.RelationalDatabaseRepository.java

@SuppressWarnings("unchecked")
private Map getContainmentTreeUUIDs(final String uuid) throws SQLException {
    final Connection connection = DriverManager
            .getConnection("jdbc:apache:commons:dbcp:" + REPOSITORY_CONNECTION_POOL_ID); //$NON-NLS-1$
    connection.setAutoCommit(false);/*from w w  w .  j  a  va  2s.co  m*/
    final CallableStatement fetchContainedInstanceUUIDs = execueteUUIDQuery("getContainedInstanceUUIDs", uuid);
    Timer.getInstance().start("getContainmentTreeUUIDs"); //$NON-NLS-1$
    final ResultSet rs = fetchContainedInstanceUUIDs.getResultSet();
    final ListOrderedMap uuidMap = new ListOrderedMap();
    while (rs.next()) {
        addUUIDs(rs, uuidMap);
    }
    System.err.println("Time taken for doing getContainmentTreeUUIDs: " //$NON-NLS-1$
            + Timer.getInstance().time("getContainmentTreeUUIDs", TimeUnit.MILLISECONDS));
    rs.close();
    fetchContainedInstanceUUIDs.close();
    returnConnectionToPool(connection);
    return uuidMap;
}

From source file:org.s23m.cell.repository.RelationalDatabaseRepository.java

@SuppressWarnings({ "unchecked", "rawtypes" })
private Map getDependentInstanceUUIDs(final String uuid) throws SQLException {
    final CallableStatement fetchContainedInstanceUUIDs = execueteUUIDQuery("getDependentInstanceUUIDs", uuid);
    Timer.getInstance().start("getDependentInstanceUUIDs"); //$NON-NLS-1$
    final ResultSet rs = fetchContainedInstanceUUIDs.getResultSet();
    final ListOrderedMap uuidMap = new ListOrderedMap();
    while (rs.next()) {
        final String urr = rs.getString("urr");
        if (urr != null && !uuidMap.containsKey(urr)) {
            uuidMap.put(urr, urr);//from  w w w .j  a v  a 2  s  .c o  m
        }
    }
    System.err.println("Time taken for doing getDependentInstanceUUIDs: " //$NON-NLS-1$
            + Timer.getInstance().time("getDependentInstanceUUIDs", TimeUnit.MILLISECONDS));
    rs.close();
    fetchContainedInstanceUUIDs.close();
    return uuidMap;
}

From source file:com.cws.esolutions.core.dao.impl.ApplicationDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplication(java.lang.String)
 *//*from   www .ja  v  a2s  .  com*/
public synchronized List<Object> getApplication(final String value) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#getApplication(final String value) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("value: {}", value);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL getApplicationData(?)}");
        stmt.setString(1, value);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();

                responseData = new ArrayList<Object>(Arrays.asList(resultSet.getString(1), // APPLICATION_GUID
                        resultSet.getString(2), // APPLICATION_NAME
                        resultSet.getDouble(3), // APPLICATION_VERSION
                        resultSet.getString(4), // INSTALLATION_PATH
                        resultSet.getString(5), // PACKAGE_LOCATION
                        resultSet.getString(6), // PACKAGE_INSTALLER
                        resultSet.getString(7), // INSTALLER_OPTIONS
                        resultSet.getString(8), // LOGS_DIRECTORY
                        resultSet.getString(9) // PLATFORM_GUID
                ));

                if (DEBUG) {
                    DEBUGGER.debug("data: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainOtpSecret(java.lang.String, java.lang.String)
 *//*from  ww  w . j  a va  2  s.c  om*/
public synchronized String obtainOtpSecret(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainOtpSecret(final String userName, final String userGuid) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userName);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    String otpSecret = null;
    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL getOtpSecret(?, ?)}");
        stmt.setString(1, userGuid); // guid
        stmt.setString(2, userName);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();

                otpSecret = resultSet.getString(1);
            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return otpSecret;
}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerList(String) throws SQLException
 *//*from  w  w  w.j  a  va 2  s .c om*/
@Override
public List<Object[]> getCareerList(final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerList(final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", lang);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain connection to application datasource");
        }

        stmt = sqlConn.prepareCall("{ CALL getCareersList(?) }");
        stmt.setString(1, lang);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

        if (DEBUG) {
            DEBUGGER.debug("ResultSet: {}", resultSet);
        }

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object[]>();

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // REQ_ID
                        resultSet.getString(2), // POST_DATE
                        resultSet.getString(3), // UNPOST_DATE
                        resultSet.getString(4), // JOB_TITLE
                        resultSet.getBigDecimal(5), // JOB_SHORT_DESC
                        resultSet.getString(6), // JOB_DESCRIPTION
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    if (DEBUG) {
        DEBUGGER.debug("results: {}", results);
    }

    return results;
}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerData(String, String) throws SQLException
 *///  www.j  a va2  s .  c o m
@Override
public List<Object> getCareerData(final String reqId, final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerData(final int reqId, final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", reqId);
        DEBUGGER.debug("Value: {}", lang);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    List<Object> results = null;
    CallableStatement stmt = null;

    try {
        sqlConn = this.dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain connection to application datasource");
        }

        stmt = sqlConn.prepareCall("{ CALL getCareerData(?, ?) }");
        stmt.setString(1, reqId);
        stmt.setString(2, lang);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

        if (DEBUG) {
            DEBUGGER.debug("ResultSet: {}", resultSet);
        }

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object>();

            while (resultSet.next()) {
                results.add(resultSet.getString(1)); // REQ_ID
                results.add(resultSet.getDate(2)); // POST_DATE
                results.add(resultSet.getDate(3)); // UNPOST_DATE
                results.add(resultSet.getString(4)); // JOB_TITLE
                results.add(resultSet.getString(5)); // JOB_SHORT_DESC
                results.add(resultSet.getString(6)); // JOB_DESCRIPTION
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    if (DEBUG) {
        DEBUGGER.debug("results: {}", results);
    }

    return results;
}

From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listServicesForGroup(java.lang.String)
 *///  ww w.  ja  v a 2 s . com
public synchronized List<String> listServicesForGroup(final String groupName) throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME
            + "#listServicesForGroup(final String groupName) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", groupName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> serviceList = null;

    try {
        sqlConn = dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL listServicesForGroup(?)}");
        stmt.setString(1, groupName);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();
                serviceList = new ArrayList<String>();

                for (String service : StringUtils.split(resultSet.getString(1), ",")) // single row response
                {
                    if (DEBUG) {
                        DEBUGGER.debug("Service: {}", service);
                    }

                    serviceList.add(service);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", serviceList);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return serviceList;
}

From source file:com.cws.esolutions.core.dao.impl.ApplicationDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#listApplications(int)
 *//*w w w. ja  v  a2 s  .c o m*/
public synchronized List<String[]> listApplications(final int startRow) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#listApplications(final int startRow) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", startRow);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL listApplications(?, ?)}");
        stmt.setInt(1, startRow);
        stmt.registerOutParameter(2, Types.INTEGER);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // APPLICATION_GUID
                            resultSet.getString(2), // APPLICATION_NAME
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("Value: {}", (Object[]) data);
                    }

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#listServices(int)
 *///www  . j a v a2s.  c  o  m
public synchronized List<String[]> listServices(final int startRow) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME + "#listServices(final int startRow) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", startRow);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL listServices(?)}");
        stmt.setInt(1, startRow);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // SERVICE_TYPE
                            resultSet.getString(3), // NAME
                    };

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainSecurityData(java.lang.String, java.lang.String)
 *//*  w w  w. j av  a 2  s  .c o m*/
public synchronized List<String> obtainSecurityData(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainSecurityData(final String userName, final String userGuid) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userName);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> userSecurity = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL getUserByAttribute(?, ?)}");
        stmt.setString(1, userName); // guid
        stmt.setInt(2, 0); // count

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();

                while (resultSet.next()) {
                    if (StringUtils.equals(resultSet.getString(2), userName)) {
                        String cn = resultSet.getString(1);
                        String username = resultSet.getString(2);

                        if (DEBUG) {
                            DEBUGGER.debug("String: {}", cn);
                            DEBUGGER.debug("String: {}", username);
                        }

                        resultSet.close();
                        stmt.close();

                        // found the user we want
                        stmt = sqlConn.prepareCall("{ CALL getSecurityQuestions(?, ?) }");
                        stmt.setString(1, username); // common name
                        stmt.setString(2, cn);

                        if (DEBUG) {
                            DEBUGGER.debug("CallableStatement: {}", stmt);
                        }

                        if (stmt.execute()) {
                            resultSet = stmt.getResultSet();

                            if (DEBUG) {
                                DEBUGGER.debug("ResultSet: {}", resultSet);
                            }

                            if (resultSet.next()) {
                                userSecurity = new ArrayList<String>(
                                        Arrays.asList(resultSet.getString(1), resultSet.getString(2)));

                                if (DEBUG) {
                                    DEBUGGER.debug("userSecurity: {}", userSecurity);
                                }
                            }
                        }
                    }
                }

            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return userSecurity;
}