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:dbProcs.Getter.java

/**
 * Used to determine if a user has completed a module already
 * @param ApplicationRoot The current running context of an application
 * @param moduleId The module identifier 
 * @param userId The user identifier//from   w  w  w.j  av  a  2 s .  co m
 * @return The module name of the module IF the user has not completed AND the user has previously opened the challenge. 
 */
public static String checkPlayerResult(String ApplicationRoot, String moduleId, String userId) {
    log.debug("*** Getter.checkPlayerResult ***");

    String result = null;
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        log.debug("Preparing userCheckResult call");
        CallableStatement callstmnt = conn.prepareCall("call userCheckResult(?, ?)");
        callstmnt.setString(1, moduleId);
        callstmnt.setString(2, userId);
        log.debug("Executing userCheckResult");
        ResultSet resultSet = callstmnt.executeQuery();
        resultSet.next();
        result = resultSet.getString(1);
    } catch (SQLException e) {
        log.debug("userCheckResult Failure: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END checkPlayerResult ***");
    return result;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#addService(java.util.List)
 *//*w  ww.j a va 2 s. c om*/
public synchronized boolean addService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#addService(final List<String> data) throws SQLException";

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

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL addNewService(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

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

        isComplete = (!(stmt.execute()));

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

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

    return isComplete;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#updateService(java.util.List)
 *//*from   www  .  j a va  2 s .  c o m*/
public synchronized boolean updateService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#updateService(final List<String> data) throws SQLException";

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

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL updateServiceData(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

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

        isComplete = (!(stmt.execute()));

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

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

    return isComplete;
}

From source file:dbProcs.Getter.java

/**
 * This method is used to determine if a CSRF level has been completed. 
 * A call is made to the DB that returns the CSRF counter for a level. 
 * If this counter is greater than 0, the level has been completed
 * @param applicationRoot Running context of the application
 * @param moduleHash Hash ID of the CSRF module you wish to check if a user has completed
 * @param userId the ID of the user to check
 * @return True or False value depicting if the user has completed the module
 *//*from ww  w.j  av a 2 s . c  om*/
public static boolean isCsrfLevelComplete(String applicationRoot, String moduleId, String userId) {
    log.debug("*** Setter.isCsrfLevelComplete ***");

    boolean result = false;

    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        log.debug("Preparing csrfLevelComplete call");
        CallableStatement callstmnt = conn.prepareCall("call csrfLevelComplete(?, ?)");
        callstmnt.setString(1, moduleId);
        callstmnt.setString(2, userId);
        log.debug("moduleId: " + moduleId);
        log.debug("userId: " + userId);
        log.debug("Executing csrfLevelComplete");
        ResultSet resultSet = callstmnt.executeQuery();
        resultSet.next();
        result = resultSet.getInt(1) > 0; // If Result is > 0, then the CSRF level is complete
        if (result)
            log.debug("CSRF Level is complete");
    } catch (SQLException e) {
        log.error("csrfLevelComplete Failure: " + e.toString());
        result = false;
    }
    Database.closeConnection(conn);
    log.debug("*** END isCsrfLevelComplete ***");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * The CSRF forum is used in CSRF levels for users to deliver CSRF attacks against each other. URLs are contained in IFRAME tags
 * @param ApplicationRoot The current running context of the application
 * @param classId Identifier of the class to populate the forum with
 * @param moduleId The module in which to return the forum for
 * @param bundle Strings Package for the Language Local of the user making the request
 * @return A HTML table of a Class's CSRF Submissions for a specific module
 *//*from   w ww  . j a v  a2  s  .c o m*/
public static String getCsrfForumWithIframe(String ApplicationRoot, String classId, String moduleId,
        ResourceBundle bundle) {
    log.debug("*** Getter.getCsrfForum ***");
    log.debug("Getting stored messages from class: " + classId);
    Encoder encoder = ESAPI.encoder();
    String htmlOutput = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        if (classId != null) {
            CallableStatement callstmt = conn.prepareCall("call resultMessageByClass(?, ?)");
            log.debug("Gathering resultMessageByClass ResultSet");
            callstmt.setString(1, classId);
            callstmt.setString(2, moduleId);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("resultMessageByClass executed");

            //Table Header
            htmlOutput = "<table><tr><th>" + bundle.getString("forum.userName") + "</th><th>"
                    + bundle.getString("forum.message") + "</th></tr>";

            log.debug("Opening Result Set from resultMessageByClass");
            int counter = 0;
            while (resultSet.next()) {
                counter++;
                //Table content
                htmlOutput += "<tr><td>" + encoder.encodeForHTML(resultSet.getString(1))
                        + "</td><td><iframe sandbox=\"allow-scripts allow-forms\" src=\""
                        + encoder.encodeForHTMLAttribute(resultSet.getString(2)) + "\"></iframe></td></tr>";
            }
            if (counter > 0)
                log.debug("Added a " + counter + " row table");
            else
                log.debug("No results from query");
            //Table end
            htmlOutput += "</table>";
        } else {
            log.error("User with Null Class detected");
            htmlOutput = "<p><font color='red'>" + bundle.getString("error.noClass") + "</font></p>";
        }
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        htmlOutput = "<p>" + bundle.getString("error.occurred ") + "</p>";
    } catch (Exception e) {
        log.fatal("Could not return CSRF Forum: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getCsrfForum ***");
    return htmlOutput;
}

From source file:dbProcs.Getter.java

/**
 * The CSRF forum is used in CSRF levels for users to deliver CSRF attacks against each other. URLs are contained in IMG tags
 * @param ApplicationRoot The current running context of the application
 * @param classId Identifier of the class to populate the forum with
 * @param moduleId The module in which to return the forum for
 * @param bundle The strings package for the language of the user
 * @return A HTML table of a Class's CSRF Submissions for a specific module
 *//*  w  ww. ja  v  a 2s .  c  o  m*/
public static String getCsrfForumWithImg(String ApplicationRoot, String classId, String moduleId,
        ResourceBundle bundle) {
    log.debug("*** Getter.getCsrfForum ***");
    log.debug("Getting stored messages from class: " + classId);
    Encoder encoder = ESAPI.encoder();
    String htmlOutput = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        if (classId != null) {
            CallableStatement callstmt = conn.prepareCall("call resultMessageByClass(?, ?)");
            log.debug("Gathering resultMessageByClass ResultSet");
            callstmt.setString(1, classId);
            callstmt.setString(2, moduleId);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("resultMessageByClass executed");

            //Table Header
            htmlOutput = "<table><tr><th>" + bundle.getString("forum.userName") + "</th><th>"
                    + bundle.getString("forum.image") + "</th></tr>";

            log.debug("Opening Result Set from resultMessageByClass");
            int counter = 0;
            while (resultSet.next()) {
                counter++;
                //Table content
                htmlOutput += "<tr><td>" + encoder.encodeForHTML(resultSet.getString(1))
                        + "</td><td><img src=\"" + encoder.encodeForHTMLAttribute(resultSet.getString(2))
                        + "\"/></td></tr>";
            }
            if (counter > 0)
                log.debug("Added a " + counter + " row table");
            else
                log.debug("No results from query");
            //Table end
            htmlOutput += "</table>";
        } else {
            log.error("User with Null Class detected");
            htmlOutput = "<p><font color='red'>" + bundle.getString("error.noClass") + "</font></p>";
        }
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        htmlOutput = "<p>" + bundle.getString("error.occurred") + "</p>";
    } catch (Exception e) {
        log.fatal("Could not return CSRF Forum: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getCsrfForum ***");
    return htmlOutput;
}

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)
 *//*from ww w .ja v a2 s.  co 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;
}

