Example usage for java.sql CallableStatement setString

List of usage examples for java.sql CallableStatement setString

Introduction

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

Prototype

void setString(String parameterName, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:dbProcs.Getter.java

/**
 * This method prepares the incremental module menu. This is when Security Shepherd is in "Game Mode".
 * Users are presented with one uncompleted module at a time. This method also returns a script to be executed every time the menu is chanegd.
 * This is script defines the animation and operations to be carried out when the menu is interacted with
 * @param ApplicationRoot The running context of the application.
 * @param userId The user identifier of the user.
 * @param csrfToken The cross site request forgery token
 * @return A HTML menu of a users current module progress and a script for interaction with this menu
 *///w  ww .  j  a v a2  s. c  o m
public static String getIncrementalModules(String ApplicationRoot, String userId, String lang,
        String csrfToken) {
    log.debug("*** Getter.getIncrementalChallenges ***");
    String output = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);

    Locale.setDefault(new Locale("en"));
    Locale locale = new Locale(lang);
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", locale);
    ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", locale);

    try {
        CallableStatement callstmt = conn.prepareCall("call moduleIncrementalInfo(?)");
        callstmt.setString(1, userId);
        log.debug("Gathering moduleIncrementalInfo ResultSet");
        ResultSet modules = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleIncrementalInfo");
        boolean lastRow = false;
        boolean completedModules = false;

        //Preparing first Category header; "Completed"
        output = "<li><a id='completedList' href='javascript:;'><div class='menuButton'>"
                + bundle.getString("getter.button.completed") + "</div></a>\n"
                + "<ul id='theCompletedList' style='display: none;' class='levelList'>";

        while (modules.next() && !lastRow) {
            //For each row, prepair the modules the users can select
            if (modules.getString(4) != null) //If not Last Row
            {
                completedModules = true;
                output += "<li>";
                output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(modules.getString(3))
                        + "' href='javascript:;'>"
                        + encoder.encodeForHTML(levelNames.getString(modules.getString(1))) + "</a>";
                output += "</li>";
            } else {
                lastRow = true;
                //Last Row - Highlighed Next Challenge
                if (completedModules) {
                    output += "</ul></li><li>";
                } else {
                    //NO completed modules, so dont show any...
                    output = new String();
                }

                //Second category - Uncompleted
                output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(modules.getString(3))
                        + "' href='javascript:;'>" + "<div class='menuButton'>"
                        + bundle.getString("getter.button.nextChallenge") + "</div>" + "</a>";
                output += "</li>";
            }
        }

        if (!lastRow) //If true, then the user has completed all challenges
        {
            output += "<h2 id='uncompletedList'><a href='javascript:;'>"
                    + bundle.getString("getter.button.finished") + "</a></h2>\n" + "</li>";
        }
        if (output.isEmpty()) //If this method has gone so far without any output, create a error message
        {
            output = "<li><a href='javascript:;'>" + bundle.getString("getter.button.noModulesFound")
                    + "</a></li>";
        } else //final tags to ensure valid HTML
        {
            log.debug("Appending End tags");
            //output += "</ul></li>"; //Commented Out to prevent Search Box being pushed into Footer
        }

        //This is the script for menu interaction
        output += "<script>applyMenuButtonActionsCtfMode('" + encoder.encodeForHTML(csrfToken) + "', \""
                + encoder.encodeForHTML(bundle.getString("generic.text.sorryError")) + "\");</script>";
    } catch (Exception e) {
        log.error("Challenge Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getIncrementalChallenges() ***");
    return output;
}

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.
 *//*  w w  w .j  a  va2  s  .  c  om*/
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

/**
 * Convert module hash to ID/*from ww  w .j  a va2  s .  c  o  m*/
 * @param ApplicationRoot The current running context of the application
 * @param moduleHash The module hash to use for look up
 * @return The identifier of the module with the module hash of the moduleHash parameter
 */
public static String getModuleIdFromHash(String ApplicationRoot, String moduleHash) {
    log.debug("*** Getter.getModuleIdFromHash ***");
    log.debug("Getting ID from Hash: " + moduleHash);
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetIdFromHash(?)");
        log.debug("Gathering moduleGetIdFromHash ResultSet");
        callstmt.setString(1, moduleHash);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetIdFromHash");
        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 getModuleIdFromHash ***");
    return result;
}

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

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductList(String) throws SQLException
 *///from w w  w.  ja  v a2 s .c  o m
@Override
public List<Object[]> getProductList(final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductList(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 getProductList(?) }");
        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), // PRODUCT_ID
                        resultSet.getString(2), // PRODUCT_NAME
                        resultSet.getString(3), // PRODUCT_SHORT_DESC
                        resultSet.getString(4), // PRODUCT_DESC
                        resultSet.getBigDecimal(5), // PRODUCT_PRICE
                        resultSet.getBoolean(6) // IS_FEATURED
                };

                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.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#performLogon(java.lang.String, java.lang.String)
 *//*  ww  w. j ava  2s.com*/
