List of usage examples for java.sql Statement setFetchSize
void setFetchSize(int rows) throws SQLException;
ResultSet objects generated by this Statement. From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the LoanPhysicalObjects. * @return true if no errors//from w w w. ja v a 2 s. co m */ public boolean convertGiftPreparations() { setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); setIdentityInsertONCommandForSQLServer(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType); // automatically closes the connection if (getNumRecords(oldDBConn, "loanphysicalobject") == 0) { setIdentityInsertOFFCommandForSQLServer(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType); return true; } Integer recCount = getCount(oldDBConn, "SELECT count(*) FROM loan WHERE Category = 1 ORDER BY LoanID"); if (recCount == null || recCount == 0) { return true; } // This mapping is used by Gifts IdMapperIFace giftsIdMapper = IdMapperMgr.getInstance().get("gift", "GiftID"); //if (shouldCreateMapTables) //{ // giftsIdMapper.mapAllIdsWithSQL(); //} // This mapping is used by Gifts Preps IdMapperIFace giftPrepsIdMapper = IdMapperMgr.getInstance().get("giftphysicalobject", "id"); TableWriter tblWriter = convLogger.getWriter("convertGiftPreparations.html", "Gift Preparations"); TimeLogger timeLogger = new TimeLogger(); try { Map<String, String> colNewToOldMap = createFieldNameMap( new String[] { "PreparationID", "PhysicalObjectID" }); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); StringBuilder str = new StringBuilder(); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("SELECT "); List<String> names = getFieldNamesFromSchema(oldDBConn, "loanphysicalobject"); sql.append(buildSelectFieldList(names, "loanphysicalobject")); oldFieldNames.addAll(names); sql.append( " FROM loanphysicalobject INNER JOIN loan ON loanphysicalobject.LoanID = loan.LoanID WHERE loan.Category = 1 ORDER BY loanphysicalobject.LoanPhysicalObjectID"); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "giftpreparation"); log.info("Number of Fields in New giftpreparation " + newFieldMetaData.size()); String sqlStr = sql.toString(); colNewToOldMap.put("GiftID", "LoanID"); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); } String tableName = "loanphysicalobject"; //int quantityIndex = oldNameIndex.get("Quantity"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); int loanPhysIdIndex = oldNameIndex.get("LoanPhysicalObjectID"); log.info(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); if (hasFrame) { if (rs.last()) { setProcess(0, rs.getRow()); rs.first(); } else { rs.close(); stmt.close(); return true; } } else { if (!rs.first()) { rs.close(); stmt.close(); return true; } } String insertStmtStr = null; int count = 0; do { //int quantity = getIntValue(rs, quantityIndex); String lastEditedBy = rs.getString(lastEditedByInx); str.setLength(0); if (insertStmtStr == null) { StringBuffer fieldList = new StringBuffer(); fieldList.append("( "); for (int i = 0; i < newFieldMetaData.size(); i++) { if ((i > 0) && (i < newFieldMetaData.size())) { fieldList.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName + " "); } fieldList.append(")"); insertStmtStr = "INSERT INTO giftpreparation " + fieldList + " VALUES ("; } str.append(insertStmtStr); for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) str.append(", "); String newFieldName = newFieldMetaData.get(i).getName(); if (i == 0) { Integer oldId = rs.getInt(loanPhysIdIndex); Integer newID = giftPrepsIdMapper.get(oldId); if (newID != null) { str.append(getStrValue(newID)); } else { String msg = String.format( "loanPhysIdIndex: %d; Old Id: %d could be mapped to a new ID. (This was a fatal error).", loanPhysIdIndex, oldId); tblWriter.logError(msg); log.error(msg); return false; } } else if (newFieldName.equals("ReceivedComments")) { str.append("NULL"); } else if (newFieldName.equalsIgnoreCase("Version")) { str.append("0"); } else if (newFieldName.equalsIgnoreCase("DisciplineID")) { str.append(getDisciplineId()); } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) { str.append(getModifiedByAgentId(lastEditedBy)); } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) { str.append(getCreatorAgentId(null)); } else { Integer index = null; String oldMappedColName = colNewToOldMap.get(newFieldName); if (oldMappedColName != null) { index = oldNameIndex.get(oldMappedColName); } else { index = oldNameIndex.get(newFieldName); oldMappedColName = newFieldName; } Object data; if (index == null) { String msg = "convertGiftPreparations - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // stmt.close(); // tblWriter.logError(msg); // showError(msg); // throw new RuntimeException(msg); data = null; } else { data = rs.getObject(index); } if (data != null) { if (newFieldName.equalsIgnoreCase("GiftID")) { data = giftsIdMapper.get((Integer) data); } else { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { IdMapperIFace idMapper = idMapperMgr.get(tableName, oldMappedColName); if (idMapper != null) { Integer oldId = rs.getInt(index); data = idMapper.get(oldId); if (data == null) { String msg = "No Map ID for [" + tableName + "][" + oldMappedColName + "] for ID[" + oldId + "]"; log.error(msg); tblWriter.logError(msg); } } else { String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]"; log.error(msg); tblWriter.logError(msg); } } } } str.append(getStrValue(data, newFieldMetaData.get(i).getType())); } } str.append(")"); if (hasFrame) { if (count % 500 == 0) { setProcess(count); } } else { if (count % 2000 == 0) { log.info("LoanPreparation Records: " + count); } } try { Statement updateStatement = newDBConn.createStatement(); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); } // log.debug("executring: " + str.toString()); // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); updateStatement.executeUpdate(str.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } catch (SQLException e) { log.error("Count: " + count); e.printStackTrace(); log.error(e); rs.close(); stmt.close(); throw new RuntimeException(e); } count++; // if (count > 10) break; } while (rs.next()); if (hasFrame) { setProcess(count); log.info("Processed LoanPreparation " + count + " records."); } else { log.info("Processed LoanPreparation " + count + " records."); } rs.close(); stmt.close(); tblWriter.log(String.format("Determinations Processing Time: %s", timeLogger.end())); } catch (SQLException e) { e.printStackTrace(); log.error(e); setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation", BasicSQLUtils.myDestinationServerType); throw new RuntimeException(e); } log.info("Done processing LoanPhysicalObject"); setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation", BasicSQLUtils.myDestinationServerType); tblWriter.close(); return true; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @return/*from w w w .j a v a2 s . co m*/ */ public boolean convertLoanPreparations() { setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); setIdentityInsertONCommandForSQLServer(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType); // automatically closes the connection if (getNumRecords(oldDBConn, "loanphysicalobject") == 0) { setIdentityInsertOFFCommandForSQLServer(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType); return true; } Integer recCount = getCount(oldDBConn, "SELECT count(*) FROM loan WHERE Category = 0 ORDER BY LoanID"); if (recCount == null || recCount == 0) { return true; } TableWriter tblWriter = convLogger.getWriter("convertLoanPreparations.html", "Loan Preparations"); IdTableMapper loanPrepsMapper = (IdTableMapper) IdMapperMgr.getInstance().get("loanphysicalobject", "LoanPhysicalObjectID"); if (loanPrepsMapper == null) { String msg = "LoanPrepsMapper not found. (This was a fatal error)."; tblWriter.logError(msg); log.error(msg); return false; } TimeLogger timeLogger = new TimeLogger(); try { Map<String, String> colNewToOldMap = createFieldNameMap( new String[] { "PreparationID", "PhysicalObjectID", }); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); StringBuilder str = new StringBuilder(); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("SELECT "); List<String> names = getFieldNamesFromSchema(oldDBConn, "loanphysicalobject"); sql.append(buildSelectFieldList(names, "loanphysicalobject")); oldFieldNames.addAll(names); sql.append( " FROM loanphysicalobject INNER JOIN loan ON loanphysicalobject.LoanID = loan.LoanID WHERE loan.Category = 0"); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "loanpreparation"); log.info("Number of Fields in New loanpreparation " + newFieldMetaData.size()); String sqlStr = sql.toString(); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); } String tableName = "loanphysicalobject"; int loanPhysIdIndex = oldNameIndex.get("LoanPhysicalObjectID"); int quantityIndex = oldNameIndex.get("Quantity"); int quantityRetIndex = oldNameIndex.get("QuantityReturned"); int quantityResIndex = oldNameIndex.get("QuantityResolved"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); log.info(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); if (hasFrame) { if (rs.last()) { setProcess(0, rs.getRow()); rs.first(); } else { rs.close(); stmt.close(); return true; } } else { if (!rs.first()) { rs.close(); stmt.close(); return true; } } String insertStmtStr = null; int count = 0; do { boolean skipInsert = false; int quantity = getIntValue(rs, quantityIndex); int quantityResolved = getIntValue(rs, quantityResIndex); int quantityReturned = getIntValue(rs, quantityRetIndex); Boolean isResolved = quantityReturned == quantity || quantityResolved == quantity; String lastEditedBy = rs.getString(lastEditedByInx); str.setLength(0); if (insertStmtStr == null) { StringBuffer fieldList = new StringBuffer(); fieldList.append("( "); for (int i = 0; i < newFieldMetaData.size(); i++) { if ((i > 0) && (i < newFieldMetaData.size())) { fieldList.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName + " "); } fieldList.append(")"); insertStmtStr = "INSERT INTO loanpreparation " + fieldList + " VALUES ("; } str.append(insertStmtStr); /*int loanPhysId = rs.getInt(loanPhysIdIndex); String loanNumber = BasicSQLUtils.querySingleObj(oldDBConn, "SELECT LoanNumber FROM loan l INNER JOIN loanphysicalobject lp ON l.LoanID = lp.LoanID WHERE LoanPhysicalObjectID = "+loanPhysId); if (loanNumber != null && loanNumber.equals("25")) { debug = true; System.out.println("-------------------------------------\n"+loanPhysId); }*/ for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) str.append(", "); String newFieldName = newFieldMetaData.get(i).getName(); if (i == 0) { Integer oldId = rs.getInt(loanPhysIdIndex); Integer newID = loanPrepsMapper.get(oldId); if (newID != null) { str.append(getStrValue(newID)); } else { String msg = String.format( "loanPhysIdIndex: %d; Old Id: %d could be mapped to a new ID. (This was a fatal error).", loanPhysIdIndex, oldId); tblWriter.logError(msg); log.error(msg); return false; } } else if (newFieldName.equals("ReceivedComments")) { str.append("NULL"); } else if (newFieldName.equals("IsResolved")) { str.append(getStrValue(isResolved)); } else if (newFieldName.equalsIgnoreCase("Version")) { str.append("0"); } else if (newFieldName.equalsIgnoreCase("DisciplineID")) { str.append(getDisciplineId()); } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) { str.append(getModifiedByAgentId(lastEditedBy)); } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) { str.append(getCreatorAgentId(null)); } else { Integer index = null; String oldMappedColName = colNewToOldMap.get(newFieldName); if (oldMappedColName != null) { index = oldNameIndex.get(oldMappedColName); } else { index = oldNameIndex.get(newFieldName); oldMappedColName = newFieldName; } Object data; if (index == null) { String msg = "convertLoanPreparations - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // stmt.close(); // tblWriter.logError(msg); // showError(msg); // throw new RuntimeException(msg); data = null; } else { data = rs.getObject(index); } if (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { IdMapperIFace idMapper = idMapperMgr.get(tableName, oldMappedColName); if (idMapper != null) { Integer oldId = rs.getInt(index); data = idMapper.get(oldId); if (data == null) { String msg = "No Map ID for [" + tableName + "][" + oldMappedColName + "] for ID[" + oldId + "]"; log.error(msg); tblWriter.logError(msg); skipInsert = true; } } else { String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]"; log.error(msg); tblWriter.logError(msg); skipInsert = true; } } } str.append(getStrValue(data, newFieldMetaData.get(i).getType())); } } str.append(")"); if (hasFrame) { if (count % 500 == 0) { setProcess(count); } } else { if (count % 2000 == 0) { log.info("LoanPreparation Records: " + count); } } try { if (!skipInsert) { Statement updateStatement = newDBConn.createStatement(); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); } // log.debug("executring: " + str.toString()); // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); updateStatement.executeUpdate(str.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } } catch (SQLException e) { log.error("Count: " + count); e.printStackTrace(); log.error(e); rs.close(); stmt.close(); throw new RuntimeException(e); } count++; // if (count > 10) break; } while (rs.next()); if (hasFrame) { setProcess(count); log.info("Processed LoanPreparation " + count + " records."); } else { log.info("Processed LoanPreparation " + count + " records."); } rs.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); log.error(e); setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation", BasicSQLUtils.myDestinationServerType); throw new RuntimeException(e); } log.info("Done processing LoanPhysicalObject"); setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation", BasicSQLUtils.myDestinationServerType); //tblWriter.log(String.format("Loan Preps Processing Time: %s", timeLogger.end())); tblWriter.close(); return true; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the Determinations.//from w w w .ja va 2 s . c om * @return true if no errors */ public boolean convertDeterminationRecords() { TableWriter tblWriter = convLogger.getWriter("convertDeterminations.html", "Determinations"); setIdentityInsertONCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); // automatically closes the connection if (getNumRecords(oldDBConn, "determination") == 0) { return true; } TimeLogger timeLogger = new TimeLogger(); String oldDetermination_Current = "Current"; String oldDetermination_Date = "Date"; /*if (BasicSQLUtils.mySourceServerType == BasicSQLUtils.SERVERTYPE.MySQL) { oldDetermination_Date = "Date1"; oldDetermination_Current = "IsCurrent"; }*/ Map<String, String> colNewToOldMap = createFieldNameMap( new String[] { "CollectionObjectID", "BiologicalObjectID", // meg is this right? "IsCurrent", oldDetermination_Current, "DeterminedDate", oldDetermination_Date, // want to change over to DateField TODO Meg!!! "TaxonID", "TaxonNameID" }); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("SELECT "); List<String> names = getFieldNamesFromSchema(oldDBConn, "determination"); sql.append(buildSelectFieldList(names, "determination")); oldFieldNames.addAll(names); sql.append( ", cc.CatalogSeriesID AS CatSeriesID FROM determination Inner Join collectionobjectcatalog AS cc ON determination.BiologicalObjectID = cc.CollectionObjectCatalogID"); log.info(sql); if (BasicSQLUtils.mySourceServerType == BasicSQLUtils.SERVERTYPE.MS_SQLServer) { log.debug("FIXING select statement to run against SQL Server......."); log.debug("old string: " + sql.toString()); String currentSQL = sql.toString(); currentSQL = currentSQL.replaceAll("Current", "[" + "Current" + "]"); log.debug("new string: " + currentSQL); sql = new StringBuilder(currentSQL); } oldFieldNames.add("CatSeriesID"); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "determination"); log.info("Number of Fields in New Determination " + newFieldMetaData.size()); String sqlStr = sql.toString(); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); } String tableName = "determination"; //int isCurrentInx = oldNameIndex.get(oldDetermination_Current) + 1; log.info(sqlStr); System.err.println(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); if (hasFrame) { if (rs.last()) { setProcess(0, rs.getRow()); rs.first(); } else { rs.close(); stmt.close(); return true; } } else { if (!rs.first()) { rs.close(); stmt.close(); return true; } } PartialDateConv partialDateConv = new PartialDateConv(); IdMapperIFace detIdMapper = IdMapperMgr.getInstance().get("determination", "DeterminationID"); IdMapperIFace colObjIdMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID"); IdMapperIFace colObjCatIdMapper = idMapperMgr.get("collectionobject", "CollectionObjectID"); Integer catSeriesIdInx = oldNameIndex.get("CatSeriesID"); Integer oldRecIDInx = oldNameIndex.get("DeterminationID"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); Integer detDateInx = oldNameIndex.get("Date"); System.err.println("catSeriesIdInx: " + catSeriesIdInx); HashMap<String, Integer> nameToInxHash = new HashMap<String, Integer>(); StringBuffer fieldList = new StringBuffer(); StringBuilder insertQuesDB = new StringBuilder(); for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) { fieldList.append(','); insertQuesDB.append(','); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName); insertQuesDB.append('?'); nameToInxHash.put(newFieldName, (i + 1)); System.out.println(newFieldName + " " + (i + 1)); } String insertStmtStr = "INSERT INTO determination (" + fieldList + ") VALUES (" + insertQuesDB.toString() + ')'; log.debug(insertStmtStr); PreparedStatement pStmt = newDBConn.prepareStatement(insertStmtStr); int count = 0; do { partialDateConv.nullAll(); String lastEditedBy = rs.getString(lastEditedByInx); Integer catSeriesId = rs.getInt(catSeriesIdInx); if (catSeriesId != null && rs.wasNull()) { String msg = String.format( "Error - The Determination had a null CatalogSeries DeterminationID %d; it's CollectionObjectID: %d", rs.getInt(1), rs.getInt(6)); log.error(msg); tblWriter.logError(msg); //if (rs.next()) //{ continue; //} //break; } Vector<Integer> collectionIdList = catSeriesToNewCollectionID.get(catSeriesId); if (collectionIdList == null) { //Integer colObjId = rs.getInt(idIndex); throw new RuntimeException("There are no Collections mapped to CatSeriesId[" + catSeriesId + "] (converting Determinations)"); } if (collectionIdList.size() > 1) { UIRegistry.showError( "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now."); } Integer collectionId = collectionIdList.get(0); if (collectionId == null) { throw new RuntimeException("CollectionId is null when mapped from CatSeriesId"); } this.curCollectionID = collectionId; boolean isError = false; for (int i = 0; i < newFieldMetaData.size(); i++) { String newFieldName = newFieldMetaData.get(i).getName(); int fldInx = nameToInxHash.get(newFieldName); if (i == 0) { Integer recId = rs.getInt(oldRecIDInx); Integer newId = detIdMapper.get(recId); if (newId != null) { pStmt.setInt(fldInx, newId); } else { String msg = String.format("Error - Unable to map old id %d to new Id", recId); log.error(msg); tblWriter.logError(msg); isError = true; continue; } } else if (newFieldName.equals("Version")) // User/Security changes { pStmt.setInt(fldInx, 0); } else if (newFieldName.equals("DeterminedDate")) { //System.out.println("["+rs.getObject(detDateInx)+"]"); if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (!partialDateConv.isNull()) { int len = partialDateConv.getDateStr().length(); if (len == 12) { String tsStr = partialDateConv.getDateStr().length() == 12 ? partialDateConv.getDateStr().substring(1, 11) : partialDateConv.getDateStr(); pStmt.setString(fldInx, tsStr); } else { if (!partialDateConv.getDateStr().equals("NULL")) log.error("Determined Date was in error[" + partialDateConv.getDateStr() + "]"); pStmt.setObject(fldInx, null); } } else { pStmt.setObject(fldInx, null); } /* if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (isNotEmpty(partialDateConv.getDateStr())) { try { Date tsDate = sdf.parse(partialDateConv.getDateStr()); pStmt.setTimestamp(fldInx, new Timestamp(tsDate.getTime())); } catch (ParseException e) { e.printStackTrace(); pStmt.setObject(fldInx, null); } } else { pStmt.setObject(fldInx, null); } */ } else if (newFieldName.equals("DeterminedDatePrecision")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (partialDateConv.getPartial() != null) { if (partialDateConv.getPartial().length() > 1) { pStmt.setInt(fldInx, 1); } else { pStmt.setInt(fldInx, Integer.parseInt(partialDateConv.getPartial())); } } else { pStmt.setInt(fldInx, 1); } } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes { Integer agentId = getCreatorAgentId(null); pStmt.setInt(fldInx, agentId); } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes { Integer agentId = getModifiedByAgentId(lastEditedBy); pStmt.setInt(fldInx, agentId); } else if (newFieldName.equals("Qualifier") || newFieldName.equals("SubSpQualifier") || newFieldName.equals("VarQualifier") || newFieldName.equals("Addendum") || newFieldName.equals("AlternateName") || newFieldName.equals("NameUsage") || newFieldName.equals("GUID") || newFieldName.equals("PreferredTaxonID")) { pStmt.setObject(fldInx, null); } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes { pStmt.setInt(fldInx, getCollectionMemberId()); } else { Integer index = null; String oldMappedColName = colNewToOldMap.get(newFieldName); if (oldMappedColName != null) { index = oldNameIndex.get(oldMappedColName); } else { index = oldNameIndex.get(newFieldName); oldMappedColName = newFieldName; } Object data; if (index == null) { String msg = "convertDeterminationRecords - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // stmt.close(); // tblWriter.logError(msg); // throw new RuntimeException(msg); data = null; } else { data = rs.getObject(index); } if (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { Integer oldId = (Integer) data; IdMapperIFace idMapper; if (oldMappedColName.equals("BiologicalObjectID")) { data = colObjIdMapper.get(oldId); if (data == null) { data = colObjCatIdMapper.get(oldId); } } else { idMapper = idMapperMgr.get(tableName, oldMappedColName); if (idMapper != null) { data = idMapper.get(oldId); } else { String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]"; log.error(msg); tblWriter.logError(msg); isError = true; break; } } if (data == null) { String msg = "The determination with recordID[" + rs.getInt(oldRecIDInx) + "] could not find a mapping for record ID[" + oldId + "] for Old Field[" + oldMappedColName + "]"; log.debug(msg); tblWriter.logError(msg); tblWriter.log(ConvertVerifier.dumpSQL(oldDBConn, "SELECT * FROM determination WHERE DeterminationId = " + rs.getInt(oldRecIDInx))); if (isValueRequired(tableName, newFieldName)) { msg = "For table[" + tableName + "] the field [" + newFieldName + "] is null and can't be. Old value[" + oldId + "]"; log.error(msg); tblWriter.logError(msg); } isError = true; break; } } } //fixTimestamps(newFieldName, newFieldMetaData.get(i).getType(), data, str); FieldMetaData fldMetaData = newFieldMetaData.get(i); if (fldMetaData == null) { String msg = "For table[" + tableName + "] the field [" + newFieldName + "] FieldMeataDate was null for index[" + i + "]"; log.error(msg); tblWriter.logError(msg); } else { //System.out.println(fldMetaData.getName()+" "+fldMetaData.getSqlType()+" "+fldMetaData.getType()); BasicSQLUtils.setData(pStmt, newFieldMetaData.get(i).getSqlType(), fldInx, data); } } } if (hasFrame) { if (count % 500 == 0) { setProcess(count); } } else { if (count % 2000 == 0) { log.info("Determination Records: " + count); } } if (!isError) { try { if (pStmt.executeUpdate() != 1) { log.error("Count: " + count); log.error("Error inserting record."); } } catch (SQLException e) { log.error("Count: " + count); e.printStackTrace(); log.error(e); rs.close(); stmt.close(); showError(e.toString()); throw new RuntimeException(e); } } count++; // if (count > 10) break; } while (rs.next()); pStmt.close(); if (hasFrame) { setProcess(count); } else { log.info("Processed Determination " + count + " records."); } rs.close(); stmt.close(); tblWriter.log(String.format("Determination Processing Time: %s", timeLogger.end())); tblWriter.close(); } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); return true; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the CollectionObject Physical records and CollectionObjectCatalog Records into * the new schema Preparation table./*from ww w . java 2s. c o m*/ * @return true if no errors */ public boolean convertPreparationRecords(final Hashtable<Integer, Map<String, PrepType>> collToPrepTypeHash) { TableWriter tblWriter = convLogger.getWriter("convertPreparations.html", "Preparations"); deleteAllRecordsFromTable(newDBConn, "preparation", BasicSQLUtils.myDestinationServerType); TimeLogger timeLogger = new TimeLogger(); // BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "preparation", // BasicSQLUtils.myDestinationServerType); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); StringBuilder str = new StringBuilder(); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("SELECT "); List<String> names = getFieldNamesFromSchema(oldDBConn, "collectionobject"); sql.append(buildSelectFieldList(names, "co")); sql.append(", "); oldFieldNames.addAll(names); names = getFieldNamesFromSchema(oldDBConn, "collectionobjectcatalog"); sql.append(buildSelectFieldList(names, "cc")); oldFieldNames.addAll(names); String sqlPostfix = " FROM collectionobject co LEFT JOIN collectionobjectcatalog cc ON co.CollectionObjectID = cc.CollectionObjectCatalogID " + "WHERE NOT (co.DerivedFromID IS NULL) AND CatalogSeriesID IS NOT NULL ORDER BY co.CollectionObjectID"; sql.append(sqlPostfix); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "preparation"); log.info("Number of Fields in (New) Preparation " + newFieldMetaData.size()); for (FieldMetaData field : newFieldMetaData) { log.info(field.getName()); } String sqlStr = sql.toString(); log.debug(sql); log.debug("------------------------ Old Names"); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); log.debug("OldName: " + name + " " + (inx - 1)); } log.debug("------------------------"); Hashtable<String, String> newToOld = new Hashtable<String, String>(); newToOld.put("PreparationID", "CollectionObjectID"); newToOld.put("CollectionObjectID", "DerivedFromID"); newToOld.put("StorageLocation", "Location"); boolean doDebug = false; ResultSet rs = stmt.executeQuery(sqlStr); if (!rs.next()) { rs.close(); stmt.close(); setProcess(0, 0); return true; } Statement prepStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); prepStmt.setFetchSize(Integer.MIN_VALUE); IdTableMapper prepIdMapper = idMapperMgr.addTableMapper("CollectionObject", "CollectionObjectID", doDeleteAllMappings); if (shouldCreateMapTables) { String sql2 = "SELECT c.CollectionObjectID FROM collectionobject c WHERE NOT (c.DerivedFromID IS NULL) ORDER BY c.CollectionObjectID"; prepIdMapper.mapAllIds(sql2); } else { prepIdMapper = (IdTableMapper) idMapperMgr.get("preparation", "PreparationID"); } String insertStmtStr = null; boolean shouldCheckPrepAttrs = BasicSQLUtils.getCountAsInt( "SELECT COUNT(*) FROM preparation WHERE PreparedByID IS NOT NULL OR PreparedDate IS NOT NULL") > 0; Statement prepTypeStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); prepTypeStmt.setFetchSize(Integer.MIN_VALUE); PartialDateConv partialDateConv = new PartialDateConv(); prepIdMapper.setShowLogErrors(false); int totalPrepCount = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*)" + sqlPostfix); setProcess(0, totalPrepCount); Statement updateStatement = newDBConn.createStatement(); //int prepDateInx = oldNameIndex.get("CatalogedDate") + 1; int lastEditedByInx = oldNameIndex.get("LastEditedBy"); Integer idIndex = oldNameIndex.get("CollectionObjectID"); Integer catSeriesIdInx = oldNameIndex.get("CatalogSeriesID"); int count = 0; do { partialDateConv.nullAll(); Integer preparedById = null; if (shouldCheckPrepAttrs) { Integer recordId = rs.getInt(idIndex + 1); String subQueryStr = "select PreparedByID, PreparedDate from preparation where PreparationID = " + recordId; ResultSet subQueryRS = prepTypeStmt.executeQuery(subQueryStr); if (subQueryRS.next()) { preparedById = subQueryRS.getInt(1); getPartialDate(rs.getObject(2), partialDateConv); } else { partialDateConv.setDateStr("NULL"); partialDateConv.setPartial("NULL"); } subQueryRS.close(); } Integer catSeriesId = rs.getInt(catSeriesIdInx); //log.debug("catSeriesId "+catSeriesId+" catSeriesIdInx "+catSeriesIdInx); Vector<Integer> collectionIdList = catSeriesToNewCollectionID.get(catSeriesId); if (collectionIdList == null) { //Integer colObjId = rs.getInt(idIndex); throw new RuntimeException("There are no Collections mapped to CatSeriesId[" + catSeriesId + "] (converting Preps)"); } if (collectionIdList.size() == 0) { UIRegistry.showError( "There are NO Collections assigned to the same CatalogSeries and we can't handle that right now."); return false; } if (collectionIdList.size() > 1) { UIRegistry.showError( "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now."); return false; } Integer colId = collectionIdList.get(0); Collection collection = collIdToCollObj.get(colId); if (collection == null) { Session localSession = HibernateUtil.getCurrentSession(); List<Collection> colList = (List<Collection>) localSession .createQuery("FROM Collection WHERE id = " + colId).list(); if (colList == null || colList.size() == 0) { UIRegistry.showError("The collection is null for Catalog Series ID: " + catSeriesId); return false; } collection = colList.get(0); collIdToCollObj.put(colId, collection); } Map<String, PrepType> prepTypeMap = collToPrepTypeHash.get(collectionIdList.get(0)); String lastEditedBy = rs.getString(lastEditedByInx); /* * int catNum = rs.getInt(oldNameIndex.get("CatalogNumber")+1); doDebug = catNum == * 30972; * * if (doDebug) { log.debug("CatalogNumber "+catNum); * log.debug("CollectionObjectID * "+rs.getInt(oldNameIndex.get("CollectionObjectID")+1)); * log.debug("DerivedFromID * "+rs.getInt(oldNameIndex.get("DerivedFromID"))); } */ str.setLength(0); if (insertStmtStr == null) { StringBuffer fieldList = new StringBuffer(); fieldList.append("( "); for (int i = 0; i < newFieldMetaData.size(); i++) { if ((i > 0) && (i < newFieldMetaData.size())) { fieldList.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName + " "); } fieldList.append(")"); insertStmtStr = "INSERT INTO preparation " + fieldList + " VALUES ("; } str.append(insertStmtStr); Integer oldId = null; boolean isError = false; for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) { str.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); String mappedName = newToOld.get(newFieldName); //log.debug("["+newFieldName+"]["+mappedName+"]"); if (mappedName != null) { newFieldName = mappedName; } else { mappedName = newFieldName; } if (i == 0) { oldId = rs.getInt(1); Integer newId = prepIdMapper.get(oldId); if (newId == null) { isError = true; break; //throw new RuntimeException("Preparations - Couldn't find new ID for old ID["+oldId+"]"); } str.append(newId); } else if (newFieldName.equals("PreparedByID")) { if (agentIdMapper != null) { str.append(getStrValue(agentIdMapper.get(preparedById))); } else { str.append("NULL"); //log.error("No Map for PreparedByID[" + preparedById + "]"); } } else if (newFieldName.equals("PreparedDate")) { str.append(partialDateConv.getDateStr()); } else if (newFieldName.equals("PreparedDatePrecision")) { str.append(partialDateConv.getPartial()); } else if (newFieldName.equals("DerivedFromIDX")) { // skip } else if (newFieldName.equals("PreparationAttributeID")) { Integer id = rs.getInt(idIndex + 1); Object data = prepIdMapper.get(id); if (data == null) { // throw new RuntimeException("Couldn't map ID for new // PreparationAttributesID [CollectionObjectID]["+id+"]"); str.append("NULL"); } else { ResultSet prepRS = prepStmt.executeQuery( "select PreparationID from preparation where PreparationID = " + id); if (prepRS.first()) { str.append(getStrValue(data)); } else { str.append("NULL"); } prepRS.close(); } } else if (newFieldName.equals("CountAmt")) { Integer value = rs.getInt("Count"); if (rs.wasNull()) { value = null; } str.append(getStrValue(value)); } else if (newFieldName.equalsIgnoreCase("SampleNumber") || newFieldName.equalsIgnoreCase("Status") || newFieldName.equalsIgnoreCase("YesNo3")) { str.append("NULL"); } else if (newFieldName.equalsIgnoreCase("Version")) { str.append("0"); } else if (newFieldName.equalsIgnoreCase("CollectionMemberID")) { str.append(getCollectionMemberId()); } else if (newFieldName.equalsIgnoreCase("TimestampCreated")) { Object value = rs.getString(oldNameIndex.get("TimestampCreated")); if (value == null) { value = new Timestamp(Calendar.getInstance().getTime().getTime()); } str.append(getStrValue(value, newFieldMetaData.get(i).getType())); } else if (newFieldName.equalsIgnoreCase("TimestampModified")) { Object value = rs.getString(oldNameIndex.get("TimestampModified")); if (value == null) { value = new Timestamp(Calendar.getInstance().getTime().getTime()); } str.append(getStrValue(value, newFieldMetaData.get(i).getType())); } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) { str.append(getModifiedByAgentId(lastEditedBy)); } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) { str.append(getCreatorAgentId(null)); } else if (newFieldName.equals("PrepTypeID")) { String value = rs.getString(oldNameIndex.get("PreparationMethod")); if (value == null || value.length() == 0) { value = "n/a"; } /*if (value.equalsIgnoreCase("Slide")) { PrepType prepType = prepTypeMap.get(value.toLowerCase()); if (prepType != null) { Integer prepTypeId = prepType.getPrepTypeId(); System.err.println(String.format("%s -> %d %s", value, prepTypeId, prepType.getName())); } }*/ PrepType prepType = prepTypeMap.get(value.toLowerCase()); if (prepType != null) { Integer prepTypeId = prepType.getPrepTypeId(); if (prepTypeId != null) { str.append(getStrValue(prepTypeId)); } else { str.append("NULL"); String msg = "***************** Couldn't find PreparationMethod[" + value + "] in PrepTypeMap"; log.error(msg); tblWriter.log(msg); } } else { String msg = "Couldn't find PrepType[" + value + "] creating it."; log.info(msg); tblWriter.log(msg); prepType = new PrepType(); prepType.initialize(); prepType.setName(value); prepType.setCollection(collection); prepTypeMap.put(value, prepType); Session tmpSession = null; try { tmpSession = HibernateUtil.getCurrentSession(); Transaction trans = tmpSession.beginTransaction(); trans.begin(); tmpSession.save(prepType); trans.commit(); str.append(getStrValue(prepType.getPrepTypeId())); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } } } else if (newFieldName.equals("StorageID") || newFieldName.equals("Storage")) { str.append("NULL"); } else { Integer index = oldNameIndex.get(newFieldName); Object data; if (index == null) { // convertPreparationRecords String msg = "convertPreparationRecords - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); //stmt.close(); //throw new RuntimeException(msg); data = null; } else { data = rs.getObject(index); } if (idMapperMgr != null && mappedName.endsWith("ID") && !mappedName.endsWith("GUID")) { //log.debug(mappedName); IdMapperIFace idMapper; if (mappedName.equals("DerivedFromID")) { idMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID"); } else { idMapper = idMapperMgr.get("collectionobject", mappedName); } if (idMapper != null) { //Object prevObj = data; data = idMapper.get((Integer) data); if (data == null) { String msg = "The mapped value came back null for old record Id [" + oldId + "] field [" + mappedName + "] => [" + data + "]"; log.error(msg); tblWriter.logError(msg); isError = true; break; } } else { String msg = "The could find mapper collectionobject_" + mappedName + " for old record Id [" + oldId + "] field=[" + data + "]"; log.error(msg); tblWriter.logError(msg); isError = true; break; } } str.append(getStrValue(data, newFieldMetaData.get(i).getType())); } } str.append(")"); // log.info("\n"+str.toString()); if (hasFrame) { if (count % 500 == 0) { setProcess(count); log.info("Preparation Records: " + count); } } else { if (count % 2000 == 0) { log.info("Preparation Records: " + count); } } if (!isError) { try { // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { removeForeignKeyConstraints(newDBConn, "preparation", BasicSQLUtils.myDestinationServerType); } if (doDebug) { log.debug(str.toString()); } //log.debug(str.toString()); updateStatement.executeUpdate(str.toString()); } catch (Exception e) { log.error("Error trying to execute: " + str.toString()); log.error("Count: " + count); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } count++; // if (count == 1) break; } while (rs.next()); prepTypeStmt.close(); prepStmt.close(); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; if (hasFrame) { if (count % 2000 == 0) { final int cnt = count; SwingUtilities.invokeLater(new Runnable() { @Override public void run() { setProcess(cnt); } }); } } else { if (count % 2000 == 0) { log.info("Processed CollectionObject " + count + " records."); } } String postSQL = " FROM collectionobject co " + "INNER JOIN collectionobjectcatalog cc ON co.CollectionObjectID = cc.CollectionObjectCatalogID " + "WHERE NOT (co.DerivedFromID IS NOT NULL) AND Location IS NOT NULL"; int cntTotal = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) " + postSQL); if (cntTotal > 0) { frame.setProcess(0, cntTotal); frame.setDesc("Moving Location data to Preparations..."); IdMapperIFace idMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID"); PreparedStatement pStmt = newDBConn .prepareStatement("UPDATE preparation SET StorageLocation=? WHERE CollectionObjectID=?"); stmt = oldDBConn.createStatement(); sqlStr = "SELECT CollectionObjectID, Location " + postSQL; log.debug(sqlStr); rs = stmt.executeQuery(sqlStr); int cnt = 0; while (rs.next()) { int id = rs.getInt(1); String locStr = rs.getString(2); Integer newId = idMapper.get(id); if (newId != null) { pStmt.setString(1, locStr); pStmt.setInt(2, newId); pStmt.execute(); } cnt++; if (cnt % 100 == 0) { frame.setProcess(cnt); } } rs.close(); stmt.close(); pStmt.close(); frame.setProcess(cntTotal); } } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } tblWriter.log(String.format("Preparations Processing Time: %s", timeLogger.end())); tblWriter.close(); return true; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * //from w ww . j ava 2 s. c om */ public void convertLocality() { int errorsToShow = BasicSQLUtils.SHOW_ALL; log.debug("Preparing to convert localities"); // Ignore these field names from new table schema when mapping IDs setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); setIdentityInsertONCommandForSQLServer(newDBConn, "locality", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable("locality", BasicSQLUtils.myDestinationServerType); Hashtable<String, String> newToOldColMap = new Hashtable<String, String>(); newToOldColMap.put("Visibility", "GroupPermittedToView"); String[] fieldsToIgnore = new String[] { "GML", "NamedPlaceExtent", "GeoRefAccuracyUnits", "GeoRefDetRef", "GeoRefDetDate", "GeoRefDetBy", "NoGeoRefBecause", "GeoRefRemarks", "GeoRefVerificationStatus", "NationalParkName", "VisibilitySetBy", "GeoRefDetByID", "Drainage", // TODO make sure this is right, meg added due to conversion non-mapping errors???? "Island", // TODO make sure this is right, meg added due to conversion non-mapping errors???? "IslandGroup", // TODO make sure this is right, meg added due to conversion non-mapping errors???? "WaterBody", // TODO make sure this is right, meg added due to conversion non-mapping errors???? "Version", "CreatedByAgentID", "CollectionMemberID", "ShortName", "DisciplineID", "GUID", "GML", "SrcLatLongUnit", "Visibility", "VisibilitySetByID", // Special String "LocalityName", "NamedPlace", "RelationToNamedPlace", "SGRStatus", "PaleoContextID", "Text3", "Text4", "Text5", "VerbatimLatitude", "VerbatimLongitude" }; setFieldsToIgnoreWhenMappingNames(fieldsToIgnore); errorsToShow &= ~BasicSQLUtils.SHOW_NULL_FK; // Turn off this error for LocalityID errorsToShow &= ~BasicSQLUtils.SHOW_NAME_MAPPING_ERROR; //Assuming new sp6 fields do not have sp5 equivalent setShowErrors(errorsToShow); TableWriter tblWriter = convLogger.getWriter("Locality.html", "Localities"); setTblWriter(tblWriter); IdHashMapper.setTblWriter(tblWriter); Map<String, String> mappedFields = createFieldNameMap( new String[] { "ModifiedByAgentID", "LastEditedBy", }); String sql = "SELECT locality.*,g.* FROM locality LEFT JOIN geography g on locality.GeographyID = g.GeographyID WHERE locality.GeographyID IS NOT NULL"; if (copyTable(oldDBConn, newDBConn, sql, "locality", "locality", mappedFields, null, BasicSQLUtils.mySourceServerType, BasicSQLUtils.myDestinationServerType)) { log.info("Locality/Geography copied ok."); } else { log.error("Copying locality/geography (fields) to new Locality"); } setFieldsToIgnoreWhenMappingNames(fieldsToIgnore); sql = "SELECT * FROM locality WHERE locality.GeographyID IS NULL"; if (copyTable(oldDBConn, newDBConn, sql, "locality", "locality", mappedFields, null, BasicSQLUtils.mySourceServerType, BasicSQLUtils.myDestinationServerType)) { log.info("Locality/Geography copied ok."); } else { log.error("Copying locality/geography (fields) to new Locality"); } frame.setProcess(0, BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM locality")); PreparedStatement pStmt = null; Statement stmt = null; sql = "SELECT LocalityID, LocalityName, NamedPlace, RelationToNamedPlace, Remarks FROM locality ORDER BY LocalityID"; try { IdMapperIFace locMapper = idMapperMgr.get("locality_LocalityID"); pStmt = newDBConn.prepareStatement( "UPDATE locality SET LocalityName=?, NamedPlace=?, RelationToNamedPlace=?, Remarks=? WHERE LocalityID=?"); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { int oldId = rs.getInt(1); Integer newId = locMapper.get(oldId); if (newId != null) { pStmt.setString(1, rs.getString(2)); pStmt.setString(2, rs.getString(3)); pStmt.setString(3, rs.getString(4)); pStmt.setString(4, rs.getString(5)); pStmt.setInt(5, newId); pStmt.execute(); /*if (!pStmt.execute()) { if () String msg = "Error Updating OldId ["+rs.getInt(1)+"] NewId ["+newId+"]"; log.error(msg); tblWriter.logErrors(Integer.toString(rs.getInt(1)), msg); }*/ } else { String msg = "No Mapping for OldId [" + rs.getInt(1) + "]"; log.error(msg); tblWriter.logErrors(Integer.toString(rs.getInt(1)), msg); } cnt++; if (cnt % 500 == 0) { frame.setProcess(cnt); } } frame.setProcess(cnt); } catch (Exception ex) { log.error(ex); tblWriter.logErrors("Exception", ex.toString()); } finally { try { if (stmt != null) stmt.close(); if (pStmt != null) pStmt.close(); } catch (SQLException ex) { } } convertLocalityExtraInfo("localitydetail", false); convertLocalityExtraInfo("geocoorddetail", true); setFieldsToIgnoreWhenMappingNames(null); setIdentityInsertOFFCommandForSQLServer(newDBConn, "locality", BasicSQLUtils.myDestinationServerType); setTblWriter(null); IdHashMapper.setTblWriter(null); }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the CollectionObject and CollectionObjectCatalog Records into the new schema * CollectionObject table. All "logical" records are moved to the CollectionObject table and all * "physical" records are moved to the Preparation table. * @return true if no errors/*from ww w.jav a 2 s . c o m*/ */ @SuppressWarnings("cast") public boolean convertCollectionObjects(final boolean useNumericCatNumbers, final boolean usePrefix) { final String ZEROES = "000000000"; UIFieldFormatterIFace formatter0 = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumber"); log.debug(formatter0); UIFieldFormatterIFace formatter = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumberNumeric"); log.debug(formatter); DisciplineType dt; Discipline discipline = (Discipline) AppContextMgr.getInstance().getClassObject(Discipline.class); if (discipline != null) { System.out.println("discipline.getType()[" + discipline.getType() + "]"); dt = DisciplineType.getDiscipline(discipline.getType()); } else { Vector<Object[]> list = query(newDBConn, "SELECT Type FROM discipline"); String typeStr = (String) list.get(0)[0]; System.out.println("typeStr[" + typeStr + "]"); dt = DisciplineType.getDiscipline(typeStr); } Pair<Integer, Boolean> objTypePair = dispToObjTypeHash.get(dt.getDisciplineType()); if (objTypePair == null) { System.out.println("objTypePair is null dt[" + dt.getName() + "][" + dt.getTitle() + "]"); for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) { Pair<Integer, Boolean> p = dispToObjTypeHash.get(key); System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]"); } } else if (objTypePair.first == null) { System.out.println("objTypePair.first is null dt[" + dt + "]"); for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) { Pair<Integer, Boolean> p = dispToObjTypeHash.get(key); System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]"); } } //int objTypeId = objTypePair.first; //boolean isEmbedded = objTypePair.second; idMapperMgr.dumpKeys(); IdHashMapper colObjTaxonMapper = (IdHashMapper) idMapperMgr.get("ColObjCatToTaxonType".toLowerCase()); IdHashMapper colObjAttrMapper = (IdHashMapper) idMapperMgr .get("biologicalobjectattributes_BiologicalObjectAttributesID"); IdHashMapper colObjMapper = (IdHashMapper) idMapperMgr .get("collectionobjectcatalog_CollectionObjectCatalogID"); colObjTaxonMapper.setShowLogErrors(false); // NOTE: TURN THIS ON FOR DEBUGGING or running new Databases through it colObjAttrMapper.setShowLogErrors(false); //IdHashMapper stratMapper = (IdHashMapper)idMapperMgr.get("stratigraphy_StratigraphyID"); //IdHashMapper stratGTPMapper = (IdHashMapper)idMapperMgr.get("stratigraphy_GeologicTimePeriodID"); String[] fieldsToSkip = { "ContainerID", "ContainerItemID", "AltCatalogNumber", "GUID", "ContainerOwnerID", "RepositoryAgreementID", "GroupPermittedToView", // this may change when converting Specify 5.x "CollectionObjectID", "VisibilitySetBy", "ContainerOwnerID", "InventoryDate", "ObjectCondition", "Notifications", "ProjectNumber", "Restrictions", "YesNo3", "YesNo4", "YesNo5", "YesNo6", "FieldNotebookPageID", "ColObjAttributesID", "DNASequenceID", "AppraisalID", "TotalValue", "Description", "SGRStatus", "OCR", "ReservedText", "Text3" }; HashSet<String> fieldsToSkipHash = new HashSet<String>(); for (String fName : fieldsToSkip) { fieldsToSkipHash.add(fName); } TableWriter tblWriter = convLogger.getWriter("convertCollectionObjects.html", "Collection Objects"); String msg = "colObjTaxonMapper: " + colObjTaxonMapper.size(); log.info(msg); tblWriter.log(msg); setIdentityInsertONCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); // automatically closes the connection TreeSet<String> badSubNumberCatNumsSet = new TreeSet<String>(); TimeLogger timeLogger = new TimeLogger(); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); StringBuilder str = new StringBuilder(); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("select "); List<String> names = getFieldNamesFromSchema(oldDBConn, "collectionobject"); sql.append(buildSelectFieldList(names, "collectionobject")); sql.append(", "); oldFieldNames.addAll(names); names = getFieldNamesFromSchema(oldDBConn, "collectionobjectcatalog"); sql.append(buildSelectFieldList(names, "collectionobjectcatalog")); oldFieldNames.addAll(names); String fromClause = " FROM collectionobject Inner Join collectionobjectcatalog ON " + "collectionobject.CollectionObjectID = collectionobjectcatalog.CollectionObjectCatalogID " + "WHERE (collectionobject.DerivedFromID IS NULL) AND collectionobjectcatalog.CollectionObjectCatalogID = "; sql.append(fromClause); log.info(sql); String sqlStr = sql.toString(); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "collectionobject"); log.info("Number of Fields in New CollectionObject " + newFieldMetaData.size()); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; log.info("---- Old Names ----"); for (String name : oldFieldNames) { log.info("[" + name + "][" + inx + "]"); oldNameIndex.put(name, inx++); } log.info("---- New Names ----"); for (FieldMetaData fmd : newFieldMetaData) { log.info("[" + fmd.getName() + "]"); } String tableName = "collectionobject"; Statement newStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); newStmt.setFetchSize(Integer.MIN_VALUE); ResultSet rsLooping = newStmt.executeQuery( "SELECT OldID, NewID FROM collectionobjectcatalog_CollectionObjectCatalogID ORDER BY OldID"); if (hasFrame) { if (rsLooping.last()) { setProcess(0, rsLooping.getRow()); rsLooping.first(); } else { rsLooping.close(); stmt.close(); return true; } } else { if (!rsLooping.first()) { rsLooping.close(); stmt.close(); return true; } } int boaCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM biologicalobjectattributes"); // ZZZ PartialDateConv partialDateConv = new PartialDateConv(); Statement stmt2 = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt2.setFetchSize(Integer.MIN_VALUE); int catNumInx = oldNameIndex.get("CatalogNumber"); int catDateInx = oldNameIndex.get("CatalogedDate"); int catSeriesIdInx = oldNameIndex.get("CatalogSeriesID"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); /*int grpPrmtViewInx = -1; Integer grpPrmtViewInxObj = oldNameIndex.get("GroupPermittedToView"); if (grpPrmtViewInxObj != null) { grpPrmtViewInx = grpPrmtViewInxObj + 1; }*/ Hashtable<Integer, CollectionInfo> oldCatSeriesIDToCollInfo = new Hashtable<Integer, CollectionInfo>(); for (CollectionInfo ci : collectionInfoShortList) { if (ci.getCatSeriesId() != null) { oldCatSeriesIDToCollInfo.put(ci.getCatSeriesId(), ci); } } String insertStmtStr = null; /*String catIdTaxIdStrBase = "SELECT cc.CollectionObjectCatalogID, cc.CatalogSeriesID, ct.TaxonomyTypeID " + "FROM collectionobjectcatalog AS cc " + "Inner Join collectionobject AS co ON cc.CollectionObjectCatalogID = co.CollectionObjectID " + "Inner Join collectiontaxonomytypes as ct ON co.CollectionObjectTypeID = ct.BiologicalObjectTypeID " + "where cc.CollectionObjectCatalogID = ";*/ int colObjAttrsNotMapped = 0; int count = 0; boolean skipRecord = false; do { String catSQL = sqlStr + rsLooping.getInt(1); //log.debug(catSQL); ResultSet rs = stmt.executeQuery(catSQL); if (!rs.next()) { log.error("Couldn't find CO with old id[" + rsLooping.getInt(1) + "] " + catSQL); continue; } partialDateConv.nullAll(); skipRecord = false; CollectionInfo collInfo = oldCatSeriesIDToCollInfo.get(rs.getInt(catSeriesIdInx)); /*String catIdTaxIdStr = catIdTaxIdStrBase + rs.getInt(1); //log.info(catIdTaxIdStr); ResultSet rs2 = stmt2.executeQuery(catIdTaxIdStr); if (!rs2.next()) { log.info("QUERY failed to return results:\n"+catIdTaxIdStr+"\n"); continue; } Integer catalogSeriesID = rs2.getInt(2); Integer taxonomyTypeID = rs2.getInt(3); Integer newCatSeriesId = collectionHash.get(catalogSeriesID + "_" + taxonomyTypeID); String prefix = prefixHash.get(catalogSeriesID + "_" + taxonomyTypeID); rs2.close(); if (newCatSeriesId == null) { msg = "Can't find " + catalogSeriesID + "_" + taxonomyTypeID; log.info(msg); tblWriter.logError(msg); continue; }*/ /*if (false) { String stratGTPIdStr = "SELECT co.CollectionObjectID, ce.CollectingEventID, s.StratigraphyID, g.GeologicTimePeriodID FROM collectionobject co " + "LEFT JOIN collectingevent ce ON co.CollectingEventID = ce.CollectingEventID " + "LEFT JOIN stratigraphy s ON ce.CollectingEventID = s.StratigraphyID " + "LEFT JOIN geologictimeperiod g ON s.GeologicTimePeriodID = g.GeologicTimePeriodID " + "WHERE co.CollectionObjectID = " + rs.getInt(1); log.info(stratGTPIdStr); rs2 = stmt2.executeQuery(stratGTPIdStr); Integer coId = null; Integer ceId = null; Integer stId = null; Integer gtpId = null; if (rs2.next()) { coId = rs2.getInt(1); ceId = rs2.getInt(2); stId = rs2.getInt(3); gtpId = rs2.getInt(4); } rs2.close(); }*/ String catalogNumber = null; String colObjId = null; str.setLength(0); if (insertStmtStr == null) { StringBuffer fieldList = new StringBuffer(); fieldList.append("( "); for (int i = 0; i < newFieldMetaData.size(); i++) { if ((i > 0) && (i < newFieldMetaData.size())) { fieldList.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName + " "); } fieldList.append(")"); insertStmtStr = "INSERT INTO collectionobject " + fieldList + " VALUES ("; } str.append(insertStmtStr); for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) { str.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); if (i == 0) { Integer oldColObjId = rs.getInt(1); Integer newColObjId = colObjMapper.get(oldColObjId); if (newColObjId == null) { msg = "Couldn't find new ColObj Id for old [" + oldColObjId + "]"; tblWriter.logError(msg); showError(msg); throw new RuntimeException(msg); } colObjId = getStrValue(newColObjId); if (contains(colObjId, '.')) { String msgStr = String.format("CatalogNumber '%d' contains a decimal point.", colObjId); log.debug(msgStr); tblWriter.logError(msgStr); skipRecord = true; break; } str.append(colObjId); if (useNumericCatNumbers) { catalogNumber = rs.getString(catNumInx); if (catalogNumber != null) { int catNumInt = (int) Math.abs(rs.getDouble(catNumInx)); catalogNumber = Integer.toString(catNumInt); if (catalogNumber.length() > 0 && catalogNumber.length() < ZEROES.length()) { catalogNumber = "\"" + ZEROES.substring(catalogNumber.length()) + catalogNumber + "\""; } else if (catalogNumber.length() > ZEROES.length()) { showError( "Catalog Number[" + catalogNumber + "] is too long for formatter of 9"); } } else { String mssg = "Empty catalog number."; log.debug(mssg); //showError(msg); tblWriter.logError(mssg); } } else { String prefix = collInfo.getCatSeriesPrefix(); float catNum = rs.getFloat(catNumInx); catalogNumber = "\"" + (usePrefix && isNotEmpty(prefix) ? (prefix + "-") : "") + String.format("%9.0f", catNum).trim() + "\""; } int subNumber = rs.getInt(oldNameIndex.get("SubNumber")); if (subNumber < 0 || rs.wasNull()) { badSubNumberCatNumsSet.add(catalogNumber); skipRecord = true; //msg = "Collection Object is being skipped because SubNumber is less than zero CatalogNumber["+ catalogNumber + "]"; //log.error(msg); //tblWriter.logError(msg); //showError(msg); break; } } else if (fieldsToSkipHash.contains(newFieldName)) { str.append("NULL"); } else if (newFieldName.equals("CollectionID")) // User/Security changes { str.append(collInfo.getCollectionId()); } else if (newFieldName.equals("Version")) // User/Security changes { str.append("0"); } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes { str.append(getCreatorAgentId(null)); } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes { String lastEditedByStr = rs.getString(lastEditedByInx); str.append(getModifiedByAgentId(lastEditedByStr)); } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes { str.append(collInfo.getCollectionId()); } else if (newFieldName.equals("PaleoContextID")) { str.append("NULL");// newCatSeriesId); } else if (newFieldName.equals("CollectionObjectAttributeID")) // User/Security changes { Object idObj = rs.getObject(1); if (idObj != null) { Integer coId = rs.getInt(1); Integer newId = colObjAttrMapper.get(coId); if (newId != null) { str.append(getStrValue(newId)); } else { if (boaCnt > 0) colObjAttrsNotMapped++; str.append("NULL"); } } else { str.append("NULL"); } } else if (newFieldName.equals("CatalogedDate")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(catDateInx), partialDateConv); } str.append(partialDateConv.getDateStr()); } else if (newFieldName.equals("CatalogedDatePrecision")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(catDateInx), partialDateConv); } str.append(partialDateConv.getPartial()); } else if (newFieldName.equals("CatalogedDateVerbatim")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(catDateInx), partialDateConv); } str.append(partialDateConv.getVerbatim()); } else if (newFieldName.equals("Availability")) { str.append("NULL"); } else if (newFieldName.equals("CatalogNumber")) { str.append(catalogNumber); } else if (newFieldName.equals("Visibility")) // User/Security changes { //str.append(grpPrmtViewInx > -1 ? rs.getObject(grpPrmtViewInx) : "NULL"); str.append("0"); } else if (newFieldName.equals("VisibilitySetByID")) // User/Security changes { str.append("NULL"); } else if (newFieldName.equals("CountAmt")) { Integer index = oldNameIndex.get("Count1"); if (index == null) { index = oldNameIndex.get("Count"); } Object countObj = rs.getObject(index); if (countObj != null) { str.append(getStrValue(countObj, newFieldMetaData.get(i).getType())); } else { str.append("NULL"); } } else { Integer index = oldNameIndex.get(newFieldName); Object data; if (index == null) { msg = "convertCollectionObjects - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // tblWriter.logError(msg); // showError(msg); data = null; // for (String key : oldNameIndex.keySet()) // { // log.info("["+key+"]["+oldNameIndex.get(key)+"]"); // } //stmt.close(); //throw new RuntimeException(msg); } else { data = rs.getObject(index); } if (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { IdMapperIFace idMapper = idMapperMgr.get(tableName, newFieldName); if (idMapper != null) { Integer origValue = rs.getInt(index); data = idMapper.get(origValue); if (data == null) { msg = "No value [" + origValue + "] in map [" + tableName + "][" + newFieldName + "]"; log.error(msg); tblWriter.logError(msg); //showError(msg); } } else { msg = "No Map for [" + tableName + "][" + newFieldName + "]"; log.error(msg); tblWriter.logError(msg); //showError(msg); } } } str.append(getStrValue(data, newFieldMetaData.get(i).getType())); } } if (!skipRecord) { str.append(")"); // log.info("\n"+str.toString()); if (hasFrame) { if (count % 500 == 0) { setProcess(count); } if (count % 5000 == 0) { log.info("CollectionObject Records: " + count); } } else { if (count % 2000 == 0) { log.info("CollectionObject Records: " + count); } } try { Statement updateStatement = newDBConn.createStatement(); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); } // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); //if (count < 50) System.err.println(str.toString()); updateStatement.executeUpdate(str.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } catch (SQLException e) { log.error("Count: " + count); log.error("Key: [" + colObjId + "][" + catalogNumber + "]"); log.error("SQL: " + str.toString()); e.printStackTrace(); log.error(e); showError(e.getMessage()); rs.close(); stmt.close(); throw new RuntimeException(e); } count++; } else { tblWriter.logError("Skipping - CatNo:" + catalogNumber); } // if (count > 10) break; rs.close(); } while (rsLooping.next()); /*if (boaCnt > 0) { msg = "CollectionObjectAttributes not mapped: " + colObjAttrsNotMapped + " out of "+boaCnt; log.info(msg); tblWriter.logError(msg); }*/ stmt2.close(); if (hasFrame) { setProcess(count); } else { log.info("Processed CollectionObject " + count + " records."); } tblWriter.log(String.format("Collection Objects Processing Time: %s", timeLogger.end())); tblWriter.log("Processed CollectionObject " + count + " records."); rsLooping.close(); newStmt.close(); stmt.close(); tblWriter.append( "<br><br><b>Catalog Numbers rejected because the SubNumber was NULL or less than Zero</b><br>"); tblWriter.startTable(); tblWriter.logHdr("Catalog Number"); for (String catNum : badSubNumberCatNumsSet) { tblWriter.log(catNum); } tblWriter.endTable(); } catch (SQLException e) { setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); e.printStackTrace(); log.error(e); tblWriter.logError(e.getMessage()); showError(e.getMessage()); throw new RuntimeException(e); } finally { tblWriter.close(); } setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); return true; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param treeDef/* w ww. ja v a 2 s . c om*/ * @throws SQLException */ public void convertLithoStratGeneral(final LithoStratTreeDef treeDef, final LithoStrat earth, final TableWriter tblWriter, final String srcTableName) 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; } IdMapperIFace gtpIdMapper = IdMapperMgr.getInstance().get("geologictimeperiod", "GeologicTimePeriodID"); IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID"); if (ceMapper == null) { ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null, false); } 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); Map<Integer, Pair<Integer, Integer>> stratHash = new HashMap<Integer, Pair<Integer, Integer>>(); 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 (oldGTPId != null) { gtpId = gtpIdMapper.get(oldGTPId); 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 Integer lithoStratID = null; 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) { lithoStratID = newStrat.getLithoStratId(); lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId()); } else { String msg = String.format("Strat Fields were all null for oldID", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } } if (lithoStratID != null || gtpId != null) { Integer newCEId = ceMapper.get(oldStratId); if (newCEId == null) { 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++; } else { stratHash.put(newCEId, new Pair<Integer, Integer>(gtpId, lithoStratID)); } } } 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(); 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 Pair<Integer, Integer> strat = stratHash.get(ceId); Integer newLithoId = null; Integer gtpId = null; if (strat != null) { gtpId = strat.getFirst(); newLithoId = strat.getSecond(); } if (newLithoId == null) { missingStrat++; missingStratIds.add(ceId); if (gtpId == 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
/** * @param treeDef/*from w w w .jav a2 s . c o m*/ * @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:net.sourceforge.squirrel_sql.plugins.sqlscript.table_script.CreateFileOfCurrentSQLCommand.java
/** * Create a {@link Statement} that will stream the result instead of loading into the memory. * @param connection the connection to use * @return A Statement, that will stream the result. * @throws SQLException // ww w.ja v a 2s.c om * @see http://javaquirks.blogspot.com/2007/12/mysql-streaming-result-set.html * @see http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html */ private Statement createStatementForStreamingResults(Connection connection) throws SQLException { Statement stmt; DialectType dialectType = DialectFactory.getDialectType(getSession().getMetaData()); if (DialectType.MYSQL5 == dialectType) { /* * MYSQL will load the whole result into memory. To avoid this, we must use the streaming mode. * * http://javaquirks.blogspot.com/2007/12/mysql-streaming-result-set.html * http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html */ stmt = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); } else { stmt = connection.createStatement(); } return stmt; }
From source file:nl.nn.adapterframework.jdbc.JdbcListener.java
protected Object getRawMessage(Connection conn, Map threadContext) throws ListenerException { boolean inTransaction = false; try {/*from ww w. j a v a2 s.c om*/ inTransaction = JtaUtil.inTransaction(); } catch (Exception e) { log.warn(getLogPrefix() + "could not determing XA transaction status, assuming not in XA transaction: " + e.getMessage()); inTransaction = false; } try { if (!inTransaction) { execute(conn, getStartLocalTransactionQuery()); } String query = preparedSelectQuery; try { Statement stmt = null; try { stmt = conn.createStatement(); stmt.setFetchSize(1); ResultSet rs = null; try { if (trace && log.isDebugEnabled()) log.debug("executing query for [" + query + "]"); rs = stmt.executeQuery(query); if (!rs.next()) { return null; } Object result; String key = rs.getString(getKeyField()); if (StringUtils.isNotEmpty(getMessageField())) { String message; if ("clob".equalsIgnoreCase(getMessageFieldType())) { message = JdbcUtil.getClobAsString(rs, getMessageField(), false); } else { if ("blob".equalsIgnoreCase(getMessageFieldType())) { message = JdbcUtil.getBlobAsString(rs, getMessageField(), getBlobCharset(), false, isBlobsCompressed(), isBlobSmartGet(), false); } else { message = rs.getString(getMessageField()); } } // log.debug("building wrapper for key ["+key+"], message ["+message+"]"); MessageWrapper mw = new MessageWrapper(); mw.setId(key); mw.setText(message); result = mw; } else { result = key; } return result; } finally { if (rs != null) { rs.close(); } } } finally { if (stmt != null) { stmt.close(); } } } catch (Exception e) { throw new ListenerException( getLogPrefix() + "caught exception retrieving message using query [" + query + "]", e); } } finally { if (!inTransaction) { execute(conn, getCommitLocalTransactionQuery()); } } }