Example usage for java.sql CallableStatement setString

List of usage examples for java.sql CallableStatement setString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. 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:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductData(String, String) throws SQLException
 *///from w w  w .j  av a 2 s .  c  om
@Override
public List<Object> getProductData(final String productId, final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductData(final int productId, final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", productId);
        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 getProductData(?, ?) }");
        stmt.setString(1, productId);
        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)); // PRODUCT_ID
                results.add(resultSet.getString(2)); // PRODUCT_NAME
                results.add(resultSet.getString(3)); // PRODUCT_SHORT_DESC
                results.add(resultSet.getString(4)); // PRODUCT_DESC
                results.add(resultSet.getBigDecimal(5)); // PRODUCT_PRICE
                results.add(resultSet.getBoolean(6)); // IS_FEATURED
            }

            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 HTML menu for challenges. Challenges are only referenced by their id, 
 * The user will have to go through another servlet to get the module's View address
 * @param ApplicationRoot The current running context of the application
 * @return HTML menu for challenges   /*from w  w  w  .j  ava  2 s  . c  o  m*/
 */
public static String getChallenges(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getChallenges ***");
    String output = new String();
    //Getting Translated Level Names
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    //Encoder to prevent XSS
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleAllInfo(?, ?)");
        callstmt.setString(1, "challenge");
        callstmt.setString(2, userId);
        log.debug("Gathering moduleAllInfo ResultSet");
        ResultSet challenges = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleAllInfo");
        String challengeCategory = new String();
        int rowNumber = 0; // Identifies the first row, ie the start of the list. This is slightly different output to every other row
        while (challenges.next()) {
            if (!challengeCategory.equalsIgnoreCase(challenges.getString(2))) {
                challengeCategory = challenges.getString(2);
                //log.debug("New Category Detected: " + challengeCategory);
                if (rowNumber > 0) //output prepared for Every row after row 1
                    output += "</ul></li><li><a href='javascript:;' class='challengeHeader' >"
                            + encoder.encodeForHTML(bundle.getString("category." + challengeCategory))
                            + "</a><ul class='challengeList' style='display: none;'>";
                else //output prepared for First row in entire challenge
                    output += "<li><a href='javascript:;' class='challengeHeader'>"
                            + encoder.encodeForHTML(bundle.getString("category." + challengeCategory))
                            + "</a><ul class='challengeList' style='display: none;'>";
                //log.debug("Compiling Challenge Category - " + challengeCategory);
            }
            output += "<li>"; //Starts next LI element
            if (challenges.getString(4) != null) {
                output += "<img src='css/images/completed.png'/>"; //Completed marker
            } else {
                output += "<img src='css/images/uncompleted.png'/>"; //Incomplete marker
            }
            //Final out put compilation
            output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(challenges.getString(3))
                    + "' href='javascript:;'>"
                    + encoder.encodeForHTML(bundle.getString(challenges.getString(1))) + "</a>";
            output += "</li>";
            rowNumber++;
        }
        //Check if output is empty
        if (output.isEmpty()) {
            output = "<li><a href='javascript:;'>No challenges found</a></li>";
        } else {
            log.debug("Appending End tags");
            output += "</ul></li>";
        }
    } catch (Exception e) {
        log.error("Challenge Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getChallenges() ***");
    return output;
}

From source file:dbProcs.Getter.java

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

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 does not return the JS script describing how the menu used should work
 * @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 w ww .  j  a  v  a 2  s .c o  m*/
public static String getIncrementalModulesWithoutScript(String ApplicationRoot, String userId, String lang,
        String csrfToken) {
    log.debug("*** Getter.getIncrementalChallengesWithoutScript ***");
    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
        }
    } catch (Exception e) {
        log.error("Challenge Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getIncrementalChallengesWithoutScript() ***");
    return output;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplication(java.lang.String)
 *//*from   w w  w  .jav  a  2s  . c o m*/
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:dbProcs.Getter.java

/**
 * Used to decipher whether or not a user exists as a player
 * @param userId The user identifier of the player to be found
 * @return A boolean reflecting the state of existence of the player
 *//* w  w  w .  j a va2 s.  c  om*/
public static boolean findPlayerById(String ApplicationRoot, String userId) {
    log.debug("*** Getter.findPlayerById ***");
    boolean userFound = false;
    //Get connection
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call playerFindById(?)");
        log.debug("Gathering playerFindById ResultSet");
        callstmt.setString(1, userId);
        ResultSet userFind = callstmt.executeQuery();
        log.debug("Opening Result Set from playerFindById");
        userFind.next(); //This will throw an exception if player not found
        log.debug("Player Found: " + userFind.getString(1)); //This line will not execute if player not found
        userFound = true;
    } catch (Exception e) {
        log.error("Player did not exist: " + e.toString());
        userFound = false;
    }
    Database.closeConnection(conn);
    log.debug("*** END findPlayerById ***");
    return userFound;
}

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
 *///w  w w .  jav a2  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

/**
 * @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   www .j  a va2  s . com
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
 *//*w  ww.j a va  2  s  .  com*/
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;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param moduleId Identifier of module//from  www .  j  a va 2s.  co m
 * @return The db stored solution key value for the moduleId submitted
 */
public static String getModuleResult(String ApplicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleResult ***");
    String moduleFound = null;
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetResult(?)");
        log.debug("Gathering moduleGetResult ResultSet");
        callstmt.setString(1, moduleId);
        ResultSet moduleFind = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetResult");
        moduleFind.next();
        log.debug("Module " + moduleFind.getString(1) + " Found");
        moduleFound = moduleFind.getString(2);
    } catch (Exception e) {
        log.error("Module did not exist: " + e.toString());
        moduleFound = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleResult ***");
    return moduleFound;
}