List of usage examples for java.sql CallableStatement setString
void setString(String parameterName, String x) throws SQLException;
String
value. 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; }