List of usage examples for java.sql ResultSet TYPE_SCROLL_INSENSITIVE
int TYPE_SCROLL_INSENSITIVE
To view the source code for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.
Click Source Link
ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. From source file:edu.ku.brc.specify.conversion.SynonymCleanup.java
/** * @param newDBConn/*w w w.ja va 2 s . c o m*/ * @param tblWriter * @param phHelper * @param parentLevelRankID * @param childLevelRankID */ public void fixMisparentedSynonymsLevel(final Connection newDBConn, final TableWriter tblWriter, final PlaceholderHelper phHelper, final int parentLevelRankID, final int childLevelRankID, final boolean skipBadParentRanks, final int[] stats, final Set<Integer> processedKeys, int progressInterval, boolean doPercent) { DBTableInfo ti = DBTableIdMgr.getInstance().getInfoById(Taxon.getClassTableId()); String whereStr = QueryAdjusterForDomain.getInstance().getSpecialColumns(ti, false); String parentName = BasicSQLUtils.querySingleObj(String .format("SELECT Name FROM taxontreedefitem WHERE %s AND RankID = %d", whereStr, parentLevelRankID)); String childName = BasicSQLUtils.querySingleObj(String .format("SELECT Name FROM taxontreedefitem WHERE %s AND RankID = %d", whereStr, childLevelRankID)); int numFixed = BasicSQLUtils.update(newDBConn, "UPDATE taxon SET IsAccepted=1 WHERE IsAccepted = 0 AND AcceptedID IS NULL AND " + whereStr); log.debug("Number of IsAccepted Fixed: " + numFixed); log.debug(String.format("\nParent: %s (%d) Child: %s (%d)", parentName, parentLevelRankID, childName, childLevelRankID)); Taxon phTop = phHelper.getHighestPlaceHolder(); if (phTop.getId() != null && phTop.getNodeNumber() != null && phTop.getHighestChildNodeNumber() != null) { whereStr = "((" + whereStr + ") AND NOT NodeNumber BETWEEN " + phTop.getNodeNumber() + " AND " + phTop.getHighestChildNodeNumber() + ")"; } String postfix = " FROM taxon WHERE IsAccepted = 0 AND AcceptedID IS NOT NULL AND RankID = " + childLevelRankID + " AND " + whereStr; int totalCnt = BasicSQLUtils.getCountAsInt("SELECT COUNT(TaxonID) " + postfix); //System.out.println("fixMisparentedSynonymsLevel: " + parentLevelRankID + " > " + childLevelRankID + " (" + totalCnt + ")"); if (totalCnt == 0) { return; } UIFieldFormatterIFace catNumFmt = DBTableIdMgr.getFieldFormatterFor(CollectionObject.class, "CatalogNumber"); log.debug("SELECT COUNT(TaxonID) " + postfix); int cnt = stats[3]; try { tooManyCnt = 0; notFoundCnt = 0; int processCnt = stats[0]; // int withCatNumCnt = stats[1]; // int correct = stats[2]; // int fndCnt = stats[4]; // int phCnt = stats[5]; // int err = stats[6]; // int updateErr = stats[7]; // String searchStr = String.format( "SELECT TaxonID, FullName, Name FROM taxon WHERE IsAccepted <> 0 AND BINARY FullName = ? AND RankID = ? AND %s", whereStr); pTaxNodeStmt = newDBConn.prepareStatement(searchStr); pCatNumStmt = newDBConn.prepareStatement( "SELECT co.CatalogNumber FROM taxon t INNER JOIN determination d ON t.TaxonID = d.TaxonID " + "INNER JOIN collectionobject co ON d.CollectionObjectID = co.CollectionObjectID " + "WHERE t.TaxonID = ?"); pUpdateStmt = newDBConn.prepareStatement("UPDATE taxon SET ParentID=? WHERE TaxonID = ?"); Statement st = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.executeQuery( "SELECT TaxonID, RankID, FullName, ParentID " + postfix + " ORDER BY FullNAme ASC"); while (rs.next()) { int taxonID = rs.getInt(1); if (processedKeys != null && processedKeys.contains(taxonID)) { continue; } int rankId = rs.getInt(2); String fullName = rs.getString(3); int oldParentId = rs.getInt(4); boolean oldParentIdNull = rs.wasNull(); //System.err.println("-------["+fullName+"]-------"); String oldParentName = ""; Integer parentRankID = null; if (oldParentIdNull) { oldParentName = NBSP; } else { Object[] row = BasicSQLUtils .queryForRow("SELECT FullName,RankID FROM taxon WHERE TaxonID = " + oldParentId); oldParentName = (String) row[0]; parentRankID = (Integer) row[1]; } boolean parentRankOK = parentRankID != null && parentRankID == parentLevelRankID; if (!parentRankOK && parentRankID != null && skipBadParentRanks) { continue; } if (processedKeys != null) { processedKeys.add(taxonID); } boolean getParent = !((moveAllSynsOfLowerRanksToPlaceHolder && (parentRankID != null && parentRankID >= childLevelRankID)) || childLevelRankID > maxSynRankToReparent); ArrayList<String> names = parseFullName(fullName); String parent = parentLevelRankID == 180 ? names.get(0) : getParentFullName(names); // names.get(0); Integer newParentID = parentRankOK && getParent ? getTaxonNode(parent, parentLevelRankID) : null; String oldFamily = !oldParentIdNull && parentRankOK ? getFamilyName(oldParentId) : NBSP; String catNums = getCatNumsForTaxon(taxonID, catNumFmt); if (!parentRankOK) { oldParentName = NBSP; } if (!catNums.equals(NBSP)) { withCatNumCnt++; } if (newParentID != null) { if (newParentID != oldParentId) // Search for new Parent and // found one { cnt++; String newFamily = getFamilyName(newParentID); tblWriter.logWithSpaces(Integer.toString(cnt), fullName, oldParentName, oldFamily, parent, newFamily, catNums); if (doCleanup) { if (update(newParentID, taxonID)) { updateErr++; } } fndCnt++; } else { correct++; } } else { Taxon placeHolder = getPlaceHolder(phHelper, parentLevelRankID, childLevelRankID); if (placeHolder != null) { cnt++; tblWriter.logWithSpaces(Integer.toString(cnt), fullName, oldParentName, oldFamily, placeHolder.getName(), NBSP, catNums); phCnt++; if (doCleanup) { if (update(placeHolder.getId(), taxonID)) { updateErr++; } } } else { cnt++; tblWriter.logErrors(Integer.toString(cnt), fullName, String.format("Bad RankID %s", rankId), oldFamily, parent, NBSP, catNums); err++; } } processCnt++; if (processCnt % progressInterval == 0) { int p = (int) (((processCnt * 100) / (double) totalCnt) + 0.5); setProgress(doPercent ? p : processCnt, null); } } rs.close(); stats[0] = processCnt; //1 stats[1] = withCatNumCnt; //2 stats[2] = correct; //3 stats[3] = cnt; //4 stats[4] = fndCnt; //5 stats[5] = phCnt; //6 stats[6] = err; //7 stats[7] = updateErr; //8 pTaxNodeStmt.close(); pCatNumStmt.close(); pUpdateStmt.close(); st.close(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:org.riversun.d6.core.D6Crud.java
/** * Execute select statement for the joined multiple table.<br> * <br>// ww w .j av a 2 s. co m * <br> * -About SQL<br> * You can use prepared SQL.<br> * <br> * In addition,you can also use non-wildcard ('?') SQL (=raw SQL).In this * case searchKeys must be null or empty array(size 0 array).<br> * When you use a wildcard('?'), you must not include the "'"(=>single * quotes) to preparedSQL.<br> * * <br> * -About processing<br> * Used when you execute the SQL that is JOIN multiple tables.<br> * * In this method, you can specify more than one model class.<br> * * When the column name specified in the annotation of the model classes is * included in the resultSet,<br> * a value corresponding to the column name is set to the corresponding * field of model objects.<br> * * In other words, if multiple model class has the same column name, values * in the resultSet is set in the same manner for each mode class.<br> * * * @param preparedSql * @param searchKeys * If the prepared SQL includes a wild card (?), Here is list of * the string to be substituted for wild card. * * The order of value to be included in the array must be the * same as order of appearance of the wild card. * * @param modelClazz * More than one model class in a comma-separated manner for * mapping the results * * @return SQL execution result is returned as MAP. <br> * MAP,key is the model class, value is of instance of the model * class specified as key. * */ @SuppressWarnings({ "rawtypes", "unchecked" }) public Map<Class<?>, List<Object>> execSelectTableWithJoin(String preparedSql, Object[] searchKeys, Class<? extends D6Model>... modelClazz) { log("#execSelectTableWithJoin preparedSql=" + preparedSql + " searchKeys=" + searchKeys + " modelClazz=" + modelClazz); final Map<Class<?>, List<Object>> resultMap = new HashMap<Class<?>, List<Object>>(); final List<ModelWrapper> modelList = new ArrayList<ModelWrapper>(); for (int i = 0; i < modelClazz.length; i++) { @SuppressWarnings("unchecked") final ModelWrapper model = new ModelWrapper(modelClazz[i]); modelList.add(model); } PreparedStatement preparedStmt = null; ResultSet rs = null; final Connection conn = createConnection(); try { preparedStmt = conn.prepareStatement(preparedSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); final StringBuilder logSb = new StringBuilder(); if (searchKeys != null) { logSb.append("/ "); for (int i = 0; i < searchKeys.length; i++) { // Object object = searchKeys[i]; setObject((i + 1), preparedStmt, object); logSb.append("key(" + (i + 1) + ")=" + searchKeys[i]); logSb.append(" "); } } log("#execSelectTableWithJoin SQL=" + preparedSql + " " + logSb.toString()); // execute SQL rs = preparedStmt.executeQuery(); final ResultSetMetaData rsMetaData = rs.getMetaData(); final int numberOfColumns = rsMetaData.getColumnCount(); final List<String> columnNameList = new ArrayList<String>(); // cache column names of this result set for (int i = 0; i < numberOfColumns; i++) { String columnName = rsMetaData.getColumnName(i + 1); columnNameList.add(columnName); } while (rs.next()) { // Processing of a single resultset[begin]============= for (int i = 0; i < numberOfColumns; i++) { // Get from the current resultSet final String columnName = columnNameList.get(i); final Object value = rs.getObject(i + 1); // Set the values to all the properties of model class (You // know property is corresponding to each column of the DB) // via modelWrapper for (ModelWrapper model : modelList) { // set value to model wrapper model.setValue(columnName, value); } } // Processing of a single resultset[end]============= for (ModelWrapper model : modelList) { final Class<?> modelClazzName = model.getClazz(); List<Object> modelObjectList = resultMap.get(modelClazzName); // Generate the result list corresponding to a certain model // class if the list have not been generated. if (modelObjectList == null) { modelObjectList = new ArrayList<Object>(); resultMap.put(modelClazzName, modelObjectList); } // Generates a model object having a property value held in // the model wrapper, and stores the model object in the // modelObjectList final Object resultModelObject = model.getAsObject(); modelObjectList.add(resultModelObject); model.initializeFieldMap(); } } } catch (Exception e) { loge("#execSelectTableWithJoin General ", e); } finally { try { if (rs != null) { rs.close(); } if (preparedStmt != null) { preparedStmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { loge("#execSelectTableWithJoin SQLException ", e); } } return resultMap; }
From source file:com.udps.hive.jdbc.HiveConnection.java
@Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { if (resultSetConcurrency != ResultSet.CONCUR_READ_ONLY) { throw new SQLException( "Statement with resultset concurrency " + resultSetConcurrency + " is not supported", "HYC00"); // Optional // feature // not/*from ww w .j av a 2 s .c o m*/ // implemented } if (resultSetType == ResultSet.TYPE_SCROLL_SENSITIVE) { throw new SQLException("Statement with resultset type " + resultSetType + " is not supported", "HYC00"); // Optional // feature // not // implemented } return new HiveStatement(this, client, sessHandle, resultSetType == ResultSet.TYPE_SCROLL_INSENSITIVE); }
From source file:lasige.steeldb.jdbc.BFTRowSet.java
/** * Sets the properties for this <code>CachedRowSetImpl</code> object to * their default values. This method is called internally by the * default constructor.//from w w w.j av a2 s . c o m */ private void initProperties() throws SQLException { if (resBundle == null) { try { resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle(); } catch (IOException ioe) { throw new RuntimeException(ioe); } } setShowDeleted(false); setQueryTimeout(0); setMaxRows(0); setMaxFieldSize(0); setType(ResultSet.TYPE_SCROLL_INSENSITIVE); setConcurrency(ResultSet.CONCUR_UPDATABLE); if (rvh.size() > 0 && isReadOnly() == false) setReadOnly(false); else setReadOnly(true); setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); setEscapeProcessing(true); setTypeMap(null); checkTransactionalWriter(); //Instantiating the vector for MatchColumns iMatchColumns = new Vector<Integer>(10); for (int i = 0; i < 10; i++) { iMatchColumns.add(i, new Integer(-1)); } strMatchColumns = new Vector<String>(10); for (int j = 0; j < 10; j++) { strMatchColumns.add(j, null); } }
From source file:org.openbizview.util.Bvt002.java
/** * Leer Datos de Usuarios/*from w w w . ja v a2 s .com*/ *<p> Parametros del Metodo: String coduser, String desuser. * String pool * @throws IOException **/ public void selectLogin(String user, String pool) throws NamingException { //Pool de conecciones JNDI. Cambio de metodologia de conexion a Bd. Julio 2010 Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); try { Statement stmt; ResultSet rs; Connection con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin //Class.forName(getDriver()); //con = DriverManager.getConnection( // getUrl(), getUsuario(), getClave()); stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = ""; //System.out.println( productName ); switch (productName) { case "Oracle": query = "SELECT trim(coduser), trim(cluser), trim(B_CODROL), trim(desuser), trim(mail), instancia"; query += " FROM BVT002"; query += " where coduser = '" + user.toUpperCase() + "'"; break; case "PostgreSQL": query = "SELECT trim(coduser), trim(cluser), trim(B_CODROL), trim(desuser), trim(mail), instancia"; query += " FROM BVT002"; query += " where coduser = '" + user.toUpperCase() + "'"; break; case "Microsoft SQL Server": query = "SELECT coduser, cluser, B_CODROL, desuser, mail, instancia"; query += " FROM BVT002"; query += " where coduser = '" + user.toUpperCase() + "'"; break; } // System.out.println(query); try { rs = stmt.executeQuery(query); rows = 1; rs.last(); rows = rs.getRow(); //System.out.println(rows); ResultSetMetaData rsmd = rs.getMetaData(); columns = rsmd.getColumnCount(); //System.out.println(columns); arr = new String[rows][columns]; int i = 0; rs.beforeFirst(); while (rs.next()) { for (int j = 0; j < columns; j++) { arr[i][j] = rs.getString(j + 1); } i++; } } catch (SQLException e) { } stmt.close(); con.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:pagecode.RPTs.OffenceUtil.java
private void processOffenceInfoOld() { logger.info("In OffenceUtil::processOffenceInfoOld"); String selectSql = null;/*from w ww .j a v a 2s. com*/ Connection iicfConnection = ReportsConnectionManager.getIICFConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; if (null != iicfConnection && true == isOffenceNumberValid()) { try { String dbLetterPrefix; //If Offence is Red Light Camera or parking tickets then use //the "P" database [The photo radar DB] in IMS i.e. "P" tables in IICF if ("997".equals(jurisdictionNumber) || "901".equals(jurisdictionNumber)) { dbLetterPrefix = "P"; } else { dbLetterPrefix = "O"; } selectSql = "SELECT " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC AS OFCS_CRT_OFC, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM AS OFCS_JRSDCTN_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY AS OFCS_INFO_YY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX AS OFCS_INFO_PREFIX, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX AS OFCS_INFO_SUFFIX, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_TRL_LANG_CD AS OFCS_TRL_LANG_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INTERP_LANG_CD AS OFCS_INTERP_LANG_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_CNT_NUM AS OCNT_CNT_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_OFNC_DT AS OCNT_OFNC_DT, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_OFCR_BDG_NUM AS OFCS_OFCR_BDG_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_LIC_PLATE_NUM AS OFCS_LIC_PLATE_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_DRVR_LIC_PART_1 AS OFCS_DRVR_LIC_PART_1, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_DRVR_LIC_PART_2 AS OFCS_DRVR_LIC_PART_2, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_DRVR_LIC_PART_3 AS OFCS_DRVR_LIC_PART_3, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ENF_AGCY AS OFCS_ENF_AGCY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_SEX_CD AS OFCS_SEX_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_BRTH_DT AS OFCS_BRTH_DT, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_CVOR_NUM AS OCNT_CVOR_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ENF_AGCY_DIV_CD AS OFCS_ENF_AGCY_DIV_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_GVN_NM AS OFCS_GVN_NM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INIT AS OFCS_INIT, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_SRNM AS OFCS_SRNM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_ST AS OFCS_ADDR_ST, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_CTY AS OFCS_ADDR_CTY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_PROV AS OFCS_ADDR_PROV, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_PSTL_CD AS OFCS_ADDR_PSTL_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_TM AS OCNT_TM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_LOCN_DESC AS OCNT_LOCN_DESC, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_STATU_CD AS OCNT_STATU_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_SCTN_ID AS OCNT_SCTN_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_SUB_SCTN_ID AS OCNT_SUB_SCTN_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_PARA_ID AS OCNT_PARA_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_OFNC_CLAS_ID AS OCNT_OFNC_CLAS_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_BYLAW_CD AS OCNT_BYLAW_CD " + "FROM " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT " + "WHERE " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_CNT_NUM = '0001' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC = '" + courtOfficeId + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM = '" + jurisdictionNumber + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY = '" + infoYear + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX = '" + infoPrefix + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX = '" + infoSuffix + "' " + " " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_CRT_OFC = '" + courtOfficeId + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_JRSDCTN_NUM = '" + jurisdictionNumber + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_INFO_YY = '" + infoYear + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_INFO_PREFIX = '" + infoPrefix + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_INFO_SUFFIX = '" + infoSuffix + "' "; selectSql += " "; logger.info("+++++SQL: " + selectSql); //TODO remove this if (true) { //return; } preparedStatement = iicfConnection.prepareStatement(selectSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = preparedStatement.executeQuery(); resultSet.last(); int resultSetCount = resultSet.getRow(); logger.info("resultSetCount: " + resultSetCount); resultSet.beforeFirst(); if (resultSetCount != 1) { throw new Exception("Offence NOT found or MORE than one found!!!"); } else { //move to first record resultSet.next(); paramOffenceNumber = ""; if (null != resultSet.getString("OFCS_CRT_OFC")) { paramOffenceNumber = resultSet.getString("OFCS_CRT_OFC").trim() + " "; } if (null != resultSet.getString("OFCS_JRSDCTN_NUM")) { paramOffenceNumber += resultSet.getString("OFCS_JRSDCTN_NUM").trim() + " "; } if (null != resultSet.getString("OFCS_INFO_YY")) { paramOffenceNumber += resultSet.getString("OFCS_INFO_YY").trim() + " "; } if (null != resultSet.getString("OFCS_INFO_PREFIX")) { paramOffenceNumber += resultSet.getString("OFCS_INFO_PREFIX").trim() + " "; } if (null != resultSet.getString("OFCS_INFO_SUFFIX")) { paramOffenceNumber += resultSet.getString("OFCS_INFO_SUFFIX").trim(); } if (null != resultSet.getString("OFCS_TRL_LANG_CD")) { String langChar = resultSet.getString("OFCS_TRL_LANG_CD").trim(); isEnglish = (true == langChar.equalsIgnoreCase("F") ? false : true); } //NOTE: if runAsStandAlone == true then assume it is always French if (true == runAsStandAlone) { this.isEnglish = false; } paramInterpreterLanguage = ""; if (null != resultSet.getString("OFCS_INTERP_LANG_CD") && !resultSet.getString("OFCS_INTERP_LANG_CD").trim().equals("")) { paramInterpreterLanguage = resultSet.getString("OFCS_INTERP_LANG_CD").trim(); InterpreterLanguageUtil interpreterLanguageUtil = new InterpreterLanguageUtil(); interpreterLanguageUtil.setIsEnglish(this.isEnglish()); interpreterLanguageUtil.setInterpreterLanguageUtil(paramInterpreterLanguage); paramInterpreterLanguage = interpreterLanguageUtil.getInterpreterLanguageDesc(); } IconDateUtil offenceDateUtil = new IconDateUtil(); offenceDateUtil.setIsEnglish(this.isEnglish); paramOffenceDate = ""; if (null != resultSet.getString("OCNT_OFNC_DT")) { offenceDateUtil.setIconDate(resultSet.getString("OCNT_OFNC_DT").trim()); paramOffenceDate = offenceDateUtil.getDateWithSlashes(); } paramOfficerNumber = ""; if (null != resultSet.getString("OFCS_OFCR_BDG_NUM")) { paramOfficerNumber = resultSet.getString("OFCS_OFCR_BDG_NUM").trim(); } paramDriversLicenseNumber = ""; if (null != resultSet.getString("OFCS_DRVR_LIC_PART_1") && null != resultSet.getString("OFCS_DRVR_LIC_PART_2") && null != resultSet.getString("OFCS_DRVR_LIC_PART_1")) { paramDriversLicenseNumber = resultSet.getString("OFCS_DRVR_LIC_PART_1").trim() + "-" + resultSet.getString("OFCS_DRVR_LIC_PART_2").trim() + "-" + resultSet.getString("OFCS_DRVR_LIC_PART_3").trim(); } paramPlateNumber = ""; if (null != resultSet.getString("OFCS_LIC_PLATE_NUM")) { paramPlateNumber = resultSet.getString("OFCS_LIC_PLATE_NUM").trim(); } paramEnforcementAgency = ""; if (null != resultSet.getString("OFCS_ENF_AGCY")) { paramEnforcementAgency = resultSet.getString("OFCS_ENF_AGCY").trim(); } paramSex = ""; if (null != resultSet.getString("OFCS_SEX_CD")) { paramSex = resultSet.getString("OFCS_SEX_CD").trim(); } paramDateOfBirth = ""; IconDateUtil dateOfBirthUtil = new IconDateUtil(); if (null != resultSet.getString("OFCS_BRTH_DT")) { dateOfBirthUtil.setIconDate(resultSet.getString("OFCS_BRTH_DT").trim()); paramDateOfBirth = dateOfBirthUtil.getDateWithSlashes(); } paramCVOR = ""; if (null != resultSet.getString("OCNT_CVOR_NUM")) { paramCVOR = resultSet.getString("OCNT_CVOR_NUM").trim(); } paramUnit = ""; if (null != resultSet.getString("OFCS_ENF_AGCY_DIV_CD")) { paramUnit = resultSet.getString("OFCS_ENF_AGCY_DIV_CD").trim(); } paramOffenderFullName = ""; if (null != resultSet.getString("OFCS_GVN_NM")) { paramOffenderFullName = resultSet.getString("OFCS_GVN_NM").trim() + " "; } if (null != resultSet.getString("OFCS_INIT")) { paramOffenderFullName += resultSet.getString("OFCS_INIT").trim() + " "; } if (null != resultSet.getString("OFCS_SRNM")) { paramOffenderFullName += resultSet.getString("OFCS_SRNM").trim(); } paramAddressLine1 = ""; if (null != resultSet.getString("OFCS_ADDR_ST")) { paramAddressLine1 = resultSet.getString("OFCS_ADDR_ST").trim(); } paramAddressLine2 = ""; if (null != resultSet.getString("OFCS_ADDR_CTY")) { paramAddressLine2 = resultSet.getString("OFCS_ADDR_CTY").trim(); } if (null != resultSet.getString("OFCS_ADDR_PROV")) { paramAddressLine2 += ", " + resultSet.getString("OFCS_ADDR_PROV").trim(); } if (null != resultSet.getString("OFCS_ADDR_PSTL_CD")) { paramAddressLine3 = resultSet.getString("OFCS_ADDR_PSTL_CD").trim(); } paramOffenceDay = paramOffenceMonth = paramOffenceYear = ""; if (null != resultSet.getString("OCNT_OFNC_DT")) { offenceDateUtil.setIconDate(resultSet.getString("OCNT_OFNC_DT").trim()); paramOffenceDay = offenceDateUtil.getStrDay(); paramOffenceMonth = offenceDateUtil.getStrMonth(); paramOffenceYear = offenceDateUtil.getStrYear(); } //offenceDateUtil = iconDateUtil; paramOffenceTime = ""; if (null != resultSet.getString("OCNT_TM")) { offenceDateUtil.setIconTime(resultSet.getString("OCNT_TM").trim()); paramOffenceTime = offenceDateUtil.getFormatedTime(); } paramOffenceLocation = ""; if (null != resultSet.getString("OCNT_LOCN_DESC")) { paramOffenceLocation = resultSet.getString("OCNT_LOCN_DESC").trim(); } String tempOCNT_STATU_CD, tempOCNT_SCTN_ID, tempOCNT_SUB_SCTN_ID, tempOCNT_PARA_ID, tempOCNT_OFNC_CLAS_ID, tempOCNT_BYLAW_CD; tempOCNT_STATU_CD = ""; if (null != resultSet.getString("OCNT_STATU_CD")) { tempOCNT_STATU_CD = resultSet.getString("OCNT_STATU_CD").trim(); } tempOCNT_SCTN_ID = ""; if (null != resultSet.getString("OCNT_SCTN_ID")) { tempOCNT_SCTN_ID = resultSet.getString("OCNT_SCTN_ID").trim(); } tempOCNT_SUB_SCTN_ID = null; if (null != resultSet.getString("OCNT_SUB_SCTN_ID")) { tempOCNT_SUB_SCTN_ID = resultSet.getString("OCNT_SUB_SCTN_ID").trim(); } tempOCNT_PARA_ID = null; if (null != resultSet.getString("OCNT_PARA_ID")) { tempOCNT_PARA_ID = resultSet.getString("OCNT_PARA_ID").trim(); } tempOCNT_OFNC_CLAS_ID = null; if (null != resultSet.getString("OCNT_OFNC_CLAS_ID")) { tempOCNT_OFNC_CLAS_ID = resultSet.getString("OCNT_OFNC_CLAS_ID").trim(); } tempOCNT_BYLAW_CD = null; if (null != resultSet.getString("OCNT_BYLAW_CD")) { tempOCNT_BYLAW_CD = resultSet.getString("OCNT_BYLAW_CD").trim(); } OffenceStatuteDescriptionUtil offenceStatuteDescriptionUtil = new OffenceStatuteDescriptionUtil(); offenceStatuteDescriptionUtil.setIsEnglish(this.isEnglish); offenceStatuteDescriptionUtil.setOffenceStatuteDescriptionUtil(tempOCNT_STATU_CD, tempOCNT_SCTN_ID, tempOCNT_SUB_SCTN_ID, tempOCNT_PARA_ID, tempOCNT_OFNC_CLAS_ID, tempOCNT_BYLAW_CD, offenceDateUtil); paramOffenceDesc = offenceStatuteDescriptionUtil.getOffenceDescription(); paramOffenceStatue = offenceStatuteDescriptionUtil.getStatuteDescription(); paramOffenceSection = tempOCNT_SCTN_ID; if (null != tempOCNT_SUB_SCTN_ID && !tempOCNT_SUB_SCTN_ID.equals("")) { paramOffenceSection += "(" + tempOCNT_SUB_SCTN_ID + ")"; } if (null != tempOCNT_PARA_ID && !tempOCNT_PARA_ID.equals("")) { paramOffenceSection += "(" + tempOCNT_PARA_ID + ")"; } if (null != tempOCNT_OFNC_CLAS_ID && !tempOCNT_OFNC_CLAS_ID.equals("")) { paramOffenceSection += "(" + tempOCNT_OFNC_CLAS_ID + ")"; } //clean up for reuse if (null != preparedStatement) { preparedStatement.close(); preparedStatement = null; } if (null != resultSet) { resultSet.close(); resultSet = null; } selectSql = "SELECT " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC AS OFCS_CRT_OFC, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM AS OFCS_JRSDCTN_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY AS OFCS_INFO_YY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX AS OFCS_INFO_PREFIX, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX AS OFCS_INFO_SUFFIX, " + " " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_TTL_FINE_AMT, " + " " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_PRE_PD_FINE_TTL_AMT, " + " " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_PRE_PD_COST_TTL_AMT " + "FROM " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS " + "INNER JOIN " + " " + ICON_SCHEMA_NAME + ".ICOSARRT " + " ON " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_CRT_OFC" + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_JRSDCTN_NUM " + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_INFO_YY " + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_INFO_PREFIX " + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_INFO_SUFFIX " + " ) " + "WHERE " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC = '" + courtOfficeId + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM = '" + jurisdictionNumber + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY = '" + infoYear + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX = '" + infoPrefix + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX = '" + infoSuffix + "' "; selectSql += " "; logger.info("+++++SQL: " + selectSql); preparedStatement = iicfConnection.prepareStatement(selectSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = preparedStatement.executeQuery(); resultSet.last(); resultSetCount = resultSet.getRow(); logger.info("resultSetCount: " + resultSetCount); resultSet.beforeFirst(); paramOffenceTotalPayable = ""; if (resultSetCount == 1) { resultSet.next(); // if(null != resultSet.getString("ARRT_TTL_FINE_AMT")){ // NumberFormat formatUS = NumberFormat.getCurrencyInstance(Locale.US); // //logger.info("formatUS: " + formatUS.format(resultSet.getDouble("ARRT_TTL_FINE_AMT")) ); // paramOffenceTotalPayable = formatUS.format(resultSet.getDouble("ARRT_TTL_FINE_AMT")); // } //ARRT_TTL_FINE_AMT, ARRT_PRE_PD_FINE_TTL_AMT, ARRT_PRE_PD_COST_TTL_AMT BigDecimal totalPayable; BigDecimal totalFineAmount = resultSet.getBigDecimal("ARRT_TTL_FINE_AMT"); BigDecimal totalPrePDFineAmount = resultSet.getBigDecimal("ARRT_PRE_PD_FINE_TTL_AMT"); BigDecimal totalPrePDCostAmount = resultSet.getBigDecimal("ARRT_PRE_PD_COST_TTL_AMT"); //these will never be null so NO need to check for null totalPayable = totalPrePDFineAmount.add(totalPrePDCostAmount); //IF(ARRT_PRE_PD_FINE_TTL_AMT + ARRT_PRE_PD_COST_TTL_AMT < 0) THEN if (totalPayable.compareTo(new BigDecimal("0.0")) < 0) { totalPayable = totalFineAmount; } NumberFormat formatUS = NumberFormat.getCurrencyInstance(Locale.US); paramOffenceTotalPayable = formatUS.format(totalPayable.doubleValue()); } } // if(resultSetCount != 1) } catch (SQLException sqlException) { sqlException.printStackTrace(); } catch (Exception e) { paramOffenceNumber = ""; paramOffenceDate = ""; paramOfficerNumber = ""; paramDriversLicenseNumber = ""; paramPlateNumber = ""; paramEnforcementAgency = ""; paramSex = ""; paramDateOfBirth = ""; paramCVOR = ""; paramUnit = ""; paramOffenderFullName = ""; paramAddressLine1 = ""; paramAddressLine2 = ""; paramAddressLine3 = ""; paramOffenceDay = ""; paramOffenceMonth = ""; paramOffenceYear = ""; paramOffenceTime = ""; paramOffenceLocation = ""; paramOffenceDesc = ""; paramOffenceStatue = ""; paramOffenceSection = ""; paramOffenceTotalPayable = ""; paramInterpreterLanguage = ""; e.printStackTrace(); } finally { // setDiscreteParameterValue(this, "paramOffenceNumber", "", paramOffenceNumber); // setDiscreteParameterValue(this, "paramOffenceDate", "", paramOffenceDate); // setDiscreteParameterValue(this, "paramOfficerNumber", "", paramOfficerNumber); // setDiscreteParameterValue(this, "paramDriversLicenseNumber", "", paramDriversLicenseNumber); // setDiscreteParameterValue(this, "paramPlateNumber", "", paramPlateNumber); // setDiscreteParameterValue(this, "paramEnforcementAgency", "", paramEnforcementAgency); // setDiscreteParameterValue(this, "paramSex", "", paramSex); // setDiscreteParameterValue(this, "paramDateOfBirth", "", paramDateOfBirth); // setDiscreteParameterValue(this, "paramCVOR", "", paramCVOR); // setDiscreteParameterValue(this, "paramUnit", "", paramUnit); // setDiscreteParameterValue(this, "paramOffenderFullName", "", paramOffenderFullName); // setDiscreteParameterValue(this, "paramAddressLine1", "", paramAddressLine1); // setDiscreteParameterValue(this, "paramAddressLine2", "", paramAddressLine2); // setDiscreteParameterValue(this, "paramAddressLine3", "", paramAddressLine3); // setDiscreteParameterValue(this, "paramOffenceDay", "", paramOffenceDay); // setDiscreteParameterValue(this, "paramOffenceMonth", "", paramOffenceMonth); // setDiscreteParameterValue(this, "paramOffenceYear", "", paramOffenceYear); // setDiscreteParameterValue(this, "paramOffenceTime", "", paramOffenceTime); // setDiscreteParameterValue(this, "paramOffenceLocation", "", paramOffenceLocation); // setDiscreteParameterValue(this, "paramOffenceDesc", "", paramOffenceDesc); // setDiscreteParameterValue(this, "paramOffenceStatue", "", paramOffenceStatue); // setDiscreteParameterValue(this, "paramOffenceSection", "", paramOffenceSection); // setDiscreteParameterValue(this, "paramOffenceTotalPayable", "", paramOffenceTotalPayable); // setDiscreteParameterValue(this, "paramTrialDay", "", paramTrialDay); // setDiscreteParameterValue(this, "parmaTrialMonth", "", parmaTrialMonth); // setDiscreteParameterValue(this, "paramTrailYear", "", paramTrailYear); // setDiscreteParameterValue(this, "paramTrailTimeHour", "", paramTrailTimeHour); // setDiscreteParameterValue(this, "paramTrailLocation", "", paramTrailLocationCourtOfficeId); // setDiscreteParameterValue(this, "paramIssuedAt", "", paramIssuedAt); // setDiscreteParameterValue(this, "paramIssuedDay", "", paramIssuedDay); // setDiscreteParameterValue(this, "paramIssuedMonth", "", paramIssuedMonth); // setDiscreteParameterValue(this, "paramIssuedYear", "", paramIssuedYear); // //Modified Oct 17, 2007 // setDiscreteParameterValue(this, "paramFormNumberLabel", "", paramFormNumberLabel); try { if (null != preparedStatement) { preparedStatement.close(); preparedStatement = null; } if (null != iicfConnection) { iicfConnection.close(); iicfConnection = null; } } catch (SQLException e1) { } } //try } //if(null != iicfConnection && true == isOffenceNumberValid()) }
From source file:org.sha.util.Bvt002.java
/** * Leer Datos de Usuarios/*from w w w.ja v a2 s . co m*/ *<p> Parametros del Metodo: String coduser, String desuser. * String pool * @throws IOException **/ public void selectLogin(String user, String pool) throws NamingException { //Pool de conecciones JNDI. Cambio de metodologia de conexion a Bd. Julio 2010 Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); try { Statement stmt; ResultSet rs; Connection con = ds.getConnection(); //Class.forName(getDriver()); //con = DriverManager.getConnection( // getUrl(), getUsuario(), getClave()); stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = "SELECT CODUSER, CLUSER, B_CODROL, DESUSER, MAIL, SUCURSAL"; query += " FROM SHABVT002"; query += " WHERE CODUSER = '" + user.toUpperCase() + "'"; //System.out.println(query); try { rs = stmt.executeQuery(query); rows = 1; rs.last(); rows = rs.getRow(); //System.out.println(rows); ResultSetMetaData rsmd = rs.getMetaData(); columns = rsmd.getColumnCount(); //System.out.println(columns); arr = new String[rows][columns]; int i = 0; rs.beforeFirst(); while (rs.next()) { for (int j = 0; j < columns; j++) { arr[i][j] = rs.getString(j + 1); } i++; } } catch (SQLException e) { } stmt.close(); con.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:fr.bird.bloom.model.GeographicTreatment.java
/** * Select wrong geospatial and write in a file : * tag "hasGeospatialIssues_" = true//from w w w . ja v a2 s .c o m * * @return File wrong geospatial */ public List<String> deleteWrongGeospatial() { Statement statement = null; try { statement = ConnectionDatabase.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DatabaseTreatment newConnection = new DatabaseTreatment(statement); List<String> messages = new ArrayList<>(); messages.add("\n--- Select wrong geospatialIssues ---"); String sqlRetrieveWrongGeo = "SELECT abstract_,acceptedNameUsage_,acceptedNameUsageID_,accessRights_,accrualMethod_,accrualPeriodicity_,accrualPolicy_,alternative_,associatedMedia_,associatedOccurrences_,associatedOrganisms_,associatedReferences_,associatedSequences_,associatedTaxa_,audience_,available_,basisOfRecord_,bed_,behavior_,bibliographicCitation_,catalogNumber_,class_,classKey_,collectionCode_,collectionID_,conformsTo_,continent_,contributor_,coordinateAccuracy_,coordinatePrecision_,coordinateUncertaintyInMeters_,country_,countryCode_,county_,coverage_,created_,creator_,dataGeneralizations_,datasetID_,datasetKey_,datasetName_,date_,dateAccepted_,dateCopyrighted_,dateIdentified_,dateSubmitted_,day_,decimalLatitude_,decimalLongitude_,depth_,depthAccuracy_,description_,disposition_,distanceAboveSurface_,distanceAboveSurfaceAccuracy_,dynamicProperties_,earliestAgeOrLowestStage_,earliestEonOrLowestEonothem_,earliestEpochOrLowestSeries_,earliestEraOrLowestErathem_,earliestPeriodOrLowestSystem_,educationLevel_,elevation_,elevationAccuracy_,endDayOfYear_,establishmentMeans_,event_,eventDate_,eventID_,eventRemarks_,eventTime_,extent_,family_,familyKey_,fieldNotes_,fieldNumber_,footprintSpatialFit_,footprintSRS_,footprintWKT_,format_,formation_,gbifID_,genericName_,genus_,genusKey_,geodeticDatum_,geologicalContext_,geologicalContextID_,georeferencedBy_,georeferencedDate_,georeferenceProtocol_,georeferenceRemarks_,georeferenceSources_,georeferenceVerificationStatus_,group_,habitat_,hasCoordinate_,hasFormat_,hasGeospatialIssues_,hasPart_,hasVersion_,higherClassification_,higherGeography_,higherGeographyID_,highestBiostratigraphicZone_,identification_,identificationID_,identificationQualifier_,identificationReferences_,identificationRemarks_,identificationVerificationStatus_,identifiedBy_,identifier_,idFile_,individualCount_,individualID_,informationWithheld_,infraspecificEpithet_,institutionCode_,institutionID_,instructionalMethod_,isFormatOf_,island_,islandGroup_,isPartOf_,isReferencedBy_,isReplacedBy_,isRequiredBy_,issue_,issued_,isVersionOf_,kingdom_,kingdomKey_,language_,lastCrawled_,lastInterpreted_,lastParsed_,latestAgeOrHighestStage_,latestEonOrHighestEonothem_,latestEpochOrHighestSeries_,latestEraOrHighestErathem_,latestPeriodOrHighestSystem_,license_,lifeStage_,lithostratigraphicTerms_,livingSpecimen_,locality_,locationAccordingTo_,locationID_,locationRemarks_,lowestBiostratigraphicZone_,machineObservation_,materialSample_,materialSampleID_,maximumDepthinMeters_,maximumDistanceAboveSurfaceInMeters_,maximumElevationInMeters_,measurementAccuracy_,measurementDeterminedBy_,measurementDeterminedDate_,measurementID_,measurementMethod_,measurementOrFact_,measurementRemarks_,measurementType_,measurementUnit_,mediator_,mediaType_,medium_,member_,minimumDepthinMeters_,minimumDistanceAboveSurfaceInMeters_,minimumElevationInMeters_,modified_,month_,municipality_,nameAccordingTo_,nameAccordingToID_,namePublishedIn_,namePublishedInID_,namePublishedInYear_,nomenclaturalCode_,nomenclaturalStatus_,occurrence_,occurrenceDetails_,occurrenceID_,occurrenceRemarks_,occurrenceStatus_,order_,orderKey_,organism_,organismID_,organismName_,organismRemarks_,organismScope_,originalNameUsage_,originalNameUsageID_,otherCatalogNumbers_,ownerInstitutionCode_,parentNameUsage_,parentNameUsageID_,phylum_,phylumKey_,pointRadiusSpatialFit_,preparations_,preservedSpecimen_,previousIdentifications_,protocol_,provenance_,publisher_,publishingCountry_,recordedBy_,recordNumber_,references_,relatedResourceID_,relationshipAccordingTo_,relationshipEstablishedDate_,relationshipRemarks_,relation_,replaces_,reproductiveCondition_,requires_,resourceID_,resourceRelationship_,resourceRelationshipID_,rights_,rightsHolder_,samplingEffort_,samplingProtocol_,scientificName_,scientificNameAuthorship_,scientificNameID_,sex_,source_,spatial_,species_,speciesKey_,specificEpithet_,startDayOfYear_,stateProvince_,subgenus_,subgenusKey_,subject_,tableOfContents_,taxon_,taxonConceptID_,taxonID_,taxonKey_,taxonomicStatus_,taxonRank_,taxonRemarks_,temporal_,title_,type_,typeStatus_,typifiedName_,valid_,verbatimCoordinates_,verbatimCoordinateSystem_,verbatimDate_,verbatimDepth_,verbatimElevation_,verbatimEventDate_,verbatimLatitude_,verbatimLocality_,verbatimLongitude_,verbatimSRS_,verbatimTaxonRank_,vernacularName_,waterBody_,year_ FROM Workflow.DarwinCoreInput WHERE (UUID_=\"" + this.getUuid() + "\") AND hasGeospatialIssues_='true' AND !(decimalLatitude_=0 OR decimalLatitude_>90 OR decimalLatitude_<-90 OR decimalLongitude_=0 OR decimalLongitude_>180 OR decimalLongitude_<-180);"; messages.addAll(newConnection.executeSQLcommand("executeQuery", sqlRetrieveWrongGeo)); List<String> resultatSelect = newConnection.getResultatSelect(); messages.add("nb lignes affectes : " + Integer.toString(resultatSelect.size() - 1)); this.setNbWrongGeospatialIssues(resultatSelect.size() - 1); if (!new File(BloomConfig.getDirectoryPath() + "temp/" + this.getUuid() + "/wrong/").exists()) { BloomUtils.createDirectory(BloomConfig.getDirectoryPath() + "temp/" + this.getUuid() + "/wrong/"); } for (int j = 0; j < messages.size(); j++) { System.out.println(messages.get(j)); } this.setNbWrongGeospatialIssues(resultatSelect.size() - 1); return resultatSelect; }
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); }/* w w w . ja v a 2 s . c om*/ 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); } } } }
From source file:net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.java
protected static int getResultSetType(String type) { if (TYPE_FORWARD_ONLY.equals(type)) { return ResultSet.TYPE_FORWARD_ONLY; } else if (TYPE_SCROLL_INSENSITIVE.equals(type)) { return ResultSet.TYPE_SCROLL_INSENSITIVE; } else if (TYPE_SCROLL_SENSITIVE.equals(type)) { return ResultSet.TYPE_SCROLL_SENSITIVE; }//from w ww . ja v a 2 s .com return ResultSet.TYPE_FORWARD_ONLY; }