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.GenericDBConversion.java
/** * @param treeDef/* w w w . j av a2 s. c om*/ * @throws SQLException */ public void convertLithoStratCustom(final LithoStratTreeDef treeDef, final LithoStrat earth, final TableWriter tblWriter, final String srcTableName, final boolean doMapGTPIds) throws SQLException { Statement stmt = null; ResultSet rs = null; String s = ""; try { // get a Hibernate session for saving the new records Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); int count = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM " + srcTableName); if (count < 1) return; if (hasFrame) { setProcess(0, count); } // create an ID mapper for the geography table (mainly for use in converting localities) IdHashMapper lithoStratIdMapper = IdMapperMgr.getInstance().addHashMapper("stratigraphy_StratigraphyID", true); if (lithoStratIdMapper == null) { UIRegistry.showError("The lithoStratIdMapper was null."); return; } IdTableMapper gtpIdMapper = IdMapperMgr.getInstance().addTableMapper("geologictimeperiod", "GeologicTimePeriodID", null, false); if (doMapGTPIds) { gtpIdMapper.clearRecords(); gtpIdMapper.mapAllIds(); } Hashtable<Integer, Integer> stratGTPIdHash = new Hashtable<Integer, Integer>(); //Hashtable<Integer, Integer> newCEIdToNewStratIdHash = new Hashtable<Integer, Integer>(); // stratigraphy2 goes here. IdHashMapper newCEIdToNewStratIdHash = IdMapperMgr.getInstance() .addHashMapper("stratigraphy_StratigraphyID_2", true); newCEIdToNewStratIdHash.setShowLogErrors(false); IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID"); if (ceMapper == null) { ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null, false); } // get all of the old records // String sql = String.format("SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + // "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + // "WHERE s.SuperGroup IS NOT NULL OR s.Group IS NOT NULL OR s.Formation IS NOT NULL OR " + // "s.Member IS NOT NULL OR s.Bed IS NOT NULL ORDER BY s.StratigraphyID", srcTableName); String sql = String.format( "SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + "ORDER BY s.StratigraphyID", srcTableName); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); rs = stmt.executeQuery(sql); int stratsWithNoGTP = 0; int stratsWithNoMappedGTP = 0; int missingCEMapping = 0; int lithoCnt = 0; int counter = 0; // for each old record, convert the record while (rs.next()) { if (counter % 500 == 0) { if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } } // grab the important data fields from the old record int oldStratId = rs.getInt(1); // This is a one-to-one with CollectingEvent String superGroup = rs.getString(2); String lithoGroup = rs.getString(3); String formation = rs.getString(4); String member = rs.getString(5); String bed = rs.getString(6); String remarks = escapeStringLiterals(rs.getString(7)); String text1 = escapeStringLiterals(rs.getString(8)); String text2 = escapeStringLiterals(rs.getString(9)); Double number1 = rs.getObject(10) != null ? rs.getDouble(10) : null; Double number2 = rs.getObject(11) != null ? rs.getDouble(11) : null; Boolean yesNo1 = rs.getObject(12) != null ? rs.getBoolean(12) : null; Boolean yesNo2 = rs.getObject(13) != null ? rs.getBoolean(13) : null; Integer oldGTPId = rs.getObject(14) != null ? rs.getInt(14) : null; // Check to see if there is any Litho information OR an GTP Id // If both are missing then skip the record. boolean hasLithoFields = isNotEmpty(superGroup) || isNotEmpty(lithoGroup) || isNotEmpty(formation) || isNotEmpty(member); if (!hasLithoFields && oldGTPId == null) { continue; } Integer gtpId = null; if (doMapGTPIds) { if (oldGTPId != null) { gtpId = oldGTPId; } } else { gtpId = oldStratId; } if (gtpId != null) { gtpId = gtpIdMapper.get(gtpId); if (gtpId == null) { tblWriter.logError("Old GTPID[" + gtpId + "] in the Strat record could not be mapped for Old StratID[" + oldStratId + "]"); stratsWithNoMappedGTP++; } } else { stratsWithNoGTP++; } // There may not be any Litho information to add to the LithoStrat tree, // but it did have GTP Information if we got here if (hasLithoFields) { // create a new Geography object from the old data LithoStrat[] newStrats = convertOldStratRecord(superGroup, lithoGroup, formation, member, bed, remarks, text1, text2, number1, number2, yesNo1, yesNo2, earth, localSession); LithoStrat newStrat = getLastLithoStrat(newStrats); counter++; lithoCnt += newStrats.length; // Map Old LithoStrat ID to the new Tree Id //System.out.println(oldStratId + " " + newStrat.getLithoStratId()); if (newStrat != null) { lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId()); // Convert Old CEId (StratID) to new CEId, then map the new CEId -> new StratId Integer newCEId = ceMapper.get(oldStratId); if (newCEId != null) { newCEIdToNewStratIdHash.put(newCEId, newStrat.getLithoStratId()); } else { String msg = String.format( "No CE mapping for Old StratId %d, when they are a one-to-one.", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } // Map the New StratId to the new GTP Id if (gtpId != null && stratGTPIdHash.get(newStrat.getLithoStratId()) == null) { stratGTPIdHash.put(newStrat.getLithoStratId(), gtpId); // new ID to new ID } } else { String msg = String.format("Strat Fields were all null for oldID", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } } } stmt.close(); System.out.println("lithoCnt: " + lithoCnt); if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); HibernateUtil.commitTransaction(); log.info("Converted " + counter + " Stratigraphy records"); rs.close(); Statement updateStatement = newDBConn.createStatement(); //Hashtable<Integer, Integer> ceToPCHash = new Hashtable<Integer, Integer>(); int ceCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT Count(CollectingEventID) FROM collectingevent"); int stratCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format("SELECT Count(CollectingEventID) FROM collectingevent " + "INNER JOIN %s ON CollectingEventID = StratigraphyID", srcTableName)); String msg = String.format("There are %d CE->Strat and %d CEs. The diff is %d", stratCnt, ceCnt, (ceCnt - stratCnt)); tblWriter.log(msg); log.debug(msg); // Create a PaleoContext for each ColObj stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); int processCnt = BasicSQLUtils .getCountAsInt("SELECT COUNT(*) FROM collectionobject WHERE CollectingEventID IS NOT NULL"); if (frame != null) { frame.setDesc("Converting PaleoContext..."); frame.setProcess(0, processCnt); } TreeSet<Integer> missingStratIds = new TreeSet<Integer>(); int missingStrat = 0; int missingGTP = 0; int coUpdateCnt = 0; int cnt = 0; sql = "SELECT CollectionObjectID, CollectingEventID FROM collectionobject WHERE CollectingEventID IS NOT NULL ORDER BY CollectionObjectID"; rs = stmt.executeQuery(sql); while (rs.next()) { int coId = rs.getInt(1); // New CO Id Integer ceId = rs.getInt(2); // New CE Id // Use the new CE ID to get the new Strat Id Integer newLithoId = newCEIdToNewStratIdHash.get(ceId); Integer gtpId = null; if (newLithoId == null) { missingStrat++; missingStratIds.add(ceId); Integer oldStratID = ceMapper.reverseGet(ceId); if (oldStratID != null) { sql = "SELECT GeologicTimePeriodID FROM stratigraphy WHERE StratigraphyID = " + oldStratID; Integer oldGTPId = BasicSQLUtils.getCount(oldDBConn, sql); if (oldGTPId != null) { gtpId = gtpIdMapper.get(oldGTPId); } } if (gtpId == null) continue; } // Use the new StratID to get the new GTP Id (ChronosStratigraphy) if (gtpId == null) { gtpId = stratGTPIdHash.get(newLithoId); if (gtpId == null) { missingGTP++; if (newLithoId == null) continue; } } try { String updateStr = "INSERT INTO paleocontext (TimestampCreated, TimestampModified, DisciplineID, Version, CreatedByAgentID, ModifiedByAgentID, LithoStratID, ChronosStratID) " + "VALUES ('" + nowStr + "','" + nowStr + "'," + getDisciplineId() + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + "," + (newLithoId != null ? newLithoId : "NULL") + "," + (gtpId != null ? gtpId : "NULL") + ")"; updateStatement.executeUpdate(updateStr, Statement.RETURN_GENERATED_KEYS); Integer paleoContextID = getInsertedId(updateStatement); if (paleoContextID == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } String sqlUpdate = "UPDATE collectionobject SET PaleoContextID=" + paleoContextID + " WHERE CollectionObjectID = " + coId; updateStatement.executeUpdate(sqlUpdate); coUpdateCnt++; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.getMessage()); throw new RuntimeException(e); } processCnt++; if (frame != null && cnt % 100 == 0) frame.setProcess(cnt); } rs.close(); stmt.close(); if (frame != null) frame.setProcess(processCnt); msg = String.format("There are %d unmappable Strat Records and %d unmappable GTP records.", missingStrat, missingGTP); tblWriter.log(msg); log.debug(msg); msg = String.format("There are %d CO records updated.", coUpdateCnt); tblWriter.log(msg); log.debug(msg); updateStatement.close(); msg = String.format("No CE mapping for Old StratId Count: %d", missingCEMapping); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with No GTP Count: %d", stratsWithNoGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with missing Mapping to GTP Count: %d", stratsWithNoMappedGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Number of Old StratIds mapped to a new Strat ID Count: %d", lithoStratIdMapper.size()); tblWriter.logError(msg); log.error(msg); StringBuilder sb = new StringBuilder(); sb.append("Missing New Strat: "); if (missingStratIds.size() == 0) sb.append("None"); for (Integer id : missingStratIds) { sb.append(String.format("%d, ", id)); } tblWriter.logError(sb.toString()); log.error(sb.toString()); } catch (Exception ex) { ex.printStackTrace(); } // Now in this Step we Add the PaleoContext to the Collecting Events }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Walks the old GTP records and creates a GTP tree def and items based on the ranks and rank * names found in the old records/*www . j a v a 2s . c o m*/ * * @return the new tree def * @throws SQLException on any error while contacting the old database */ public GeologicTimePeriodTreeDef convertGTPDefAndItems(final boolean isPaleo) throws SQLException { deleteAllRecordsFromTable("geologictimeperiodtreedef", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable("geologictimeperiodtreedefitem", BasicSQLUtils.myDestinationServerType); log.info("Inferring geologic time period definition from old records"); int count = 0; // get all of the old records String sql = "SELECT RankCode, RankName from geologictimeperiod"; Statement statement = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); statement.setFetchSize(Integer.MIN_VALUE); ResultSet oldGtpRecords = statement.executeQuery(sql); Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); GeologicTimePeriodTreeDef def = new GeologicTimePeriodTreeDef(); def.initialize(); def.setName("Inferred Geologic Time Period Definition"); def.setRemarks(""); def.setFullNameDirection(TreeDefIface.REVERSE); localSession.save(def); Vector<GeologicTimePeriodTreeDefItem> newItems = new Vector<GeologicTimePeriodTreeDefItem>(); GeologicTimePeriodTreeDefItem rootItem = addGtpDefItem(0, "Time Root", def); rootItem.setIsEnforced(true); rootItem.setIsInFullName(false); rootItem.setFullNameSeparator(", "); localSession.save(rootItem); newItems.add(rootItem); ++count; if (isPaleo) { while (oldGtpRecords.next()) { // we're modifying the rank since the originals were 1,2,3,... // to make them 100, 200, 300, ... (more like the other trees) Integer rankCode = oldGtpRecords.getInt(1) * 100; String rankName = oldGtpRecords.getString(2); GeologicTimePeriodTreeDefItem newItem = addGtpDefItem(rankCode, rankName, def); if (newItem != null) { newItem.setFullNameSeparator(", "); localSession.save(newItem); newItems.add(newItem); } if (++count % 1000 == 0) { log.info(count + " geologic time period records processed"); } } } // sort the vector to put them in parent/child order Comparator<GeologicTimePeriodTreeDefItem> itemComparator = new Comparator<GeologicTimePeriodTreeDefItem>() { public int compare(GeologicTimePeriodTreeDefItem o1, GeologicTimePeriodTreeDefItem o2) { return o1.getRankId().compareTo(o2.getRankId()); } }; Collections.sort(newItems, itemComparator); // set the parent/child pointers for (int i = 0; i < newItems.size() - 1; ++i) { GeologicTimePeriodTreeDefItem parent = newItems.get(i); GeologicTimePeriodTreeDefItem child = newItems.get(i + 1); parent.setChild(child); child.setParent(parent); } HibernateUtil.commitTransaction(); log.info("Finished inferring GTP tree definition and items"); return def; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param tblWriter/*from www . j av a 2s . c o m*/ * @param treeDef * @param isPaleo * @throws SQLException */ public void convertGTP(final TableWriter tblWriter, final GeologicTimePeriodTreeDef treeDef, final boolean isPaleo) throws SQLException { deleteAllRecordsFromTable("geologictimeperiod", BasicSQLUtils.myDestinationServerType); log.info("Converting old geologic time period records"); int count = 0; // create an ID mapper for the geologictimeperiod table IdTableMapper gtpIdMapper = IdMapperMgr.getInstance().addTableMapper("geologictimeperiod", "GeologicTimePeriodID"); Hashtable<Integer, GeologicTimePeriod> oldIdToGTPMap = new Hashtable<Integer, GeologicTimePeriod>(); // String sql = "SELECT g.GeologicTimePeriodID,g.RankCode,g.Name,g.Standard,g.Remarks,g.TimestampModified,g.TimestampCreated,p1.Age as Upper," + // "p1.AgeUncertainty as UpperUncertainty,p2.Age as Lower,p2.AgeUncertainty as LowerUncertainty FROM geologictimeperiod g, " + // "geologictimeboundary p1, geologictimeboundary p2 WHERE g.UpperBoundaryID=p1.GeologicTimeBoundaryID AND " + // "g.LowerBoundaryID=p2.GeologicTimeBoundaryID ORDER BY Lower DESC, RankCode"; String sql = "SELECT g.GeologicTimePeriodID,g.RankCode,g.Name,g.Standard,g.Remarks,g.TimestampModified,g.TimestampCreated,gb1.Age as Upper," + "gb1.AgeUncertainty as UpperUncertainty,gb2.Age as Lower,gb2.AgeUncertainty as LowerUncertainty FROM geologictimeperiod g " + "LEFT OUTER JOIN geologictimeboundary gb1 ON g.UpperBoundaryID = gb1.GeologicTimeBoundaryID " + "LEFT OUTER JOIN geologictimeboundary gb2 ON g.LowerBoundaryID = gb2.GeologicTimeBoundaryID " + "ORDER BY Lower DESC, RankCode"; Statement statement = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); statement.setFetchSize(Integer.MIN_VALUE); ResultSet rs = statement.executeQuery(sql); Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); Vector<GeologicTimePeriod> newItems = new Vector<GeologicTimePeriod>(); GeologicTimePeriod allTime = new GeologicTimePeriod(); allTime.initialize(); allTime.setDefinition(treeDef); GeologicTimePeriodTreeDefItem rootDefItem = treeDef.getDefItemByRank(0); allTime.setDefinitionItem(rootDefItem); allTime.setRankId(0); allTime.setName("Time"); allTime.setFullName("Time"); allTime.setStartPeriod(100000f); allTime.setEndPeriod(0f); allTime.setEndUncertainty(0f); allTime.setTimestampCreated(now); ++count; newItems.add(allTime); ArrayList<GeologicTimePeriod> needsPlaceHolderList = new ArrayList<GeologicTimePeriod>(); boolean needsTbl = true; if (isPaleo) { while (rs.next()) { Integer id = rs.getInt(1); Integer rank = rs.getInt(2) * 100; String name = rs.getString(3); String std = rs.getString(4); String rem = rs.getString(5); Date modTDate = rs.getDate(6); Date creTDate = rs.getDate(7); Timestamp modT = (modTDate != null) ? new Timestamp(modTDate.getTime()) : null; Timestamp creT = (creTDate != null) ? new Timestamp(creTDate.getTime()) : null; Float upper = (Float) rs.getObject(8); Float uError = (Float) rs.getObject(9); Float lower = (Float) rs.getObject(10); Float lError = (Float) rs.getObject(11); if (isEmpty(name)) { if (needsTbl) { tblWriter.startTable(); tblWriter.logHdr("ID", "Rank Name", "Name", "Reason"); needsTbl = false; } tblWriter.log(id.toString(), rank.toString(), name, "Name is null, Name set to 'XXXX'"); log.error("The Name is empty (or null) for GTP ID[" + id + "] Rank[" + rank + "]"); name = "XXXX"; } if (modT == null && creT == null) { creT = now; modT = now; } else if (modT == null && creT != null) { modT = new Timestamp(creT.getTime()); } else if (modT != null && creT == null) { creT = new Timestamp(modT.getTime()); } // else (neither are null, so do nothing) GeologicTimePeriodTreeDefItem defItem = rank != null ? treeDef.getDefItemByRank(rank) : null; GeologicTimePeriod gtp = new GeologicTimePeriod(); gtp.initialize(); gtp.setName(name); gtp.setFullName(name); gtp.setDefinitionItem(defItem); gtp.setRankId(rank); gtp.setDefinition(treeDef); gtp.setStartPeriod(lower); gtp.setStartUncertainty(lError); gtp.setEndPeriod(upper); gtp.setEndUncertainty(uError); gtp.setStandard(std); gtp.setRemarks(rem); gtp.setTimestampCreated(creT); gtp.setTimestampModified(modT); if (lower == null || upper == null || rank == null) { needsPlaceHolderList.add(gtp); log.debug("PlaceHold Old ID: " + id); } else { newItems.add(gtp); } oldIdToGTPMap.put(id, gtp); if (++count % 500 == 0) { log.info(count + " geologic time period records converted"); } } // now we need to fix the parent/pointers for (int i = 0; i < newItems.size(); ++i) { GeologicTimePeriod gtp = newItems.get(i); for (int j = 0; j < newItems.size(); ++j) { GeologicTimePeriod child = newItems.get(j); if (isParentChildPair(gtp, child)) { gtp.addChild(child); } } } if (needsPlaceHolderList.size() > 0) { int rank = 100; for (GeologicTimePeriodTreeDefItem di : treeDef.getTreeDefItems()) { System.out.println(di.getName() + " -> " + di.getRankId()); } GeologicTimePeriodTreeDefItem defItem = treeDef.getDefItemByRank(rank); GeologicTimePeriod gtp = new GeologicTimePeriod(); gtp.initialize(); gtp.setName("Placeholder"); gtp.setFullName("Placeholder"); gtp.setDefinitionItem(defItem); gtp.setRankId(rank); gtp.setDefinition(treeDef); gtp.setStartPeriod(0.0f); gtp.setStartUncertainty(0.0f); gtp.setEndPeriod(0.0f); gtp.setEndUncertainty(0.0f); gtp.setStandard(null); gtp.setRemarks(null); gtp.setTimestampCreated(now); gtp.setTimestampModified(now); allTime.addChild(gtp); rank = 200; defItem = treeDef.getDefItemByRank(rank); for (GeologicTimePeriod gtpPH : needsPlaceHolderList) { gtpPH.setDefinition(treeDef); gtpPH.setDefinitionItem(defItem); gtpPH.setRankId(rank); gtpPH.setStartPeriod(0.0f); gtpPH.setStartUncertainty(0.0f); gtpPH.setEndPeriod(0.0f); gtpPH.setEndUncertainty(0.0f); gtp.addChild(gtpPH); } } TreeHelper.fixFullnameForNodeAndDescendants(allTime); } // fix node number, child node number stuff allTime.setNodeNumber(1); fixNodeNumbersFromRoot(allTime); localSession.save(allTime); HibernateUtil.commitTransaction(); if (shouldCreateMapTables) { // add all of the ID mappings for (Integer oldId : oldIdToGTPMap.keySet()) { if (oldId != null) { GeologicTimePeriod gtp = oldIdToGTPMap.get(oldId); if (gtp != null) { if (gtp.getId() != null) { gtpIdMapper.put(oldId, gtp.getId()); } else { log.debug("GTP id is null: " + gtp.getName()); } } else { log.debug("GTP missing in hash for Old ID: " + oldId); } } else { log.debug("Old ID in Hash is null: " + oldId); } } } // set up geologictimeperiod foreign key mapping for stratigraphy IdMapperMgr.getInstance().mapForeignKey("Stratigraphy", "GeologicTimePeriodID", "GeologicTimePeriod", "GeologicTimePeriodID"); log.info(count + " geologic time period records converted"); if (!needsTbl) { tblWriter.endTable(); } }