List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet getInt
int getInt(String columnLabel) throws InvalidResultSetAccessException;
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String loginUser(HttpServletRequest req) { String val = "Ok"; SqlRowSet srs1 = db.getJdbc().queryForRowSet("select [value] from info_values where name='ver'"), srs2; if (srs1.next()) { if (!srs1.getString("value").equals(req.getParameter("ver"))) return "2"; } else/* w w w .j a v a 2 s.c o m*/ return "0"; srs1 = db.getJdbc().queryForRowSet( "select * from businessgame.dbo.[user] where name='" + req.getParameter("user") + "'"); System.out.println("Ver : " + req.getParameter("ver")); User userAcc; if (srs1.next()) { if (req.getParameter("pass").equals(srs1.getString("pass"))) { HashMap<String, String> storages = new HashMap<String, String>(), marketLicenses = new HashMap<String, String>(), sectorBlueprints = new HashMap<String, String>(); HashMap<String, Double> sectorCost = new HashMap<String, Double>(); ArrayList<Installment> installments = getUserInstallments(req.getParameter("user")); srs2 = db.getJdbc() .queryForRowSet("select cost from info_zone where id='" + srs1.getString("zone") + "'"); double propCost; if (srs2.next()) { propCost = srs2.getDouble("cost"); } else return "0"; srs2 = db.getJdbc().queryForRowSet( "select id,[zone] from storage where [user]='" + req.getParameter("user") + "'"); while (srs2.next()) { storages.put(srs2.getString("zone"), srs2.getString("id")); } srs2 = db.getJdbc().queryForRowSet("select id,[zone] from user_market_license where [user]='" + req.getParameter("user") + "'"); while (srs2.next()) { marketLicenses.put(srs2.getString("zone"), srs2.getString("id")); } System.out.println(marketLicenses); srs2 = db.getJdbc().queryForRowSet( "select id,sector,cost from user_sector_blueprint,info_sector where [user]='" + req.getParameter("user") + "' and sector=name"); while (srs2.next()) { sectorBlueprints.put(srs2.getString("sector"), srs2.getString("id")); sectorCost.put(srs2.getString("sector"), srs2.getDouble("cost")); } userAcc = new User(srs1.getString("name"), srs1.getString("email"), srs1.getString("dob"), srs1.getString("about"), srs1.getDouble("money"), propCost, srs1.getLong("rep"), srs1.getString("zone"), srs1.getInt("level"), storages, marketLicenses, sectorBlueprints, sectorCost, installments); val = gson.toJson(userAcc); storages = null; marketLicenses = null; sectorBlueprints = null; sectorCost = null; installments = null; } else val = "0"; } else val = "1"; userAcc = null; srs1 = null; srs2 = null; gc(); return val; }
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String sellStorageEquipment(HttpServletRequest req) { String val = "0", idInc, sqls[]; ArrayList<String> sqlL = new ArrayList<String>(); SqlRowSet srs1, srs2; idInc = getUniqueIncrementIdNew("market_equipment"); sqlL.add("insert into market_equipment values ('" + KEY_MARKET_EQUIPMENT + idInc + "','" + req.getParameter("equipmentId") + "','" + req.getParameter("marketZone") + "'," + req.getParameter("price") + ")"); System.out.println("insert into market_equipment values ('" + KEY_MARKET_EQUIPMENT + idInc + "','" + req.getParameter("equipmentId") + "','" + req.getParameter("marketZone") + "'," + req.getParameter("price") + ")"); sqls = new String[sqlL.size()]; sqlL.toArray(sqls);//from ww w.j a va2 s . c o m db.getJdbc().batchUpdate(sqls); // srs1 = db.getJdbc().queryForRowSet("select storage_equipment.id,equipment,quality,durability,size,operational,draw from storage_equipment,list_equipment,desc_equipment,info_equipment where storage='"+req.getParameter("storage")+"' and storage_equipment.id=list_equipment.id and list_equipment.desc=desc_equipment.id and name=equipment"); // ArrayList<StorageEquipment> equipments = new ArrayList<StorageEquipment>(); // while(srs1.next()){ // srs2 = db.getJdbc().queryForRowSet("select id from market_equipment where storage_equipment_id='"+srs1.getString("id")+"'"); // if(!srs2.next()) // equipments.add(new StorageEquipment(srs1.getString("id"), srs1.getString("equipment"), srs1.getInt("quality"), srs1.getDouble("durability"), srs1.getDouble("size"), srs1.getDouble("operational"), srs1.getString("draw"))); // } srs1 = db.getJdbc().queryForRowSet( "select storage_equipment.id,equipment,quality,durability,size,operational,draw from storage_equipment,list_equipment,desc_equipment,info_equipment where storage='" + req.getParameter("storage") + "' and storage_equipment.id=list_equipment.id and list_equipment.[desc]=desc_equipment.id and name=equipment"); ArrayList<StorageEquipment> storageEquipments = new ArrayList<StorageEquipment>(); ArrayList<MarketEquipment> marketEquipments = new ArrayList<MarketEquipment>(); while (srs1.next()) { srs2 = db.getJdbc().queryForRowSet( "select market_equipment.id,equipment,market_equipment.price,quality,durability,size,operational,draw from storage_equipment,market_equipment,desc_equipment,list_equipment,info_equipment where storage_equipment_id='" + srs1.getString("id") + "' and storage_equipment.id=storage_equipment_id and list_equipment.id=storage_equipment.id and list_equipment.[desc]=desc_equipment.id and equipment=name"); if (srs2.next()) { marketEquipments.add(new MarketEquipment(srs2.getString("id"), "", srs2.getString("equipment"), srs2.getDouble("price"), srs2.getInt("quality"), srs2.getDouble("durability"), srs2.getDouble("size"), srs2.getDouble("operational"), srs2.getString("draw"))); } else { storageEquipments.add(new StorageEquipment(srs1.getString("id"), srs1.getString("equipment"), srs1.getInt("quality"), srs1.getDouble("durability"), srs1.getDouble("size"), srs1.getDouble("operational"), srs1.getString("draw"))); } } ArrayList<String> data = new ArrayList<String>(); data.add(gson.toJson(storageEquipments)); data.add(gson.toJson(marketEquipments)); val = gson.toJson(data); storageEquipments = null; marketEquipments = null; sqlL = null; sqls = null; srs1 = null; srs2 = null; idInc = null; gc(); return val; }
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String sellStorageProduct(HttpServletRequest req) { String val = "0", idInc, sqls[]; double remain, size, offer, basePrice, price, lowest, highest; ArrayList<String> sqlL = new ArrayList<String>(); SqlRowSet srs1, srs2; //0 = internal error //1 = CBM yg ditawarkan lebih besar dari yg dimiliki //2 = harga melampaui 25% lebih murah dari harga jual yang disarankan //3 = harga melampaui 25% lebih mahal dari harga jual yang disarankan srs1 = db.getJdbc().queryForRowSet("select price,size from storage_product,desc_product where id='" + req.getParameter("productId") + "' and storage_product.[desc]=desc_product.id"); if (srs1.next()) { size = srs1.getDouble("size"); basePrice = srs1.getDouble("price"); } else// www.ja v a 2s . c o m return "0"; srs1 = db.getJdbc().queryForRowSet( "select size from market_product where storage_product_id='" + req.getParameter("productId") + "'"); if (srs1.next()) size -= srs1.getDouble("size"); offer = Double.parseDouble(req.getParameter("offer")); price = Double.parseDouble(req.getParameter("price")); lowest = basePrice - (basePrice * 0.25); highest = basePrice + (basePrice * 0.25); if (size < offer) return "1"; if (price < lowest) return "2"; if (price > highest) return "3"; srs1 = db.getJdbc() .queryForRowSet("select id,size from market_product where storage_product_id='" + req.getParameter("productId") + "' and [zone]='" + req.getParameter("zone") + "' and price='" + req.getParameter("price") + "'"); if (srs1.next()) sqlL.add("update market_product set size='" + (srs1.getDouble("size") + offer) + "' where id='" + srs1.getString("id") + "'"); else { idInc = getUniqueIncrementIdNew("market_product"); sqlL.add("insert into market_product values ('" + KEY_MARKET_PRODUCT + idInc + "','" + req.getParameter("productId") + "','','" + req.getParameter("zone") + "','" + req.getParameter("price") + "','" + offer + "')"); } sqls = new String[sqlL.size()]; sqlL.toArray(sqls); db.getJdbc().batchUpdate(sqls); val = "Ok"; // srs1 = db.getJdbc().queryForRowSet("select storage_product.id,product,quality,size,draw from storage_product,desc_product,info_product where storage='"+req.getParameter("storage")+"' and desc_product.id=storage_product.desc and product=name"); // ArrayList<StorageProduct> products = new ArrayList<StorageProduct>(); // while(srs1.next()){ // remain = srs1.getDouble("size"); // srs2 = db.getJdbc().queryForRowSet("select size from market_product where storage_product_id='"+srs1.getString("id")+"'"); // while(srs2.next()){ // remain -= srs2.getDouble("size"); // } // if(remain > 0) // products.add(new StorageProduct(srs1.getString("id"), srs1.getString("product"), srs1.getInt("quality"), new BigDecimal(Double.valueOf(remain)).setScale(2, BigDecimal.ROUND_HALF_EVEN).doubleValue(), srs1.getString("draw"))); // } srs1 = db.getJdbc().queryForRowSet( "select storage_product.id,product,quality,size,draw from storage_product,desc_product,info_product where storage='" + req.getParameter("storage") + "' and desc_product.id=storage_product.[desc] and product=name"); ArrayList<StorageProduct> storageProducts = new ArrayList<StorageProduct>(); ArrayList<MarketProduct> marketProducts = new ArrayList<MarketProduct>(); while (srs1.next()) { remain = srs1.getDouble("size"); srs2 = db.getJdbc().queryForRowSet( "select market_product.id,product,market_product.price,quality,market_product.size,draw from market_product,desc_product,info_product,storage_product where storage_product_id='" + srs1.getString("id") + "' and storage_product_id=storage_product.id and desc_product.id=storage_product.[desc] and product=name"); while (srs2.next()) { remain -= srs2.getDouble("size"); marketProducts.add(new MarketProduct(srs2.getString("id"), "", srs2.getString("product"), srs2.getDouble("price"), srs2.getInt("quality"), srs2.getDouble("size"), srs2.getString("draw"))); } if (remain > 0) storageProducts .add(new StorageProduct(srs1.getString("id"), srs1.getString("product"), srs1.getInt("quality"), new BigDecimal(Double.valueOf(remain)) .setScale(2, BigDecimal.ROUND_HALF_EVEN).doubleValue(), srs1.getString("draw"))); } ArrayList<String> data = new ArrayList<String>(); data.add(gson.toJson(storageProducts)); data.add(gson.toJson(marketProducts)); val = gson.toJson(data); sqlL = null; storageProducts = null; marketProducts = null; sqls = null; srs1 = null; srs2 = null; idInc = null; gc(); return val; }
From source file:org.kuali.kfs.vnd.batch.dataaccess.DebarredVendorDaoJdbc.java
@Override public List<DebarredVendorMatch> match() { String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'"; String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl"; String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl"; String where = " WHERE " + active; String eplsFields = "excl.VNDR_EXCL_ID, excl.VNDR_EXCL_LOAD_DT, excl.VNDR_EXCL_NM, excl.VNDR_EXCL_LN1_ADDR, excl.VNDR_EXCL_LN2_ADDR, excl.VNDR_EXCL_CTY_NM" + ", excl.VNDR_EXCL_ST_CD, excl.VNDR_EXCL_PRVN_NM, excl.VNDR_EXCL_ZIP_CD, excl.VNDR_EXCL_OTHR_NM, excl.VNDR_EXCL_DESC_TXT"; String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromName = " FROM pur_vndr_dtl_t dtl"; String name = filter("dtl.VNDR_NM", "., "); String eplsName = filter("excl.VNDR_EXCL_NM", "., "); String onName = " ON " + compare(name, eplsName, false); // use = to compare String sqlName = selectName + fromName + joinExcl + onName + where; String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromAlias = " FROM pur_vndr_alias_t als"; String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; String alias = filter("als.VNDR_ALIAS_NM", "., "); String eplsAlias = filter("excl.VNDR_EXCL_NM", "., "); String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where; String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID"; String fromAddr = " FROM pur_vndr_addr_t addr"; String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; ///* w ww . j a v a 2 s .co m*/ String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# "); String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# "); String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare // String city = filter("addr.VNDR_CTY_NM", "., "); String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., "); String cmpCity = compare(city, eplsCity, false); // use = to compare // String state = "upper(addr.VNDR_ST_CD)"; String eplsState = "upper(excl.VNDR_EXCL_ST_CD)"; String cmpState = compare(state, eplsState, false); // use = to compare // String zip = filter("addr.VNDR_ZIP_CD", "-"); String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-"); String cmpZip = compare(zip, eplsZip, false); // use = to compare String fullZip = "length(addr.VNDR_ZIP_CD) > 5"; // String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState; String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where; String max = ", MAX(VNDR_ADDR_GNRTD_ID)"; String selectFields = "VNDR_HDR_GNRTD_ID, VNDR_DTL_ASND_ID, VNDR_EXCL_ID, VNDR_EXCL_LOAD_DT, VNDR_EXCL_NM, VNDR_EXCL_LN1_ADDR, VNDR_EXCL_LN2_ADDR, VNDR_EXCL_CTY_NM" + ", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT"; String select = "SELECT " + selectFields + max; String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr; String from = " FROM (" + subqr + ")"; String group = " GROUP BY " + selectFields; String sql = select + from + group; List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>(); try { SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql); DebarredVendorMatch match; while (rs.next()) { match = new DebarredVendorMatch(); match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1))); match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2))); match.setLoadDate(rs.getDate(4)); match.setName(rs.getString(5)); match.setAddress1(rs.getString(6)); match.setAddress2(rs.getString(7)); match.setCity(rs.getString(8)); match.setState(rs.getString(9)); match.setProvince(rs.getString(10)); match.setZip(rs.getString(11)); match.setAliases(rs.getString(12)); match.setDescription(rs.getString(13)); match.setAddressGeneratedId(rs.getLong(14)); // didn't find a matched address, search for best one if (match.getAddressGeneratedId() == 0) { match.setAddressGeneratedId(getMatchAddressId(match)); } DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match); if (oldMatch == null) { // store the match only if an exact old match doesn't exist match.setConfirmStatusCode("U"); // status - Unprocessed matches.add(match); } } } catch (Exception e) { // if exception occurs, return empty results throw new RuntimeException(e); } return matches; }
From source file:org.ohdsi.webapi.service.IRAnalysisService.java
/** * Exports the analysis definition and results * * @param id - the IR Analysis ID to export * @return Response containing binary stream of zipped data *//*from www . ja va 2 s. co m*/ @GET @Produces(MediaType.APPLICATION_JSON) @Path("/{id}/export") @Transactional public Response export(@PathParam("id") final int id) { Response response = null; HashMap<String, String> fileList = new HashMap<>(); HashMap<Integer, String> distTypeLookup = new HashMap<>(); distTypeLookup.put(1, "TAR"); distTypeLookup.put(2, "TTO"); try { IncidenceRateAnalysis analysis = this.irAnalysisRepository.findOne(id); Set<ExecutionInfo> executions = analysis.getExecutionInfoList(); fileList.put("analysisDefinition.json", analysis.getDetails().getExpression()); // squentially return reults of IR calculation. In Spring 1.4.2, we can utlilize @Async operations to do this in parallel. // store results in single CSV file ArrayList<String[]> summaryLines = new ArrayList<>(); ArrayList<String[]> strataLines = new ArrayList<>(); ArrayList<String[]> distLines = new ArrayList<>(); for (ExecutionInfo execution : executions) { Source source = execution.getSource(); String resultsTableQualifier = source.getTableQualifier(SourceDaimon.DaimonType.Results); // perform this query to CDM in an isolated transaction to avoid expensive JDBC transaction synchronization DefaultTransactionDefinition requresNewTx = new DefaultTransactionDefinition(); requresNewTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); TransactionStatus initStatus = this.getTransactionTemplateRequiresNew().getTransactionManager() .getTransaction(requresNewTx); // get the summary data List<AnalysisReport.Summary> summaryList = getAnalysisSummaryList(id, source); if (summaryLines.isEmpty()) { summaryLines.add("db_id#targetId#outcomeId#total#timeAtRisk#cases".split("#")); } for (AnalysisReport.Summary summary : summaryList) { summaryLines.add(new String[] { source.getSourceKey(), String.valueOf(summary.targetId), String.valueOf(summary.outcomeId), String.valueOf(summary.totalPersons), String.valueOf(summary.timeAtRisk), String.valueOf(summary.cases) }); } // get the strata results List<AnalysisReport.StrataStatistic> strataList = getStrataStatistics(id, source); if (strataLines.isEmpty()) { strataLines.add( "db_id#targetId#outcomeId#strata_id#strata_name#total#timeAtRisk#cases".split("#")); } for (AnalysisReport.StrataStatistic strata : strataList) { strataLines.add(new String[] { source.getSourceKey(), String.valueOf(strata.targetId), String.valueOf(strata.outcomeId), String.valueOf(strata.id), String.valueOf(strata.name), String.valueOf(strata.totalPersons), String.valueOf(strata.timeAtRisk), String.valueOf(strata.cases) }); } // get the distribution data String distQuery = String.format( "select '%s' as db_id, target_id, outcome_id, strata_sequence, dist_type, total, avg_value, std_dev, min_value, p10_value, p25_value, median_value, p75_value, p90_value, max_value from %s.ir_analysis_dist where analysis_id = %d", source.getSourceKey(), resultsTableQualifier, id); String translatedSql = SqlTranslate.translateSql(distQuery, "sql server", source.getSourceDialect(), SessionUtils.sessionId(), resultsTableQualifier); SqlRowSet rs = this.getSourceJdbcTemplate(source).queryForRowSet(translatedSql); this.getTransactionTemplateRequiresNew().getTransactionManager().commit(initStatus); if (distLines.isEmpty()) { distLines.add(rs.getMetaData().getColumnNames()); } while (rs.next()) { ArrayList<String> columns = new ArrayList<>(); for (int i = 1; i <= rs.getMetaData().getColumnNames().length; i++) { switch (rs.getMetaData().getColumnName(i)) { case "dist_type": columns.add(distTypeLookup.get(rs.getInt(i))); break; default: columns.add(rs.getString(i)); break; } } distLines.add(columns.toArray(new String[0])); } } // Write report lines to CSV StringWriter sw = null; CSVWriter csvWriter = null; sw = new StringWriter(); csvWriter = new CSVWriter(sw); csvWriter.writeAll(summaryLines); csvWriter.flush(); fileList.put("ir_summary.csv", sw.getBuffer().toString()); sw = new StringWriter(); csvWriter = new CSVWriter(sw); csvWriter.writeAll(strataLines); csvWriter.flush(); fileList.put("ir_strata.csv", sw.getBuffer().toString()); sw = new StringWriter(); csvWriter = new CSVWriter(sw); csvWriter.writeAll(distLines); csvWriter.flush(); fileList.put("ir_dist.csv", sw.getBuffer().toString()); // build zip output ByteArrayOutputStream baos = new ByteArrayOutputStream(); ZipOutputStream zos = new ZipOutputStream(baos); for (String fileName : fileList.keySet()) { ZipEntry resultsEntry = new ZipEntry(fileName); zos.putNextEntry(resultsEntry); zos.write(fileList.get(fileName).getBytes()); } zos.closeEntry(); zos.close(); baos.flush(); baos.close(); response = Response.ok(baos).type(MediaType.APPLICATION_OCTET_STREAM).header("Content-Disposition", String.format("attachment; filename=\"%s\"", "ir_analysis_" + id + ".zip")).build(); } catch (Exception ex) { throw new RuntimeException(ex); } return response; }
From source file:repository.ClientsDAO.java
/** * Gets a count of how many clients in database, mainly for pagination * @return/*ww w.ja va 2 s .c om*/ */ public int getClientsCount() { String sql = "SELECT COUNT(ClientID) AS rowcount FROM clients"; SqlRowSet rs = template.queryForRowSet(sql); if (rs.next()) { return rs.getInt("rowcount"); } return 1; }
From source file:repository.UsersDAO.java
/** * Gets a count of all users, mainly for pagination * @return//from ww w .ja va2 s. c o m */ public int getUsersCount() { String sql = "SELECT COUNT(UserID) AS rowcount FROM users"; SqlRowSet rs = template.queryForRowSet(sql); if (rs.next()) { return rs.getInt("rowcount"); } return 1; }