List of usage examples for java.sql ResultSet getDouble
double getDouble(String columnLabel) throws SQLException;
ResultSet object as a double in the Java programming language. From source file:ProcessRequest.java
public JSONArray parseQueryResults(ResultSet rs, String table) throws SQLException, JSONException { JSONArray resultJSONArray = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); int columns = rsmd.getColumnCount(); while (rs.next()) { JSONObject result = new JSONObject(); JSONObject resultMeta = new JSONObject(); resultMeta.put("table", table); result.put("metadata", resultMeta); for (int i = 1; i <= columns; i++) { //out.println("<td>"+rs.getString(i)+"</td>"); int type = rsmd.getColumnType(i); //result.put(rsmd.getColumnName(i), rs.get) switch (type) { case Types.BIT: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.TINYINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.SMALLINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.INTEGER: //System.out.println(rsmd.getColumnName(i) + " type: "+type); result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.BIGINT: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.FLOAT: result.put(rsmd.getColumnName(i), rs.getFloat(i)); break; case Types.REAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DOUBLE: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.NUMERIC: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.DECIMAL: result.put(rsmd.getColumnName(i), rs.getDouble(i)); break; case Types.CHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.VARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.DATE: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; }//from ww w . ja v a 2 s. c o m case Types.TIME: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.TIMESTAMP: { java.util.Date date = rs.getDate(i); result.put(rsmd.getColumnName(i), date.getTime()); break; } case Types.BINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.VARBINARY: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.LONGVARBINARY: result.put(rsmd.getColumnName(i), rs.getLong(i)); break; case Types.NULL: result.put(rsmd.getColumnName(i), ""); break; case Types.BOOLEAN: result.put(rsmd.getColumnName(i), rs.getBoolean(i)); break; case Types.ROWID: result.put(rsmd.getColumnName(i), rs.getInt(i)); break; case Types.NCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.NVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.LONGNVARCHAR: result.put(rsmd.getColumnName(i), rs.getString(i)); break; case Types.SQLXML: case Types.NCLOB: case Types.DATALINK: case Types.REF: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.BLOB: case Types.CLOB: default: result.put(rsmd.getColumnName(i), rs.getString(i)); break; } } //if(table.equals("Ticket")) //System.out.println(result.toString(5)); resultJSONArray.put(result); } return resultJSONArray; }
From source file:Query8Servlet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*www . ja v a 2s . c om*/ * * @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 { Connection connection = null; Statement stmt = null; String patientswithAllSqlStat = null; String patientswithoutAllSqlStat = null; ResultSet rsetForALL = null; ResultSet rsetForNOTALL = null; String patientID = null; double expValue = 0; List<Double> exprValuesForPatientID = null; Map<String, List<Double>> patientToExpValueALL = new HashMap<String, List<Double>>(); Map<String, List<Double>> patientToExpValueNOTALL = new HashMap<String, List<Double>>(); //Variables for average correlation int i, j; double correlation = 0; Set<String> patientsForALLSet = null; String[] patientsForALL = null; Set<String> patientsForNOTALLSet = null; String[] patientsForNOTALL = null; Set<String> patientsForTestCaseSet = null; String[] patientsForTestCase = null; List<Double> patientAllExprValues = null; Double[] patientAllExprValuesArr = null; List<Double> patientNotAllExprValues = null; Double[] patientNotAllExprValuesArr = null; List<Double> testPatientExprValues = null; Double[] testPatientExprValuesArr = null; JSONObject diseaseClassification = new JSONObject(); JSONArray testResults = new JSONArray(); response.setContentType("application/json"); try (PrintWriter out = response.getWriter()) { /* TODO output your page here. You may use following sample code. */ Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@VIVEK:1521/ORCL", "system", "vivek"); stmt = connection.createStatement(); patientswithAllSqlStat = "SELECT C.P_ID, M.EXPRESSION FROM MICROARRAY_FACT M, PROBE P, " + "CLINICAL_FACT C WHERE M.PB_ID = P.PB_ID AND M.S_ID = C.S_ID " + "AND C.P_ID IN (SELECT P_ID FROM CLINICAL_FACT, DISEASE " + "WHERE CLINICAL_FACT.DS_ID = DISEASE.DS_ID AND NAME = 'ALL') " + "AND P.USER_ID IN(65772884,17144710,31997186,85557586,11333636," + "41333415,45926811,13947282,83398521,23552119," + "74496827,24984526,28863379,88257558,31308500," + "41464216,43866587,60661836,37998407,94113401," + "88596261,48199244,69156037,4826120,1433276," + "16073088,75492172,58672549,87592194,38422427," + "58792011,15295292,21633757,89697658,97606543," + "18493181,75434512,72920004,52948490,53478188,40567338)"; patientswithoutAllSqlStat = "SELECT C.P_ID, M.EXPRESSION FROM MICROARRAY_FACT M, PROBE P, " + "CLINICAL_FACT C WHERE M.PB_ID = P.PB_ID AND M.S_ID = C.S_ID " + "AND C.P_ID IN (SELECT P_ID FROM CLINICAL_FACT, DISEASE " + "WHERE CLINICAL_FACT.DS_ID = DISEASE.DS_ID AND NAME <> 'ALL') " + "AND P.USER_ID IN(65772884,17144710,31997186,85557586,11333636," + "41333415,45926811,13947282,83398521,23552119," + "74496827,24984526,28863379,88257558,31308500," + "41464216,43866587,60661836,37998407,94113401," + "88596261,48199244,69156037,4826120,1433276," + "16073088,75492172,58672549,87592194,38422427," + "58792011,15295292,21633757,89697658,97606543," + "18493181,75434512,72920004,52948490,53478188,40567338)"; //Executing the query for ALL rsetForALL = stmt.executeQuery(patientswithAllSqlStat); //Converting the result set for "ALL" to the map while (rsetForALL.next()) { patientID = rsetForALL.getString(1); expValue = rsetForALL.getDouble(2); if (patientToExpValueALL.get(patientID) != null) { exprValuesForPatientID = patientToExpValueALL.get(patientID); } else { exprValuesForPatientID = new ArrayList<Double>(); } exprValuesForPatientID.add(expValue); patientToExpValueALL.put(patientID, exprValuesForPatientID); } //Executing the query for NOT ALL rsetForNOTALL = stmt.executeQuery(patientswithoutAllSqlStat); //Converting the result set for "NOT ALL" to the map while (rsetForNOTALL.next()) { patientID = rsetForNOTALL.getString(1); expValue = rsetForNOTALL.getDouble(2); if (patientToExpValueNOTALL.get(patientID) != null) { exprValuesForPatientID = patientToExpValueNOTALL.get(patientID); } else { exprValuesForPatientID = new ArrayList<Double>(); } exprValuesForPatientID.add(expValue); patientToExpValueNOTALL.put(patientID, exprValuesForPatientID); } //Getting the information of test cases from the file Map<String, List<Double>> testPatientsToExprValues = getTestCaseInformation(); //Getting the data structures ready for calculating P value patientsForALLSet = patientToExpValueALL.keySet(); patientsForALL = new String[patientsForALLSet.size()]; patientsForALLSet.toArray(patientsForALL); patientsForNOTALLSet = patientToExpValueNOTALL.keySet(); patientsForNOTALL = new String[patientsForNOTALLSet.size()]; patientsForNOTALLSet.toArray(patientsForNOTALL); patientsForTestCaseSet = testPatientsToExprValues.keySet(); patientsForTestCase = new String[patientsForTestCaseSet.size()]; patientsForTestCaseSet.toArray(patientsForTestCase); //Iterating through the test patients to calculate ra's and rb's for (i = 0; i < patientsForTestCase.length; i++) { testPatientExprValues = testPatientsToExprValues.get(patientsForTestCase[i]); testPatientExprValuesArr = new Double[testPatientExprValues.size()]; testPatientExprValues.toArray(testPatientExprValuesArr); //Initializing the test patient's correlation array with the two groups double[] correlation_ra = new double[patientsForALL.length]; double[] correlation_rb = new double[patientsForNOTALL.length]; //Calculating the correlation for between ALL for (j = 0; j < patientsForALL.length; j++) { patientAllExprValues = patientToExpValueALL.get(patientsForALL[j]); patientAllExprValuesArr = new Double[patientAllExprValues.size()]; ; patientAllExprValues.toArray(patientAllExprValuesArr); correlation = new PearsonsCorrelation().correlation( ArrayUtils.toPrimitive(patientAllExprValuesArr), ArrayUtils.toPrimitive(testPatientExprValuesArr)); correlation_ra[j] = correlation; } //Calculating the correlation for between ALL and AML for (j = 0; j < patientsForNOTALL.length; j++) { patientNotAllExprValues = patientToExpValueNOTALL.get(patientsForNOTALL[j]); patientNotAllExprValuesArr = new Double[patientNotAllExprValues.size()]; ; patientNotAllExprValues.toArray(patientNotAllExprValuesArr); correlation = new PearsonsCorrelation().correlation( ArrayUtils.toPrimitive(patientNotAllExprValuesArr), ArrayUtils.toPrimitive(testPatientExprValuesArr)); correlation_rb[j] = correlation; } //Calculating the p value of the two correlation arrays ra and rb double p_value = new TTest().tTest(correlation_ra, correlation_rb); if (p_value < 0.01) { diseaseClassification.put(patientsForTestCase[i], "ALL"); } else { diseaseClassification.put(patientsForTestCase[i], "NOT ALL"); } } testResults.put(diseaseClassification); out.print(diseaseClassification); //Closing the statement and connection stmt.close(); connection.close(); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); e.printStackTrace(); return; } catch (ClassNotFoundException e) { System.out.println("Where is your Oracle JDBC Driver?"); e.printStackTrace(); return; } catch (JSONException e) { System.out.println("Where is your Oracle JDBC Driver?"); e.printStackTrace(); return; } }
From source file:com.sdcs.courierbooking.service.UserServiceImpl.java
@Override public String delBoyDeliveredList(String deliveryBoyId) { // TODO Auto-generated method stub JSONObject deliveredList = new JSONObject(); ResultSet deliveredListResultSet = userDao.delBoyDeliveredList(deliveryBoyId); if (deliveredListResultSet != null) { Timestamp name = null;/*from w w w.j a va2 s .co m*/ String name2 = "0"; int number = 0; double extraDistance = 0.00; double damount = 0.00; double tamount = 0.00; double textradistance = 0.00; double tAmount = 0.0; double tKm = 0.0; int tNo = 0; String date = ""; try { JSONArray couriersArray = new JSONArray(); while (deliveredListResultSet.next()) { JSONObject couriersObject = new JSONObject(); name = deliveredListResultSet.getTimestamp("time_of_booking"); String amounta = deliveredListResultSet.getString("deliveryBoyamount"); damount = Double.parseDouble(amounta); extraDistance = deliveredListResultSet.getDouble("extra_distance"); String DATE_FORMAT = "yyyy/MM/dd"; SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT); date = sdf.format(name); if (date.equals(name2)) { tamount = damount + tamount; textradistance = textradistance + extraDistance; number = number + 1; } else if (name2 != ("0")) { couriersObject.put("restaurant_name", name2); couriersObject.put("number_of_couriers", number); couriersObject.put("distance", textradistance); couriersObject.put("amount", tamount); couriersArray.put(couriersObject); tamount = damount; textradistance = extraDistance; number = 1; name2 = date; tAmount = 0.0; tKm = 0.0; tNo = 0; ResultSet set = userDao.tranferLogicMoneyAddition(deliveryBoyId, date); if (set != null) { while (set.next()) { if (set.getString("del_boy_id_1").equals(deliveryBoyId)) { ++tNo; tAmount = tAmount + Double.parseDouble(set.getString("del_boy1_amount")); String km = set.getString("del_boy1_distance"); String kmRemove = km.replace(" km", ""); tKm = tKm + Double.parseDouble(kmRemove); } if (set.getString("del_boy_id_2").equals(deliveryBoyId)) { ++tNo; tAmount = tAmount + Double.parseDouble(set.getString("del_boy2_amount")); String km = set.getString("del_boy2_distance"); String kmRemove = km.replace(" km", ""); tKm = tKm + Double.parseDouble(kmRemove); } if (set.getString("del_boy_id_3").equals(deliveryBoyId)) { ++tNo; tAmount = tAmount + Double.parseDouble(set.getString("del_boy3_amount")); String km = set.getString("del_boy3_distance"); String kmRemove = km.replace(" km", ""); tKm = tKm + Double.parseDouble(kmRemove); } } } number += tNo; textradistance += tKm; tamount += tAmount; tAmount = 0.0; tKm = 0.0; tNo = 0; } else { tAmount = 0.0; tKm = 0.0; tNo = 0; ResultSet set = userDao.tranferLogicMoneyAddition(deliveryBoyId, date); if (set != null) { while (set.next()) { if (set.getString("del_boy_id_1").equals(deliveryBoyId)) { ++tNo; tAmount = tAmount + Double.parseDouble(set.getString("del_boy1_amount")); String km = set.getString("del_boy1_distance"); String kmRemove = km.replace(" km", ""); tKm = tKm + Double.parseDouble(kmRemove); } if (set.getString("del_boy_id_2").equals(deliveryBoyId)) { ++tNo; tAmount = tAmount + Double.parseDouble(set.getString("del_boy2_amount")); String km = set.getString("del_boy2_distance"); String kmRemove = km.replace(" km", ""); tKm = tKm + Double.parseDouble(kmRemove); } if (set.getString("del_boy_id_3").equals(deliveryBoyId)) { ++tNo; tAmount = tAmount + Double.parseDouble(set.getString("del_boy3_amount")); String km = set.getString("del_boy3_distance"); String kmRemove = km.replace(" km", ""); tKm = tKm + Double.parseDouble(kmRemove); } } } number += tNo; textradistance += tKm; tamount += tAmount; tAmount = 0.0; tKm = 0.0; tNo = 0; tamount = damount; textradistance = extraDistance; number = 1; name2 = date; } } JSONObject couriersObject = new JSONObject(); couriersObject.put("restaurant_name", date); couriersObject.put("number_of_couriers", number + tNo); couriersObject.put("distance", textradistance + tKm); couriersObject.put("amount", tamount + tAmount); couriersArray.put(couriersObject); deliveredList.put("result", true); deliveredList.put("Delivered_courierss", couriersArray); System.out.println(deliveredList); } catch (SQLException e) { deliveredList.put("result", false); try { deliveredListResultSet.close(); } catch (SQLException sqlEx) { System.out.println("Exception in QuickHistoryServerImpl"); } } finally { try { deliveredListResultSet.close(); } catch (SQLException e) { System.out.println("Exception in QuickHistoryServerImpl"); } } } else { deliveredList.put("result", false); } return deliveredList.toString(); }
From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java
@SuppressWarnings("deprecation") private void assertNonExistingColumn(final ResultSet rs) throws Exception { int nonExistingColIndex = Integer.MAX_VALUE; String nonExistingColName = "col" + nonExistingColIndex; try {/*from w w w .java2s. c o m*/ rs.getString(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getString(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBoolean(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBoolean(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getByte(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getByte(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getShort(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getShort(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getInt(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getInt(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getLong(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getLong(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getFloat(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getFloat(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDouble(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getDouble(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColIndex, 1); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColName, 1); fail(); } catch (SQLException ignore) { } try { rs.getBytes(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBytes(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getUnicodeStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getUnicodeStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(nonExistingColName); fail(); } catch (SQLException ignore) { } }
From source file:de.innovationgate.webgate.api.jdbc.WGDatabaseImpl.java
private int determinePatchLevel(JDBCConnectionProvider connProvider) { int patchLevel = 0; Statement stmt = null;//from www . j ava 2 s . com ResultSet res = null; try { stmt = connProvider.getConnection().createStatement(); res = stmt.executeQuery( "SELECT datatype, numbervalue, textvalue FROM extensiondata WHERE entity_id IS NULL AND name='" + DBMETA_PATCH_LEVEL.toLowerCase() + "'"); if (res.next()) { int type = res.getInt(1); Double numberValue = res.getDouble(2); String textValue = res.getString(3); if (type == WGDocumentImpl.ITEMTYPE_NUMBER) { patchLevel = numberValue.intValue(); } else if (type == WGDocumentImpl.ITEMTYPE_SERIALIZED_XSTREAM) { XStream xstream = new XStream(new Dom4JDriver()); patchLevel = ((Number) xstream.fromXML(textValue)).intValue(); } } } catch (Exception e) { WGFactory.getLogger().error("Exception determining CS5 patch level", e); } finally { try { if (res != null) { res.close(); } if (stmt != null) { stmt.close(); } } catch (Exception e) { WGFactory.getLogger().error("Exception closing resource for CS version determination", e); } } return patchLevel; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the commission percentages.// ww w . ja va2 s . c o m * * @param spId the sp id * @param appId the app id * @return the commission percentages * @throws Exception the exception */ public Map<String, CommissionPercentagesDTO> getCommissionPercentages(String spId, Integer appId) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append("select id,subscriber,merchant_code,app_id,sp_commission,ads_commission,opco_commission from ") .append("rates_percentages where subscriber=? and app_id=?"); Map<String, CommissionPercentagesDTO> requestSet = new HashMap<String, CommissionPercentagesDTO>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setString(1, spId); ps.setInt(2, appId); results = ps.executeQuery(); String merchantCode = null; while (results.next()) { merchantCode = results.getString("merchant_code"); CommissionPercentagesDTO dto = new CommissionPercentagesDTO(); dto.setId(results.getInt("id")); dto.setSpId(results.getString("subscriber")); dto.setMerchantCode(merchantCode); dto.setAppId(results.getInt("app_id")); dto.setSpCommission(new BigDecimal(results.getDouble("sp_commission"))); dto.setAdsCommission(new BigDecimal(results.getDouble("ads_commission"))); dto.setOpcoCommission(new BigDecimal(results.getDouble("opco_commission"))); requestSet.put(merchantCode, dto); } } catch (Exception e) { handleException("getCommissionPercentages", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } return requestSet; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the time consumption by api.//from w ww . j a v a 2 s. co m * * @param operator the operator * @param userId the user id * @param fromDate the from date * @param toDate the to date * @param api the api * @return the time consumption by api * @throws Exception the exception */ public String[] getTimeConsumptionByAPI(String operator, String userId, String fromDate, String toDate, String api) throws Exception { if (log.isDebugEnabled()) { log.debug("getAllResponseTimesForAllAPIs() for Operator " + operator + " Subscriber " + userId + " betweeen " + fromDate + " to " + toDate); } if (operator.contains("__ALL__")) { operator = "%"; } if (userId.contains("__ALL__")) { userId = "%"; } Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append( "SELECT api, MAX(hightestTime) as highestConsumption, SUM(sumServiceTime)/SUM(sumCount) as avgTotalConsump FROM (") .append("SELECT api,SUM(response_count) as sumCount, SUM(serviceTime) as sumServiceTime, (SUM(serviceTime)/SUM(response_count)) as hightestTime, STR_TO_DATE(time,'%Y-%m-%d') as date FROM ") .append(ReportingTable.SB_API_RESPONSE_SUMMARY.getTObject()) .append(" WHERE api = ? AND (time BETWEEN ? AND ?) AND operatorId LIKE ? AND userId LIKE ? GROUP BY date) AS T;"); String[] timeConsumerData = new String[3]; try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setString(1, api); ps.setString(2, fromDate + " 00:00:00"); ps.setString(3, toDate + " 23:59:59"); ps.setString(4, operator); ps.setString(5, userId); results = ps.executeQuery(); while (results.next()) { timeConsumerData[0] = results.getString("api"); timeConsumerData[1] = Double.toString(results.getDouble("highestConsumption")); timeConsumerData[2] = Double.toString(results.getDouble("avgTotalConsump")); } } catch (Exception e) { handleException("getTimeConsumptionByAPI", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } return timeConsumerData; }
From source file:rems.Global.java
public static double get_Accnt_Net_Bals(int accntID) { try {/*from ww w . j av a2 s .c o m*/ String strSql = "SELECT a.net_balance " + "FROM accb.accb_chart_of_accnts a " + "WHERE(a.accnt_id = " + accntID + ")"; ResultSet dtst = Global.selectDataNoParams(strSql); while (dtst.next()) { return dtst.getDouble(1); } return 0.00; } catch (SQLException ex) { return 0.00; } }
From source file:rems.Global.java
public static double get_Accnt_Bls_Bals(int accntID, long blsID) { try {//from w w w .ja va2 s. co m String strSql = "SELECT a.net_balance " + "FROM accb.accb_balsheet_details a " + "WHERE(a.accnt_id = " + accntID + " and a.balsheet_header_id = " + blsID + ")"; ResultSet dtst = Global.selectDataNoParams(strSql); while (dtst.next()) { return dtst.getDouble(1); } return 0.00; } catch (SQLException ex) { return 0.00; } }
From source file:rems.Global.java
public static double getAcntsBdgtdAmnt(long bdgtID, int accntID, String trnsdate) { try {/*from w w w . j a v a2s .c o m*/ SimpleDateFormat frmtr = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); SimpleDateFormat frmtr1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); trnsdate = frmtr1.format(frmtr.parse(trnsdate)); String strSql = "SELECT a.limit_amount " + "FROM accb.accb_budget_details a " + "WHERE((a.budget_id = " + bdgtID + ") and (a.accnt_id = " + accntID + ") and (to_timestamp('" + trnsdate + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.start_date,'YYYY-MM-DD HH24:MI:SS')" + " AND to_timestamp(a.end_date,'YYYY-MM-DD HH24:MI:SS')))"; ResultSet dtst = Global.selectDataNoParams(strSql); while (dtst.next()) { return dtst.getDouble(1); } return 0.00; } catch (ParseException ex) { return 0.00; } catch (SQLException ex) { return 0.00; } }