public synchronized List<Object> performLogon(final String username, final String password)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#performLogon(final String user, final String password) throws AuthenticatorException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> userAccount = 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 performAuthentication(?, ?)}");
        stmt.setString(1, username); // username
        stmt.setString(2, password); // password

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

        if (!(stmt.execute())) {
            throw new AuthenticatorException("No user was found for the provided user information");
        }

        resultSet = stmt.getResultSet();

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

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

            userAccount = new ArrayList<Object>();

            for (String returningAttribute : userAttributes.getReturningAttributes()) {
                if (DEBUG) {
                    DEBUGGER.debug("returningAttribute: {}", returningAttribute);
                }

                userAccount.add(resultSet.getString(returningAttribute));
            }

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

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#removeService(java.lang.String)
 *//* w w  w  .j  a  v  a2 s . c  o  m*/
public synchronized boolean removeService(final String datacenter) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#removeService(final String datacenter) throws SQLException";

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

    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 removeServiceData(?)}");
        stmt.setString(1, datacenter);

        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

/**
 * Used to return a module cheat sheet//from w w  w . j  av a2s  .c om
 * @param ApplicationRoot The current running context of the application
 * @param moduleId The identifier of the module to return the cheat sheet for
 * @param lang The Locale the user has enabled
 * @return String[] containing {ModuleName, CheatSheetSolution}
 */
public static String[] getModuleSolution(String ApplicationRoot, String moduleId, Locale lang) {
    log.debug("*** Getter.getModuleSolution ***");
    String[] result = new String[2];
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    //Getting Translations
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.cheatsheets.solutions", lang);
    try {
        CallableStatement callstmt = conn.prepareCall("call cheatSheetGetSolution(?)");
        log.debug("Gathering cheatSheetGetSolution ResultSet");
        callstmt.setString(1, moduleId);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from cheatSheetGetSolution");
        resultSet.next();
        result[0] = resultSet.getString(1);
        result[1] = bundle.getString(resultSet.getString(2));

    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleSolution ***");
    return result;
}

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

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getFeaturedProducts(String) throws SQLException
 *//*  w w  w .j av a  2s .  c o  m*/
@Override
public List<Object[]> getFeaturedProducts(final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getFeaturedProducts(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 getFeaturedProducts(?) }");
        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), // PRODUCT_ID
                        resultSet.getString(2), // PRODUCT_NAME
                        resultSet.getString(3), // PRODUCT_SHORT_DESC
                        resultSet.getString(4), // PRODUCT_DESC
                        resultSet.getBigDecimal(5), // PRODUCT_PRICE
                        resultSet.getBoolean(6) // IS_FEATURED
                };

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

/**
 * Returns the result key for a module using the module's hash for the lookup procedure.
 * @param ApplicationRoot The current running context of the application
 * @param moduleHash The hash to use for module look up
 * @return The db stored solution key value for the moduleHash submited
 *///from  www.  j  a v  a 2s  .  c  o  m
