List of usage examples for java.sql Statement clearBatch
void clearBatch() throws SQLException;
Statement object's current list of SQL commands. From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * //from ww w. ja v a2 s . com */ @SuppressWarnings("cast") public void convertDivision(final Integer institutionId) { try { strBuf.setLength(0); curDivisionID = getNextIndex(); Statement updateStatement = newDBConn.createStatement(); // Adding Institution strBuf.append( "INSERT INTO division (DivisionID, InstitutionID, TimestampModified, DisciplineType, Name, AltName, Abbrev, TimestampCreated, "); strBuf.append("CreatedByAgentID, ModifiedByAgentID, Version, UserGroupScopeId) VALUES ("); strBuf.append(curDivisionID + ","); strBuf.append(institutionId + ","); strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampModified strBuf.append("'" + disciplineType.getName() + "',"); strBuf.append("'" + disciplineType.getTitle() + "',"); strBuf.append("NULL,"); strBuf.append("'" + disciplineType.getAbbrev() + "',"); strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampCreated strBuf.append(getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + ",0, "); strBuf.append(curDivisionID); // UserGroupScopeID strBuf.append(")"); log.info(strBuf.toString()); updateStatement.executeUpdate(strBuf.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } catch (Exception ex) { ex.printStackTrace(); } Session cacheSession = DataBuilder.getSession(); DataBuilder.setSession(null); Session localSession = HibernateUtil.getNewSession(); List<?> list = (List<?>) localSession.createQuery("FROM Division WHERE id = " + curDivisionID).list(); division = (Division) list.get(0); localSession.close(); AppContextMgr.getInstance().setClassObject(Division.class, division); DataBuilder.setSession(cacheSession); }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
public Integer createInstitution(final String instName) { StorageTreeDef storageTreeDef = buildSampleStorageTreeDef(); try {//www .ja v a 2 s .c o m BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "institution", BasicSQLUtils.myDestinationServerType); Statement updateStatement = newDBConn.createStatement(); int institutionId = getNextIndex(); strBuf.setLength(0); // Adding Institution strBuf.append( "INSERT INTO institution (InstitutionID, IsServerBased, IsAccessionsGlobal, IsSingleGeographyTree, IsSharingLocalities, TimestampModified, Name, TimestampCreated, StorageTreeDefID, "); strBuf.append( "CreatedByAgentID, ModifiedByAgentID, Version, UserGroupScopeId, IsSecurityOn, Remarks) VALUES ("); strBuf.append(institutionId + ",FALSE,FALSE,FALSE,FALSE,"); strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampModified strBuf.append("'" + instName + "',"); strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampCreated strBuf.append(storageTreeDef.getStorageTreeDefId() + ","); // StorageTreeDefID strBuf.append(getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + ",0, "); strBuf.append(institutionId); // UserGroupScopeID strBuf.append(", 0"); // IsSecurityOn strBuf.append(", 'Sp5Converted'"); // Remarks strBuf.append(")"); log.info(strBuf.toString()); updateStatement.executeUpdate(strBuf.toString()); updateStatement.clearBatch(); updateStatement.close(); return institutionId; } catch (Exception ex) { ex.printStackTrace(); System.exit(0); } return null; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * //from w ww .j a va 2 s. c om */ public void createAndFillStatTable() { try { Statement stmtNew = newDBConn.createStatement(); String str = "DROP TABLE `webstats`"; try { stmtNew.executeUpdate(str); } catch (SQLException ex) { ex.printStackTrace(); } str = "CREATE TABLE `webstats` (`WebStatsID` int(11) NOT NULL default '0', " + "`UniqueVisitors1` int(11), " + "`UniqueVisitors2` int(11), " + "`UniqueVisitors3` int(11), " + "`UniqueVisitors4` int(11), " + "`UniqueVisitors5` int(11), " + "`UniqueVisitors6` int(11), " + "`UniqueVisitorsMon1` varchar(32), " + "`UniqueVisitorsMon2` varchar(32), " + "`UniqueVisitorsMon3` varchar(32), " + "`UniqueVisitorsMon4` varchar(32), " + "`UniqueVisitorsMon5` varchar(32), " + "`UniqueVisitorsMon6` varchar(32), " + "`UniqueVisitorsYear` varchar(32), " + "`Taxon1` varchar(32), " + "`TaxonCnt1` int(11), " + "`Taxon2` varchar(32), " + "`TaxonCnt2` int(11), " + "`Taxon3` varchar(32), " + "`TaxonCnt3` int(11), " + "`Taxon4` varchar(32), " + "`TaxonCnt4` int(11), " + "`Taxon5` varchar(32), " + "`TaxonCnt5` int(11), " + " PRIMARY KEY (`WebStatsID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1"; // log.info(str); stmtNew.executeUpdate(str); str = "INSERT INTO webstats VALUES (0, 234, 189, 211, 302, 229, 276, " + "'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'Apr', " + " 2621, " + "'Etheostoma', 54," + "'notatus', 39," + "'lutrensis', 22," + "'anomalum', 12," + "'platostomus', 8" + ")"; stmtNew.executeUpdate(str); stmtNew.clearBatch(); stmtNew.close(); } catch (SQLException ex) { ex.printStackTrace(); } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts Object Defs./*ww w .j av a 2 s .com*/ * @param specifyUserId * @return true on success, false on failure */ public boolean convertCollectionObjectTypes(final int specifyUserId) { try { HashSet<Integer> hashSet = new HashSet<Integer>(); StringBuilder inSB = new StringBuilder(); for (CollectionInfo ci : collectionInfoShortList) { if (!hashSet.contains(ci.getTaxonomyTypeId())) { if (inSB.length() > 0) inSB.append(','); inSB.append(ci.getTaxonomyTypeId()); hashSet.add(ci.getTaxonomyTypeId()); } } StringBuilder sb = new StringBuilder( "SELECT TaxonomyTypeID FROM taxonomytype WHERE TaxonomyTypeId in ("); sb.append(inSB); sb.append(')'); log.debug(sb.toString()); // This mapping is used by Discipline //for (Object txTypIdObj : BasicSQLUtils.querySingleCol(oldDBConn, sb.toString())) //{ // Integer txTypId = (Integer)txTypIdObj; // taxonomyTypeMapper.put(txTypId, getNextIndex()); //} // Create a Hashtable to track which IDs have been handled during the conversion process deleteAllRecordsFromTable(newDBConn, "datatype", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "discipline", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "collection", BasicSQLUtils.myDestinationServerType); // BasicSQLUtils.deleteAllRecordsFromTable(newDBConn, "collection_colobjdef"); Hashtable<Integer, Integer> newColObjIDTotaxonomyTypeID = new Hashtable<Integer, Integer>(); TableWriter tblWriter = convLogger.getWriter("convertCollectionObjectTypes.html", "Collection Object Type"); // Create a Hashed List of CollectionInfo for each unique TaxonomyTypeId // where the TaxonomyTypeId is a Discipline HashMap<Integer, Vector<CollectionInfo>> collDispHash = new HashMap<Integer, Vector<CollectionInfo>>(); for (CollectionInfo info : collectionInfoShortList) { Vector<CollectionInfo> colInfoList = collDispHash.get(info.getTaxonomyTypeId()); if (colInfoList == null) { colInfoList = new Vector<CollectionInfo>(); collDispHash.put(info.getTaxonomyTypeId(), colInfoList); } colInfoList.add(info); } String dateTimeNow = dateTimeFormatter.format(now); int collectionCnt = 0; for (Integer taxonTypeId : collDispHash.keySet()) { Vector<CollectionInfo> collInfoList = collDispHash.get(taxonTypeId); // Pick any of the CollectionInfo objects because they will // all share the same Discipline CollectionInfo info = null; for (CollectionInfo ci : collInfoList) { if (ci.getCatSeriesId() != null) { info = ci; break; } } if (info == null) { //UIRegistry.showError("No viable CatSeriesId to create Discipline. \n(Picking one...)"); info = collInfoList.get(0); //System.exit(0); } String taxonomyTypeName = info.getTaxonomyTypeName(); Integer taxonomyTypeID = info.getTaxonomyTypeId(); String lastEditedBy = null; String msg = "Creating a new Discipline for taxonomyTypeName[" + taxonomyTypeName + "] disciplineType[" + disciplineType.getTitle() + "]"; log.info(msg); tblWriter.log(msg); DisciplineType disciplineTypeObj = info.getDisciplineTypeObj(); if (disciplineTypeObj == null) { disciplineTypeObj = getStandardDisciplineName(info.getTaxonomyTypeName(), info.getColObjTypeName(), info.getCatSeriesName()); } taxonomyTypeName = disciplineTypeObj.getName(); // Figure out what type of standard data type this is from the // CollectionObjectTypeName setIdentityInsertOFFCommandForSQLServer(newDBConn, "datatype", BasicSQLUtils.myDestinationServerType); int dataTypeId = createDataType(); if (dataTypeId == -1) { msg = "**** Had to Skip record because of DataType mapping error[" + taxonomyTypeName + "]"; log.error(msg); tblWriter.logError(msg); System.exit(1); } String taxTypeName = info.getTaxonomyTypeName(); lastEditedBy = info.getCatSeriesLastEditedBy(); taxonomyTypeID = info.getTaxonomyTypeId(); //System.err.println(String.format("TaxonomyTypeName: %s taxonomyTypeID: %d", taxTypeName, taxonomyTypeID, info.get)); //--------------------------------------------------------------------------------- //-- Create Discipline //--------------------------------------------------------------------------------- //Integer newColObjDefID = getNextIndex();//taxonomyTypeMapper.get(taxonomyTypeID); //if (newColObjDefID == null) //{ // UIRegistry.showError("Was unable to map old TaxonomyTypeId["+taxonomyTypeID+"] to new ColectionObjectDefId. \nSeries Name: ["+info.getCatSeriesName()+"]\n(Exiting...)"); // //System.exit(0); //} // use the old CollectionObjectTypeName as the new Discipline name setIdentityInsertONCommandForSQLServer(newDBConn, "discipline", BasicSQLUtils.myDestinationServerType); Statement updateStatement = newDBConn.createStatement(); StringBuilder strBuf2 = new StringBuilder(); curDisciplineID = getNextIndex(); info.setDisciplineId(curDisciplineID); // adding DivisioniID strBuf2.setLength(0); strBuf2.append( "INSERT INTO discipline (DisciplineID, TimestampModified, Type, Name, TimestampCreated, "); strBuf2.append( "DataTypeID, GeographyTreeDefID, GeologicTimePeriodTreeDefID, TaxonTreeDefID, DivisionID, "); strBuf2.append( "CreatedByAgentID, ModifiedByAgentID, Version, UserGroupScopeId, PaleoContextChildTable, IsPaleoContextEmbedded) VALUES ("); strBuf2.append(info.getDisciplineId() + ","); strBuf2.append("'" + dateTimeNow + "',"); // TimestampModified strBuf2.append("'" + disciplineTypeObj.getName() + "',"); strBuf2.append("'" + disciplineTypeObj.getTitle() + "',"); strBuf2.append("'" + dateTimeNow + "',"); // TimestampCreated strBuf2.append(dataTypeId + ","); strBuf2.append("1,"); // GeographyTreeDefID strBuf2.append("1,"); // GeologicTimePeriodTreeDefID strBuf2.append("1,"); // TaxonTreeDefID strBuf2.append(division.getDivisionId() + ","); // DivisionID strBuf2.append(getCreatorAgentId(null) + "," + getModifiedByAgentIdForAgent(lastEditedBy) + ",0, "); strBuf2.append(curDisciplineID + ","); // UserGroupScopeId strBuf2.append("'collectionobject', true)"); //PaleoContextChildTable, IsPaleoContextEmbedded // strBuf2.append("NULL)");// UserPermissionID//User/Security changes log.info(strBuf2.toString()); removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); updateStatement.executeUpdate(strBuf2.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; setIdentityInsertOFFCommandForSQLServer(newDBConn, "discipline", BasicSQLUtils.myDestinationServerType); //Integer disciplineID = getHighestId(newDBConn, "DisciplineID", "discipline"); newColObjIDTotaxonomyTypeID.put(curDisciplineID, taxonomyTypeID); msg = "**** Created new discipline[" + taxonomyTypeName + "] is dataType [" + dataTypeId + "]"; log.info(msg); tblWriter.log(msg); Session localSession = HibernateUtil.getNewSession(); for (CollectionInfo collInfo : collInfoList) { Integer catalogSeriesID = collInfo.getCatSeriesId(); String seriesName = collInfo.getCatSeriesName(); String prefix = collInfo.getCatSeriesPrefix(); String remarks = collInfo.getCatSeriesRemarks(); collInfo.setDisciplineId(curDisciplineID); AutoNumberingScheme cns = null; if (catalogSeriesID != null && isNotEmpty(seriesName)) { cns = catSeriesToAutoNumSchemeHash.get(catalogSeriesID); if (cns == null) { try { cns = new AutoNumberingScheme(); cns.initialize(); cns.setIsNumericOnly(true); cns.setSchemeClassName(""); cns.setSchemeName(seriesName); cns.setTableNumber(CollectionObject.getClassTableId()); Transaction trans = localSession.beginTransaction(); localSession.save(cns); trans.commit(); catSeriesToAutoNumSchemeHash.put(catalogSeriesID, cns); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } } } else { seriesName = taxTypeName; } Integer catNumSchemeId = cns != null ? cns.getAutoNumberingSchemeId() : null; collInfo.setCollectionId(getNextIndex()); curCollectionID = collInfo.getCollectionId(); msg = "**** Created new Collection [" + seriesName + "] is curCollectionID [" + curCollectionID + "]"; log.info(msg); updateStatement = newDBConn.createStatement(); strBuf.setLength(0); strBuf.append( "INSERT INTO collection (CollectionID, DisciplineID, CollectionName, Code, Remarks, CatalogFormatNumName, "); strBuf.append( "IsEmbeddedCollectingEvent, TimestampCreated, TimestampModified, CreatedByAgentID, ModifiedByAgentID, "); strBuf.append("Version, UserGroupScopeId) VALUES ("); strBuf.append(curCollectionID + ","); strBuf.append(curDisciplineID + ","); strBuf.append(getStrValue(seriesName) + ","); strBuf.append(getStrValue(prefix) + ","); strBuf.append(getStrValue(remarks) + ","); strBuf.append("'CatalogNumberNumeric',"); strBuf.append((isSharingCollectingEvents ? 0 : 1) + ","); strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampModified strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampCreated strBuf.append(getCreatorAgentId(null) + "," + getModifiedByAgentId(lastEditedBy) + ", 0, "); strBuf.append(curCollectionID); // UserGroupScopeId strBuf.append(")"); log.debug(strBuf.toString()); updateStatement.executeUpdate(strBuf.toString()); //curCollectionID = getInsertedId(updateStatement); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; if (catNumSchemeId != null && catalogSeriesID != null) { joinCollectionAndAutoNum(curCollectionID, catNumSchemeId); String hashKey = catalogSeriesID + "_" + taxonomyTypeID; Integer newCatSeriesID = getHighestId(newDBConn, "CollectionID", "collection"); collectionHash.put(hashKey, newCatSeriesID); if (isNotEmpty(prefix)) { prefixHash.put(hashKey, prefix); } msg = "Collection New[" + newCatSeriesID + "] [" + seriesName + "] [" + prefix + "] curDisciplineID[" + curDisciplineID + "]"; } else { msg = "Collection New[" + seriesName + "] [" + prefix + "] curDisciplineID[" + curDisciplineID + "]"; } log.info(msg); tblWriter.log(msg); //recordCnt++; //msg = "Collection Join Records: " + recordCnt; //log.info(msg); //tblWriter.log(msg); //rs.close(); //stmt.close(); collectionCnt++; } // Collection for loop localSession.close(); } // for loop tblWriter.close(); for (CollectionInfo ci : collectionInfoShortList) { if (ci.getCatSeriesId() != null) { log.debug("Cat Series: " + ci.getCatSeriesId() + " " + ci.getCollectionId()); Vector<Integer> colList = catSeriesToNewCollectionID.get(ci.getCatSeriesId()); if (colList == null) { colList = new Vector<Integer>(); catSeriesToNewCollectionID.put(ci.getCatSeriesId(), colList); } colList.add(ci.getCollectionId()); } } for (Integer catSeriesId : catSeriesToNewCollectionID.keySet()) { Vector<Integer> colList = catSeriesToNewCollectionID.get(catSeriesId); if (colList.size() > 1) { UIRegistry.showError( "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now."); return false; } } return true; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.toString()); System.exit(0); } return false; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param tableName/*from w ww. j a v a 2 s . c o m*/ */ protected void convertLocalityExtraInfo(final String tableName, final boolean isGeoCoordDetail) { removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); String capName = capitalize(tableName); TableWriter tblWriter = convLogger.getWriter(capName + ".html", capName); setTblWriter(tblWriter); IdHashMapper.setTblWriter(tblWriter); setDesc("Converting " + capName); List<String> localityDetailNamesTmp = getFieldNamesFromSchema(newDBConn, tableName); List<String> localityDetailNames = new ArrayList<String>(); Hashtable<String, Boolean> nameHash = new Hashtable<String, Boolean>(); for (String fieldName : localityDetailNamesTmp) { localityDetailNames.add(fieldName); nameHash.put(fieldName, true); System.out.println("[" + fieldName + "]"); } String fieldList = buildSelectFieldList(localityDetailNames, null); log.info(fieldList); IdMapperIFace locIdMapper = idMapperMgr.get("locality", "LocalityID"); IdMapperIFace agtIdMapper = idMapperMgr.get("agent", "AgentID"); Statement updateStatement = null; try { updateStatement = newDBConn.createStatement(); Hashtable<String, Boolean> usedFieldHash = new Hashtable<String, Boolean>(); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); Integer countRows = getCount( "select count(LocalityID) from locality,geography where locality.GeographyID = geography.GeographyID"); if (countRows != null) { frame.setProcess(0, countRows); } ResultSet rs = stmt.executeQuery( "select locality.*,geography.* from locality LEFT JOIN geography on locality.GeographyID = geography.GeographyID "); StringBuilder colSQL = new StringBuilder(); StringBuilder valuesSQL = new StringBuilder(); int rows = 0; while (rs.next()) { usedFieldHash.clear(); valuesSQL.setLength(0); boolean hasData = false; ResultSetMetaData metaData = rs.getMetaData(); int cols = metaData.getColumnCount(); for (int i = 1; i <= cols; i++) { String colName = metaData.getColumnName(i); // Old Column Name if (colName.equals("GeoRefDetBy")) { colName = "AgentID"; } if ((nameHash.get(colName) == null || usedFieldHash.get(colName) != null) && !colName.startsWith("Range")) { if (rows == 0) { log.debug("Skipping[" + colName + "]"); } continue; } usedFieldHash.put(colName, true); if (rows == 0) { System.err.println("[" + colName + "]"); if (colName.equals("Range")) { if (!isGeoCoordDetail) { if (colSQL.length() > 0) colSQL.append(","); colSQL.append("RangeDesc"); } } else if (isGeoCoordDetail) { if (!colName.equals("RangeDirection")) { if (colSQL.length() > 0) colSQL.append(","); colSQL.append(colName); } } else { if (colSQL.length() > 0) colSQL.append(","); colSQL.append(colName); } } String value; if (colName.equals("LocalityID")) { Integer oldId = rs.getInt(i); Integer newId = locIdMapper.get(oldId); if (newId != null) { value = Integer.toString(newId); } else { String msg = "Couldn't map LocalityId oldId[" + rs.getInt(i) + "]"; log.error(msg); tblWriter.logError(msg); value = "NULL"; } } else if (isGeoCoordDetail && colName.equals("GeoRefDetDate")) { Integer dateInt = rs.getInt(i); value = getStrValue(dateInt, "date"); } else if (colName.startsWith("YesNo")) { Integer bool = rs.getInt(i); if (bool == null) { value = "NULL"; } else if (bool == 0) { value = "0"; } else { value = "1"; } } else if (isGeoCoordDetail && colName.equals("AgentID")) { Integer agentID = (Integer) rs.getObject(i); if (agentID != null) { Integer newID = agtIdMapper.get(agentID); if (newID != null) { value = newID.toString(); } else { String msg = "Couldn't map GeoRefDetBY (Agent) oldId[" + agentID + "]"; log.error(msg); tblWriter.logError(msg); value = "NULL"; } } else { value = "NULL"; } } else if (colName.equals("Range") || colName.equals("RangeDirection")) { if (!isGeoCoordDetail) { String range = rs.getString(i); range = escapeStringLiterals(range); if (range != null) { hasData = true; value = "'" + range + "'"; } else { value = "NULL"; } } else { value = null; } } else { Object obj = rs.getObject(i); if (obj != null && !colName.equals("TimestampCreated") && !colName.equals("TimestampModified")) { hasData = true; } /*if (obj instanceof String) { String str = (String)obj; int inx = str.indexOf('\''); if (inx > -1) { obj = escapeStringLiterals(str); } }*/ value = getStrValue(obj); } // log.debug(colName+" ["+value+"]"); if (value != null) { if (valuesSQL.length() > 0) { valuesSQL.append(","); } valuesSQL.append(value); } } if (hasData) { String insertSQL = "INSERT INTO " + tableName + " (" + colSQL.toString() + ", Version, CreatedByAgentID, ModifiedByAgentID) " + " VALUES(" + valuesSQL.toString() + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + ")"; /*if (true) { log.info(insertSQL); }*/ try { updateStatement.executeUpdate(insertSQL); updateStatement.clearBatch(); } catch (Exception ex) { System.out.println("isGeoCoordDetail: " + isGeoCoordDetail); System.out.println(insertSQL); ex.printStackTrace(); } } rows++; if (rows % 500 == 0) { frame.setProcess(rows); } } rs.close(); stmt.close(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { updateStatement.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeRoleFromUser(String userName, List<String> roles) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;/*from w w w. j av a 2s . c o m*/ ; Statement ps = null; boolean success = false; userName = userName.toLowerCase(); List<String> roleLowerCase = new ArrayList<String>(roles.size()); for (String role : roles) { roleLowerCase.add(role.toLowerCase()); } roles = roleLowerCase; try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke role to user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke role to user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.createStatement(); String sql = "select user_name from tdwuser where user_name='" + userName + "'"; boolean isPrivFind = false; ResultSet userSet = ps.executeQuery(sql); while (userSet.next()) { isPrivFind = true; break; } userSet.close(); if (!isPrivFind) { throw new NoSuchObjectException("can not find user:" + userName); } for (String role : roles) { sql = "select role_name from tdwrole where role_name='" + role + "'"; boolean isRoleFind = false; ResultSet roleTempSet = ps.executeQuery(sql); while (roleTempSet.next()) { isRoleFind = true; } roleTempSet.close(); if (!isRoleFind) { throw new InvalidObjectException("Role does not exist: " + role); } } for (String role : roles) { ps.addBatch( "delete from tdwuserrole where user_name='" + userName + "' and role_name='" + role + "'"); } ps.executeBatch(); ps.clearBatch(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("revoke role from user error , user=" + userName + ", msg=" + ex.getMessage()); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * @param fromConn/*w ww. j av a2 s . co m*/ * @param toConn * @param sql * @param fromTableName * @param toTableName * @param colNewToOldMap * @param verbatimDateMapper * @param newColDefValues * @param sourceServerType * @param destServerType * @return */ public static boolean copyTable(final Connection fromConn, final Connection toConn, final String sql, final String countSQL, final String fromTableName, final String toTableName, final Map<String, String> colNewToOldMap, final Map<String, String> verbatimDateMapper, final Map<String, String> newColDefValues, final SERVERTYPE sourceServerType, final SERVERTYPE destServerType) { //Timestamp now = new Timestamp(System.currentTimeMillis()); IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); if (frame != null) { frame.setDesc("Copying Table " + fromTableName); } log.info("Copying Table " + fromTableName); List<String> fromFieldNameList = getFieldNamesFromSchema(fromConn, fromTableName); String sqlStr = sql + " ORDER BY " + fromTableName + "." + fromFieldNameList.get(0); log.debug(sqlStr); int numRecs; if (countSQL == null) { numRecs = getNumRecords(fromConn, fromTableName); } else { numRecs = getCountAsInt(fromConn, countSQL); } setProcess(0, numRecs); DBTableInfo tblInfo = DBTableIdMgr.getInstance().getInfoByTableName(toTableName); Statement updateStatement = null; String id = ""; try { updateStatement = toConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { BasicSQLUtils.removeForeignKeyConstraints(toConn, BasicSQLUtils.myDestinationServerType); } //HashMap<String, Integer> newDBFieldHash = new HashMap<String, Integer>(); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(toConn, toTableName); //int inx = 1; //for (FieldMetaData fmd : newFieldMetaData) //{ // newDBFieldHash.put(fmd.getName(), inx++); //} Statement stmt = fromConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //System.out.println(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); ResultSetMetaData rsmd = rs.getMetaData(); Vector<Integer> dateColumns = new Vector<Integer>(); //System.out.println(toTableName); Hashtable<String, Integer> fromHash = new Hashtable<String, Integer>(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { String colName = rsmd.getColumnName(i); fromHash.put(colName, i); //System.out.println(rsmd.getColumnName(i)+" -> "+i); if (rsmd.getColumnType(i) == java.sql.Types.DATE || colName.toLowerCase().endsWith("date") || colName.toLowerCase().startsWith("date")) { //System.out.println("Date: "+rsmd.getColumnName(i)+" -> "+i); dateColumns.add(i); } } Hashtable<String, String> oldNameToNewNameHash = new Hashtable<String, String>(); if (colNewToOldMap != null) { for (String newName : colNewToOldMap.keySet()) { String oldName = colNewToOldMap.get(newName); System.out .println("Mapping oldName[" + (oldName == null ? newName : oldName) + " -> " + newName); oldNameToNewNameHash.put(oldName == null ? newName : oldName, newName); } } // System.out.println("Num Cols: "+rsmd.getColumnCount()); Map<String, PartialDateConv> dateMap = new Hashtable<String, PartialDateConv>(); String insertSQL = null; // Get the columns that have dates in case we get a TimestampCreated date that is null // and then we can go looking for an older date to try to figure it out Integer timestampModifiedInx = fromHash.get("TimestampModified"); Integer timestampCreatedInx = fromHash.get("TimestampCreated"); boolean isAccessionTable = fromTableName.equals("accession"); boolean hasInstIdCol = fromTableName.equals("permit") || fromTableName.equals("journal") || fromTableName.equals("referencework"); StringBuffer str = new StringBuffer(1024); int count = 0; while (rs.next()) { boolean skipRecord = false; dateMap.clear(); // Start by going through the resultset and converting all dates from Integers // to real dates and keep the verbatium date information if it is a partial date for (int i : dateColumns) { String oldColName = rsmd.getColumnName(i); Integer oldColIndex = fromHash.get(oldColName); if (oldColIndex == null) { log.error("Couldn't find new column for old column for date for Table[" + fromTableName + "] Col Name[" + newFieldMetaData.get(i).getName() + "]"); continue; } if (oldColIndex > newFieldMetaData.size()) { continue; } String newColName = colNewToOldMap != null ? oldNameToNewNameHash.get(oldColName) : null; if (newColName == null) { newColName = oldColName; } Object dataObj = rs.getObject(i); if (dataObj instanceof Integer) { PartialDateConv datep = new PartialDateConv(); getPartialDate((Integer) dataObj, datep); // fills in Verbatim also dateMap.put(newColName, datep); } } // OK here we make sure that both the created dated ad modified date are not null // and we copy the date if one has a value and the other does not. Date timestampCreatedCached = now; Date timestampModifiedCached = now; if (timestampModifiedInx != null && timestampCreatedInx != null) { timestampModifiedCached = rs.getDate(timestampModifiedInx); timestampCreatedCached = rs.getDate(timestampCreatedInx); if (timestampModifiedCached == null && timestampCreatedCached == null) { timestampCreatedCached = Calendar.getInstance().getTime(); timestampModifiedCached = Calendar.getInstance().getTime(); } else if (timestampModifiedCached == null && timestampCreatedCached != null) { timestampModifiedCached = new Date(timestampCreatedCached.getTime()); } else { timestampCreatedCached = timestampModifiedCached != null ? new Date(timestampModifiedCached.getTime()) : new Date(); } } else { if (timestampModifiedInx != null) { timestampModifiedCached = rs.getDate(timestampModifiedInx); if (timestampModifiedCached == null) { timestampModifiedCached = now; } } if (timestampCreatedInx != null) { timestampCreatedCached = rs.getDate(timestampCreatedInx); if (timestampCreatedCached == null) { timestampCreatedCached = now; } } } str.setLength(0); if (insertSQL == 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(")"); str.append("INSERT INTO " + toTableName + " " + fieldList + " VALUES ("); insertSQL = str.toString(); log.debug(str); } else { str.append(insertSQL); } id = rs.getString(1); // For each column in the new DB table... for (int i = 0; i < newFieldMetaData.size(); i++) { FieldMetaData newFldMetaData = newFieldMetaData.get(i); String newColName = newFldMetaData.getName(); String oldMappedColName = null; //System.out.println("["+newColName+"]"); // Get the Old Column Index from the New Name // String oldName = colNewToOldMap != null ? colNewToOldMap.get(newColName) : newColName; Integer columnIndex = fromHash.get(newColName); if (columnIndex == null && colNewToOldMap != null) { oldMappedColName = colNewToOldMap.get(newColName); if (oldMappedColName != null) { columnIndex = fromHash.get(oldMappedColName); } else if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null || ignoreMappingFieldNames.get(newColName) == null)) { String msg = "No Map for table [" + fromTableName + "] from New Name[" + newColName + "] to Old Name[" + oldMappedColName + "]"; log.error(msg); writeErrLog(msg); } } else { oldMappedColName = newColName; } String verbatimDateFieldName = null; if (verbatimDateMapper != null) { verbatimDateFieldName = verbatimDateMapper.get(newColName); } //System.out.println("new["+newColName+"] old["+oldMappedColName+"]"); if (columnIndex != null) { if (i > 0) str.append(", "); Object dataObj = rs.getObject(columnIndex); if (idMapperMgr != null && oldMappedColName != null && oldMappedColName.endsWith("ID")) { IdMapperIFace idMapper = idMapperMgr.get(fromTableName, oldMappedColName); if (idMapper != null) { int showNullOption = SHOW_NULL_FK; int showFkLookUpOption = SHOW_FK_LOOKUP; int oldPrimaryKeyId = rs.getInt(columnIndex); if (oldMappedColName.equalsIgnoreCase(fromTableName + "id")) { showNullOption = SHOW_NULL_PM; showFkLookUpOption = SHOW_PM_LOOKUP; } // if the value was null, getInt() returns 0 // use wasNull() to distinguish real 0 from a null return if (rs.wasNull()) { dataObj = null; if (isOptionOn(showNullOption)) { String msg = "Unable to Map " + (showNullOption == SHOW_NULL_FK ? "Foreign" : "Primary") + " Key Id[NULL] old Name[" + oldMappedColName + "] colInx[" + columnIndex + "] newColName[" + newColName + "]"; log.error(msg); writeErrLog(msg); skipRecord = true; } } else { dataObj = idMapper.get(oldPrimaryKeyId); if (dataObj == null && isOptionOn(showFkLookUpOption)) { String msg = "Unable to Map Primary Id[" + oldPrimaryKeyId + "] old Name[" + oldMappedColName + "] table[" + fromTableName + "]"; log.error(msg); writeErrLog(msg); skipRecord = true; } } } else { if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldIDs == null || ignoreMappingFieldIDs.get(oldMappedColName) == null)) { // !!!!!!!!!!!!!!!!!!!!!!!!!!!!! // XXX Temporary fix so it doesn't hide other errors // Josh has promised his first born if he doesn't fix this! // !!!!!!!!!!!!!!!!!!!!!!!!!!!!! if (!oldMappedColName.equals("RankID")) { //idMapperMgr.dumpKeys(); String msg = "No ID Map for [" + fromTableName + "] Old Column Name[" + oldMappedColName + "]"; log.error(msg); writeErrLog(msg); skipRecord = true; } } } } // First check to see if it is null if (dataObj == null) { if (newFldMetaData.getName().equals("TimestampCreated")) { if (timestampCreatedInx != null) { if (isAccessionTable) { Date date = UIHelper .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned"))); str.append(date != null ? getStrValue(date) : getStrValue(timestampCreatedCached, newFldMetaData.getType())); } else { str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType())); } } else { str.append(getStrValue(timestampCreatedCached, newFldMetaData.getType())); } } else if (newFldMetaData.getName().equals("TimestampModified")) { if (timestampModifiedInx != null) { if (isAccessionTable) { Date date = UIHelper .convertIntToDate(rs.getInt(fromHash.get("DateAccessioned"))); str.append(date != null ? getStrValue(date) : getStrValue(timestampCreatedCached, newFldMetaData.getType())); } else { str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType())); } } else { str.append(getStrValue(timestampModifiedCached, newFldMetaData.getType())); } } else { str.append("NULL"); } } else if (dataObj instanceof Integer && (newFldMetaData.getSqlType() == java.sql.Types.DATE || newColName.toLowerCase().endsWith("date") || newColName.toLowerCase().startsWith("date"))) { PartialDateConv datePr = dateMap.get(newColName); if (datePr != null) { str.append(datePr.getDateStr()); } else { str.append("NULL"); } } else if (verbatimDateFieldName != null) { PartialDateConv datePr = dateMap.get(newColName); str.append(datePr != null ? datePr.getVerbatim() : "NULL"); } else if (dataObj instanceof Number) { DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName); String type = newFldMetaData.getType().toLowerCase().startsWith("tiny") ? fi.getType() : newFldMetaData.getType(); str.append(getStrValue(dataObj, type)); } else { if (columnValueMapper != null) { BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newColName); if (valueMapper != null) { dataObj = valueMapper.mapValue(dataObj); } } if (dataObj instanceof String && newFldMetaData.isString()) { DBFieldInfo fi = tblInfo.getFieldByColumnName(newColName); String s = (String) dataObj; if (s.length() > fi.getLength()) { String msg = String.format( "Truncating Table '%s' Field '%s' with Length %d, db len %d Value[%s]", toTableName, newColName, s.length(), fi.getLength(), s); tblWriter.logError(msg); log.error(msg); dataObj = s.substring(0, fi.getLength()); } } str.append(getStrValue(dataObj, newFldMetaData.getType())); } } else if (hasInstIdCol && newFldMetaData.getName().equals("InstitutionID")) { if (i > 0) str.append(", "); str.append("1"); } else if (newColName.endsWith("Version")) { if (i > 0) str.append(", "); str.append("0"); } else if (newColName.endsWith("DatePrecision")) { if (i > 0) str.append(", "); String cName = newColName.substring(0, newColName.length() - 9); PartialDateConv datePr = dateMap.get(cName); if (datePr != null) { str.append(datePr.getPartial()); } else { str.append("NULL"); } } else if (idMapperMgr != null && newColName.endsWith("ID") && oneToOneIDHash != null && oneToOneIDHash.get(newColName) != null) { IdMapperIFace idMapper = idMapperMgr.get(toTableName, newColName); if (idMapper != null) { idMapper.setShowLogErrors(false); Integer newPrimaryId = idMapper.get(Integer.parseInt(id)); if (newPrimaryId != null) { if (i > 0) str.append(", "); str.append(newPrimaryId); } else { if (i > 0) str.append(", "); str.append("NULL"); if (isOptionOn(SHOW_VAL_MAPPING_ERROR)) { String msg = "For Table[" + fromTableName + "] mapping new Column Name[" + newColName + "] ID[" + id + "] was not mapped"; log.error(msg); writeErrLog(msg); skipRecord = true; } } } } else // there was no old column that maps to this new column { String newColValue = null; if (newColDefValues != null) { newColValue = newColDefValues.get(newColName); } if (newColValue == null) { newColValue = "NULL"; //System.out.println("ignoreMappingFieldNames" + ignoreMappingFieldNames); //System.out.println("ignoreMappingFieldNames.get(colName)" + ignoreMappingFieldNames.get(colName)); if (isOptionOn(SHOW_NAME_MAPPING_ERROR) && (ignoreMappingFieldNames == null || ignoreMappingFieldNames.get(newColName) == null)) { String msg = "For Table[" + fromTableName + "] mapping new Column Name[" + newColName + "] was not mapped"; log.error(msg); writeErrLog(msg); skipRecord = true; } } if (i > 0) str.append(", "); BasicSQLUtilsMapValueIFace valueMapper = columnValueMapper.get(newFldMetaData.getName()); if (valueMapper != null) { newColValue = valueMapper.mapValue(newColValue); } str.append(newColValue); } } str.append(")"); if (frame != null) { if (count % 500 == 0) { frame.setProcess(count); } } else { if (count % 2000 == 0) { log.info(toTableName + " processed: " + count); } } //setQuotedIdentifierOFFForSQLServer(toConn, BasicSQLUtils.myDestinationServerType); //exeUpdateCmd(updateStatement, "SET FOREIGN_KEY_CHECKS = 0"); //if (str.toString().toLowerCase().contains("insert into locality")) //{ //log.debug(str.toString()); //} //String str2 = "SET QUOTED_IDENTIFIER ON"; //log.debug("executing: " + str); //updateStatement.execute(str2); // updateStatement.close(); if (!skipRecord) { if (isOptionOn(SHOW_COPY_TABLE)) { log.debug("executing: " + str); } int retVal = exeUpdateCmd(updateStatement, str.toString()); if (retVal == -1) { rs.close(); stmt.clearBatch(); stmt.close(); return false; } } count++; // if (count == 1) break; } if (frame != null) { frame.setProcess(count); } else { log.info(fromTableName + " processed " + count + " records."); } rs.close(); stmt.clearBatch(); stmt.close(); } catch (SQLException ex) { ex.printStackTrace(); edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex); //e.printStackTrace(); log.error(sqlStr); log.error(ex); log.error("ID: " + id); } finally { try { updateStatement.clearBatch(); updateStatement.close(); } catch (SQLException ex) { } } BasicSQLUtils.setFieldsToIgnoreWhenMappingNames(null);//meg added 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. j ava 2 s.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; }