List of usage examples for java.sql ResultSet first
boolean first() throws SQLException;
ResultSet object. From source file:org.ut.biolab.medsavant.server.serverapi.VariantManager.java
/** * Adds a new entry to the file table, returning a unique file_id. * * @param sid/*from w ww .j av a 2 s . c o m*/ * @param uploadId * @param projectID * @param referenceID * @param file * @return * @throws SQLException * @throws SessionExpiredException */ public static synchronized int addEntryToFileTable(String sid, int uploadId, int projectID, int referenceID, File file) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VariantFileTableSchema; InsertQuery q = new InsertQuery(table.getTable()); q.addColumn(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_UPLOAD_ID), uploadId); //q.addColumn(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_FILE_ID), fileId); q.addColumn(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_PROJECT_ID), projectID); q.addColumn(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceID); q.addColumn(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_FILE_NAME), file.getAbsolutePath()); ConnectionController.executeUpdate(sid, q.toString()); String query = "SELECT last_insert_id() AS last_id from " + table.getTableName(); ResultSet rs = ConnectionController.executeQuery(sid, query); if (!rs.first()) { throw new SQLException("Couldn't fetch file_id for file " + file.getAbsolutePath() + " on project " + projectID + ", ref " + referenceID); } int file_id = rs.getInt(1); if (file_id < 1) {//assert throw new SQLException("Invalid file_id for file " + file.getAbsolutePath() + " on project " + projectID + ", ref " + referenceID); } return file_id; }
From source file:com.l2jfree.gameserver.model.olympiad.Olympiad.java
public int getLastNobleOlympiadPoints(int objId) { int result = 0; Connection con = null;//from w w w .j a va 2s. c om try { con = L2DatabaseFactory.getInstance().getConnection(); PreparedStatement statement; statement = con.prepareStatement("SELECT olympiad_points FROM olympiad_nobles_eom WHERE charId = ?"); statement.setInt(1, objId); ResultSet rs = statement.executeQuery(); if (rs.first()) result = rs.getInt(1); rs.close(); statement.close(); } catch (SQLException e) { _log.warn("Could not load last olympiad points:", e); } finally { L2DatabaseFactory.close(con); } return result; }
From source file:edu.ku.brc.specify.tasks.ExpressSearchTask.java
public synchronized void exectionDone(final SQLExecutionProcessor process, final ResultSet resultSet) { if (!sqlHasResults) { try {//w w w .j a va2s. c o m sqlHasResults = resultSet.first(); } catch (SQLException ex) { ex.printStackTrace(); edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(ExpressSearchTask.class, ex); } } sqlProcessorList.remove(process); Object[] data = (Object[]) process.getData(); if (data != null) { if (data.length == 3) { SearchTableConfig searchTableConfig = (SearchTableConfig) data[0]; ExpressSearchResultsPaneIFace esrPane = (ExpressSearchResultsPaneIFace) data[1]; String searchTerm = (String) data[2]; Hashtable<String, ExpressResultsTableInfo> idToTableInfoHash = ExpressSearchConfigCache .getSearchIdToTableInfoHash(); Hashtable<String, List<ExpressResultsTableInfo>> joinIdToTableInfohash = ExpressSearchConfigCache .getJoinIdToTableInfoHash(); Hashtable<String, QueryForIdResultsSQL> resultsForJoinsHash = new Hashtable<String, QueryForIdResultsSQL>(); try { if (resultSet.next()) { String searchIdStr = Integer.toString(searchTableConfig.getTableInfo().getTableId()); ExpressResultsTableInfo tblInfo = idToTableInfoHash.get(searchIdStr); if (tblInfo == null) { throw new RuntimeException("Bad id from search[" + searchIdStr + "]"); } SearchConfig config = SearchConfigService.getInstance().getSearchConfig(); int cnt = 0; do { if (cnt < RESULTS_THRESHOLD) { collectResults(config, tblInfo.getTableId(), searchTerm, resultSet, null, joinIdToTableInfohash, resultsForJoinsHash); } cnt++; } while (resultSet.next()); //System.err.println("SQLExecutionProcessor: "+cnt); if (cnt >= RESULTS_THRESHOLD) { String reason = String.format( getResourceString("ExpressSearchTask.MAX_SEARCH_RESULTS_EXCEEDED"), RESULTS_THRESHOLD, cnt); if (searchWarningsResults == null) { searchWarningsResults = new SIQueryForIdResults(); searchWarningsResults.addReason(searchTableConfig, reason); displayResults(esrPane, searchWarningsResults, resultsForJoinsHash); } else { searchWarningsResults.addReason(searchTableConfig, reason); } } if (resultsForJoinsHash.size() > 0) { QueryForIdResultsSQL queryResults = new QueryForIdResultsSQL(searchIdStr, null, tblInfo, searchTableConfig.getDisplayOrder(), searchTerm); displayResults(esrPane, queryResults, resultsForJoinsHash); } } } catch (SQLException ex) { edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(ExpressSearchTask.class, ex); ex.printStackTrace(); } } } }
From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#getService(java.lang.String) *///from w ww. ja v a 2 s . c om public synchronized List<String> getService(final String attribute) throws SQLException { final String methodName = IServiceDataDAO.CNAME + "#getService(final String attribute) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("attribute: {}", attribute); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<String> responseData = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); // we dont know what we have here - it could be a guid or it could be a hostname // most commonly it'll be a guid, but we're going to search anyway stmt = sqlConn.prepareCall("{ CALL getServiceData(?) }"); stmt.setString(1, attribute); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("resultSet: {}", resultSet); } if (resultSet.next()) { resultSet.first(); responseData = new ArrayList<String>(Arrays.asList(resultSet.getString(1), // SERVICE_TYPE resultSet.getString(2), // NAME resultSet.getString(3), // REGION resultSet.getString(4), // NWPARTITION resultSet.getString(5), // STATUS resultSet.getString(6), // SERVERS resultSet.getString(7))); // DESCRIPTION if (DEBUG) { DEBUGGER.debug("responseData: {}", responseData); } } } } catch (SQLException 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(); } } return responseData; }
From source file:com.l2jfree.gameserver.model.quest.Quest.java
/** * Read from the database a previously saved variable for this quest. * Due to performance considerations, this function should best be used only when the quest is first loaded. * Subclasses of this class can define structures into which these loaded values can be saved. * However, on-demand usage of this function throughout the script is not prohibited, only not recommended. * Values read from this function were entered by calls to "saveGlobalQuestVar" * @param var : String designating the name of the variable for the quest * @return String : String representing the loaded value for the passed var, or an empty string if the var was invalid *//*from w ww. java 2 s. c o m*/ public final String loadGlobalQuestVar(String var) { String result = ""; Connection con = null; try { con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement; statement = con .prepareStatement("SELECT value FROM quest_global_data WHERE quest_name = ? AND var = ?"); statement.setString(1, getName()); statement.setString(2, var); ResultSet rs = statement.executeQuery(); if (rs.first()) result = rs.getString(1); rs.close(); statement.close(); } catch (Exception e) { _log.warn("could not load global quest variable:", e); } finally { L2DatabaseFactory.close(con); } return result; }
From source file:Admin.products.ProductUpdateS.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"); try (PrintWriter out = response.getWriter()) { try { String product_id = null; String product_name = null; String description = null; String specifications_name = null; //[] String specifications_value = null;//[] String specifications_unit = null;//[] String purchase_date = null; String MFD = null; String EXP = null; String purchase_price = null; // String old_price = null; String discount = null; String selling_price = null; String w_years = null; String w_months = null; String w_dates = null; String QTY = null; String pickup = null; String delivery_pond = null; String delivery_days = null; String delivery_area = null;//[] String images0 = null; String images = null;//[] String rurl = null; Collecter01.i = 0; // ilagata sepe collect karanna kalin Collecter01.specifications.clear(); Collecter01.delivery_areas = ""; Collecter01.product_images.clear(); FileItemFactory item = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(item); List<FileItem> list = upload.parseRequest(request); for (FileItem fileItem : list) { if (fileItem.isFormField()) { //form field switch (fileItem.getFieldName()) { case "hid01n": System.out.println("PRODUCT_ID---------:" + fileItem.getString()); product_id = fileItem.getString(); if (checkDigit(product_id)) { rurl = "04_admin/product/product_update.jsp?upd01n=" + product_id + "&"; } else { rurl = "04_admin/product/product_update.jsp?upd01n=" + product_id + "&"; response.sendRedirect(rurl + "msg=Please enter the product id"); } break; case "txf01n": System.out.println("PRODUCT_NAME---------:" + fileItem.getString()); product_name = fileItem.getString(); if (checkString(product_name.trim())) { } else { response.sendRedirect(rurl + "msg=Please enter the product name"); } break; case "txa01n": System.out.println("DESCRIPTION----------:" + fileItem.getString()); description = fileItem.getString(); if (checkString(description.trim())) { } else { response.sendRedirect(rurl + "msg=Please enter the description"); } break; case "spe01n": System.out.println("SPECIFICATION_NAME----------:" + fileItem.getString()); specifications_name = fileItem.getString(); if (checkString(specifications_name.trim())) { Collecter01.collectSpec(specifications_name.trim()); } else { response.sendRedirect(rurl + "msg=Please enter the specifications name"); } break; case "spe02n": System.out.println("SPECIFICATION_VALUE---------:" + fileItem.getString()); specifications_value = fileItem.getString(); if (checkString(specifications_value.trim())) { Collecter01.collectSpec(specifications_value.trim()); } else { response.sendRedirect(rurl + "msg=Please enter the specifications value"); } break; case "spe03n": System.out.println("SPECIFICATION_UNIT----------:" + fileItem.getString()); specifications_unit = fileItem.getString(); if (specifications_unit == null || specifications_unit.equals("")) { specifications_unit = ""; } else { Collecter01.collectSpec(specifications_unit.trim()); } break; case "dat01n": System.out.println("PURCHASE_DATE--------:" + fileItem.getString()); purchase_date = fileItem.getString(); if (checkString(purchase_date)) { } else { response.sendRedirect(rurl + "msg=Please select the purchase date"); } break; case "dat02n": System.out.println("MFD------------------:" + fileItem.getString()); MFD = fileItem.getString(); if (checkString(MFD)) { } else { response.sendRedirect(rurl + "msg=Please select the MFD"); } break; case "dat03n": System.out.println("EXP------------------:" + fileItem.getString()); EXP = fileItem.getString(); if (checkString(EXP)) { } else { response.sendRedirect(rurl + "msg=Please enter the EXP"); } break; case "num01n": System.out.println("PURCHASE_price-------:" + fileItem.getString()); purchase_price = fileItem.getString(); if (checkDigit(purchase_price)) { } else { response.sendRedirect(rurl + "msg=Please enter the purchase price"); } break; case "num03n": System.out.println("DISCOUNT-------------:" + fileItem.getString() + " %"); discount = fileItem.getString(); if (checkDigit(discount)) { } else { response.sendRedirect(rurl + "msg=Please enter the discount"); } break; case "num04n": System.out.println("SELLING_PRICE--------:" + fileItem.getString()); selling_price = fileItem.getString(); if (checkDigit(selling_price)) { } else { response.sendRedirect(rurl + "msg=Please enter the selling price value"); } break; case "num05n": System.out.println("W_YEARS--------------:" + fileItem.getString()); w_years = fileItem.getString(); if (checkDigit(w_years)) { } else { response.sendRedirect(rurl + "msg=Please enter the warrenty years"); } break; case "num06n": System.out.println("W_MONTS--------------:" + fileItem.getString()); w_months = fileItem.getString(); if (checkDigit(w_months)) { } else { response.sendRedirect(rurl + "msg=Please enter the warrenty months"); } break; case "num07n": System.out.println("W_DATES--------------:" + fileItem.getString()); w_dates = fileItem.getString(); if (checkDigit(w_dates)) { } else { response.sendRedirect(rurl + "msg=Please enter th warrenty dates"); } break; case "num08n": System.out.println("QTY------------------:" + fileItem.getString()); QTY = fileItem.getString(); if (checkDigit(QTY)) { } else { response.sendRedirect(rurl + "msg=Please enter the QTY"); } break; case "sel05n": System.out.println("PICKUP---------------:" + fileItem.getString()); pickup = fileItem.getString(); if (checkString(pickup)) { } else { response.sendRedirect(rurl + "msg=Please select the pickup"); } break; case "sel06n": System.out.println("DELIVERY_POND--------:" + fileItem.getString()); delivery_pond = fileItem.getString(); if (checkString(delivery_pond)) { } else { response.sendRedirect(rurl + "msg=Please select the pay on delivery"); } break; case "num09n": System.out.println("DELIVERY_DAYS--------:" + fileItem.getString()); if (delivery_pond.trim().equals("Yes")) { delivery_days = fileItem.getString(); if (checkDigit(delivery_days)) { } else { response.sendRedirect(rurl + "msg=Please add the delivery dates"); } } else { } break; case "sel07n": System.out.println("DELIVERY_AREA--------:" + fileItem.getString());//[] if (delivery_pond.trim().equals("Yes")) { delivery_area = fileItem.getString(); if (checkString(delivery_area)) { // Collecter01.collectDeliveryArea(delivery_area.trim()); } else { response.sendRedirect(rurl + "msg=Please select the delivery areas"); } } else { } break; case "hid02n": System.out.println("DELETE--------:" + fileItem.getString()); images0 = fileItem.getString(); if (checkString(images0)) { for (String imn : images0.split(">")) { System.out.println(imn); } } else { System.out.println("delete natha"); // response.sendRedirect(rurl + "msg=Please select the pay on delivery"); } break; default: break; } } else { images = fileItem.getName(); System.out.println(images); if (checkString(images)) { Long time = System.currentTimeMillis(); System.out.println("IMAGES_name----------:" + time); String apath = request.getServletContext().getRealPath("/04_admin/product/img/") + "\\" + time + ".jpg"; System.out.println("IMAGES_AP------------:" + apath); String rpath = "04_admin\\product\\img\\" + time + ".jpg"; System.out.println("IMAGES_RP------------:" + rpath); fileItem.write(new File(apath)); Collecter01.collectImages(rpath); } else { // response.sendRedirect(rurl + "msg=Please select images"); } } } System.out.println(checkString(product_id)); System.out.println(checkString(product_name)); System.out.println(checkString(description)); // System.out.println(specifications_name); //null // System.out.println(specifications_value); //null // System.out.println(specifications_unit); //null System.out.println(checkString(purchase_date)); System.out.println(checkString(MFD)); System.out.println(checkString(EXP)); System.out.println(checkDigit(purchase_price)); System.out.println(checkDigit(selling_price)); System.out.println(checkDigit(discount)); System.out.println(checkDigit(w_years)); System.out.println(checkDigit(w_months)); System.out.println(checkDigit(w_dates)); System.out.println(checkDigit(QTY)); System.out.println(checkString(pickup)); System.out.println(checkString(delivery_pond)); System.out.println(delivery_pond.trim().equals("Yes") ? checkDigit(delivery_days) : true); System.out.println(delivery_pond.trim().equals("Yes") ? checkString(delivery_area) : true); System.out.println(checkString(images)); if (checkDigit(product_id) && checkString(product_name) && checkString(description) && checkString(purchase_date) && checkString(MFD) && checkString(EXP) && checkDigit(purchase_price) && checkDigit(selling_price) && checkDigit(discount) && checkDigit(w_years) && checkDigit(w_months) && checkDigit(w_dates) && checkDigit(QTY) && checkString(pickup) && checkString(delivery_pond) && delivery_pond.trim().equals("Yes") ? checkDigit(delivery_days) : true && delivery_pond.trim().equals("Yes") ? checkString(delivery_area) : true) { System.out.println( "UPDATE VALIDATION OK---------------------------------------------------------------------"); try { String sql00 = "UPDATE product SET name=?, description=? WHERE idproduct=?"; PreparedStatement ps00 = Controller.DB.con().prepareStatement(sql00); ps00.setString(1, product_name); ps00.setString(2, description); ps00.setInt(3, Integer.parseInt(product_id)); System.out.println(ps00); int x = ps00.executeUpdate(); if (x == 1) { try { String sql01 = "UPDATE stock SET purchase_date=?, purchase_price=?, discount=?, selling_price=?, warranty=?, QTY=?, pickup=?, MFD=?, EXP=? WHERE product_idproduct=?"; PreparedStatement ps01 = Controller.DB.con().prepareStatement(sql01); ps01.setString(1, purchase_date); ps01.setInt(2, Integer.parseInt(purchase_price)); ps01.setInt(3, Integer.parseInt(discount)); ps01.setInt(4, Integer.parseInt(selling_price)); ps01.setString(5, w_years + "," + w_months + "," + w_dates); ps01.setInt(6, Integer.parseInt(QTY)); ps01.setString(7, pickup); ps01.setInt(6, Integer.parseInt(QTY)); ps01.setString(7, pickup); ps01.setString(8, MFD); ps01.setString(9, EXP); ps01.setInt(10, Integer.parseInt(product_id)); System.out.println(ps01); int x1 = ps01.executeUpdate(); if (x1 == 1) { try { String sql04 = "SELECT * FROM delivery WHERE product_idproduct=?"; PreparedStatement ps04 = Controller.DB.con().prepareStatement(sql04); ps04.setInt(1, Integer.parseInt(product_id)); System.out.println(ps04); ResultSet rs04 = ps04.executeQuery(); if (rs04.next()) { System.out.println("update karanna delivery id ata"); try { String sql02 = "UPDATE delivery SET pay_on_delivery=?, days=?, area=? WHERE product_idproduct=?"; PreparedStatement ps02 = Controller.DB.con() .prepareStatement(sql02); try { if (delivery_pond.equals("Yes")) { ps02.setString(1, delivery_pond); ps02.setInt(2, Integer.parseInt(delivery_days)); ps02.setString(3, Collecter01.delivery_areas.substring(1)); ps02.setInt(4, Integer.parseInt(product_id)); } else { ps02.setString(1, "No"); ps02.setInt(2, 0); ps02.setString(3, "No"); ps02.setInt(4, Integer.parseInt(product_id)); } System.out.println(ps02); ps02.executeUpdate(); } catch (Exception e) { } finally { try { for (String imn : images0.split(">")) { if (imn.trim().equals("")) { } else { String sql5 = "DELETE FROM image WHERE path LIKE ?;"; PreparedStatement ps5 = Controller.DB.con() .prepareStatement(sql5); ps5.setString(1, "%" + imn + "%"); System.out.println(ps5); ps5.executeUpdate(); } } for (String img_path : Collecter01.product_images) { String sql4 = "INSERT INTO image VALUES (?,?)"; PreparedStatement ps4 = Controller.DB.con() .prepareStatement(sql4); ps4.setInt(1, Integer.parseInt(product_id)); ps4.setString(2, img_path); System.out.println(ps4); ps4.executeUpdate(); } } catch (Exception e) { } finally { try { String sql5 = "SELECT idSpecifications FROM specifications WHERE name=?"; PreparedStatement ps5 = Controller.DB.con() .prepareStatement(sql5); for (Map.Entry<String, List> entry : Collecter01.specifications .entrySet()) { System.out.println(entry.getKey() + "---" + entry.getValue().get(0) + "---" + entry.getValue().get(1)); ps5.setString(1, entry.getKey()); System.out.println(ps5); ResultSet rs5 = ps5.executeQuery(); // int idSpecifications = 0; try { if (rs5.first()) { System.out.println( "Specifications name/id ata____1"); // idSpecifications = rs5.getInt(1); } else { try { System.out.println( "Specifications name/id na____2"); String sql6 = "INSERT INTO specifications VALUES (null,?)"; PreparedStatement ps6 = Controller.DB .con().prepareStatement(sql6); ps6.setString(1, entry.getKey()); System.out.println(ps6); ps6.executeUpdate(); System.out.println( "Specifications new add karanawa____2-1"); try { String sql7 = "SELECT idSpecifications FROM specifications WHERE name=?"; PreparedStatement ps7 = Controller.DB .con() .prepareStatement(sql7); ps7.setString(1, entry.getKey()); System.out.println(ps7); ResultSet rs7 = ps7.executeQuery(); if (rs7.first()) { System.out.println( "new Specifications name/id ata____3-1"); // idSpecifications = rs7.getInt(1); } else { } } catch (Exception e9) { System.out.println( "new Specifications name/id na____3-2"); } } catch (Exception e8) { System.out.println( "Specifications new add fail____2-2"); } } } catch (Exception e7) { } finally { try { String sql8 = "DELETE FROM product_has_specifications WHERE product_idproduct=?;"; PreparedStatement ps8 = Controller.DB.con() .prepareStatement(sql8); ps8.setInt(1, Integer.parseInt(product_id)); System.out.println(ps8); ps8.executeUpdate(); } catch (Exception e) { } } } try { for (Map.Entry<String, List> entry : Collecter01.specifications .entrySet()) { // System.out.println(product_id); // System.out.println(entry.getKey()); // System.out.println(entry.getValue().get(0)); // System.out.println(entry.getValue().get(1)); int idSpecifications = 0; try { String sql9 = "SELECT idSpecifications FROM specifications WHERE name=?"; PreparedStatement ps9 = Controller.DB.con() .prepareStatement(sql9); ps9.setString(1, entry.getKey()); // System.out.println(ps7); ResultSet rs9 = ps9.executeQuery(); if (rs9.first()) { // System.out.println("new Specifications name/id ata____3-1"); idSpecifications = rs9.getInt(1); } else { } } catch (Exception e) { } // System.out.println(product_id); // System.out.println(idSpecifications); // System.out.println(entry.getValue().get(0)); // System.out.println(entry.getValue().get(1)); try { String sql10 = "INSERT INTO product_has_specifications VALUES (?,?,?,?)"; PreparedStatement ps10 = Controller.DB.con() .prepareStatement(sql10); ps10.setInt(1, Integer.parseInt(product_id)); ps10.setInt(2, idSpecifications); ps10.setString(3, (String) entry.getValue().get(0)); ps10.setString(4, (String) entry.getValue().get(1)); System.out.println(ps10); ps10.executeUpdate(); System.out.println("spec value save kara"); } catch (Exception e) { System.out.println("spec value save fail"); } } } catch (Exception e) { } } catch (Exception e) { } finally { String xv = rurl + "msg=Product update successful&cl=00bf6f"; response.sendRedirect(xv); } } } } catch (Exception e) { e.printStackTrace(); } } else { System.out.println("update karanna delivery id eka na"); } } catch (Exception e) { } } else { System.out.println("stock update fail"); } } catch (Exception e) { } } else { System.out.println("product update fail"); } } catch (Exception e) { } } else { } } catch (Exception e) { } } }
From source file:edu.ku.brc.specify.dbsupport.cleanuptools.GeographyAssignISOs.java
/** * @param geoId// w w w . j a v a 2 s . c o m * @param nameStr * @param level * @param rankId * @param parentNames * @return * @throws SQLException */ @SuppressWarnings({ "rawtypes", "unchecked" }) private void chooseGeo(final int geoId, final String nameStr, final int level, final int rankId, // the RankID it is currently working on final String[] parentNames, final int[] parentRanks) throws SQLException { // Convert RankID to level // if (rankId > 100) // { // int levelFromRankId = (rankId / 100) - 2; // if (levelFromRankId != level) // { // badRankIDs.add(new GeoSearchResultsItem(nameStr, rankId, level * 100)); // } // } Integer geonameId = selectedSearchItem != null ? selectedSearchItem.geonameId : null; GeoChooserDlg dlg = new GeoChooserDlg(nameStr, rankId, level, parentNames, parentRanks, geonameId, stCntXRef, countryInfo, doAllCountries, doInvCountry, readConn, processedCount, geographyTotal); if (luceneResults.size() > 0) { dlg.setCoInfoList(luceneResults); // this will force the dialog to use the results from Lucene } //int SKIP_BTN = CustomDialog.CANCEL_BTN; int SAVE_BTN = CustomDialog.OK_BTN; int NXTC_BTN = CustomDialog.CANCEL_BTN; int QUIT_BTN = CustomDialog.HELP_BTN; dlg.createUI(); dlg.pack(); centerAndShow(dlg); //dlg.dispose(); this.doAddISOCode = false; this.selectedSearchItem = null; if (dlg.getBtnPressed() != QUIT_BTN) { if (dlg.getBtnPressed() == SAVE_BTN) { doUpdateName = dlg.getUpdateNameCB().isSelected(); doMerge = dlg.getMergeCB() != null ? dlg.getMergeCB().isSelected() : false; doAddISOCode = dlg.getAddISOCodeCB().isSelected(); selectedSearchItem = dlg.getSelectedGeoSearchItem(); this.isoCodeStr = dlg.getISOCodeFromTextField(); if (this.isoCodeStr != null && selectedSearchItem != null && !this.isoCodeStr.equals(selectedSearchItem.isoCode)) { selectedSearchItem.isoCode = this.isoCodeStr; } String selectedGeoName = selectedSearchItem != null ? selectedSearchItem.name : null; if (doMerge && rankId == 400 && selectedGeoName != null) { int geoParentId = getCountAsInt(readConn, "SELECT ParentID FROM geography WHERE GeographyID = " + geoId); if (geoParentId != -1) { try { String cName = remove(selectedGeoName, " County"); PreparedStatement pStmt = updateConn.prepareStatement( "SELECT GeographyID FROM geography WHERE RankID = 400 AND GeographyID <> ? AND ParentID = ? AND (LOWER(Name) = ? OR LOWER(Name) = ?"); pStmt.setInt(1, geoId); pStmt.setInt(2, geoParentId); pStmt.setString(3, cName.toLowerCase()); pStmt.setString(4, selectedGeoName.toLowerCase()); ResultSet rs = pStmt.executeQuery(); if (rs.first()) { mergeToGeoId = rs.getInt(1); } rs.close(); pStmt.close(); } catch (SQLException ex) { ex.printStackTrace(); } } } parentNames[level] = selectedGeoName; parentRanks[level] = rankId; return; } if (dlg.getBtnPressed() == NXTC_BTN) { doSkipCountry = true; } } else { doStopProcessing = true; } }
From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#getServer(java.lang.String) */// w w w . ja va 2s . c o m public synchronized List<Object> getServer(final String attribute) throws SQLException { final String methodName = IServerDataDAO.CNAME + "#getServer(final String attribute) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("attribute: {}", attribute); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<Object> responseData = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); // we dont know what we have here - it could be a guid or it could be a hostname // most commonly it'll be a guid, but we're going to search anyway stmt = sqlConn.prepareCall("{ CALL retrServerData(?) }"); stmt.setString(1, attribute); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("resultSet: {}", resultSet); } if (resultSet.next()) { resultSet.first(); responseData = new ArrayList<Object>(Arrays.asList(resultSet.getString(1), // T1.SYSTEM_GUID resultSet.getString(2), // T1.SYSTEM_OSTYPE resultSet.getString(3), // T1.SYSTEM_STATUS resultSet.getString(4), // T1.SYSTEM_REGION resultSet.getString(5), // T1.NETWORK_PARTITION resultSet.getString(6), // T1.SYSTEM_TYPE resultSet.getString(7), // T1.DOMAIN_NAME resultSet.getString(8), // T1.CPU_TYPE resultSet.getInt(9), // T1.CPU_COUNT resultSet.getString(10), // T1.SERVER_RACK resultSet.getString(11), // T1.RACK_POSITION resultSet.getString(12), // T1.SERVER_MODEL resultSet.getString(13), // T1.SERIAL_NUMBER resultSet.getInt(14), // T1.INSTALLED_MEMORY resultSet.getString(15), // T1.OPER_IP resultSet.getString(16), // T1.OPER_HOSTNAME resultSet.getString(17), // T1.MGMT_IP resultSet.getString(18), // T1.MGMT_HOSTNAME resultSet.getString(19), // T1.BKUP_IP resultSet.getString(20), // T1.BKUP_HOSTNAME resultSet.getString(21), // T1.NAS_IP resultSet.getString(22), // T1.NAS_HOSTNAME resultSet.getString(23), // T1.NAT_ADDR resultSet.getString(24), // T1.COMMENTS resultSet.getString(25), // T1.ASSIGNED_ENGINEER resultSet.getTimestamp(26), // T1.ADD_DATE resultSet.getTimestamp(27), // T1.DELETE_DATE resultSet.getInt(28), // T1.DMGR_PORT resultSet.getString(29), // T1.OWNING_DMGR resultSet.getString(30), // T1.MGR_ENTRY resultSet.getString(31), // T2.GUID resultSet.getString(32))); // T2.NAME if (DEBUG) { DEBUGGER.debug("responseData: {}", responseData); } } } } catch (SQLException 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(); } } return responseData; }
From source file:com.linkedin.pinot.integration.tests.BaseClusterIntegrationTest.java
/** * Run equivalent Pinot and H2 query and compare the results. * <p>LIMITATIONS://from ww w . j av a 2s. c om * <ul> * <li>Skip comparison for selection and aggregation group-by when H2 results are too large to exhaust.</li> * <li>Do not examine the order of result records.</li> * </ul> * * @param pqlQuery Pinot PQL query. * @param sqlQueries H2 SQL queries. * @throws Exception */ protected void runQuery(String pqlQuery, @Nullable List<String> sqlQueries) throws Exception { try { _queryCount++; // Run the query. // TODO Use Pinot client API for this JSONObject response = postQuery(pqlQuery); // Check exceptions. JSONArray exceptions = response.getJSONArray("exceptions"); if (exceptions.length() > 0) { String failureMessage = "Got exceptions: " + exceptions; failure(pqlQuery, sqlQueries, failureMessage, null); return; } // Check total docs. long numTotalDocs = response.getLong("totalDocs"); if (numTotalDocs != TOTAL_DOCS) { String failureMessage = "Number of total documents does not match, expected: " + TOTAL_DOCS + ", got: " + numTotalDocs; failure(pqlQuery, sqlQueries, failureMessage, null); return; } // Skip comparison if SQL queries not specified. if (sqlQueries == null) { return; } // Check results. Statement h2statement = _connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); int numDocsScanned = response.getInt("numDocsScanned"); if (response.has("aggregationResults")) { // Aggregation and Group-by results. // Get results type. JSONArray aggregationResultsArray = response.getJSONArray("aggregationResults"); int numAggregationResults = aggregationResultsArray.length(); int numSqlQueries = sqlQueries.size(); if (numAggregationResults != numSqlQueries) { String failureMessage = "Number of aggregation results: " + numAggregationResults + " does not match number of SQL queries: " + numSqlQueries; failure(pqlQuery, sqlQueries, failureMessage); } JSONObject firstAggregationResult = aggregationResultsArray.getJSONObject(0); if (firstAggregationResult.has("value")) { // Aggregation results. // Check over all aggregation functions. for (int i = 0; i < numAggregationResults; i++) { // Get expected value for the aggregation. h2statement.execute(sqlQueries.get(i)); ResultSet sqlResultSet = h2statement.getResultSet(); sqlResultSet.first(); String sqlValue = sqlResultSet.getString(1); // If SQL value is null, it means no record selected in H2. if (sqlValue == null) { // Check number of documents scanned is 0. if (numDocsScanned != 0) { String failureMessage = "No record selected in H2 but number of records selected in Pinot: " + numDocsScanned; failure(pqlQuery, sqlQueries, failureMessage); } // Skip further comparison. return; } // Get actual value for the aggregation. String pqlValue = aggregationResultsArray.getJSONObject(i).getString("value"); // Fuzzy compare expected value and actual value. double expectedValue = Double.parseDouble(sqlValue); double actualValue = Double.parseDouble(pqlValue); if (Math.abs(actualValue - expectedValue) >= 1.0) { String failureMessage = "Value: " + i + " does not match, expected: " + sqlValue + ", got: " + pqlValue; failure(pqlQuery, sqlQueries, failureMessage); return; } } } else if (firstAggregationResult.has("groupByResult")) { // Group-by results. // Get number of groups and number of group keys in each group. JSONArray firstGroupByResults = aggregationResultsArray.getJSONObject(0) .getJSONArray("groupByResult"); int numGroups = firstGroupByResults.length(); // If no group-by result returned by Pinot, set numGroupKeys to 0 since no comparison needed. int numGroupKeys; if (numGroups == 0) { numGroupKeys = 0; } else { numGroupKeys = firstGroupByResults.getJSONObject(0).getJSONArray("group").length(); } // Check over all aggregation functions. for (int i = 0; i < numAggregationResults; i++) { // Get number of group keys. JSONArray groupByResults = aggregationResultsArray.getJSONObject(i) .getJSONArray("groupByResult"); // Construct expected result map from group keys to value. h2statement.execute(sqlQueries.get(i)); ResultSet sqlResultSet = h2statement.getResultSet(); Map<String, String> expectedValues = new HashMap<>(); int sqlNumGroups; for (sqlNumGroups = 0; sqlResultSet.next() && sqlNumGroups < MAX_COMPARISON_LIMIT; sqlNumGroups++) { if (numGroupKeys != 0) { StringBuilder groupKey = new StringBuilder(); for (int groupKeyIndex = 1; groupKeyIndex <= numGroupKeys; groupKeyIndex++) { // Convert boolean value to lower case. groupKey.append( convertBooleanToLowerCase(sqlResultSet.getString(groupKeyIndex))) .append(' '); } expectedValues.put(groupKey.toString(), sqlResultSet.getString(numGroupKeys + 1)); } } if (sqlNumGroups == 0) { // No record selected in H2. // Check if no record selected in Pinot. if (numGroups != 0) { String failureMessage = "No group returned in H2 but number of groups returned in Pinot: " + numGroups; failure(pqlQuery, sqlQueries, failureMessage); return; } // Check if number of documents scanned is 0. if (numDocsScanned != 0) { String failureMessage = "No group returned in Pinot but number of records selected: " + numDocsScanned; failure(pqlQuery, sqlQueries, failureMessage); } // Skip further comparison. return; } else if (sqlNumGroups < MAX_COMPARISON_LIMIT) { // Only compare exhausted results. // Check that all Pinot results are contained in the H2 results. for (int resultIndex = 0; resultIndex < numGroups; resultIndex++) { // Fetch Pinot group keys. JSONObject groupByResult = groupByResults.getJSONObject(resultIndex); JSONArray group = groupByResult.getJSONArray("group"); StringBuilder groupKeyBuilder = new StringBuilder(); for (int groupKeyIndex = 0; groupKeyIndex < numGroupKeys; groupKeyIndex++) { groupKeyBuilder.append(group.getString(groupKeyIndex)).append(' '); } String groupKey = groupKeyBuilder.toString(); // Check if Pinot group keys contained in H2 results. if (!expectedValues.containsKey(groupKey)) { String failureMessage = "Group returned in Pinot but not in H2: " + groupKey; failure(pqlQuery, sqlQueries, failureMessage); return; } // Fuzzy compare expected value and actual value. String sqlValue = expectedValues.get(groupKey); String pqlValue = groupByResult.getString("value"); double expectedValue = Double.parseDouble(sqlValue); double actualValue = Double.parseDouble(pqlValue); if (Math.abs(actualValue - expectedValue) >= 1.0) { String failureMessage = "Value: " + i + " does not match, expected: " + sqlValue + ", got: " + pqlValue + ", for group: " + groupKey; failure(pqlQuery, sqlQueries, failureMessage); return; } } } else { // Cannot get exhausted results. // Skip further comparison. LOGGER.debug("SQL: {} returned at least {} rows, skipping comparison.", sqlQueries.get(0), MAX_COMPARISON_LIMIT); return; } } } else { // Neither aggregation or group-by results. String failureMessage = "Inside aggregation results, no aggregation or group-by results found"; failure(pqlQuery, sqlQueries, failureMessage); } } else if (response.has("selectionResults")) { // Selection results. // Construct expected result set. h2statement.execute(sqlQueries.get(0)); ResultSet sqlResultSet = h2statement.getResultSet(); ResultSetMetaData sqlMetaData = sqlResultSet.getMetaData(); Set<String> expectedValues = new HashSet<>(); Map<String, String> reusableExpectedValueMap = new HashMap<>(); Map<String, List<String>> reusableMultiValuesMap = new HashMap<>(); List<String> reusableColumnOrder = new ArrayList<>(); int numResults; for (numResults = 0; sqlResultSet.next() && numResults < MAX_COMPARISON_LIMIT; numResults++) { reusableExpectedValueMap.clear(); reusableMultiValuesMap.clear(); reusableColumnOrder.clear(); int numColumns = sqlMetaData.getColumnCount(); for (int i = 1; i <= numColumns; i++) { String columnName = sqlMetaData.getColumnName(i); // Handle null result and convert boolean value to lower case. String columnValue = sqlResultSet.getString(i); if (columnValue == null) { columnValue = "null"; } else { columnValue = convertBooleanToLowerCase(columnValue); } // Handle multi-value columns. int length = columnName.length(); if (length > 5 && columnName.substring(length - 5, length - 1).equals("__MV")) { // Multi-value column. String multiValueColumnName = columnName.substring(0, length - 5); List<String> multiValue = reusableMultiValuesMap.get(multiValueColumnName); if (multiValue == null) { multiValue = new ArrayList<>(); reusableMultiValuesMap.put(multiValueColumnName, multiValue); reusableColumnOrder.add(multiValueColumnName); } multiValue.add(columnValue); } else { // Single-value column. reusableExpectedValueMap.put(columnName, columnValue); reusableColumnOrder.add(columnName); } } // Add multi-value column results to the expected values. // The reason for this step is that Pinot does not maintain order of elements in multi-value columns. for (Map.Entry<String, List<String>> entry : reusableMultiValuesMap.entrySet()) { List<String> multiValue = entry.getValue(); Collections.sort(multiValue); reusableExpectedValueMap.put(entry.getKey(), multiValue.toString()); } // Build expected value String. StringBuilder expectedValue = new StringBuilder(); for (String column : reusableColumnOrder) { expectedValue.append(column).append(':').append(reusableExpectedValueMap.get(column)) .append(' '); } expectedValues.add(expectedValue.toString()); } JSONObject selectionColumnsAndResults = response.getJSONObject("selectionResults"); JSONArray selectionColumns = selectionColumnsAndResults.getJSONArray("columns"); JSONArray selectionResults = selectionColumnsAndResults.getJSONArray("results"); int numSelectionResults = selectionResults.length(); if (numResults == 0) { // No record selected in H2. // Check if no record selected in Pinot. if (numSelectionResults != 0) { String failureMessage = "No record selected in H2 but number of records selected in Pinot: " + numSelectionResults; failure(pqlQuery, sqlQueries, failureMessage); return; } // Check if number of documents scanned is 0. if (numDocsScanned != 0) { String failureMessage = "No selection result returned in Pinot but number of records selected: " + numDocsScanned; failure(pqlQuery, sqlQueries, failureMessage); } } else if (numResults < MAX_COMPARISON_LIMIT) { // Only compare exhausted results. // Check that Pinot results are contained in the H2 results. int numColumns = selectionColumns.length(); for (int i = 0; i < numSelectionResults; i++) { // Build actual value String. StringBuilder actualValueBuilder = new StringBuilder(); JSONArray selectionResult = selectionResults.getJSONArray(i); for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { // Convert column name to all uppercase to make it compatible with H2. String columnName = selectionColumns.getString(columnIndex).toUpperCase(); Object columnResult = selectionResult.get(columnIndex); if (columnResult instanceof JSONArray) { // Multi-value column. JSONArray columnResultsArray = (JSONArray) columnResult; List<String> multiValue = new ArrayList<>(); int length = columnResultsArray.length(); for (int elementIndex = 0; elementIndex < length; elementIndex++) { multiValue.add(columnResultsArray.getString(elementIndex)); } for (int elementIndex = length; elementIndex < MAX_ELEMENTS_IN_MULTI_VALUE; elementIndex++) { multiValue.add("null"); } Collections.sort(multiValue); actualValueBuilder.append(columnName).append(':').append(multiValue.toString()) .append(' '); } else { // Single-value column. actualValueBuilder.append(columnName).append(':').append((String) columnResult) .append(' '); } } String actualValue = actualValueBuilder.toString(); // Check actual value in expected values set. if (!expectedValues.contains(actualValue)) { String failureMessage = "Selection result returned in Pinot but not in H2: " + actualValue; failure(pqlQuery, sqlQueries, failureMessage); return; } } } else { // Cannot get exhausted results. LOGGER.debug("SQL: {} returned at least {} rows, skipping comparison.", sqlQueries.get(0), MAX_COMPARISON_LIMIT); } } else { // Neither aggregation or selection results. String failureMessage = "No aggregation or selection results found for query: " + pqlQuery; failure(pqlQuery, sqlQueries, failureMessage); } } catch (Exception e) { String failureMessage = "Caught exception while running query."; failure(pqlQuery, sqlQueries, failureMessage, e); } }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.java
@Override public PartialList<Serializable> query(String query, String queryType, QueryFilter queryFilter, long countUpTo) throws StorageException { if (dialect.needsPrepareUserReadAcls()) { prepareUserReadAcls(queryFilter); }// ww w. j a va 2s. c o m QueryMaker queryMaker = findQueryMaker(queryType); if (queryMaker == null) { throw new StorageException("No QueryMaker accepts query: " + queryType + ": " + query); } QueryMaker.Query q = queryMaker.buildQuery(sqlInfo, model, pathResolver, query, queryFilter); if (q == null) { logger.log("Query cannot return anything due to conflicting clauses"); return new PartialList<Serializable>(Collections.<Serializable>emptyList(), 0); } long limit = queryFilter.getLimit(); long offset = queryFilter.getOffset(); if (logger.isLogEnabled()) { String sql = q.selectInfo.sql; if (limit != 0) { sql += " -- LIMIT " + limit + " OFFSET " + offset; } if (countUpTo != 0) { sql += " -- COUNT TOTAL UP TO " + countUpTo; } logger.logSQL(sql, q.selectParams); } String sql = q.selectInfo.sql; if (countUpTo == 0 && limit > 0 && dialect.supportsPaging()) { // full result set not needed for counting sql = dialect.addPagingClause(sql, limit, offset); limit = 0; offset = 0; } else if (countUpTo > 0 && dialect.supportsPaging()) { // ask one more row sql = dialect.addPagingClause(sql, Math.max(countUpTo + 1, limit + offset), 0); } PreparedStatement ps = null; try { ps = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); int i = 1; for (Serializable object : q.selectParams) { setToPreparedStatement(ps, i++, object); } ResultSet rs = ps.executeQuery(); countExecute(); // limit/offset long totalSize = -1; boolean available; if ((limit == 0) || (offset == 0)) { available = rs.first(); if (!available) { totalSize = 0; } if (limit == 0) { limit = -1; // infinite } } else { available = rs.absolute((int) offset + 1); } Column column = q.selectInfo.whatColumns.get(0); List<Serializable> ids = new LinkedList<Serializable>(); int rowNum = 0; while (available && (limit != 0)) { Serializable id = column.getFromResultSet(rs, 1); ids.add(id); rowNum = rs.getRow(); available = rs.next(); limit--; } // total size if (countUpTo != 0 && (totalSize == -1)) { if (!available && (rowNum != 0)) { // last row read was the actual last totalSize = rowNum; } else { // available if limit reached with some left // rowNum == 0 if skipped too far rs.last(); totalSize = rs.getRow(); } if (countUpTo > 0 && totalSize > countUpTo) { // the result where truncated we don't know the total size totalSize = -2; } } if (logger.isLogEnabled()) { logger.logIds(ids, countUpTo != 0, totalSize); } return new PartialList<Serializable>(ids, totalSize); } catch (Exception e) { checkConnectionReset(e); throw new StorageException("Invalid query: " + query, e); } finally { if (ps != null) { try { closeStatement(ps); } catch (SQLException e) { log.error("Cannot close connection", e); } } } }