From source file:dbProcs.Getter.java

/**
 * Used to present the progress of a class in a series of loading bars
 * @param applicationRoot The current running context of the application
 * @param classId The identifier of the class to use in lookup
 * @return A HTML representation of a class's progress in the application
 *//*  w ww .  j  a va2 s .  co m*/
public static String getProgress(String applicationRoot, String classId) {
    log.debug("*** Getter.getProgress ***");

    String result = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        log.debug("Preparing userProgress call");
        CallableStatement callstmnt = conn.prepareCall("call userProgress(?)");
        callstmnt.setString(1, classId);
        log.debug("Executing userProgress");
        ResultSet resultSet = callstmnt.executeQuery();
        int resultAmount = 0;
        while (resultSet.next()) //For each user in a class
        {
            resultAmount++;
            if (resultSet.getString(1) != null) {
                result += "<tr><td>" + encoder.encodeForHTML(resultSet.getString(1)) + //Output their progress
                        "</td><td><div style='background-color: #A878EF; heigth: 25px; width: "
                        + widthOfUnitBar * resultSet.getInt(2) + "px;'>" + "<font color='white'><strong>"
                        + resultSet.getInt(2);
                if (resultSet.getInt(2) > 6)
                    result += " Modules";
                result += "</strong></font></div></td></tr>";
            }
        }
        if (resultAmount > 0)
            result = "<table><tr><th>Player</th><th>Progress</th></tr>" + result + "</table>";
        else
            result = new String();
    } catch (SQLException e) {
        log.error("getProgress Failure: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getProgress ***");
    return result;
}

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.  j ava 2 s . co m*/
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:dbProcs.Getter.java

