List of usage examples for java.sql ResultSet TYPE_SCROLL_INSENSITIVE
int TYPE_SCROLL_INSENSITIVE
To view the source code for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.
Click Source Link
ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. From source file:it.cnr.icar.eric.server.persistence.rdb.SQLPersistenceManagerImpl.java
/** * Executes an SQL Query./* w w w . j a va 2s. c om*/ */ @SuppressWarnings({ "static-access", "unchecked" }) public List<IdentifiableType> executeSQLQuery(ServerRequestContext context, String sqlQuery, List<String> queryParams, ResponseOptionType ebResponseOptionType, String tableName, List<?> objectRefs, IterativeQueryParams paramHolder) throws RegistryException { @SuppressWarnings("rawtypes") List ebIdentifiableTypeResultList = null; Connection connection = null; int startIndex = paramHolder.startIndex; int maxResults = paramHolder.maxResults; int totalResultCount = -1; Statement stmt = null; try { connection = context.getConnection(); java.sql.ResultSet rs = null; tableName = Utility.getInstance().mapTableName(tableName); ReturnType returnType = ebResponseOptionType.getReturnType(); @SuppressWarnings("unused") boolean returnComposedObjects = ebResponseOptionType.isReturnComposedObjects(); if (maxResults < 0) { if (queryParams == null) { stmt = connection.createStatement(); } else { stmt = connection.prepareStatement(sqlQuery); } } else { if (queryParams == null) { stmt = connection.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY); } else { stmt = connection.prepareStatement(sqlQuery, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY); } } if (log.isDebugEnabled()) { log.debug("Executing query: '" + sqlQuery + "'"); if (dumpStackOnQuery) { Thread.currentThread().dumpStack(); } } if (queryParams == null) { rs = stmt.executeQuery(sqlQuery); } else { Iterator<String> iter = queryParams.iterator(); int paramCount = 0; while (iter.hasNext()) { Object param = iter.next(); ((PreparedStatement) stmt).setObject(++paramCount, param); } rs = ((PreparedStatement) stmt).executeQuery(); } if (maxResults >= 0) { rs.last(); totalResultCount = rs.getRow(); // Reset back to before first row so that DAO can correctly // scroll // through the result set rs.beforeFirst(); } @SuppressWarnings("unused") Iterator<?> iter = null; log.debug("::3:: SQL result ReturnType" + returnType.toString()); if (returnType == ReturnType.OBJECT_REF) { ebIdentifiableTypeResultList = new ArrayList<Object>(); if (startIndex > 0) { rs.last(); totalResultCount = rs.getRow(); rs.beforeFirst(); // calling rs.next() is a workaround for some drivers, such // as Derby's, that do not set the cursor during call to // rs.relative(...) rs.next(); @SuppressWarnings("unused") boolean onRow = rs.relative(startIndex - 1); } int cnt = 0; while (rs.next()) { ObjectRefType ebObjectRefType = bu.rimFac.createObjectRefType(); // TODO: JAXBElement String id = rs.getString(1); ebObjectRefType.setId(id); ebIdentifiableTypeResultList.add(ebObjectRefType); if (++cnt == maxResults) { break; } } } else if (returnType == ReturnType.REGISTRY_OBJECT) { context.setResponseOption(ebResponseOptionType); RegistryObjectDAO roDAO = new RegistryObjectDAO(context); ebIdentifiableTypeResultList = roDAO.getObjects(rs, startIndex, maxResults); } else if ((returnType == ReturnType.LEAF_CLASS) || (returnType == ReturnType.LEAF_CLASS_WITH_REPOSITORY_ITEM)) { ebIdentifiableTypeResultList = getObjects(context, connection, rs, tableName, ebResponseOptionType, objectRefs, startIndex, maxResults); } else { throw new RegistryException(ServerResourceBundle.getInstance() .getString("message.invalidReturnType", new Object[] { returnType })); } } catch (SQLException e) { throw new RegistryException(e); } finally { try { if (stmt != null) { stmt.close(); } } catch (SQLException sqle) { log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), sqle); } } paramHolder.totalResultCount = totalResultCount; return ebIdentifiableTypeResultList; }
From source file:org.ims.ssp.samplerte.server.SSP_Servlet.java
/** * * Retrieve record from DB./*from www . ja v a2 s . c om*/ * * @param iLearnerID * @param iBucketID * @param iCourseID * @param iSCOID * @param iManagedBucketIndex * @param iPersistence * @param iReallocateFailure * * @return - Status or result information about the outcome of this call. */ private BucketAllocation retrieveDBRecord(String iLearnerID, String iBucketID, String iCourseID, String iSCOID, int iManagedBucketIndex, int iPersistence, boolean iReallocateFailure) { BucketAllocation result = new BucketAllocation(); ResultSet rsSSP_BucketTbl = null; try { new SSP_DBHandler(); Connection conn = SSP_DBHandler.getConnection(); Statement stmtSelectSSP_BucketTbl = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String sqlSelectSSP_BucketTbl = ""; if (iLearnerID != null) { sqlSelectSSP_BucketTbl = "LearnerID = '" + iLearnerID + "'"; } if (iBucketID != null) { if (sqlSelectSSP_BucketTbl != "") { sqlSelectSSP_BucketTbl += " AND "; } sqlSelectSSP_BucketTbl += "BucketID = '" + iBucketID + "'"; } if (iCourseID != null) { if (sqlSelectSSP_BucketTbl != "") { sqlSelectSSP_BucketTbl += " AND "; } sqlSelectSSP_BucketTbl += "CourseID = '" + iCourseID + "'"; } if (iSCOID != null) { if (sqlSelectSSP_BucketTbl != "") { sqlSelectSSP_BucketTbl += " AND "; } sqlSelectSSP_BucketTbl += "SCOID = '" + iSCOID + "'"; } if (iManagedBucketIndex >= 0) { if (sqlSelectSSP_BucketTbl != "") { sqlSelectSSP_BucketTbl += " AND "; } sqlSelectSSP_BucketTbl += "ManagedBucketIndex = " + iManagedBucketIndex; } if (sqlSelectSSP_BucketTbl != "") { sqlSelectSSP_BucketTbl += " AND "; } sqlSelectSSP_BucketTbl += "ReallocateFailure = " + iReallocateFailure; if (iPersistence != -1) { if (StringUtils.isNotEmpty(sqlSelectSSP_BucketTbl)) { sqlSelectSSP_BucketTbl += " AND "; } sqlSelectSSP_BucketTbl += "Persistence = " + iPersistence; } sqlSelectSSP_BucketTbl = "SELECT * FROM SSP_BucketAllocateTbl WHERE " + sqlSelectSSP_BucketTbl; if (_Debug) { System.out.println("SQL stmt in retieve record: " + sqlSelectSSP_BucketTbl); } synchronized (stmtSelectSSP_BucketTbl) { rsSSP_BucketTbl = stmtSelectSSP_BucketTbl.executeQuery(sqlSelectSSP_BucketTbl); } // determine how many records came back int bucketCount = 0; while (rsSSP_BucketTbl.next()) { bucketCount++; } if (bucketCount == 1) { rsSSP_BucketTbl.first(); result.setAllocationStatus(rsSSP_BucketTbl.getInt("Status")); result.setBucketID(iBucketID); result.setBucketType(rsSSP_BucketTbl.getString("BucketType")); result.setMinimumSizeInt(rsSSP_BucketTbl.getInt("Min")); result.setPersistence(rsSSP_BucketTbl.getInt("Persistence")); result.setReducibleBoolean(rsSSP_BucketTbl.getBoolean("Reducible")); result.setRequestedSizeInt(rsSSP_BucketTbl.getInt("Requested")); result.setSCOID(iSCOID); result.setActivityID(rsSSP_BucketTbl.getInt("ActivityID")); } stmtSelectSSP_BucketTbl.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } return result; }
From source file:edu.ku.brc.specify.conversion.ConvertTaxonHelper.java
/** * //from w w w .ja va 2s. c o m */ public void convertTaxonCitationToTaxonImage() { String sql = "SELECT tn.TaxonNameID, c.Text1 "; String fromStr = " FROM taxonname AS tn Inner Join taxoncitation AS c ON tn.TaxonNameID = c.TaxonNameID"; String whereStr = " WHERE c.Text1 IS NOT NULL"; String updateStr = "UPDATE taxon SET GUID=? WHERE TaxonID = ?"; int numTaxCit = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) " + fromStr + whereStr); if (numTaxCit > 0) { if (frame != null) { frame.setDesc(String.format("Fixing Taxon Citations", numTaxCit)); frame.setProcess(0, numTaxCit); } // process stranded rows String sqlStr = sql + fromStr + whereStr; log.debug(sqlStr); Statement stmt = null; PreparedStatement pStmt = null; try { stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pStmt = newDBConn.prepareStatement(updateStr); int cnt = 0; ResultSet rs = stmt.executeQuery(sqlStr); while (rs.next()) { int oldTaxonId = rs.getInt(1); Integer newTaxonID = txMapper.get(oldTaxonId); if (newTaxonID != null) { String imgURL = rs.getString(2); pStmt.setString(1, imgURL); pStmt.setInt(2, newTaxonID); if (pStmt.executeUpdate() != 1) { String msg = String.format("Unable to update new taxonID %d with image url[%s].", newTaxonID, imgURL); log.error(msg); tblWriter.logError(msg); } cnt++; if (frame != null) { frame.setProcess(cnt); } } else { String msg = String.format("Unable to map old id [%d] to new taxonID.", oldTaxonId); log.error(msg); tblWriter.logError(msg); } } rs.close(); if (frame != null) { frame.setProcess(numTaxCit); } } catch (SQLException ex) { ex.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); if (pStmt != null) pStmt.close(); } catch (Exception ex) { } } } }
From source file:edu.ku.brc.specify.conversion.AgentConverter.java
/** * @param oldAgentId//from ww w . j a v a 2s . c o m * @param agentIDMapper * @param tblWriter */ protected void copyAgentFromOldToNew(final Integer oldAgentId, final IdTableMapper agentIDMapper) { boolean doDebug = false; DBTableInfo agentTI = DBTableIdMgr.getInstance().getByShortClassName("Agent"); DBFieldInfo lastNameField = agentTI.getFieldByColumnName("LastName"); DBFieldInfo firstNameField = agentTI.getFieldByColumnName("FirstName"); StringBuilder sql = new StringBuilder("SELECT "); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { BasicSQLUtils.removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); } BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); List<String> oldAgentFieldNames = getFieldNamesFromSchema(oldDBConn, "agent"); String oldFieldListStr = buildSelectFieldList(oldAgentFieldNames, "agent"); sql.append(oldFieldListStr); sql.append(" FROM agent WHERE AgentID = " + oldAgentId); //log.info(oldFieldListStr); List<String> newAgentFieldNames = getFieldNamesFromSchema(newDBConn, "agent"); String newFieldListStr = buildSelectFieldList(newAgentFieldNames, "agent"); //log.info(newFieldListStr); int lastNameLen = 120; HashMap<String, Integer> oldIndexFromNameMap = new HashMap<String, Integer>(); int inx = 1; for (String fldName : oldAgentFieldNames) { oldIndexFromNameMap.put(fldName, inx++); } HashMap<String, Integer> newIndexFromNameMap = new HashMap<String, Integer>(); inx = 1; for (String fldName : newAgentFieldNames) { newIndexFromNameMap.put(fldName, inx++); } try { // So first we hash each AddressID and the value is set to 0 (false) Statement stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rsX = stmtX.executeQuery(sql.toString()); int agentIDInx = oldIndexFromNameMap.get("AgentID"); int agentTypeInx = oldIndexFromNameMap.get("AgentType"); int nameInx = oldIndexFromNameMap.get("Name"); int lastNameInx = oldIndexFromNameMap.get("LastName"); int firstNameInx = oldIndexFromNameMap.get("FirstName"); // log.debug(sql.toString()); int cnt = 0; while (rsX.next()) { int agentId = rsX.getInt(1); StringBuilder sqlStr = new StringBuilder(); sqlStr.append("INSERT INTO agent "); sqlStr.append("(" + newFieldListStr); sqlStr.append(")"); sqlStr.append(" VALUES ("); int fCnt = 0; for (String fieldName : newAgentFieldNames) { if (fCnt > 0) sqlStr.append(", "); if (StringUtils.contains(fieldName.toLowerCase(), "disciplineid")) { sqlStr.append(conv.getDisciplineId()); } else if (StringUtils.contains(fieldName, "FirstName")) { String firstName = rsX.getString(firstNameInx); if (firstName != null && firstName.length() > firstNameField.getLength()) { String str = firstName.substring(0, firstNameField.getLength()); tblWriter.logError("Agent id: " + rsX.getString(agentIDInx) + " - Concatinating First Name FROM [" + firstName + "] to [" + str + "]"); firstName = str; } sqlStr.append(BasicSQLUtils.getStrValue(firstName)); } else if (StringUtils.contains(fieldName, "LastName")) { int oldType = rsX.getInt(agentTypeInx); int srcColInx = oldType != 1 ? nameInx : lastNameInx; String lName = rsX.getString(srcColInx); if (lName == null && oldType != 1) { lName = rsX.getString(lastNameInx); } if (lName != null && lName.length() > lastNameField.getLength()) { String str = lName.substring(0, firstNameField.getLength()); tblWriter.logError("Agent id: " + rsX.getString(agentIDInx) + " - Concatinating Last Name FROM [" + lName + "] to [" + str + "]"); lName = str; } String lstName = lName; lName = lstName == null ? null : lstName.length() <= lastNameLen ? lstName : lstName.substring(0, lastNameLen); sqlStr.append(BasicSQLUtils.getStrValue(lName)); } else { String value = ""; Integer index; if (fieldName.equals("ModifiedByAgentID")) { index = oldIndexFromNameMap.get("LastEditedBy"); } else { index = oldIndexFromNameMap.get(fieldName); } if (index == null) { // log.debug(fieldName); value = "NULL"; } else if (fCnt == 0) { value = agentIDMapper.get(agentId).toString(); } else { value = BasicSQLUtils.getStrValue(rsX.getObject(index.intValue())); } BasicSQLUtilsMapValueIFace valueMapper = conv.getColumnValueMapper().get(fieldName); if (valueMapper != null) { value = valueMapper.mapValue(value); } sqlStr.append(value); } fCnt++; } sqlStr.append(")"); // log.info(sqlStr.toString()); Statement updateStatement = newDBConn.createStatement(); // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); if (doDebug) { log.info(sqlStr.toString()); } updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement); if (newAgentId == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; //conv.addAgentDisciplineJoin(newAgentId, conv.getDisciplineId()); cnt++; BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); } } catch (Exception ex) { log.error(ex); ex.printStackTrace(); System.exit(0); } }
From source file:uk.ac.kcl.texthunter.core.AnnotationEditor.java
public ResultSet getResultSet(String sql) throws SQLException { ResultSet newResultSet;/*from w w w . j ava2s. c o m*/ if (con == null) { AnnotationEditor.infoBox("Database not Connected", "Error"); } Statement newStmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //order AL results if (this.activeLearningRadioButton.isSelected()) { setUpActiveLearningTempTable(); } cleanUpResultSet(); newResultSet = newStmt.executeQuery(sql); System.out.println("ResultSet retrieved"); return newResultSet; }
From source file:edu.ku.brc.specify.conversion.ConvertVerifier.java
private void getResultSetsNotRetarded(final String oldSQLArg, final String newSQLArg, String oldValue, String newValue) throws SQLException { try {//from w ww . j a v a 2s.c om PreparedStatement newStmnt = newDBConn.prepareStatement(newSQLArg, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); PreparedStatement oldStmnt = oldDBConn.prepareStatement(compareTo6DBs ? newSQLArg : oldSQLArg, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); newStmnt.setString(1, newValue); oldStmnt.setString(1, oldValue); newDBRS = newStmnt.executeQuery(); oldDBRS = oldStmnt.executeQuery(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:org.jobjects.dao.annotation.Manager.java
/** * @param min Numero de ligne minimum// w w w . j a va2 s.com * @param max Numero de ligne maximum * @param wherefields Filtre * @param orderfields Ordonancement * @return la liste des beans * @throws FinderException retourne un exception si il y a une erreur. * La liste peut tre vide. */ public final List<T> findAll(final int min, final int max, final WhereFields wherefields, final OrderFields orderfields) throws FinderException { List<T> returnValue = null; try { returnValue = new ArrayList<T>(); // 26.375 if (null == sql_findAll) { sql_findAll = loadSqlFindAll(usualTable, fields); } String sql = sql_findAll + getSqlWhereAndOrder(wherefields, orderfields); // sql = "SELECT * FROM (SELECT ROWNUM N, P.* FROM (" + sql; // sql += ") P WHERE ROWNUM < " + max + ")"; // sql += "WHERE (N>" + min + ")AND(N<" + max + ")"; try { Connection connection = getConnection(); try { Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { ResultSet rs = stmt.executeQuery(sql); rs.absolute(min); stmt.setMaxRows(max); try { while (rs.next()) { T data = entityClass.newInstance(); int i = 1; for (Field field : fields) { Annotation[] annotations = field.getAnnotations(); for (Annotation annotation : annotations) { if (annotation instanceof DaoField) { BeanUtils.setProperty(returnValue, field.getName(), rs.getObject(i++)); break; } } } returnValue.add(data); } } finally { rs.close(); } rs = null; } finally { stmt.close(); } stmt = null; } finally { connection.close(); } connection = null; } catch (SQLException sqle) { log.error(sql, sqle); throw new FinderException(sql, sqle); } } catch (Exception e) { throw new FinderException(e); } return returnValue; }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * @param fromConn// w w w. j a v a2 s .com * @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.AgentConverter.java
/** * @param cntSQL// w w w . jav a 2 s.c o m * @param sql * @param tableName * @param mapperName */ private void doAddressOfRecord(final String cntSQL, final String sql, final String tableName, final String mapperName) { IdMapperIFace agentMapper = IdMapperMgr.getInstance().get("agent_AgentID"); Session session = HibernateUtil.getNewSession(); Transaction trans = null; try { conv.setProcess(0, BasicSQLUtils.getCountAsInt(oldDBConn, cntSQL)); IdMapperIFace loanMapper = IdMapperMgr.getInstance().get(mapperName); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { int col = 1; Integer loanID = rs.getInt(col++); Integer agentID = rs.getInt(col++); String address = rs.getString(col++); String city = rs.getString(col++); String state = rs.getString(col++); String country = rs.getString(col++); String postalCode = rs.getString(col++); String remarks = rs.getString(col++); Timestamp timestampModified = rs.getTimestamp(col++); Timestamp timestampCreated = rs.getTimestamp(col++); Integer newAgentId = agentMapper.get(agentID); if (newAgentId != null) { List<?> list = session.createQuery("FROM Agent WHERE id = " + newAgentId).list(); if (list != null && list.size() == 1) { Agent agent = (Agent) list.get(0); Integer newLoanId = loanMapper.get(loanID); if (newLoanId != null) { list = session.createQuery("FROM " + tableName + " WHERE id = " + newLoanId).list(); if (list != null && list.size() == 1) { trans = session.beginTransaction(); AddressOfRecord aor = new AddressOfRecord(); aor.initialize(); aor.setAddress(address); aor.setAgent(agent); aor.setCity(city); aor.setCountry(country); aor.setPostalCode(postalCode); aor.setRemarks(remarks); aor.setState(state); aor.setTimestampCreated(timestampCreated); aor.setTimestampModified(timestampModified); FormDataObjIFace parentObj = (FormDataObjIFace) list.get(0); DataModelObjBase.setDataMember(parentObj, "addressOfRecord", aor); session.saveOrUpdate(parentObj); //session.saveOrUpdate(aor); trans.commit(); } } } } if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rs.close(); stmt.close(); } catch (Exception ex) { try { if (trans != null) trans.rollback(); } catch (Exception ex1) { } ex.printStackTrace(); log.error(ex); } finally { session.close(); } }
From source file:org.nuxeo.ecm.core.storage.sql.Mapper.java
/** * Makes a NXQL query to the database.//from w ww. j ava2 s .c o m * * @param query the query * @param queryFilter the query filter * @param countTotal if {@code true}, count the total size without * limit/offset * @param session the current session (to resolve paths) * @return the list of matching document ids * @throws StorageException * @throws SQLException */ public PartialList<Serializable> query(String query, QueryFilter queryFilter, boolean countTotal, Session session) throws StorageException { QueryMaker queryMaker = findQueryMaker(query); if (queryMaker == null) { throw new StorageException("No QueryMaker accepts query: " + query); } QueryMaker.Query q = queryMaker.buildQuery(sqlInfo, model, session, query, queryFilter); if (q == null) { log("Query cannot return anything due to conflicting clauses"); return new PartialList<Serializable>(Collections.<Serializable>emptyList(), 0); } long limit = queryFilter.getLimit(); long offset = queryFilter.getOffset(); if (isLogEnabled()) { String sql = q.selectInfo.sql; if (limit != 0) { sql += " -- LIMIT " + limit + " OFFSET " + offset; } if (countTotal) { sql += " -- COUNT TOTAL"; } logSQL(sql, q.selectParams); } PreparedStatement ps = null; try { ps = connection.prepareStatement(q.selectInfo.sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); int i = 1; for (Object object : q.selectParams) { if (object instanceof Calendar) { Calendar cal = (Calendar) object; Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(i++, ts, cal); // cal passed for timezone } else if (object instanceof String[]) { Array array = sqlInfo.dialect.createArrayOf(Types.VARCHAR, (Object[]) object, connection); ps.setArray(i++, array); } else { ps.setObject(i++, object); } } ResultSet rs = ps.executeQuery(); // get all the returned data. name -> id Column column = q.selectInfo.whatColumns.get(0); Map<Serializable, String> id2Name = new LinkedHashMap<Serializable, String>(); while (rs.next()) { String id = (String) column.getFromResultSet(rs, 1); String name = rs.getString("name"); id2Name.put(id, name); } String uname = queryFilter.getPrincipal().getName(); Map<Serializable, CRMPermission> permissions = CRMPermissionRestService.filterHierarchyResult(uname, id2Name); long totalSize = permissions.size(); List<Serializable> allDocIds = new ArrayList<Serializable>(); allDocIds.addAll(permissions.keySet()); List<Serializable> pageDocIds = null; if (limit == 0) { limit = allDocIds.size(); } int startIdx = (offset > allDocIds.size()) ? allDocIds.size() : (int) offset; int endIdx = ((offset + limit) > allDocIds.size()) ? allDocIds.size() : (int) (offset + limit); pageDocIds = allDocIds.subList(startIdx, endIdx); return new PartialList<Serializable>(pageDocIds, totalSize); } catch (SQLException e) { checkConnectionReset(e); throw new StorageException("Invalid query: " + query, e); } finally { if (ps != null) { try { closePreparedStatement(ps); } catch (SQLException e) { log.error("Cannot close connection", e); } } } }