List of usage examples for java.sql CallableStatement executeQuery
ResultSet executeQuery() throws SQLException;
PreparedStatement object and returns the ResultSet object generated by the query. 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 */// w w w .j a v a 2s . c om 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 *///from w w w . j a v a 2 s . c om 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.rosy.bill.dao.hibernate.SimpleHibernateDao.java
/** * ?String// w ww . j av a 2s . c o m * @param execStr * @param params * @param outIndex * @return */ @SuppressWarnings("deprecation") public String getNumByExecuteProc(String execStr, String[] params, int outIndex) { java.sql.CallableStatement cstmt = null; String rtn = null; try { cstmt = this.getSession().connection().prepareCall(execStr); cstmt.registerOutParameter(outIndex, oracle.jdbc.driver.OracleTypes.LONGVARCHAR); if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { if (i != outIndex) { cstmt.setString(i, params[i].toString()); } } } cstmt.executeQuery(); java.sql.ResultSet rs = (java.sql.ResultSet) cstmt.getObject(outIndex); if (rs != null) { rtn = rs.getString(0); } } catch (HibernateException e1) { e1.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return rtn; }
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 w ww . j a va 2 s. com*/ 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; }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<NajminajucejsiSpotrebitel> getNajnminajucejsiSpotrebitelia( NajminajucejsiSpotrebiteliaParams params) { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection .prepareCall("select meno, cislo_odberatela from (select rank() over (\n" + " order by get_spotreba_za_obdobie(cislo_odberatela,?,?,?)) as rn,\n" + " count(*) over() as pocet,\n" + " meno||' '||priezvisko as meno,\n" + " cislo_odberatela \n" + " from SE_ODBERATEL join SE_OSOBA using(rod_cislo)) \n" + "where rn<pocet*0.1"); stmnt.setString(3, params.getVelicina().name().toLowerCase()); stmnt.setDate(1, Utils.utilDateToSqlDate(params.getDatumOd())); stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumDo())); ResultSet result = stmnt.executeQuery(); List<NajminajucejsiSpotrebitel> output = new LinkedList<>(); while (result.next()) { NajminajucejsiSpotrebitel o = new NajminajucejsiSpotrebitel(); o.setMeno(result.getString("meno")); o.setCisloOdberatela(result.getInt("cislo_odberatela")); output.add(o);//from w w w . j ava 2 s . c o m } return output; } catch (SQLException e) { throw new RuntimeException(e); } }
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 *///w w w . j av a 2s .c o m 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:DAO.Poll_Tbl_pkg.Poll_TblJDBCTemplate.java
public boolean create(int uid, String cid_json, String title, String description, String qtn_json, String ans_json, String poll_link, String start_ts, String end_ts, int reward, String poll_type) { // String SQL = "insert into poll_tbl(uid,cid_json,title,description,qtn_json,ans_json,poll_link,start_ts,end_ts,reward,poll_type) values(?,?,?,?,?,?,?,?,?,?,?)"; // int ty=jdbcTemplateObject.update( SQL, uid, cid_json,title,description, qtn_json, ans_json,poll_link,start_ts,end_ts, reward, poll_type); // return true; System.out.println("reached3"); CallableStatement st; /* (IN handle_i varchar(45),IN username_i varchar(45),IN email_i varchar(45),IN country_i varchar(45), IN state_i varchar(45),IN city_i varchar(45),IN zip_i varchar(45),IN religion_i varchar(45),IN sex_i varchar(45),IN dob_i varchar(45),IN phone_i varchar(45), IN profile_pic_i varchar(45),IN fb_i varchar(100), IN category_list_json_i varchar(1000),IN exp_json_i varchar(1000),IN fish_i int)*/ try {/*from ww w . ja v a 2s. com*/ con = conn.getDataSource().getConnection(); System.out.println("10 dec 4pm"); st = con.prepareCall("call createPoll('" + uid + "','" + cid_json + "','" + title + "','" + description + "','" + qtn_json + "','" + ans_json + "','" + poll_link + "','" + start_ts + "','" + end_ts + "'," + reward + ",'" + poll_type + "')"); st.executeQuery(); con.close(); System.out.println("15 dec 12:07am"); return true; } catch (Exception e) { System.out.println("createPoll procedure error=" + e); return false; } }
From source file:org.gofleet.openLS.ddbb.dao.postgis.PostGisHBGeoCodingDAO.java
@Transactional(readOnly = true) public List<AbstractResponseParametersType> geocoding(final GeocodeRequestType param) { HibernateCallback<List<AbstractResponseParametersType>> action = new HibernateCallback<List<AbstractResponseParametersType>>() { public List<AbstractResponseParametersType> doInHibernate(Session session) throws HibernateException, SQLException { List<AddressType> addressList = param.getAddress(); List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>(); for (AddressType addressType : addressList) { // TODO change deprecation? @SuppressWarnings("deprecation") CallableStatement consulta = session.connection() .prepareCall("{call gls_geocoding(?, ?, ?, ?, ?)}"); String street = GeoUtil.extractStreet(addressType); String munsub = GeoUtil.extractMunSub(addressType); String mun = GeoUtil.extractMun(addressType); String subcountry = GeoUtil.extractSubCountry(addressType); String country = GeoUtil.extractCountry(addressType); consulta.setString(1, street); consulta.setString(2, munsub); consulta.setString(3, mun); consulta.setString(4, subcountry); consulta.setString(5, country); LOG.debug(consulta);/* w ww .ja v a2s .c o m*/ ResultSet o = consulta.executeQuery(); GeocodeResponseType grt = new GeocodeResponseType(); while (o.next()) { GeocodeResponseListType geocode = new GeocodeResponseListType(); try { PGgeometry g = (PGgeometry) o.getObject("geometry"); Jdbc4Array address = (Jdbc4Array) o.getArray("address"); GeocodedAddressType addresstype = new GeocodedAddressType(); addresstype.setPoint(PostGisUtils.getReferencedPoint(g)); addresstype.setAddress(PostGisUtils.getAddress(address)); geocode.getGeocodedAddress().add(addresstype); geocode.setNumberOfGeocodedAddresses(BigInteger.valueOf(1l)); grt.getGeocodeResponseList().add(geocode); } catch (Throwable t) { LOG.error("Error extracting data from database.", t); } res_.add(grt); } } return res_; } }; return hibernateTemplate.executeWithNativeSession(action); }
From source file:com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.java
public void runStoredProcedures(String coffeeNameArg, double maximumPercentageArg, double newPriceArg) throws SQLException { CallableStatement cs = null; try {/*from w w w.j ava 2 s .c o m*/ System.out.println("\nCalling the stored procedure GET_SUPPLIER_OF_COFFEE"); cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}"); cs.setString(1, coffeeNameArg); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String supplierName = cs.getString(2); if (supplierName != null) { System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName); } else { System.out.println("\nUnable to find the coffee " + coffeeNameArg); } System.out.println("\nCalling the procedure SHOW_SUPPLIERS"); cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}"); ResultSet rs = cs.executeQuery(); while (rs.next()) { String supplier = rs.getString("SUP_NAME"); String coffee = rs.getString("COF_NAME"); System.out.println(supplier + ": " + coffee); } System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); System.out.println("\nCalling the procedure RAISE_PRICE"); cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}"); cs.setString(1, coffeeNameArg); cs.setDouble(2, maximumPercentageArg); cs.registerOutParameter(3, Types.DOUBLE); cs.setDouble(3, newPriceArg); cs.execute(); System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3)); System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (cs != null) { cs.close(); } } }
From source file:org.gofleet.openLS.ddbb.dao.GeoCodingDAO.java
@Transactional(readOnly = true) public List<List<AbstractResponseParametersType>> geocoding(final GeocodeRequestType param) { HibernateCallback<List<List<AbstractResponseParametersType>>> action = new HibernateCallback<List<List<AbstractResponseParametersType>>>() { public List<List<AbstractResponseParametersType>> doInHibernate(Session session) throws HibernateException, SQLException { List<List<AbstractResponseParametersType>> res = new LinkedList<List<AbstractResponseParametersType>>(); List<AddressType> addressList = param.getAddress(); for (AddressType addressType : addressList) { List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>(); // TODO change deprecation? @SuppressWarnings("deprecation") CallableStatement consulta = session.connection() .prepareCall("{call gls_geocoding(?, ?, ?, ?, ?)}"); String street = GeoUtil.extractStreet(addressType); String munsub = GeoUtil.extractMunSub(addressType); String mun = GeoUtil.extractMun(addressType); String subcountry = GeoUtil.extractSubCountry(addressType); String country = GeoUtil.extractCountry(addressType); consulta.setString(1, street); consulta.setString(2, munsub); consulta.setString(3, mun); consulta.setString(4, subcountry); consulta.setString(5, country); LOG.debug(consulta);//from ww w . j av a2 s . c o m ResultSet o = consulta.executeQuery(); GeocodeResponseType grt = new GeocodeResponseType(); while (o.next()) { GeocodeResponseListType geocode = new GeocodeResponseListType(); try { PGgeometry g = (PGgeometry) o.getObject("geometry"); Jdbc4Array address = (Jdbc4Array) o.getArray("address"); GeocodedAddressType addresstype = new GeocodedAddressType(); addresstype.setPoint(GeoUtil.getReferencedPoint(g)); addresstype.setAddress(GeoUtil.getAddress(address)); geocode.getGeocodedAddress().add(addresstype); geocode.setNumberOfGeocodedAddresses(BigInteger.valueOf(1l)); grt.getGeocodeResponseList().add(geocode); } catch (Throwable t) { LOG.error("Error extracting data from database.", t); } res_.add(grt); } res.add(res_); } return res; } }; return hibernateTemplate.executeWithNativeSession(action); }