List of usage examples for java.sql ResultSet first
boolean first() throws SQLException;
ResultSet object. From source file:com.commander4j.thread.AutoLabellerThread.java
public void run() { logger.debug("AutoLabeller Thread running"); setSessionID(JUnique.getUniqueID()); JDBUser user = new JDBUser(getHostID(), getSessionID()); user.setUserId("interface"); user.setPassword("interface"); user.setLoginPassword("interface"); Common.userList.addUser(getSessionID(), user); Common.sd.setData(getSessionID(), "silentExceptions", "Yes", true); Boolean dbconnected = false;//ww w .ja v a 2s . c o m if (Common.hostList.getHost(hostID).isConnected(sessionID) == false) { dbconnected = Common.hostList.getHost(hostID).connect(sessionID, hostID); } else { dbconnected = true; } if (dbconnected) { JDBViewAutoLabellerPrinter alp = new JDBViewAutoLabellerPrinter(getHostID(), getSessionID()); LinkedList<JDBViewAutoLabellerPrinter> autolabellerList = new LinkedList<JDBViewAutoLabellerPrinter>(); int noOfMessages = 0; while (true) { JWait.milliSec(500); if (allDone) { if (dbconnected) { Common.hostList.getHost(hostID).disconnect(getSessionID()); } return; } autolabellerList.clear(); autolabellerList = alp.getModifiedPrinterLines(); noOfMessages = autolabellerList.size(); if (noOfMessages > 0) { for (int x = 0; x < noOfMessages; x++) { JWait.milliSec(100); JDBViewAutoLabellerPrinter autolabview = autolabellerList.get(x); messageProcessedOK = true; messageError = ""; if (autolabview.getPrinterObj().isEnabled()) { logger.debug("Line =" + autolabview.getAutoLabellerObj().getLine()); logger.debug("Line Description =" + autolabview.getAutoLabellerObj().getDescription()); logger.debug("Printer ID =" + autolabview.getPrinterObj().getPrinterID()); logger.debug("Printer Enabled =" + autolabview.getPrinterObj().isEnabled()); logger.debug("Export Path =" + autolabview.getPrinterObj().getExportRealPath()); logger.debug("Export Enabled =" + autolabview.getPrinterObj().isExportEnabled()); logger.debug("Export Format =" + autolabview.getPrinterObj().getExportFormat()); logger.debug("Direct Print =" + autolabview.getPrinterObj().isDirectPrintEnabled()); logger.debug("Printer Type =" + autolabview.getPrinterObj().getPrinterType()); logger.debug("Printer IP =" + autolabview.getPrinterObj().getIPAddress()); logger.debug("Printer Port =" + autolabview.getPrinterObj().getPort()); logger.debug("Process Order =" + autolabview.getLabelDataObj().getProcessOrder()); logger.debug("Material =" + autolabview.getLabelDataObj().getMaterial()); logger.debug("Module ID =" + autolabview.getModuleObj().getModuleId()); logger.debug("Module Type =" + autolabview.getModuleObj().getType()); if (autolabview.getPrinterObj().isExportEnabled()) { String exportPath = JUtility.replaceNullStringwithBlank( JUtility.formatPath(autolabview.getPrinterObj().getExportRealPath())); if (exportPath.equals("") == false) { if (exportPath.substring(exportPath.length() - 1) .equals(File.separator) == false) { exportPath = exportPath + File.separator; } } else { exportPath = Common.interface_output_path + "Auto Labeller" + File.separator; } String exportFilename = exportPath + JUtility.removePathSeparators(autolabview.getAutoLabellerObj().getLine()) + "_" + JUtility.removePathSeparators(autolabview.getPrinterObj().getPrinterID()) + "." + autolabview.getPrinterObj().getExportFormat(); String exportFilenameTemp = exportFilename + ".out"; logger.debug("Export Filename =" + exportFilename); /* ================CSV================ */ if (autolabview.getPrinterObj().getExportFormat().equals("CSV")) { try { PreparedStatement stmt = null; ResultSet rs; String labelType = autolabview.getLabelDataObj().getLabelType(); stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement( Common.hostList.getHost(getHostID()).getSqlstatements() .getSQL("DBVIEW_AUTO_LABELLER_PRINTER.getProperties" + "_" + labelType)); stmt.setString(1, autolabview.getAutoLabellerObj().getLine()); stmt.setString(2, autolabview.getPrinterObj().getPrinterID()); stmt.setFetchSize(50); rs = stmt.executeQuery(); logger.debug("Writing CSV"); CSVWriter writer = new CSVWriter(new FileWriter(exportFilenameTemp), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END); writer.writeAll(rs, true); rs.close(); stmt.close(); writer.close(); File fromFile = new File(exportFilenameTemp); File toFile = new File(exportFilename); FileUtils.deleteQuietly(toFile); FileUtils.moveFile(fromFile, toFile); fromFile = null; toFile = null; } catch (Exception e) { messageProcessedOK = false; messageError = e.getMessage(); } } /* ================XML================ */ if (autolabview.getPrinterObj().getExportFormat().equals("XML")) { try { PreparedStatement stmt = null; ResultSet rs; stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()) .prepareStatement(Common.hostList.getHost(getHostID()) .getSqlstatements() .getSQL("DBVIEW_AUTO_LABELLER_PRINTER.getProperties")); stmt.setString(1, autolabview.getAutoLabellerObj().getLine()); stmt.setString(2, autolabview.getPrinterObj().getPrinterID()); stmt.setFetchSize(50); rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); int colCount = rsmd.getColumnCount(); DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Element message = (Element) document.createElement("message"); Element hostUniqueID = addElement(document, "hostRef", Common.hostList.getHost(getHostID()).getUniqueID()); message.appendChild(hostUniqueID); Element messageRef = addElement(document, "messageRef", autolabview.getAutoLabellerObj().getUniqueID()); message.appendChild(messageRef); Element messageType = addElement(document, "interfaceType", "Auto Labeller Data"); message.appendChild(messageType); Element messageInformation = addElement(document, "messageInformation", "Unique ID=" + autolabview.getAutoLabellerObj().getUniqueID()); message.appendChild(messageInformation); Element messageDirection = addElement(document, "interfaceDirection", "Output"); message.appendChild(messageDirection); Element messageDate = addElement(document, "messageDate", JUtility.getISOTimeStampStringFormat(JUtility.getSQLDateTime())); message.appendChild(messageDate); if (rs.first()) { Element labelData = (Element) document.createElement("LabelData"); Element row = document.createElement("Row"); labelData.appendChild(row); for (int i = 1; i <= colCount; i++) { String columnName = rsmd.getColumnName(i); Object value = rs.getObject(i); Element node = document.createElement(columnName); node.appendChild(document.createTextNode(value.toString())); row.appendChild(node); } message.appendChild(labelData); document.appendChild(message); JXMLDocument xmld = new JXMLDocument(); xmld.setDocument(document); // =============================== DOMImplementationLS DOMiLS = null; FileOutputStream FOS = null; // testing the support for DOM // Load and Save if ((document.getFeature("Core", "3.0") != null) && (document.getFeature("LS", "3.0") != null)) { DOMiLS = (DOMImplementationLS) (document.getImplementation()) .getFeature("LS", "3.0"); // get a LSOutput object LSOutput LSO = DOMiLS.createLSOutput(); FOS = new FileOutputStream(exportFilename); LSO.setByteStream((OutputStream) FOS); // get a LSSerializer object LSSerializer LSS = DOMiLS.createLSSerializer(); // do the serialization LSS.write(document, LSO); FOS.close(); } // =============================== } rs.close(); stmt.close(); } catch (Exception e) { messageError = e.getMessage(); } } if (autolabview.getPrinterObj().getExportFormat().equals("LQF")) { } } if (autolabview.getPrinterObj().isDirectPrintEnabled()) { } } if (messageProcessedOK == true) { autolabview.getAutoLabellerObj().setModified(false); autolabview.getAutoLabellerObj().update(); } else { logger.debug(messageError); } autolabview = null; } } } } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Looks up all the current Permit Types and uses them, instead of the usystable *///from w w w .j a v a 2s . co m @SuppressWarnings("unchecked") public void createPermitTypePickList() { /* * try { Statement stmt = oldDBConn.createStatement(); String sqlStr = "select count(Type) * from (select distinct Type from permit where Type is not null) as t"; * * log.info(sqlStr); * * boolean useField = false; ResultSet rs = stmt.executeQuery(sqlStr); } catch (SQLException * e) { e.printStackTrace(); log.error(e); } */ Session localSession = HibernateUtil.getCurrentSession(); PickList pl = new PickList(); pl.initialize(); Set<PickListItemIFace> items = pl.getItems(); try { pl.setName("Permit"); pl.setSizeLimit(-1); HibernateUtil.beginTransaction(); localSession.saveOrUpdate(pl); HibernateUtil.commitTransaction(); } catch (Exception ex) { log.error("******* " + ex); HibernateUtil.rollbackTransaction(); throw new RuntimeException("Couldn't create PickList for [Permit]"); } try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); String sqlStr = "select distinct Type from permit where Type is not null"; log.info(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); // check for no records which is OK if (!rs.first()) { return; } int count = 0; do { String typeStr = rs.getString(1); if (typeStr != null) { log.info("Permit Type[" + typeStr + "]"); PickListItem pli = new PickListItem(); pli.initialize(); pli.setTitle(typeStr); pli.setValue(typeStr); pli.setTimestampCreated(now); items.add(pli); pli.setPickList(pl); count++; } } while (rs.next()); log.info("Processed Permit Types " + count + " records."); HibernateUtil.beginTransaction(); localSession.saveOrUpdate(pl); HibernateUtil.commitTransaction(); } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts an old USYS table to a PickList. * @param usysTableName old table name/*w w w. ja v a 2 s . c om*/ * @param pickListName new pciklist name * @return true on success, false on failure */ @SuppressWarnings("unchecked") public boolean convertUSYSToPicklist(final Session localSession, final Collection collection, final String usysTableName, final String pickListName) { List<FieldMetaData> fieldMetaData = getFieldMetaDataFromSchema(oldDBConn, usysTableName); int ifaceInx = -1; int dataInx = -1; int fieldSetInx = -1; int i = 0; for (FieldMetaData md : fieldMetaData) { if (ifaceInx == -1 && md.getName().equals("InterfaceID")) { ifaceInx = i + 1; } else if (fieldSetInx == -1 && md.getName().equals("FieldSetSubTypeID")) { fieldSetInx = i + 1; } else if (dataInx == -1 && md.getType().toLowerCase().indexOf("varchar") > -1) { dataInx = i + 1; } i++; } if (ifaceInx == -1 || dataInx == -1 || fieldSetInx == -1) { throw new RuntimeException("Couldn't decypher USYS table ifaceInx[" + ifaceInx + "] dataInx[" + dataInx + "] fieldSetInx[" + fieldSetInx + "]"); } PickList pl = new PickList(); pl.initialize(); try { pl.setName(pickListName); if (pickListName.equals("PrepType")) { pl.setReadOnly(true); pl.setSizeLimit(-1); pl.setIsSystem(true); pl.setTableName("preptype"); pl.setType((byte) 1); } else { pl.setReadOnly(false); pl.setSizeLimit(-1); } pl.setCollection(collection); collection.getPickLists().add(pl); Transaction trans = localSession.beginTransaction(); localSession.saveOrUpdate(pl); localSession.saveOrUpdate(collection); trans.commit(); localSession.flush(); } catch (Exception ex) { log.error("******* " + ex); HibernateUtil.rollbackTransaction(); throw new RuntimeException("Couldn't create PickList for [" + usysTableName + "]"); } try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); String sqlStr = "select * from " + usysTableName + " where InterfaceID is not null"; log.info(sqlStr); boolean useField = false; ResultSet rs = stmt.executeQuery(sqlStr); // check for no records which is OK if (!rs.first()) { return true; } do { Object fieldObj = rs.getObject(fieldSetInx); if (fieldObj != null) { useField = true; break; } } while (rs.next()); Hashtable<String, String> values = new Hashtable<String, String>(); //log.info("Using FieldSetSubTypeID " + useField); rs.first(); int count = 0; do { if (!useField || rs.getObject(fieldSetInx) != null) { String val = rs.getString(dataInx); String lowerStr = val.toLowerCase(); if (values.get(lowerStr) == null) { //log.info("[" + val + "]"); pl.addItem(val, val); values.put(lowerStr, val); count++; } else { log.info("Discarding duplicate picklist value[" + val + "]"); } } } while (rs.next()); log.info("Processed " + usysTableName + " " + count + " records."); Transaction trans = localSession.beginTransaction(); localSession.saveOrUpdate(pl); trans.commit(); localSession.flush(); return true; } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Convert all the biological attributes to Collection Object Attributes. Each old record may * end up being multiple records in the new schema. This will first figure out which columns in * the old schema were used and only map those columns to the new database.<br> * <br>/*from w w w . ja v a 2s . c om*/ * It also will use the old name if there is not mapping for it. The old name is converted from * lower/upper case to be space separated where each part of the name starts with a capital * letter. * * @param discipline the Discipline * @param colToNameMap a mape for old names to new names * @param typeMap a map for changing the type of the data (meaning an old value may be a boolean * stored in a float) * @return true for success */ public boolean convertBiologicalAttrs(final Discipline discipline, @SuppressWarnings("unused") final Map<String, String> colToNameMap, final Map<String, Short> typeMap) { AttributeIFace.FieldType[] attrTypes = { AttributeIFace.FieldType.IntegerType, AttributeIFace.FieldType.FloatType, AttributeIFace.FieldType.DoubleType, AttributeIFace.FieldType.BooleanType, AttributeIFace.FieldType.StringType, // AttributeIFace.FieldType.MemoType }; Session localSession = HibernateUtil.getCurrentSession(); deleteAllRecordsFromTable(newDBConn, "collectionobjectattr", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "attributedef", BasicSQLUtils.myDestinationServerType); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); // grab the field and their type from the old schema List<FieldMetaData> oldFieldMetaData = new ArrayList<FieldMetaData>(); Map<String, FieldMetaData> oldFieldMetaDataMap = getFieldMetaDataFromSchemaHash(oldDBConn, "biologicalobjectattributes"); // create maps to figure which columns where used List<String> columnsInUse = new ArrayList<String>(); Map<String, AttributeDef> attrDefs = new Hashtable<String, AttributeDef>(); List<Integer> counts = new ArrayList<Integer>(); int totalCount = 0; for (FieldMetaData md : oldFieldMetaData) { // Skip these fields if (md.getName().indexOf("ID") == -1 && md.getName().indexOf("Timestamp") == -1 && md.getName().indexOf("LastEditedBy") == -1) { oldFieldMetaDataMap.put(md.getName(), md); // add to map for later // log.info(convertColumnName(md.getName())+" "+ md.getType()); String sqlStr = "select count(" + md.getName() + ") from biologicalobjectattributes where " + md.getName() + " is not null"; ResultSet rs = stmt.executeQuery(sqlStr); if (rs.first() && rs.getInt(1) > 0) { int rowCount = rs.getInt(1); totalCount += rowCount; counts.add(rowCount); log.info(md.getName() + " has " + rowCount + " rows of values"); columnsInUse.add(md.getName()); AttributeDef attrDef = new AttributeDef(); String newName = convertColumnName(md.getName()); attrDef.setFieldName(newName); log.debug("mapping[" + newName + "][" + md.getName() + "]"); // newNameToOldNameMap.put(newName, md.getName()); short dataType = -1; if (typeMap != null) { Short type = typeMap.get(md.getName()); if (type == null) { dataType = type; } } if (dataType == -1) { dataType = getDataType(md.getName(), md.getType()).getType(); } attrDef.setDataType(dataType); attrDef.setDiscipline(discipline); attrDef.setTableType(GenericDBConversion.TableType.CollectionObject.getType()); attrDef.setTimestampCreated(now); attrDefs.put(md.getName(), attrDef); try { HibernateUtil.beginTransaction(); localSession.save(attrDef); HibernateUtil.commitTransaction(); } catch (Exception e) { log.error("******* " + e); HibernateUtil.rollbackTransaction(); throw new RuntimeException(e); } } rs.close(); } } // for log.info("Total Number of Attrs: " + totalCount); // Now that we know which columns are being used we can start the conversion process log.info("biologicalobjectattributes columns in use: " + columnsInUse.size()); if (columnsInUse.size() > 0) { int inx = 0; StringBuilder str = new StringBuilder("select BiologicalObjectAttributesID"); for (String name : columnsInUse) { str.append(", "); str.append(name); inx++; } str.append(" from biologicalobjectattributes order by BiologicalObjectAttributesID"); log.info("sql: " + str.toString()); ResultSet rs = stmt.executeQuery(str.toString()); int[] countVerify = new int[counts.size()]; for (int i = 0; i < countVerify.length; i++) { countVerify[i] = 0; } boolean useHibernate = false; StringBuilder strBufInner = new StringBuilder(); int recordCount = 0; while (rs.next()) { if (useHibernate) { Criteria criteria = localSession.createCriteria(CollectionObject.class); criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); criteria.add(Restrictions.eq("collectionObjectId", rs.getInt(1))); List<?> list = criteria.list(); if (list.size() == 0) { log.error("**** Can't find the CollectionObject " + rs.getInt(1)); } else { CollectionObject colObj = (CollectionObject) list.get(0); inx = 2; // skip the first column (the ID) for (String name : columnsInUse) { AttributeDef attrDef = attrDefs.get(name); // the needed // AttributeDef by name FieldMetaData md = oldFieldMetaDataMap.get(name); // Create the new Collection Object Attribute CollectionObjectAttr colObjAttr = new CollectionObjectAttr(); colObjAttr.setCollectionObject(colObj); colObjAttr.setDefinition(attrDef); colObjAttr.setTimestampCreated(now); // String oldName = newNameToOldNameMap.get(attrDef.getFieldName()); // log.debug("["+attrDef.getFieldName()+"]["+oldName+"]"); // log.debug(inx+" "+attrTypes[attrDef.getDataType()]+" // "+md.getName()+" "+md.getType()); setData(rs, inx, attrTypes[attrDef.getDataType()], md, colObjAttr); HibernateUtil.beginTransaction(); localSession.save(colObjAttr); HibernateUtil.commitTransaction(); inx++; if (recordCount % 2000 == 0) { log.info("CollectionObjectAttr Records Processed: " + recordCount); } recordCount++; } // for // log.info("Done - CollectionObjectAttr Records Processed: // "+recordCount); } } else { inx = 2; // skip the first column (the ID) for (String name : columnsInUse) { AttributeDef attrDef = attrDefs.get(name); // the needed AttributeDef // by name FieldMetaData md = oldFieldMetaDataMap.get(name); if (rs.getObject(inx) != null) { Integer newRecId = (Integer) getMappedId(rs.getInt(1), "biologicalobjectattributes", "BiologicalObjectAttributesID"); Object data = getData(rs, inx, attrTypes[attrDef.getDataType()], md); boolean isStr = data instanceof String; countVerify[inx - 2]++; strBufInner.setLength(0); strBufInner.append("INSERT INTO collectionobjectattr VALUES ("); strBufInner.append("NULL");// Integer.toString(recordCount)); strBufInner.append(","); strBufInner.append(getStrValue(isStr ? data : null)); strBufInner.append(","); strBufInner.append(getStrValue(isStr ? null : data)); strBufInner.append(","); strBufInner.append(getStrValue(now)); strBufInner.append(","); strBufInner.append(getStrValue(now)); strBufInner.append(","); strBufInner.append(newRecId.intValue()); strBufInner.append(","); strBufInner.append(getStrValue(attrDef.getAttributeDefId())); strBufInner.append(")"); try { Statement updateStatement = newDBConn.createStatement(); // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); if (false) { log.debug(strBufInner.toString()); } updateStatement.executeUpdate(strBufInner.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } catch (SQLException e) { log.error(strBufInner.toString()); log.error("Count: " + recordCount); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } if (recordCount % 2000 == 0) { log.info("CollectionObjectAttr Records Processed: " + recordCount); } recordCount++; } inx++; } // for } // if } // while rs.close(); stmt.close(); log.info("Count Verification:"); for (int i = 0; i < counts.size(); i++) { log.info(columnsInUse.get(i) + " [" + counts.get(i) + "][" + countVerify[i] + "] " + (counts.get(i) - countVerify[i])); } } } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } 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 w w w. ja v a 2 s.c om*/ * @return true if no errors */ public boolean convertLoanRecords(final boolean doingGifts) { String newTableName = doingGifts ? "gift" : "loan"; setIdentityInsertONCommandForSQLServer(newDBConn, newTableName, BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, newTableName, BasicSQLUtils.myDestinationServerType); // automatically closes the connection if (getNumRecords(oldDBConn, "loan") == 0) { return true; } String[] ignoredFields = { "SpecialConditions", "AddressOfRecordID", "DateReceived", "ReceivedComments", "PurposeOfLoan", "OverdueNotiSetDate", "IsFinancialResponsibility", "Version", "CreatedByAgentID", "IsFinancialResponsibility", "SrcTaxonomy", "SrcGeography", "CollectionMemberID", "PurposeOfGift", "IsFinancialResponsibility", "SpecialConditions", "ReceivedComments", "AddressOfRecordID" }; Hashtable<String, Boolean> fieldToSkip = new Hashtable<String, Boolean>(); for (String nm : ignoredFields) { fieldToSkip.put(nm, true); } IdTableMapper loanIdMapper = (IdTableMapper) idMapperMgr.get(newTableName, doingGifts ? "GiftID" : "LoanID"); 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 = getFieldNamesFromSchema(oldDBConn, "loan"); StringBuilder sql = new StringBuilder("SELECT "); sql.append(buildSelectFieldList(oldFieldNames, "loan")); sql.append(" FROM loan WHERE loan.Category = "); sql.append(doingGifts ? "1" : "0"); sql.append(" ORDER BY loan.LoanID"); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, newTableName); log.info("Number of Fields in New " + newTableName + " " + newFieldMetaData.size()); String sqlStr = sql.toString(); if (doingGifts && loanIdMapper == null) { StringBuilder mapSQL = new StringBuilder("SELECT LoanID FROM loan WHERE loan.Category = "); mapSQL.append(doingGifts ? "1" : "0"); mapSQL.append(" ORDER BY loan.LoanID"); log.info(mapSQL.toString()); BasicSQLUtils.deleteAllRecordsFromTable(oldDBConn, "gift_GiftID", BasicSQLUtils.myDestinationServerType); loanIdMapper = new IdTableMapper(newTableName, "GiftID", mapSQL.toString(), false, false); idMapperMgr.addMapper(loanIdMapper); loanIdMapper.mapAllIdsWithSQL(); } Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); } Map<String, String> colNewToOldMap = doingGifts ? createFieldNameMap(new String[] { "GiftNumber", "LoanNumber", "GiftDate", "LoanDate", "IsCurrent", "Current", "IsClosed", "Closed" }) : createFieldNameMap(new String[] { "IsCurrent", "Current", "IsClosed", "Closed", }); 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; } } PartialDateConv partialDateConv = new PartialDateConv(); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); int count = 0; do { partialDateConv.nullAll(); str.setLength(0); 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(")"); str.append("INSERT INTO " + newTableName + " " + fieldList + " VALUES ("); 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(1); Integer newID = loanIdMapper.get(oldID); if (newID != null) { str.append(getStrValue(newID)); } else { log.error(newTableName + " Old/New ID problem [" + oldID + "][" + newID + "]"); } } 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 (fieldToSkip.get(newFieldName) != null) { str.append("NULL"); } else if (newFieldName.equals("DisciplineID")) // User/Security changes { str.append(curDisciplineID); } else if (newFieldName.equals("DivisionID")) // User/Security changes { str.append(curDivisionID); } 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 = "convertLoanRecords - 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 (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { IdMapperIFace idMapper = idMapperMgr.get("loan", oldMappedColName); if (idMapper != null) { data = idMapper.get(rs.getInt(index)); } else { log.error("No Map for [" + "loan" + "][" + oldMappedColName + "]"); } } } // hack for ??bug?? found in Sp5 that inserted null values in // timestampmodified field of determination table? BasicSQLUtils.fixTimestamps(newFieldName, newFieldMetaData.get(i).getType(), data, str); } } str.append(")"); if (hasFrame) { if (count % 500 == 0) { setProcess(count); } } else { if (count % 2000 == 0) { log.info("Loan/Gifts Records: " + count); } } try { //log.debug(str.toString()); Statement updateStatement = newDBConn.createStatement(); // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); updateStatement.executeUpdate(str.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } catch (SQLException e) { log.error("Count: " + count); log.error("Exception on insert: " + str.toString()); 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); } else { log.info("Processed Loan/Gift " + count + " records."); } rs.close(); stmt.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 LoanPhysicalObjects. * @return true if no errors/*from ww w. ja va2 s.c o 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 ww w . j a va2 s. com */ 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
/** * @param treeDef/*from w ww . ja va2 s .c o m*/ * @throws SQLException */ public void convertGeography(final GeographyTreeDef treeDef, final String dispName, final boolean firstTime) throws SQLException { TableWriter tblWriter = convLogger.getWriter("Geography" + (dispName != null ? dispName : "") + ".html", "Geography"); setTblWriter(tblWriter); IdHashMapper.setTblWriter(tblWriter); if (firstTime) { // empty out any pre-existing records deleteAllRecordsFromTable(newDBConn, "geography", BasicSQLUtils.myDestinationServerType); } IdTableMapper geoIdMapper = (IdTableMapper) IdMapperMgr.getInstance().get("geography", "GeographyID"); if (geoIdMapper == null) { // create an ID mapper for the geography table (mainly for use in converting localities) geoIdMapper = IdMapperMgr.getInstance().addTableMapper("geography", "GeographyID"); } else { geoIdMapper.clearRecords(); } Hashtable<Integer, Geography> oldIdToGeoMap = new Hashtable<Integer, Geography>(); // get a Hibernate session for saving the new records Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); // get all of the old records String sql = "SELECT GeographyID,ContinentOrOcean,Country,State,County,LastEditedBy FROM geography"; Statement statement = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); statement.setFetchSize(Integer.MIN_VALUE); ResultSet oldGeoRecords = statement.executeQuery(sql); fixGeography("ContinentOrOcean"); fixGeography("Country"); fixGeography("State"); fixGeography("County"); if (hasFrame) { if (oldGeoRecords.last()) { setProcess(0, oldGeoRecords.getRow()); oldGeoRecords.first(); } } else { oldGeoRecords.first(); } // setup the root Geography record (planet Earth) Geography planetEarth = new Geography(); planetEarth.initialize(); planetEarth.setName("Earth"); planetEarth.setCommonName("Earth"); planetEarth.setRankId(0); planetEarth.setDefinition(treeDef); for (GeographyTreeDefItem defItem : treeDef.getTreeDefItems()) { if (defItem.getRankId() == 0) { planetEarth.setDefinitionItem(defItem); break; } } GeographyTreeDefItem defItem = treeDef.getDefItemByRank(0); planetEarth.setDefinitionItem(defItem); int counter = 0; // for each old record, convert the record do { if (counter % 500 == 0) { if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " geography records"); } } // grab the important data fields from the old record int oldId = oldGeoRecords.getInt(1); String cont = fixSize(tblWriter, oldId, "continent", oldGeoRecords.getString(2), 64); String country = fixSize(tblWriter, oldId, "country", oldGeoRecords.getString(3), 64); String state = fixSize(tblWriter, oldId, "state", oldGeoRecords.getString(4), 64); String county = fixSize(tblWriter, oldId, "county", oldGeoRecords.getString(5), 64); String lastEditedBy = oldGeoRecords.getString(6); Integer agtId = getCreatorAgentId(lastEditedBy); Agent createdByAgent = getCreatedByAgent(localSession, agtId); Agent modifiedByAgent = getAgentObj(localSession, getCurAgentModifierID()); /*cont = isNotEmpty(county) && cont.equals("null") ? null : cont; country = isNotEmpty(country) && country.equals("null") ? null : country; state = isNotEmpty(state) && state.equals("null") ? null : state; county = isNotEmpty(county) && county.equals("null") ? null : county; */ if (isEmpty(cont) && isEmpty(country) && isEmpty(state) && isEmpty(county)) { //String msg = "For Record Id["+oldId+"] Continent, Country, State and County are all null."; //log.error(msg); //tblWriter.logError(msg); cont = "Undefined"; country = "Undefined"; state = "Undefined"; county = "Undefined"; } else if (isEmpty(cont) && isEmpty(country) && isEmpty(state)) { //String msg = "For Record Id["+oldId+"] Continent, Country and State are all null."; //log.error(msg); //tblWriter.logError(msg); cont = "Undefined"; country = "Undefined"; state = "Undefined"; } else if (isEmpty(cont) && isEmpty(country)) { //String msg = "For Record Id["+oldId+"] Country is null."; //log.error(msg); //tblWriter.logError(msg); cont = "Undefined"; country = "Undefined"; } else if (isEmpty(cont)) { //String msg = "For Record Id["+oldId+"] Country is null."; //log.error(msg); //tblWriter.logError(msg); cont = "Undefined"; } // create a new Geography object from the old data List<Geography> newGeos = convertOldGeoRecord(cont, country, state, county, createdByAgent, modifiedByAgent, planetEarth); if (newGeos.size() > 0) { Geography lowestLevel = newGeos.get(newGeos.size() - 1); oldIdToGeoMap.put(oldId, lowestLevel); } counter++; } while (oldGeoRecords.next()); if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " geography records"); } TreeHelper.fixFullnameForNodeAndDescendants(planetEarth); planetEarth.setNodeNumber(1); fixNodeNumbersFromRoot(planetEarth); localSession.save(planetEarth); HibernateUtil.commitTransaction(); log.info("Converted " + counter + " geography records"); if (shouldCreateMapTables) { // add all of the ID mappings for (Integer oldId : oldIdToGeoMap.keySet()) { Geography geo = oldIdToGeoMap.get(oldId); geoIdMapper.put(oldId, geo.getId()); } } if (firstTime) { // set up Geography foreign key mapping for locality idMapperMgr.mapForeignKey("Locality", "GeographyID", "Geography", "GeographyID"); } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the Determinations./*from w ww . j a v a 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 .ja v a 2s . co 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; }