Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareCall

Introduction

In this page you can find the example usage for java.sql Connection prepareCall.

Prototype

CallableStatement prepareCall(String sql) throws SQLException;

Source Link

Document

Creates a CallableStatement object for calling database stored procedures.

Usage

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainApprovedServers()
 *//*from   w  ww  .  j av  a 2 s.  c o  m*/
public synchronized List<String> obtainApprovedServers() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainApprovedServers() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

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

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrApprovedServers()}");

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

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

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

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

                securityList = new ArrayList<String>();

                while (resultSet.next()) {
                    if (DEBUG) {
                        DEBUGGER.debug(resultSet.getString(1));
                    }

                    // check if column is null
                    securityList.add(resultSet.getString(1));
                }

                if (DEBUG) {
                    DEBUGGER.debug("securityList: {}", securityList);
                }
            }
        }
    } 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 securityList;
}

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainSecurityQuestionList()
 *///from   ww  w  .j  a v  a2 s .c om
public synchronized List<String> obtainSecurityQuestionList() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainSecurityQuestionList() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

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

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrieve_user_questions()}");

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

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();
            resultSet.last();
            int iRowCount = resultSet.getRow();

            if (iRowCount == 0) {
                throw new SQLException("No security questions are currently configured.");
            }

            resultSet.first();
            ResultSetMetaData resultData = resultSet.getMetaData();

            int iColumns = resultData.getColumnCount();

            questionList = new ArrayList<String>();

            for (int x = 1; x < iColumns + 1; x++) {
                if (DEBUG) {
                    DEBUGGER.debug("resultSet.getObject: {}", resultSet.getObject(resultData.getColumnName(x)));
                }

                // check if column is null
                resultSet.getObject(resultData.getColumnName(x));

                // if the column was null, insert n/a, otherwise, insert the column's contents
                questionList.add((String) (resultSet.wasNull() ? "N/A"
                        : resultSet.getObject(resultData.getColumnName(x))));
            }
        }
    } 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 questionList;
}

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)
 *///  w ww.  ja v a 2  s .  c  o  m
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:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @return Result set containing class info in the order classId, className and then classYear
 *///from   ww w .  ja  va2  s . c  o  m
public static ResultSet getClassInfo(String ApplicationRoot) {
    ResultSet result = null;
    log.debug("*** Getter.getClassInfo (All Classes) ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classesGetData()");
        log.debug("Gathering classesGetData ResultSet");
        result = callstmt.executeQuery();
        log.debug("Returning Result Set from classesGetData");
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @return The amount of classes currently existing in the database
 *//*from w w  w . j a  va  2  s. c o  m*/
public static int getClassCount(String ApplicationRoot) {
    int result = 0;
    ResultSet resultSet = null;
    log.debug("*** Getter.getClassCount ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classCount()");
        log.debug("Gathering classCount ResultSet");
        resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from classCount");
        resultSet.next();
        result = resultSet.getInt(1);
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = 0;
    }
    Database.closeConnection(conn);
    log.debug("*** END getClassCount");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param classId The identifier of the class
 * @return String Array with Class information with the format of {name, year}
 *///from  w  w  w. j av  a  2  s . c o  m
public static String[] getClassInfo(String ApplicationRoot, String classId) {
    String[] result = new String[2];
    log.debug("*** Getter.getClassInfo (Single Class) ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classFind(?)");
        callstmt.setString(1, classId);
        log.debug("Gathering classFind ResultSet");
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from classFind");
        resultSet.next();
        result[0] = resultSet.getString(1);//Name
        result[1] = resultSet.getString(2);//Year
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param applicationRoot The current running context of the application.
 * @param moduleId The identifier of a module
 * @return The hash of the module specified
 *///  ww  w.  ja v  a  2  s .c o  m
public static String getModuleHash(String applicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleHash ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetHashById(?)");
        log.debug("Gathering moduleGetHash ResultSet");
        callstmt.setString(1, moduleId);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetHash");
        resultSet.next();
        result = resultSet.getString(1);
    } catch (SQLException e) {
        log.error("Could not execute moduleGetHash: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleHash ***");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * This method retrieves the i18n local key for a module's name.
 * @param applicationRoot Application Running Context
 * @param moduleId ID of the module to lookup
 * @return Locale key for the Module's Name.
 *///from   w w w.  ja  v a2 s . com
public static String getModuleNameLocaleKey(String applicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleNameLocaleKey ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetNameLocale(?)");
        log.debug("Gathering moduleGetNameLocale ResultSet");
        callstmt.setString(1, moduleId);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetNameLocale");
        resultSet.next();
        result = resultSet.getString(1);
    } catch (SQLException e) {
        log.error("Could not execute moduleGetNameLocale: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleNameLocaleKey ***");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param userName The username of the user
 * @return The user id of the submitted user name
 *//*from  ww w .j a  va  2s .  c  o  m*/
public static String getUserIdFromName(String ApplicationRoot, String userName) {
    log.debug("*** Getter.getUserIdFromName ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call userGetIdByName(?)");
        log.debug("Gathering userGetIdByName ResultSet");
        callstmt.setString(1, userName);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from userGetIdByName");
        resultSet.next();
        result = resultSet.getString(1);
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getUserIdFromName ***");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param userId The identifier of a user
 * @return The user name of the submitted user identifier
 *//*from   w w w.  ja  v  a 2 s  . c o  m*/
public static String getUserName(String ApplicationRoot, String userId) {
    log.debug("*** Getter.getUserName ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call userGetNameById(?)");
        log.debug("Gathering userGetNameById ResultSet");
        callstmt.setString(1, userId);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from userGetNameById");
        resultSet.next();
        result = resultSet.getString(1);
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getUserName ***");
    return result;
}