/**
 * Used to present a modules feedback, including averages and raw results.
 * @param applicationRoot The current running context of the application.
 * @param moduleId The module identifier
 * @return A HTML table of the feedback for a specific module
 */// w  w  w. j  a  v a  2 s.  c om
public static String getFeedback(String applicationRoot, String moduleId) {
    log.debug("*** Getter.getFeedback ***");

    String result = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        log.debug("Preparing userUpdateResult call");
        CallableStatement callstmnt = conn.prepareCall("call moduleFeedback(?)");
        callstmnt.setString(1, moduleId);
        log.debug("Executing moduleFeedback");
        ResultSet resultSet = callstmnt.executeQuery();
        int resultAmount = 0;
        int before = 0;
        int after = 0;
        int difficulty = 0;
        boolean color = true;
        while (resultSet.next()) {
            if (resultSet.getString(1) != null) {
                resultAmount++;
                difficulty += resultSet.getInt(3);
                before += resultSet.getInt(4);
                after += resultSet.getInt(5);
                result += "<tr ";
                if (color) //Alternate row color
                {
                    color = !color;
                    result += "BGCOLOR='A878EF'";
                } else {
                    color = !color;
                    result += "BGCOLOR='D4BCF7'";
                }
                //A row off information
                result += "><td>" + encoder.encodeForHTML(resultSet.getString(1)) + "</td><td>"
                        + encoder.encodeForHTML(resultSet.getString(2)) + "</td><td>" + resultSet.getInt(3)
                        + "</td><td>" + resultSet.getInt(4) + "</td><td>" + resultSet.getInt(5) + "</td><td>"
                        + encoder.encodeForHTML(resultSet.getString(6)) + "</td></tr>";
            }
        }
        if (resultAmount > 0)//Table header
            result = "<table><tr><th>Player</th><th>Time</th><th>Difficulty</th><th>Before</th><th>After</th><th>Comments</th></tr>"
                    + "<tr><td>Average</td><td></td><td>" + difficulty / resultAmount + "</td><td>"
                    + before / resultAmount + "</td><td>" + after / resultAmount + "</td><td></td></tr>"
                    + result + "<table>";
        else // If empty, Blank output
            result = new String();
    } catch (SQLException e) {
        log.error("moduleFeedback Failure: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getFeedback ***");
    return result;
}