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:common.dao.impl.BaseDAOImpl.java
/** * SQL(?)/*from w w w. ja v a 2 s . com*/ * @param queryString * @param params */ public void executeVoidProcedureSql(final String queryString, final Object[] params) throws Exception { Session session = sessionFactory.getCurrentSession(); session.doWork(new Work() { public void execute(Connection conn) throws SQLException { ResultSet rs = null; CallableStatement call = conn.prepareCall("{" + queryString + "}"); if (null != params) { for (int i = 0; i < params.length; i++) { call.setObject(i + 1, params[i]); } } rs = call.executeQuery(); call.close(); rs.close(); } }); }
From source file:dbProcs.Getter.java
/** * This method is used to gather users according by class. Thanks to MySQL syntax, where class = null will return nothing, is null must be used. * <br/>is 'validClass' will Error, = 'validclass' must be used.<br/> * So there are two procedures this method calls. One that handles null classes, one that does not * @param ClassId Identifier of class/*from w w w.j av a2 s.c om*/ * @param ApplicationRoot The current running context of the application * @return ResultSet that contains users for the selected class in the formate {userId, userName, userAddress} */ public static ResultSet getPlayersByClass(String ApplicationRoot, String classId) { ResultSet result = null; log.debug("*** Getter.getPlayersByClass (Single Class) ***"); log.debug("classId: '" + classId + "'"); Connection conn = Database.getCoreConnection(ApplicationRoot); try { CallableStatement callstmt = null; if (classId != null) { log.debug("Gathering playersByClass ResultSet"); callstmt = conn.prepareCall("call playersByClass(?)"); callstmt.setString(1, classId); log.debug("Returning Result Set from playersByClass"); } else { log.debug("Gathering playersWithoutClass ResultSet"); callstmt = conn.prepareCall("call playersWithoutClass()"); log.debug("Returning Result Set from playersByClass"); } ResultSet resultSet = callstmt.executeQuery(); result = resultSet; resultSet.next(); } catch (SQLException e) { log.error("Could not execute query: " + e.toString()); result = null; } log.debug("*** END getPlayersByClass"); return result; }
From source file:es.emergya.bbdd.dao.RoutingHome.java
/** * Devuelve la lista de ids de la ruta desde vertice_origen a * vertice_destino.// w w w .j av a 2 s.com * * Utiliza la funcion shooting_star * * @param origin * @param goal * @return */ @Transactional(readOnly = true, rollbackFor = Throwable.class) private List<Long> shortest_path_shooting_star(final Long origin, final Long goal) { final List<Long> lista = new ArrayList<Long>(); try { Session currentSession = getSession(); CallableStatement consulta = currentSession.connection() .prepareCall("{call shortest_path_shooting_star(?,?,?,?,?)}"); consulta.setString(1, "SELECT " + id + "::integer as id, " + source + "::integer as source, " + target + "::integer as target, " + cost + " as cost," + reverse_cost + " as reverse_cost, " + "ST_X(ST_StartPoint(" + the_geom + ")) as x1," + "ST_Y(ST_StartPoint(" + the_geom + ")) as y1," + "ST_X(ST_EndPoint(" + the_geom + ")) as x2," + "ST_Y(ST_EndPoint(" + the_geom + ")) as y2," + rule + " as rule, " + to_cost + " as to_cost FROM " + table // + " order by " + id ); consulta.setInt(2, origin.intValue()); consulta.setInt(3, goal.intValue()); consulta.setBoolean(4, true); consulta.setBoolean(5, true); log.trace(consulta); ResultSet resultado = consulta.executeQuery(); while (resultado.next()) lista.add(resultado.getLong("edge_id")); } catch (Exception e) { log.error("No se pudo calcular la ruta", e); } return lista; }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<SpotrebaDomacnosti> getProblematickeDOmacnosti() { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection.prepareCall( "select cislo_odberatela, meno||' '||priezvisko meno, meracia_velicina, get_spotreba_za_obdobie(cislo_odberatela,ADD_MONTHS(sysdate,-12),sysdate,meracia_velicina) spotreba, get_pocet_vymen_zariadenia(cislo_odberatela,ADD_MONTHS(sysdate,-12),sysdate,zariadenie.MERACIA_VELICINA) as pocet_vymen\n" + "from SE_OSOBA join SE_ODBERATEL odberatel on(odberatel.rod_cislo = SE_OSOBA.ROD_CISLO) join SE_HISTORIA using (cislo_odberatela) \n" + "join SE_ZARIADENIE using(cis_zariadenia) join SE_TYP_ZARIADENIA zariadenie on(zariadenie.typ = SE_ZARIADENIE.TYP)\n" + "where get_pocet_vymen_zariadenia(cislo_odberatela,ADD_MONTHS(sysdate,-12),sysdate,zariadenie.MERACIA_VELICINA)>2\n" + "group by cislo_odberatela, SE_OSOBA.rod_cislo, meno, priezvisko,meracia_velicina, get_spotreba_za_obdobie(cislo_odberatela,ADD_MONTHS(sysdate,-12),sysdate,meracia_velicina)"); ResultSet result = stmnt.executeQuery(); List<SpotrebaDomacnosti> output = new LinkedList<>(); while (result.next()) { SpotrebaDomacnosti o = new SpotrebaDomacnosti(); o.setMeno(result.getString("meno")); o.setCisloOdberatela(result.getInt("cislo_odberatela")); o.setVelicina(MeraciaVelicina.valueOf(result.getString("MERACIA_VELICINA").toUpperCase())); o.setSpotreba(result.getDouble("SPOTREBA")); o.setPocetVymen(result.getInt("POCET_VYMEN")); output.add(o);// ww w . j a v a 2 s .com } return output; } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:dbProcs.Getter.java
/** * Use to return the current progress of a class in JSON format with information like userid, user name and score * @param applicationRoot The current running context of the application * @param classId The identifier of the class to use in lookup * @return A JSON representation of a class's score in the order {id, username, userTitle, score, scale, place, order, * goldmedalcount, goldDisplay, silverMedalCount, silverDisplay, bronzeDisplay, bronzeMedalCount} *//*from w w w . ja va2s . c o m*/ @SuppressWarnings("unchecked") public static String getJsonScore(String applicationRoot, String classId) { log.debug("classId: " + classId); String result = new String(); Connection conn = Database.getCoreConnection(applicationRoot); try { Encoder encoder = ESAPI.encoder(); //Returns User's: Name, # of Completed modules and Score CallableStatement callstmnt = null; if (ScoreboardStatus.getScoreboardClass().isEmpty() && !ScoreboardStatus.isClassSpecificScoreboard()) callstmnt = conn.prepareCall("call totalScoreboard()"); //Open Scoreboard not based on a class else { callstmnt = conn.prepareCall("call classScoreboard(?)"); //Class Scoreboard based on classId callstmnt.setString(1, classId); } //log.debug("Executing classScoreboard"); ResultSet resultSet = callstmnt.executeQuery(); JSONArray json = new JSONArray(); JSONObject jsonInner = new JSONObject(); int resultAmount = 0; int prevPlace = 0; int prevScore = 0; int prevGold = 0; int prevSilver = 0; int prevBronze = 0; float baseBarScale = 0; // float tieBreaker = 0; while (resultSet.next()) //For each user in a class { resultAmount++; jsonInner = new JSONObject(); if (resultSet.getString(1) != null) { int place = resultAmount; int score = resultSet.getInt(3); int goldMedals = resultSet.getInt(4); int silverMedals = resultSet.getInt(5); int bronzeMedals = resultSet.getInt(6); if (resultAmount == 1) //First Place is Returned First, so this will be the biggest bar on the scoreboard { int highscore = score; //log.debug("Current Highscore Listing is " + highscore); //Use the high score to scale the width of the bars for the whole scoreboard float maxBarScale = 1.02f; //High Score bar will have a scale of 1 //This will get used when a scale is added to the scoreboard baseBarScale = highscore * maxBarScale; //setting up variables for Tie Scenario Placings prevPlace = 1; prevScore = score; } else { //Does this score line match the one before (Score and Medals)? if so the place shouldnt change if (score == prevScore && goldMedals == prevGold && silverMedals == prevSilver && bronzeMedals == prevBronze) { place = prevPlace; tieBreaker = tieBreaker + 0.01f; } else { prevScore = score; prevPlace = place; prevGold = goldMedals; prevSilver = silverMedals; prevBronze = bronzeMedals; tieBreaker = 0; } } String displayMedal = new String("display: inline;"); String goldDisplayStyle = new String("display: none;"); String silverDisplayStyle = new String("display: none;"); String bronzeDisplayStyle = new String("display: none;"); if (goldMedals > 0) goldDisplayStyle = displayMedal; if (silverMedals > 0) silverDisplayStyle = displayMedal; if (bronzeMedals > 0) bronzeDisplayStyle = displayMedal; int barScale = (int) ((score * 100) / baseBarScale); //bar scale is the percentage the bar should be of the row's context (Highest Possible is depends on scale set in maxBarScale. eg: maxBarScale = 1.1 would mean the max scale would be 91% for a single row) String userMedalString = new String(); if (goldMedals > 0 || silverMedals > 0 || bronzeMedals > 0) { userMedalString += " holding "; if (goldMedals > 0) userMedalString += goldMedals + " gold"; if (silverMedals > 0) { if (goldMedals > 0) //Medals Before, puncuate { if (bronzeMedals > 0) //more medals after silver? Comma { userMedalString += ", "; } else //Say And { userMedalString += " and "; } } userMedalString += silverMedals + " silver"; } if (bronzeMedals > 0) { if (goldMedals > 0 || silverMedals > 0) //Medals Before? { userMedalString += " and "; } userMedalString += bronzeMedals + " bronze"; } //Say Medal(s) at the end of the string userMedalString += " medal"; if (goldMedals + silverMedals + bronzeMedals > 1) userMedalString += "s"; } jsonInner.put("id", new String(encoder.encodeForHTML(resultSet.getString(1)))); //User Id jsonInner.put("username", new String(encoder.encodeForHTML(resultSet.getString(2)))); //User Name jsonInner.put("userTitle", new String(encoder.encodeForHTML(resultSet.getString(2)) + " with " + score + " points" + userMedalString)); //User name encoded for title attribute jsonInner.put("score", new Integer(score)); //Score jsonInner.put("scale", barScale); //Scale of score bar jsonInner.put("place", place); //Place on board jsonInner.put("order", (place + tieBreaker)); //Order on board jsonInner.put("goldMedalCount", new Integer(goldMedals)); jsonInner.put("goldDisplay", goldDisplayStyle); jsonInner.put("silverMedalCount", new Integer(silverMedals)); jsonInner.put("silverDisplay", silverDisplayStyle); jsonInner.put("bronzeMedalCount", new Integer(bronzeMedals)); jsonInner.put("bronzeDisplay", bronzeDisplayStyle); //log.debug("Adding: " + jsonInner.toString()); json.add(jsonInner); } } if (resultAmount > 0) result = json.toString(); else result = new String(); } catch (SQLException e) { log.error("getJsonScore Failure: " + e.toString()); result = null; } catch (Exception e) { log.error("getJsonScore Unexpected Failure: " + e.toString()); result = null; } Database.closeConnection(conn); //log.debug("*** END getJsonScore ***"); return result; }
From source file:common.dao.impl.BaseDAOImpl.java
public List<Map<String, Object>> callQueryProcedure(final String sql, final Object[] params) { logger.debug("start to call procedure" + sql + ", params is " + params); final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); getCurrentSession().doWork(new Work() { public void execute(Connection conn) throws SQLException { CallableStatement cs = conn.prepareCall(sql); if (params != null) { logger.debug("params is not null it's members is " + Arrays.asList(params)); for (int i = 0; i < params.length; i++) { cs.setObject(i + 1, params[i]); }/*ww w .j a v a 2s. c o m*/ } else logger.debug("params is null"); ResultSet rs = cs.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int colCount = metaData.getColumnCount(); while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 1; i <= colCount; i++) { String colName = metaData.getColumnName(i); map.put(colName, rs.getObject(colName)); } result.add(map); } rs.close(); cs.close(); } }); return result; }
From source file:User_Manager.User_TblJDBCTemplate.java
public boolean createUser(String handle, String name, String email, String country, String state, String city, String zip, String religion, String sex, String dob, String phone, String profile_pic, int category[], String fb, String hashedpassword) throws SQLException, FileNotFoundException, IOException, Exception { System.out.println("In User_Tbl_JDBCTemplate> createUser"); System.out.println(" Handle " + handle + " name " + name + " email " + email + " country " + country + " state " + state + " city " + city + " zip " + zip + " religion " + religion + " sex " + sex + " dob " + dob + " phone " + phone + " profile_pic " + profile_pic + " categ " + Arrays.toString(category) + " fb " + fb + "hashed password " + hashedpassword); String password = encrypt(hashedpassword); //System.out.println(testpass); /* Code for User_Store */ /* URL url = new URL(profile_pic); ByteArrayOutputStream out1;/*w w w .ja va 2 s. c o m*/ try (InputStream in = new BufferedInputStream(url.openStream())) { out1 = new ByteArrayOutputStream(); byte[] buf = new byte[1024]; int n = 0; while (-1!=(n=in.read(buf))) { out1.write(buf, 0, n); } System.out.println("In try 1"); out1.close(); } byte[] response1 = out1.toByteArray(); java.util.Date date= new java.util.Date(); Timestamp ts=new Timestamp(date.getTime()); String profile_pic1=handle+".jpg"; System.out.println("out of try1. image .jpg is:"+profile_pic1); try (FileOutputStream fos = new FileOutputStream("C:\\Users\\Rishi\\Documents\\GitHub\\PollingDuck-Spring\\web\\WEB-INF\\pages\\profile_pics"+profile_pic1)) { fos.write(response1); fos.close(); System.out.println("In try 2"); } System.out.println("out of try 2"); */ String category_list_json = Arrays.toString(category); //System.out.println("Category list="+category_list_json); List<Exp_Json> exp = new ArrayList(); Exp_Json obj = null; for (int i = 0; i < category.length; i++) { obj = new Exp_Json(category[i]); try { exp.add(obj); } catch (Exception e) { System.out.println("Errror in Exp_json=" + e); return false; } } // System.out.println("next up is callablestatement"); String exp_json = gson.toJson(exp); 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 { con = conn.getDataSource().getConnection(); System.out.println("10 dec 4pm"); st = con.prepareCall("call createUser2('" + handle + "','" + name + "','" + email + "','" + country + "','" + state + "','" + city + "','" + zip + "','" + religion + "','" + sex + "'" + ",'" + dob + "','" + phone + "','" + profile_pic + "','" + fb + "','" + category_list_json + "','" + exp_json + "'," + 0 + ",'" + password + "')"); st.executeQuery(); con.close(); System.out.println("11 dec 2am"); return true; } catch (Exception e) { System.out.println("CreateUser2 procedure error=" + e); return false; } }
From source file:com.wabacus.system.dataset.sqldataset.GetDataSetByStoreProcedure.java
public Object getDataSet(ReportRequest rrequest, ReportBean rbean, Object typeObj, String sp, List<ConditionBean> lstConditionBeans, String datasource) { if (rbean.getInterceptor() != null) { Object obj = rbean.getInterceptor().beforeLoadData(rrequest, rbean, typeObj, sp); if (!(obj instanceof String)) return obj; sp = (String) obj;/*from ww w.j ava2 s . c o m*/ } if (Config.show_sql) log.info("Execute sql: " + sp); CallableStatement cstmt = null; try { if (datasource == null || datasource.trim().equals("")) datasource = rbean.getSbean().getDatasource(); cstmt = rrequest.getConnection(datasource).prepareCall(sp); AbsDatabaseType dbtype = rrequest.getDbType(datasource); VarcharType varcharObj = (VarcharType) Config.getInstance().getDataTypeByClass(VarcharType.class); IDataType datatypeObj; int idx = 1; if (lstConditionBeans != null && lstConditionBeans.size() > 0) {//?? for (ConditionBean cbTmp : lstConditionBeans) { datatypeObj = cbTmp.getDatatypeObj(); if (datatypeObj == null) datatypeObj = varcharObj; datatypeObj.setPreparedStatementValue(idx++, cbTmp.getConditionValue(rrequest, -1), cstmt, dbtype); } } if (dbtype instanceof Oracle) { cstmt.registerOutParameter(idx, OracleTypes.CURSOR); } rrequest.addUsedStatement(cstmt); cstmt.executeQuery(); ResultSet rs = null; if (dbtype instanceof Oracle) { rs = (ResultSet) cstmt.getObject(idx); } else { rs = cstmt.getResultSet(); } return rs; } catch (SQLException e) { throw new WabacusRuntimeException( "??" + rbean.getPath() + "?SQL" + sp + "", e); } }
From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java
@Override public int getAuctionCount() { int count = 0; Connection conn = null;/*ww w . j ava 2s . co m*/ CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETAUCTIONCOUNT ()}"); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "count", count }); } return count; }
From source file:com.app.uploads.ImageTest.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from ww w . j a v a 2 s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); String type = ""; CallableStatement pro; String UPLOAD_DIRECTORY = getServletContext().getRealPath("\\uploads\\"); try { if (ServletFileUpload.isMultipartContent(request)) { try { String name = ""; List<FileItem> multiparts; multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); for (FileItem item : multiparts) { if (!item.isFormField()) { name = new File(item.getName()).getName(); item.write(new File(UPLOAD_DIRECTORY + File.separator + name)); } else if (item.isFormField()) { String fiel = item.getFieldName(); InputStream is = item.getInputStream(); byte[] b = new byte[is.available()]; is.read(b); type = new String(b); } } //File uploaded successfully Connection connect = OracleConnect.getConnect(Dir.Host, Dir.Port, Dir.Service, Dir.UserName, Dir.PassWord); pro = connect.prepareCall("{call STILL_INSERT_TEST(?,?)}"); pro.setInt(1, 2); pro.setString(2, name); pro.executeQuery(); pro.close(); connect.close(); if (name != null) { request.setAttribute("type", type); request.setAttribute("success", "ok"); } } catch (Exception ex) { request.setAttribute("message", "File Upload Failed due to " + ex); } } else { request.setAttribute("message", "Sorry this Servlet only handles file upload request"); } request.getRequestDispatcher("/SearchEngine.jsp").forward(request, response); } finally { out.close(); } }