public static String getModuleResultFromHash(String ApplicationRoot, String moduleHash) {
    log.debug("*** Getter.getModuleResultFromHash ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        log.debug("hash '" + moduleHash + "'");
        CallableStatement callstmt = conn.prepareCall("call moduleGetResultFromHash(?)");
        log.debug("Gathering moduleGetResultFromHash ResultSet");
        callstmt.setString(1, moduleHash);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetResultFromHash");
        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 getModuleResultFromHash ***");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * This method prepares the Tournament module menu. This is when Security Shepherd is in "Tournament Mode".
 * Users are presented with a list of that are specified as open. 
 * @param ApplicationRoot The running context of the application.
 * @param userId The user identifier of the user.
 * @param csrfToken The cross site request forgery token
 * @return A HTML menu of a users current module progress and a script for interaction with this menu
 *//*from   www  . java2 s . c  o m*/
public static String getTournamentModules(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getTournamentModules ***");
    String levelMasterList = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    //Getting Translations
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", lang);
    ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    try {

        String listEntry = new String();
        //Get the modules
        CallableStatement callstmt = conn.prepareCall("call moduleTournamentOpenInfo(?)");
        callstmt.setString(1, userId);
        log.debug("Gathering moduleTournamentOpenInfo ResultSet for user " + userId);
        ResultSet levels = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleTournamentOpenInfo");
        int currentSection = 0; // Used to identify the first row, as it is slightly different to all other rows for output
        while (levels.next()) {
            //Create Row Entry First
            //log.debug("Adding " + lessons.getString(1));
            listEntry = "<li>";
            //Markers for completion
            if (levels.getString(4) != null) {
                listEntry += "<img src='css/images/completed.png'/>";
            } else {
                listEntry += "<img src='css/images/uncompleted.png'/>";
            }
            //Prepare entry output
            listEntry += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(levels.getString(3))
                    + "' href='javascript:;'>"
                    + encoder.encodeForHTML(levelNames.getString(levels.getString(1))) + "</a>\n";
            listEntry += "</li>";
            //What section does this belong in? Current or Next?
            if (getTounnamentSectionFromRankNumber(levels.getInt(5)) > currentSection) {
                //This level is not in the same level band as the previous level. So a new Level Band Header is required on the master list before we add the entry.
                //Do we need to close a previous list?
                if (currentSection != 0) //If a Section Select hasn't been made before, we don't need to close any previous sections
                {
                    //We've had a section before, so need to close the previous one before we make this new one
                    levelMasterList += "</ul>\n";
                }
                //Update the current section to the one we have just added to the list
                currentSection = getTounnamentSectionFromRankNumber(levels.getInt(5));
                //Which to Add?
                switch (currentSection) {
                case 1: //fieldTraining
                    //log.debug("Starting Field Training List");
                    levelMasterList += "<a id=\"fieldTrainingList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.1") + "</div></a>"
                            + "<ul id=\"theFieldTrainingList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 2: //private
                    //log.debug("Starting Private List");
                    levelMasterList += "<a id=\"privateList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.2") + "</div></a>"
                            + "<ul id=\"thePrivateList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 3: //corporal
                    //log.debug("Starting Corporal List");
                    levelMasterList += "<a id=\"corporalList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.3") + "</div></a>"
                            + "<ul id=\"theCorporalList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 4: //sergeant
                    //log.debug("Starting Sergeant List");
                    levelMasterList += "<a id=\"sergeantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.4") + "</div></a>"
                            + "<ul id=\"theSergeantList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 5: //Lieutenant
                    //log.debug("Starting Lieutenant List");
                    levelMasterList += "<a id=\"lieutenantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.5") + "</div></a>"
                            + "<ul id=\"theLieutenantList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 6: //major
                    //log.debug("Starting Major List");
                    levelMasterList += "<a id=\"majorList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.6") + "</div></a>"
                            + "<ul id=\"theMajorList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 7: //admiral
                    //log.debug("Starting Admiral List");
                    levelMasterList += "<a id=\"admiralList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.7") + "</div></a>"
                            + "<ul id=\"theAdmiralList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                }
            }
            //Now we can add the entry to the level master List and start again
            levelMasterList += listEntry;
            //log.debug("Put level in category: " + currentSection);
        }
        //If no output has been found, return an error message
        if (levelMasterList.isEmpty()) {
            levelMasterList = "<ul><li><a href='javascript:;'>"
                    + bundle.getString("getter.button.noModulesFound") + "</a></li></ul>";
        } else {
            //List is complete, but we need to close the last list we made, which deinfetly exists as the levelmasterList is not empty
            levelMasterList += "</ul>";
            log.debug("Tournament List returned");
        }
    } catch (Exception e) {
        log.error("Tournament List Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    return levelMasterList;
}