List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
java.sql.Timestamp value. From source file:edu.ucsb.eucalyptus.cloud.ws.WalrusManager.java
private void writeEvent(EntityManager em, final String eventType, final String account, final String opt, final String object_key, final Long size, final Boolean isFolder, final long syncid, final long lastModified, final String reqid, final int objseq) throws EucalyptusCloudException { Session sess = null;/* w ww . jav a2 s.c o m*/ try { sess = ((Session) em.getDelegate()); } catch (Throwable t) { sess = null; } final java.sql.Timestamp mtime = new java.sql.Timestamp(lastModified); if ("webApp".equals(eventType)) { final String sql = "insert into web_opt_log(id,rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id, reqid) values(DEFAULT,CURRENT_TIMESTAMP AT TIME ZONE 'UTC +0',?,?,?,?,?,?,NULL,?,'succ','dummy',?)"; if (sess != null) { sess.doWork(new Work() { public void execute(Connection connection) throws SQLException { PreparedStatement stmt = null; try { stmt = connection.prepareStatement(sql); stmt.setTimestamp(1, mtime); stmt.setString(2, account); stmt.setString(3, opt); stmt.setString(4, object_key); stmt.setLong(5, size); stmt.setInt(6, objseq); stmt.setBoolean(7, isFolder); stmt.setString(8, reqid); stmt.executeUpdate(); } finally { if (stmt != null) stmt.close(); } } }); } else { em.createNativeQuery(sql).setParameter(1, mtime).setParameter(2, account).setParameter(3, opt) .setParameter(4, object_key).setParameter(5, size).setParameter(6, objseq) .setParameter(7, isFolder).setParameter(8, reqid).executeUpdate(); } } else if ("mobileApp".equals(eventType)) { } else { final String sql = "insert into opt_log(id,rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id, reqid) values(DEFAULT,CURRENT_TIMESTAMP AT TIME ZONE 'UTC +0',?,?,?,?,?,?,?,?,'succ','dummy',?)"; if (sess != null) { sess.doWork(new Work() { public void execute(Connection connection) throws SQLException { PreparedStatement stmt = null; try { stmt = connection.prepareStatement(sql); stmt.setTimestamp(1, mtime); stmt.setString(2, account); stmt.setString(3, opt); stmt.setString(4, object_key); stmt.setLong(5, size); stmt.setInt(6, objseq); stmt.setLong(7, syncid); stmt.setBoolean(8, isFolder); stmt.setString(9, reqid); stmt.executeUpdate(); } finally { if (stmt != null) stmt.close(); } } } ); } else { em.createNativeQuery(sql).setParameter(1, mtime).setParameter(2, account).setParameter(3, opt) .setParameter(4, object_key).setParameter(5, size).setParameter(6, objseq) .setParameter(7, syncid).setParameter(8, isFolder).setParameter(9, reqid).executeUpdate(); } } }
From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java
/** * Update all the existing field values in the database with the default * value for that field//from w ww . j av a2 s .c om */ private void setFieldDefaultDbAction(Connection conn, BaseField field) throws SQLException, CantDoThatException, ObjectNotFoundException, CodingErrorException { if (field.hasDefault()) { String internalTableName = field.getTableContainingField().getInternalTableName(); String internalFieldName = field.getInternalFieldName(); String SQLCode = "UPDATE " + internalTableName + " SET " + internalFieldName + "=?"; PreparedStatement statement = conn.prepareStatement(SQLCode); if (field instanceof TextField) { String defaultValue = ((TextField) field).getDefault(); statement.setString(1, defaultValue); } else if (field instanceof DecimalField) { Double defaultValue = ((DecimalField) field).getDefault(); statement.setDouble(1, defaultValue); } else if (field instanceof IntegerField) { Integer defaultValue = ((IntegerField) field).getDefault(); statement.setInt(1, defaultValue); } else if (field instanceof CheckboxField) { Boolean defaultValue = ((CheckboxField) field).getDefault(); statement.setBoolean(1, defaultValue); } else if (field instanceof DateField) { Calendar defaultValueCalendar = ((DateField) field).getDefault(); Timestamp defaultValue = new Timestamp(defaultValueCalendar.getTimeInMillis()); statement.setTimestamp(1, defaultValue); } else { throw new CantDoThatException( "Unable to set default value for field type " + field.getFieldCategory()); } statement.execute(); statement.close(); } }
From source file:helma.objectmodel.db.NodeManager.java
private void setStatementValue(PreparedStatement stmt, int stmtNumber, Property p, int columnType) throws SQLException { if (p.getValue() == null) { stmt.setNull(stmtNumber, columnType); } else {/*from w w w .j a v a 2 s .c o m*/ switch (columnType) { case Types.BIT: case Types.BOOLEAN: stmt.setBoolean(stmtNumber, p.getBooleanValue()); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: stmt.setLong(stmtNumber, p.getIntegerValue()); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: stmt.setDouble(stmtNumber, p.getFloatValue()); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BINARY: case Types.BLOB: Object b = p.getJavaObjectValue(); if (b instanceof byte[]) { byte[] buf = (byte[]) b; try { stmt.setBytes(stmtNumber, buf); } catch (SQLException x) { ByteArrayInputStream bout = new ByteArrayInputStream(buf); stmt.setBinaryStream(stmtNumber, bout, buf.length); } } else { throw new SQLException( "expected byte[] for binary column '" + p.getName() + "', found " + b.getClass()); } break; case Types.LONGVARCHAR: try { stmt.setString(stmtNumber, p.getStringValue()); } catch (SQLException x) { String str = p.getStringValue(); Reader r = new StringReader(str); stmt.setCharacterStream(stmtNumber, r, str.length()); } break; case Types.CLOB: String val = p.getStringValue(); Reader isr = new StringReader(val); stmt.setCharacterStream(stmtNumber, isr, val.length()); break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: stmt.setTimestamp(stmtNumber, p.getTimestampValue()); break; case Types.NULL: stmt.setNull(stmtNumber, 0); break; default: stmt.setString(stmtNumber, p.getStringValue()); break; } } }
From source file:edu.ucsb.eucalyptus.cloud.ws.WalrusManager.java
private void writeRenameEvent(EntityManager em, final String eventType, final String account, final String bucketName, final String object_key, final String renameTo, final long syncid, final String reqid, final int objseq) throws EucalyptusCloudException { Session sess = null;// ww w .j a v a2s. c o m try { sess = ((Session) em.getDelegate()); } catch (Throwable t) { sess = null; } if ("webApp".equals(eventType)) { final String sql = "insert into web_opt_log(rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id,reqid)" + "select ?,to_timestamp(coalesce(value::bigint,0)/1000),?,'Rename',?,size,?,?,content_type='application/x-directory','succ','dummy',? from objects " + "obj left join metadata mtd on obj.object_name=mtd.object_id and mtd.\"name\"='mtime' " + "where object_key = ? AND bucket_name = ? AND owner_id = ? limit 1"; if (sess != null) { sess.doWork(new Work() { public void execute(Connection connection) throws SQLException { PreparedStatement stmt = null; try { stmt = connection.prepareStatement(sql); Calendar c = Calendar.getInstance(); stmt.setTimestamp(1, new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset())); stmt.setString(2, account); stmt.setString(3, object_key + "||" + renameTo); stmt.setInt(4, objseq); stmt.setLong(5, syncid); stmt.setString(6, reqid); stmt.setString(7, renameTo); stmt.setString(8, bucketName); stmt.setString(9, account); stmt.executeUpdate(); } finally { try { if (stmt != null) stmt.close(); } catch (Throwable t) { /*NOP*/} } } }); } else { Calendar c = Calendar.getInstance(); em.createNativeQuery(sql) .setParameter(1, new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset())) .setParameter(2, account).setParameter(3, object_key + "||" + renameTo) .setParameter(4, objseq).setParameter(5, syncid).setParameter(6, reqid) .setParameter(7, renameTo).setParameter(8, bucketName).setParameter(9, account) .executeUpdate(); } } else if ("mobileApp".equals(eventType)) { } else { final String sql = "insert into opt_log(rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id,reqid) " + "select ?,to_timestamp(coalesce(value::bigint,0)/1000),?,'Rename',?,size,?,?,content_type='application/x-directory','succ','dummy',? from objects " + "obj left join metadata mtd on obj.object_name=mtd.object_id and mtd.\"name\"='mtime' " + "where object_key = ? AND bucket_name = ? AND owner_id = ? limit 1"; if (sess != null) { sess.doWork(new Work() { public void execute(Connection connection) throws SQLException { PreparedStatement stmt = null; try { stmt = connection.prepareStatement(sql); Calendar c = Calendar.getInstance(); stmt.setTimestamp(1, new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset())); stmt.setString(2, account); stmt.setString(3, object_key + "||" + renameTo); stmt.setInt(4, objseq); stmt.setLong(5, syncid); stmt.setString(6, reqid); stmt.setString(7, renameTo); stmt.setString(8, bucketName); stmt.setString(9, account); stmt.executeUpdate(); } finally { try { if (stmt != null) stmt.close(); } catch (Throwable t) { /*NOP*/} } } }); } else { Calendar c = Calendar.getInstance(); em.createNativeQuery(sql) .setParameter(1, new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset())) .setParameter(2, account).setParameter(3, object_key + "||" + renameTo) .setParameter(4, objseq).setParameter(5, syncid).setParameter(6, reqid) .setParameter(7, renameTo).setParameter(8, bucketName).setParameter(9, account) .executeUpdate(); } } }
From source file:axiom.objectmodel.db.NodeManager.java
private void setStatementValues(PreparedStatement stmt, int stmtNumber, Property p, int columnType) throws SQLException { if (p.getValue() == null) { stmt.setNull(stmtNumber, columnType); } else {/*from w w w . j ava 2 s .c o m*/ switch (columnType) { case Types.BIT: case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: stmt.setLong(stmtNumber, p.getIntegerValue()); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: stmt.setDouble(stmtNumber, p.getFloatValue()); break; case Types.VARBINARY: case Types.BINARY: case Types.BLOB: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.LONGVARBINARY: case Types.LONGVARCHAR: try { stmt.setString(stmtNumber, p.getStringValue()); } catch (SQLException x) { String str = p.getStringValue(); Reader r = new StringReader(str); stmt.setCharacterStream(stmtNumber, r, str.length()); } break; case Types.CLOB: String val = p.getStringValue(); Reader isr = new StringReader(val); stmt.setCharacterStream(stmtNumber, isr, val.length()); break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: stmt.setTimestamp(stmtNumber, p.getTimestampValue()); break; case Types.NULL: stmt.setNull(stmtNumber, 0); break; default: stmt.setString(stmtNumber, p.getStringValue()); break; } } }
From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java
/** * @param oldDBConn//from w w w . j a v a 2 s . co m * @param newDBConn * @param disciplineID */ public static boolean moveHabitatToStratSp5(final Connection oldDBConn) { PreparedStatement pStmt1 = null; try { String sqlCreate = "CREATE TABLE `stratigraphy2` ( `StratigraphyID` int(10) NOT NULL, `GeologicTimePeriodID` int(10) DEFAULT NULL, `SuperGroup` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `Group` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `Formation` varchar(50) CHARACTER SET utf8 DEFAULT NULL, " + "`Member` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `Bed` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `Remarks` longtext, `Text1` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `Text2` varchar(300) CHARACTER SET utf8 DEFAULT NULL, `Number1` double DEFAULT NULL, " + "`Number2` double DEFAULT NULL, `TimestampCreated` datetime DEFAULT NULL, `TimestampModified` datetime DEFAULT NULL, `LastEditedBy` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `YesNo1` smallint(5) DEFAULT NULL, `YesNo2` smallint(5) DEFAULT NULL, PRIMARY KEY (`StratigraphyID`) " + //, "KEY `IX_XXXXXX` (`GeologicTimePeriodID`), " + //"CONSTRAINT `FK_Stratigraphy_CollectingEvent` FOREIGN KEY (`StratigraphyID`) REFERENCES `collectingevent` (`CollectingEventID`) ON DELETE CASCADE ON UPDATE NO ACTION, " + //"CONSTRAINT `FK_Stratigraphy_GeologicTimePeriod` FOREIGN KEY (`GeologicTimePeriodID`) REFERENCES `geologictimeperiod` (`GeologicTimePeriodID`) ON DELETE NO ACTION ON UPDATE NO ACTION " + ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"; DBMSUserMgr dbMgr = DBMSUserMgr.getInstance(); dbMgr.setConnection(oldDBConn); if (dbMgr.doesDBHaveTable("stratigraphy2")) { try { BasicSQLUtils.update(oldDBConn, "DROP TABLE stratigraphy2"); } catch (Exception ex) { } } dbMgr.setConnection(null); BasicSQLUtils.update(oldDBConn, sqlCreate); String post = " FROM collectingevent AS ce " + "Left Join habitat AS h ON ce.CollectingEventID = h.HabitatID " + "Left Join stratigraphy AS s ON ce.CollectingEventID = s.StratigraphyID " + "WHERE h.Text1 IS NOT NULL OR h.Text2 IS NOT NULL OR h.Text3 IS NOT NULL OR h.Text4 IS NOT NULL OR h.Text5 IS NOT NULL"; String sql = "SELECT ce.CollectingEventID, h.Text1, h.Text2, h.Text3, h.Text4, h.Text5, h.TimestampCreated, h.TimestampModified " + post; log.debug(sql); String cntSQL = "SELECT COUNT(*) " + post; int habCnt = BasicSQLUtils.getCountAsInt(oldDBConn, cntSQL); log.debug("****** Startigraphy Count: " + habCnt); //Timestamp now = new Timestamp(System .currentTimeMillis()); // pStmt1 = oldDBConn.prepareStatement( "INSERT INTO stratigraphy2 (StratigraphyID, SuperGroup, `Group`, Formation, Member, Bed, TimestampCreated, TimestampModified) VALUES(?,?,?,?,?,?,?,?)"); int cnt = 0; Vector<Object[]> rows = BasicSQLUtils.query(oldDBConn, sql); for (Object[] row : rows) { Integer ceID = (Integer) row[0]; String superGrp = (String) row[1]; String group = (String) row[2]; String formation = (String) row[3]; String member = (String) row[4]; String bed = (String) row[5]; Timestamp crTS = (Timestamp) row[6]; Timestamp mdTS = (Timestamp) row[7]; if (StringUtils.isNotEmpty(superGrp)) { if (superGrp.length() > 50) { superGrp = superGrp.substring(0, 50); } } if (StringUtils.isNotEmpty(bed)) { if (bed.length() > 50) { bed = bed.substring(0, 50); } } //if (hbID != null && stID == null) if (ceID != null) { pStmt1.setInt(1, ceID);//getNewRecId(oldDBConn, "stratigraphy", "StratigraphyID")); pStmt1.setString(2, superGrp); pStmt1.setString(3, group); pStmt1.setString(4, formation); pStmt1.setString(5, member); pStmt1.setString(6, bed); pStmt1.setTimestamp(7, crTS); pStmt1.setTimestamp(8, mdTS); pStmt1.execute(); cnt++; if (cnt % 100 == 0) { log.debug(cnt + " / " + habCnt); } } } return true; } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (pStmt1 != null) pStmt1.close(); } catch (Exception ex) { } } return false; }
From source file:edu.ku.brc.specify.conversion.AgentConverter.java
/** * Specify 5.x points at AgentAdress instead of an Agent. The idea was that to point at an Agent * and possibly a differnt address that represents what that person does. This was really * confusing so we are changing it to point at an Agent instead. * //from w ww .j av a 2 s . co m * So that means we need to pull apart these relationships and have all foreign keys that point * to an AgentAddress now point at an Agent and we then need to add in the Agents and then add * the Address to the Agents. * * The AgentAdress, Agent and Address (triple) can have a NULL Address but it cannot have a NULL * Agent. If there is a NULL Agent then this method will throw a RuntimeException. */ public boolean convertAgents(final boolean doFixAgents) { boolean debugAgents = false; log.debug("convert Agents"); BasicSQLUtils.removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); // Create the mappers here, but fill them in during the AgentAddress Process IdTableMapper agentIDMapper = idMapperMgr.addTableMapper("agent", "AgentID"); IdTableMapper agentAddrIDMapper = idMapperMgr.addTableMapper("agentaddress", "AgentAddressID"); agentIDMapper.setInitialIndex(4); if (shouldCreateMapTables) { log.info("Mapping Agent Ids"); agentIDMapper.mapAllIds("SELECT AgentID FROM agent ORDER BY AgentID"); } // Just like in the conversion of the CollectionObjects we // need to build up our own SELECT clause because the MetaData of columns names returned // FROM // a query doesn't include the table names for all columns, this is far more predictable List<String> oldFieldNames = new ArrayList<String>(); StringBuilder agtAdrSQL = new StringBuilder("SELECT "); List<String> agentAddrFieldNames = getFieldNamesFromSchema(oldDBConn, "agentaddress"); agtAdrSQL.append(buildSelectFieldList(agentAddrFieldNames, "agentaddress")); agtAdrSQL.append(", "); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentAddrFieldNames, "agentaddress"); List<String> agentFieldNames = getFieldNamesFromSchema(oldDBConn, "agent"); agtAdrSQL.append(buildSelectFieldList(agentFieldNames, "agent")); log.debug("MAIN: " + agtAdrSQL); agtAdrSQL.append(", "); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentFieldNames, "agent"); List<String> addrFieldNames = getFieldNamesFromSchema(oldDBConn, "address"); log.debug(agtAdrSQL); agtAdrSQL.append(buildSelectFieldList(addrFieldNames, "address")); GenericDBConversion.addNamesWithTableName(oldFieldNames, addrFieldNames, "address"); // Create a Map FROM the full table/fieldname to the index in the resultset (start at 1 not zero) HashMap<String, Integer> indexFromNameMap = new HashMap<String, Integer>(); agtAdrSQL.append( " FROM agent INNER JOIN agentaddress ON agentaddress.AgentID = agent.AgentID INNER JOIN address ON agentaddress.AddressID = address.AddressID Order By agentaddress.AgentAddressID Asc"); // These represent the New columns of Agent Table // So the order of the names are for the new table // the names reference the old table String[] agentColumns = { "agent.AgentID", "agent.TimestampModified", "agent.AgentType", "agentaddress.JobTitle", "agent.FirstName", "agent.LastName", "agent.MiddleInitial", "agent.Title", "agent.Interests", "agent.Abbreviation", "agentaddress.Email", "agentaddress.URL", "agent.Remarks", "agent.TimestampCreated", // User/Security changes "agent.ParentOrganizationID" }; HashMap<Integer, AddressInfo> addressHash = new HashMap<Integer, AddressInfo>(); // Create a HashMap to track which IDs have been handled during the conversion process try { log.info("Hashing Address Ids"); Integer agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AddressID) FROM address ORDER BY AddressID"); // 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("SELECT AgentAddressID, AddressID FROM agentaddress ORDER BY AgentAddressID"); conv.setProcess(0, agentCnt); int cnt = 0; // Needed to add in case AgentAddress table wasn't used. while (rsX.next()) { int agentAddrId = rsX.getInt(1); int addrId = rsX.getInt(2); addressHash.put(addrId, new AddressInfo(agentAddrId, addrId)); if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); conv.setProcess(0, 0); // Next we hash all the Agents and set their values to 0 (false) log.info("Hashing Agent Ids"); stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(*) FROM agent ORDER BY AgentID"); rsX = stmtX.executeQuery( "SELECT AgentID, AgentType, LastName, Name, FirstName FROM agent ORDER BY AgentID"); conv.setProcess(0, agentCnt); cnt = 0; while (rsX.next()) { int agentId = rsX.getInt(1); agentHash.put(agentId, new AgentInfo(agentId, agentIDMapper.get(agentId), rsX.getByte(2), rsX.getString(3), rsX.getString(4), rsX.getString(5))); if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); conv.setProcess(0, 0); // Now we map all the Agents to their Addresses AND // All the Addresses to their Agents. // // NOTE: A single Address Record May be used by more than one Agent so // we will need to Duplicate the Address records later // log.info("Cross Mapping Agents and Addresses"); String post = " FROM agentaddress WHERE AddressID IS NOT NULL and AgentID IS NOT NULL"; agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AgentAddressID)" + post); stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String asSQL = "SELECT AgentAddressID, AgentID" + post; log.debug(asSQL); rsX = stmtX.executeQuery(asSQL); conv.setProcess(0, agentCnt); cnt = 0; // Needed to add in case AgentAddress table wasn't used. while (rsX.next()) { int agentAddrId = rsX.getInt(1); int agentId = rsX.getInt(2); // /////////////////////// // Add Address to Agent // /////////////////////// AgentInfo agentInfo = agentHash.get(agentId); if (agentInfo == null) { String msg = "The AgentID [" + agentId + "] in AgentAddress table id[" + agentAddrId + "] desn't exist"; log.error(msg); tblWriter.logError(msg); } else { agentInfo.add(agentAddrId, agentAddrId); } if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); //dumpInfo("beforeInfo.txt", addressHash); conv.setProcess(0, 0); // It OK if the address is NULL, but the Agent CANNOT be NULL log.info("Checking for null Agents"); agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AgentAddressID) FROM agentaddress a where AddressID IS NOT NULL and AgentID is null"); // If there is a Single Record With a NULL Agent this would be BAD! if (agentCnt != null && agentCnt > 0) { showError("There are " + agentCnt + " AgentAddress Records where the AgentID is null and the AddressId IS NOT NULL!"); } // //////////////////////////////////////////////////////////////////////////////// // This does the part of AgentAddress where it has both an Address AND an Agent // //////////////////////////////////////////////////////////////////////////////// log.info(agtAdrSQL.toString()); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); log.debug("AgentAddress: " + agtAdrSQL.toString()); // Create Map of column name to column index number int inx = 1; for (String fldName : oldFieldNames) { // log.info("["+fldName+"] "+inx+" ["+rsmd.getColumnName(inx)+"]"); indexFromNameMap.put(fldName, inx++); } Statement updateStatement = newDBConn.createStatement(); // Figure out certain column indexes we will need alter int agentIdInx = indexFromNameMap.get("agent.AgentID"); int agentTypeInx = indexFromNameMap.get("agent.AgentType"); int lastEditInx = indexFromNameMap.get("agent.LastEditedBy"); int nameInx = indexFromNameMap.get("agent.Name"); int lastNameInx = indexFromNameMap.get("agent.LastName"); int firstNameInx = indexFromNameMap.get("agent.FirstName"); int recordCnt = 0; ResultSet rs = stmt.executeQuery(agtAdrSQL.toString()); while (rs.next()) { int agentAddressId = rs.getInt(1); int agentId = rs.getInt(agentIdInx); String lastEditedBy = rs.getString(lastEditInx); AgentInfo agentInfo = agentHash.get(agentId); // Deal with Agent FirstName, LastName and Name] String lastName = rs.getString(lastNameInx); String name = rs.getString(nameInx); namePair.second = StringUtils.isNotEmpty(name) && StringUtils.isEmpty(lastName) ? name : lastName; namePair.first = rs.getString(firstNameInx); // Now tell the AgentAddress Mapper the New ID to the Old AgentAddressID if (shouldCreateMapTables) { agentAddrIDMapper.setShowLogErrors(false); if (debugAgents) log.info(String.format("Map - agentAddressId (Old) %d to Agent -> New ID: %d", agentAddressId, agentInfo.getNewAgentId())); if (agentAddrIDMapper.get(agentAddressId) == null) { agentAddrIDMapper.put(agentAddressId, agentInfo.getNewAgentId()); } else { log.debug(String.format("ERROR - agentAddressId %d Already mapped to New ID: %d", agentAddressId, agentInfo.getNewAgentId())); } agentAddrIDMapper.setShowLogErrors(true); } // Because of the old DB relationships we want to make sure we only add each agent // in one time // So start by checking the HashMap to see if it has already been added if (!agentInfo.wasAdded()) { agentInfo.setWasAdded(true); //agentInfo.addWrittenAddrOldId(addrInfo.getOldAddrId()); BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); // It has not been added yet so Add it StringBuilder sqlStr = new StringBuilder(); sqlStr.append("INSERT INTO agent "); sqlStr.append( "(AgentID, DivisionId, TimestampModified, AgentType, JobTitle, FirstName, LastName, MiddleInitial, "); sqlStr.append("Title, Interests, Abbreviation, Email, URL, Remarks, TimestampCreated, "); sqlStr.append("ParentOrganizationID, CreatedByAgentID, ModifiedByAgentID, Version)"); sqlStr.append(" VALUES ("); for (int i = 0; i < agentColumns.length; i++) { if (i > 0) { sqlStr.append(","); } if (i == 0) { if (debugAgents) log.info("Adding: " + agentColumns[i] + " New ID: " + agentInfo.getNewAgentId()); sqlStr.append(agentInfo.getNewAgentId()); sqlStr.append(","); sqlStr.append(conv.getCurDivisionID()); } else if (agentColumns[i].equals("agent.ParentOrganizationID")) { Object obj = rs.getObject(indexFromNameMap.get(agentColumns[i])); if (obj != null) { int oldId = rs.getInt(agentColumns[i]); Integer newID = agentIDMapper.get(oldId); if (newID == null) { log.error("Couldn't map ParentOrganizationID [" + oldId + "]"); } sqlStr.append(BasicSQLUtils.getStrValue(newID)); } else { sqlStr.append("NULL"); } } else if (agentColumns[i].equals("agent.LastName") || agentColumns[i].equals("LastName")) { int lastNameLen = 120; String lstName = namePair.second; lstName = lstName == null ? null : lstName.length() <= lastNameLen ? lstName : lstName.substring(0, lastNameLen); sqlStr.append(BasicSQLUtils.getStrValue(lstName)); } else if (agentColumns[i].equals("agent.FirstName") || agentColumns[i].equals("FirstName")) { sqlStr.append(BasicSQLUtils.getStrValue(namePair.first)); } else { inx = indexFromNameMap.get(agentColumns[i]); sqlStr.append(BasicSQLUtils.getStrValue(rs.getObject(inx))); } } sqlStr.append("," + conv.getCreatorAgentIdForAgent(lastEditedBy) + "," + conv.getModifiedByAgentIdForAgent(lastEditedBy) + ",0"); sqlStr.append(")"); try { if (debugAgents) { 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"); } //conv.addAgentDisciplineJoin(newAgentId, conv.getDisciplineId()); } catch (SQLException e) { log.error(sqlStr.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); System.exit(0); throw new RuntimeException(e); } } BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); if (recordCnt % 250 == 0) { log.info("AgentAddress Records: " + recordCnt); } recordCnt++; } // while BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); log.info("AgentAddress Records: " + recordCnt); rs.close(); stmt.close(); // //////////////////////////////////////////////////////////////////////////////// // This does the part of AgentAddress where it has JUST Agent // //////////////////////////////////////////////////////////////////////////////// log.info("******** Doing AgentAddress JUST Agent"); int newRecordsAdded = 0; StringBuilder justAgentSQL = new StringBuilder(); justAgentSQL.setLength(0); justAgentSQL.append("SELECT "); justAgentSQL.append(buildSelectFieldList(agentAddrFieldNames, "agentaddress")); justAgentSQL.append(", "); getFieldNamesFromSchema(oldDBConn, "agent", agentFieldNames); justAgentSQL.append(buildSelectFieldList(agentFieldNames, "agent")); justAgentSQL.append( " FROM agent INNER JOIN agentaddress ON agentaddress.AgentID = agent.AgentID WHERE agentaddress.AddressID IS NULL ORDER BY agentaddress.AgentAddressID ASC"); log.info(justAgentSQL.toString()); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(justAgentSQL.toString()); oldFieldNames.clear(); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentAddrFieldNames, "agentaddress"); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentFieldNames, "agent"); indexFromNameMap.clear(); inx = 1; for (String fldName : oldFieldNames) { indexFromNameMap.put(fldName, inx++); } agentIdInx = indexFromNameMap.get("agent.AgentID"); lastEditInx = indexFromNameMap.get("agent.LastEditedBy"); agentTypeInx = indexFromNameMap.get("agent.AgentType"); recordCnt = 0; while (rs.next()) { byte agentType = rs.getByte(agentTypeInx); int agentAddressId = rs.getInt(1); int agentId = rs.getInt(agentIdInx); String lastEditedBy = rs.getString(lastEditInx); AgentInfo agentInfo = agentHash.get(agentId); // Now tell the AgentAddress Mapper the New ID to the Old AgentAddressID if (shouldCreateMapTables) { agentAddrIDMapper.put(agentAddressId, agentInfo.getNewAgentId()); } recordCnt++; if (!agentInfo.wasAdded()) { agentInfo.setWasAdded(true); BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); // Create Agent StringBuilder sqlStr = new StringBuilder("INSERT INTO agent "); sqlStr.append( "(AgentID, DivisionID, TimestampModified, AgentType, JobTitle, FirstName, LastName, MiddleInitial, Title, Interests, "); sqlStr.append("Abbreviation, Email, URL, Remarks, TimestampCreated, ParentOrganizationID, "); sqlStr.append("CreatedByAgentID, ModifiedByAgentID, Version)"); sqlStr.append(" VALUES ("); for (int i = 0; i < agentColumns.length; i++) { if (i > 0) sqlStr.append(","); if (i == 0) { if (debugAgents) log.info(agentColumns[i]); sqlStr.append(agentInfo.getNewAgentId()); sqlStr.append(","); sqlStr.append(conv.getCurDivisionID()); } else if (i == lastEditInx) { // Skip the field } else if (agentColumns[i].equals("agent.LastName")) { if (debugAgents) log.info(agentColumns[i]); int srcColInx = agentType != 1 ? nameInx : lastNameInx; String lName = BasicSQLUtils.getStrValue(rs.getObject(srcColInx)); sqlStr.append(lName); } else { if (debugAgents) log.info(agentColumns[i]); inx = indexFromNameMap.get(agentColumns[i]); sqlStr.append(BasicSQLUtils.getStrValue(rs.getObject(inx))); } } sqlStr.append("," + conv.getCreatorAgentIdForAgent(lastEditedBy) + "," + conv.getModifiedByAgentIdForAgent(lastEditedBy) + ", 0"); // '0' is Version sqlStr.append(")"); try { if (debugAgents) { 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"); } newRecordsAdded++; } catch (SQLException e) { log.error(sqlStr.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } if (recordCnt % 250 == 0) { log.info("AgentAddress (Agent Only) Records: " + recordCnt); } } // while log.info("AgentAddress (Agent Only) Records: " + recordCnt + " newRecordsAdded " + newRecordsAdded); rs.close(); updateStatement.close(); conv.setProcess(0, BasicSQLUtils.getNumRecords(oldDBConn, "agent")); conv.setDesc("Adding Agents"); // Now Copy all the Agents that where part of an Agent Address Conversions stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery("SELECT AgentID FROM agent"); recordCnt = 0; while (rs.next()) { Integer agentId = rs.getInt(1); AgentInfo agentInfo = agentHash.get(agentId); if (agentInfo == null || !agentInfo.wasAdded()) { copyAgentFromOldToNew(agentId, agentIDMapper); } recordCnt++; if (recordCnt % 50 == 0) { conv.setProcess(recordCnt); } } conv.setProcess(recordCnt); BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); //------------------------------------------------------------ // Now Copy all the Agents that where missed //------------------------------------------------------------ conv.setProcess(0); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery("SELECT AgentID FROM agent"); recordCnt = 0; while (rs.next()) { Integer agentId = rs.getInt(1); Integer newId = agentIDMapper.get(agentId); if (newId != null) { Integer isThere = BasicSQLUtils.getCount(newDBConn, "SELECT COUNT(*) FROM agent WHERE AgentID = " + newId); if (isThere == null || isThere == 0) { copyAgentFromOldToNew(agentId, agentIDMapper); } } else { tblWriter.logError("Mapping missing for old Agent id[" + agentId + "]"); } recordCnt++; if (recordCnt % 50 == 0) { conv.setProcess(recordCnt); } } conv.setProcess(recordCnt); if (doFixAgents) { fixAgentsLFirstLastName(); } //---------------------------------------------------------------------------------------------------------------------------------- // Now loop through the Agents hash and write the addresses. If the address has already been written then it will need to be // duplicate in the second step. //---------------------------------------------------------------------------------------------------------------------------------- StringBuilder sqlStr1 = new StringBuilder("INSERT INTO address "); sqlStr1.append( "(TimestampModified, Address, Address2, City, State, Country, PostalCode, Remarks, TimestampCreated, "); sqlStr1.append( "IsPrimary, IsCurrent, Phone1, Phone2, Fax, RoomOrBuilding, PositionHeld, AgentID, CreatedByAgentID, ModifiedByAgentID, Version, Ordinal)"); sqlStr1.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); PreparedStatement pStmt = newDBConn.prepareStatement(sqlStr1.toString(), Statement.RETURN_GENERATED_KEYS); // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 String addrSQL = "SELECT a.TimestampModified, a.Address, a.City, a.State, a.Country, a.Postalcode, a.Remarks, a.TimestampCreated, aa.Phone1, aa.Phone2, aa.Fax, aa.RoomOrBuilding , aa.IsCurrent, a.LastEditedBy, aa.JobTitle " + "FROM address AS a " + "INNER JOIN agentaddress AS aa ON a.AddressID = aa.AddressID WHERE aa.AgentAddressID = %d"; BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); int fixCnt = 0; for (AgentInfo agentInfo : agentHash.values()) { HashMap<Integer, Integer> addrs = agentInfo.getAddrs(); for (Integer oldAgentAddrId : addrs.keySet()) { String adrSQL = String.format(addrSQL, oldAgentAddrId); rs = stmt.executeQuery(adrSQL); if (!rs.next()) { rs.close(); continue; } String lastEditedBy = rs.getString(14); String posHeld = rs.getString(15); if (posHeld != null && posHeld.length() > 32) { posHeld = posHeld.substring(0, 32); } String addr1 = rs.getString(2); String addr2 = null; if (addr1 != null && addr1.length() > 255) { addr1 = addr1.substring(0, 255); addr2 = addr1.substring(255); } pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setString(2, addr1); pStmt.setString(3, addr2); // Address 2 pStmt.setString(4, rs.getString(3)); pStmt.setString(5, rs.getString(4)); pStmt.setString(6, rs.getString(5)); pStmt.setString(7, rs.getString(6)); pStmt.setString(8, rs.getString(7)); pStmt.setTimestamp(9, rs.getTimestamp(8)); pStmt.setBoolean(10, rs.getByte(13) != 0); pStmt.setBoolean(11, rs.getByte(13) != 0); pStmt.setString(12, rs.getString(9)); pStmt.setString(13, rs.getString(10)); pStmt.setString(14, rs.getString(11)); pStmt.setString(15, rs.getString(12)); pStmt.setString(16, posHeld); pStmt.setInt(17, agentInfo.getNewAgentId()); pStmt.setInt(18, conv.getCreatorAgentIdForAgent(lastEditedBy)); pStmt.setInt(19, conv.getModifiedByAgentIdForAgent(lastEditedBy)); pStmt.setInt(20, 0); pStmt.setInt(21, agentInfo.addrOrd); Integer newID = BasicSQLUtils.getInsertedId(pStmt); log.debug(String.format("Saved New Id %d", newID)); //agentInfo.addWrittenAddrOldId(addrInfo.getOldAddrId()); agentInfo.addrOrd++; rs.close(); try { if (debugAgents) { log.info(sqlStr1.toString()); } if (pStmt.executeUpdate() != 1) { log.error("Error inserting address.)"); } //addrInfo.setWasAdded(true); } catch (SQLException e) { log.error(sqlStr1.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } } log.info(String.format("Added %d new Addresses", fixCnt)); pStmt.close(); //------------------------------------------------------------------ // Step #2 - Now duplicate the addresses for the agents that had // already been written to the database //------------------------------------------------------------------ /*fixCnt = 0; for (AgentInfo agentInfo : agentHash.values()) { for (Integer oldAgentAddrId : agentInfo.getUnwrittenOldAddrIds()) { Integer oldAddrId = agentInfo.getAddrs().get(oldAgentAddrId); //AddressInfo addrInfo = addressHash.get(oldAddrId); System.out.println(String.format("%d %d", oldAgentAddrId, oldAddrId)); //duplicateAddress(newDBConn, addrInfo.getOldAddrId(), addrInfo.getNewAddrId(), agentInfo.getNewAgentId()); } } log.info(String.format("Duplicated %d new Addresses", fixCnt)); */ //---------------------------------------------------------------------------------------------------------------------------------- // Now loop through the Agents hash and write the addresses. If the address has already been written then it will need to be // duplicate in the second step. //---------------------------------------------------------------------------------------------------------------------------------- /*BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); sqlStr1 = new StringBuilder("INSERT INTO address "); sqlStr1.append("(TimestampModified, Address, Address2, City, State, Country, PostalCode, Remarks, TimestampCreated, "); sqlStr1.append("IsPrimary, IsCurrent, Phone1, Phone2, Fax, RoomOrBuilding, AgentID, CreatedByAgentID, ModifiedByAgentID, Version, Ordinal)"); sqlStr1.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); pStmt = newDBConn.prepareStatement(sqlStr1.toString()); // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 String addrOnlySQL = "SELECT aa.TimestampModified, a.Address, a.City, a.State, a.Country, a.Postalcode, a.Remarks, aa.TimestampCreated, aa.Phone1, aa.Phone2, aa.Fax, aa.RoomOrBuilding , aa.IsCurrent, a.LastEditedBy, aa.AgentID " + "FROM agentaddress AS aa " + "LEFT JOIN address AS a ON a.AddressID = aa.AddressID " + "WHERE a.addressID IS NULL AND aa.AgentID IS NOT NULL"; fixCnt = 0; rs = stmt.executeQuery(addrOnlySQL); while (rs.next()) { int agentId = rs.getInt(15); int newAgentId = agentIDMapper.get(agentId); String lastEditedBy = rs.getString(14); pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setString(2, rs.getString(2)); pStmt.setString(3, null); // Address 2 pStmt.setString(4, rs.getString(3)); pStmt.setString(5, rs.getString(4)); pStmt.setString(6, rs.getString(5)); pStmt.setString(7, rs.getString(6)); pStmt.setString(8, rs.getString(7)); pStmt.setTimestamp(9, rs.getTimestamp(8)); pStmt.setBoolean(10, rs.getByte(13) != 0); pStmt.setBoolean(11, rs.getByte(13) != 0); pStmt.setString(12, rs.getString(9)); pStmt.setString(13, rs.getString(10)); pStmt.setString(14, rs.getString(11)); pStmt.setString(15, rs.getString(12)); pStmt.setInt(16, newAgentId); pStmt.setInt(17, conv.getCreatorAgentIdForAgent(lastEditedBy)); pStmt.setInt(18, conv.getModifiedByAgentIdForAgent(lastEditedBy)); pStmt.setInt(19, 0); pStmt.setInt(20, 1); try { if (debugAgents) { log.info(sqlStr1.toString()); } if (pStmt.executeUpdate() != 1) { log.error("Error inserting address.)"); } else { fixCnt++; } } catch (SQLException e) { log.error(sqlStr1.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } rs.close(); log.info(String.format("Added %d new Addresses", fixCnt)); pStmt.close();*/ stmt.close(); //dumpInfo("afterInfo.txt", addressHash); BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); return true; } catch (SQLException ex) { log.error(ex); ex.printStackTrace(); System.exit(0); throw new RuntimeException(ex); } }
From source file:dk.netarkivet.harvester.datamodel.RunningJobsInfoDBDAO.java
/** * Stores a {@link StartedJobInfo} record to the persistent storage. * The record is stored in the monitor table, and if the elapsed time since * the last history sample is equal or superior to the history sample rate, * also to the history table./* w w w .j a v a 2s . co m*/ * @param startedJobInfo the record to store. */ @Override public synchronized void store(StartedJobInfo startedJobInfo) { ArgumentNotValid.checkNotNull(startedJobInfo, "StartedJobInfo startedJobInfo"); Connection c = HarvestDBConnection.get(); try { PreparedStatement stm = null; // First is there a record in the monitor table? boolean update = false; try { stm = c.prepareStatement( "SELECT jobId FROM runningJobsMonitor" + " WHERE jobId=? AND harvestName=?"); stm.setLong(1, startedJobInfo.getJobId()); stm.setString(2, startedJobInfo.getHarvestName()); // One row expected, as per PK definition update = stm.executeQuery().next(); } catch (SQLException e) { String message = "SQL error checking running jobs monitor table" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } try { // Update or insert latest progress information for this job c.setAutoCommit(false); StringBuffer sql = new StringBuffer(); if (update) { sql.append("UPDATE runningJobsMonitor SET "); StringBuffer columns = new StringBuffer(); for (HM_COLUMN setCol : HM_COLUMN.values()) { columns.append(setCol.name() + "=?, "); } sql.append(columns.substring(0, columns.lastIndexOf(","))); sql.append(" WHERE jobId=? AND harvestName=?"); } else { sql.append("INSERT INTO runningJobsMonitor ("); sql.append(HM_COLUMN.getColumnsInOrder()); sql.append(") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); } stm = c.prepareStatement(sql.toString()); stm.setLong(HM_COLUMN.jobId.rank(), startedJobInfo.getJobId()); stm.setString(HM_COLUMN.harvestName.rank(), startedJobInfo.getHarvestName()); stm.setLong(HM_COLUMN.elapsedSeconds.rank(), startedJobInfo.getElapsedSeconds()); stm.setString(HM_COLUMN.hostUrl.rank(), startedJobInfo.getHostUrl()); stm.setDouble(HM_COLUMN.progress.rank(), startedJobInfo.getProgress()); stm.setLong(HM_COLUMN.queuedFilesCount.rank(), startedJobInfo.getQueuedFilesCount()); stm.setLong(HM_COLUMN.totalQueuesCount.rank(), startedJobInfo.getTotalQueuesCount()); stm.setLong(HM_COLUMN.activeQueuesCount.rank(), startedJobInfo.getActiveQueuesCount()); stm.setLong(HM_COLUMN.retiredQueuesCount.rank(), startedJobInfo.getRetiredQueuesCount()); stm.setLong(HM_COLUMN.exhaustedQueuesCount.rank(), startedJobInfo.getExhaustedQueuesCount()); stm.setLong(HM_COLUMN.alertsCount.rank(), startedJobInfo.getAlertsCount()); stm.setLong(HM_COLUMN.downloadedFilesCount.rank(), startedJobInfo.getDownloadedFilesCount()); stm.setLong(HM_COLUMN.currentProcessedKBPerSec.rank(), startedJobInfo.getCurrentProcessedKBPerSec()); stm.setLong(HM_COLUMN.processedKBPerSec.rank(), startedJobInfo.getProcessedKBPerSec()); stm.setDouble(HM_COLUMN.currentProcessedDocsPerSec.rank(), startedJobInfo.getCurrentProcessedDocsPerSec()); stm.setDouble(HM_COLUMN.processedDocsPerSec.rank(), startedJobInfo.getProcessedDocsPerSec()); stm.setInt(HM_COLUMN.activeToeCount.rank(), startedJobInfo.getActiveToeCount()); stm.setInt(HM_COLUMN.status.rank(), startedJobInfo.getStatus().ordinal()); stm.setTimestamp(HM_COLUMN.tstamp.rank(), new Timestamp(startedJobInfo.getTimestamp().getTime())); if (update) { stm.setLong(HM_COLUMN.values().length + 1, startedJobInfo.getJobId()); stm.setString(HM_COLUMN.values().length + 2, startedJobInfo.getHarvestName()); } stm.executeUpdate(); c.commit(); } catch (SQLException e) { String message = "SQL error storing started job info " + startedJobInfo + " in monitor table" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.closeStatementIfOpen(stm); DBUtils.rollbackIfNeeded(c, "store started job info", startedJobInfo); } // Should we store an history record? Long lastHistoryStore = lastSampleDateByJobId.get(startedJobInfo.getJobId()); long time = System.currentTimeMillis(); boolean shouldSample = lastHistoryStore == null || time >= lastHistoryStore + HISTORY_SAMPLE_RATE; if (!shouldSample) { return; // we're done } try { c.setAutoCommit(false); stm = c.prepareStatement("INSERT INTO runningJobsHistory (" + HM_COLUMN.getColumnsInOrder() + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); stm.setLong(HM_COLUMN.jobId.rank(), startedJobInfo.getJobId()); stm.setString(HM_COLUMN.harvestName.rank(), startedJobInfo.getHarvestName()); stm.setLong(HM_COLUMN.elapsedSeconds.rank(), startedJobInfo.getElapsedSeconds()); stm.setString(HM_COLUMN.hostUrl.rank(), startedJobInfo.getHostUrl()); stm.setDouble(HM_COLUMN.progress.rank(), startedJobInfo.getProgress()); stm.setLong(HM_COLUMN.queuedFilesCount.rank(), startedJobInfo.getQueuedFilesCount()); stm.setLong(HM_COLUMN.totalQueuesCount.rank(), startedJobInfo.getTotalQueuesCount()); stm.setLong(HM_COLUMN.activeQueuesCount.rank(), startedJobInfo.getActiveQueuesCount()); stm.setLong(HM_COLUMN.retiredQueuesCount.rank(), startedJobInfo.getRetiredQueuesCount()); stm.setLong(HM_COLUMN.exhaustedQueuesCount.rank(), startedJobInfo.getExhaustedQueuesCount()); stm.setLong(HM_COLUMN.alertsCount.rank(), startedJobInfo.getAlertsCount()); stm.setLong(HM_COLUMN.downloadedFilesCount.rank(), startedJobInfo.getDownloadedFilesCount()); stm.setLong(HM_COLUMN.currentProcessedKBPerSec.rank(), startedJobInfo.getCurrentProcessedKBPerSec()); stm.setLong(HM_COLUMN.processedKBPerSec.rank(), startedJobInfo.getProcessedKBPerSec()); stm.setDouble(HM_COLUMN.currentProcessedDocsPerSec.rank(), startedJobInfo.getCurrentProcessedDocsPerSec()); stm.setDouble(HM_COLUMN.processedDocsPerSec.rank(), startedJobInfo.getProcessedDocsPerSec()); stm.setInt(HM_COLUMN.activeToeCount.rank(), startedJobInfo.getActiveToeCount()); stm.setInt(HM_COLUMN.status.rank(), startedJobInfo.getStatus().ordinal()); stm.setTimestamp(HM_COLUMN.tstamp.rank(), new Timestamp(startedJobInfo.getTimestamp().getTime())); stm.executeUpdate(); c.commit(); } catch (SQLException e) { String message = "SQL error storing started job info " + startedJobInfo + " in history table" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.closeStatementIfOpen(stm); DBUtils.rollbackIfNeeded(c, "store started job info", startedJobInfo); } // Remember last sampling date lastSampleDateByJobId.put(startedJobInfo.getJobId(), time); } finally { HarvestDBConnection.release(c); } }
From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java
@Override public void storeMetaData(ConnectorMessage connectorMessage, List<MetaDataColumn> metaDataColumns) { logger.debug(connectorMessage.getChannelId() + "/" + connectorMessage.getMessageId() + "/" + connectorMessage.getMetaDataId() + ": updating custom meta data"); PreparedStatement statement = null; try {//from w w w. j a va 2s . c o m List<String> metaDataColumnNames = new ArrayList<String>(); Map<String, Object> metaDataMap = connectorMessage.getMetaDataMap(); for (MetaDataColumn metaDataColumn : metaDataColumns) { Object value = metaDataMap.get(metaDataColumn.getName()); if (value != null) { metaDataColumnNames.add(metaDataColumn.getName()); } } // Don't do anything if all values were null if (!metaDataColumnNames.isEmpty()) { Map<String, Object> values = new HashMap<String, Object>(); values.put("localChannelId", getLocalChannelId(connectorMessage.getChannelId())); values.put("metaDataColumnPlaceholders", quoteChar + StringUtils.join(metaDataColumnNames, quoteChar + " = ?, " + quoteChar) + quoteChar + " = ?"); statement = connection.prepareStatement(querySource.getQuery("storeMetaData", values)); int n = 1; for (MetaDataColumn metaDataColumn : metaDataColumns) { Object value = metaDataMap.get(metaDataColumn.getName()); if (value != null) { // @formatter:off switch (metaDataColumn.getType()) { case STRING: statement.setString(n, (String) value); break; case NUMBER: statement.setBigDecimal(n, (BigDecimal) value); break; case BOOLEAN: statement.setBoolean(n, (Boolean) value); break; case TIMESTAMP: statement.setTimestamp(n, new Timestamp(((Calendar) value).getTimeInMillis())); break; } // @formatter:on n++; } } statement.setLong(n++, connectorMessage.getMessageId()); statement.setInt(n, connectorMessage.getMetaDataId()); statement.executeUpdate(); } } catch (Exception e) { throw new DonkeyDaoException("Failed to update connector message meta data", e); } finally { close(statement); } }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import data from a zip archive to a database table * * @param stmt statement to use * @param zip zip archive containing the zip entry * @param ze zip entry within the archive * @param xpath xpath containing the entries to import * @param table name of the table * @param executeInsertPhase execute the insert phase? * @param executeUpdatePhase execute the update phase? * @param updateColumns columns that should be set to <code>null</code> in a first pass (insert) * and updated to the provided values in a second pass (update), * columns that should be used in the where clause have to be prefixed * with "KEY:", to assign a default value use the expression "columnname:default value", * if the default value is "@", it will be a negative counter starting at 0, decreasing. * If the default value starts with "%", it will be set to the column following the "%" * character in the first pass * @throws Exception on errors//from w w w . j a v a2 s.c om */ protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath, final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase, final String... updateColumns) throws Exception { //analyze the table final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2"); StringBuilder sbInsert = new StringBuilder(500); StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null; if (rs == null) throw new IllegalArgumentException("Can not analyze table [" + table + "]!"); sbInsert.append("INSERT INTO ").append(table).append(" ("); final ResultSetMetaData md = rs.getMetaData(); final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0 ? new HashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0 ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; //preset to a referenced column (%column syntax) final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null; final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>( md.getColumnCount() + (counters != null ? counters.size() : 0)); int insertIndex = 1; int updateSetIndex = 1; int updateClauseIndex = 1; boolean first = true; for (int i = 0; i < md.getColumnCount(); i++) { final String currCol = md.getColumnName(i + 1).toLowerCase(); if (updateColumns.length > 0) { boolean abort = false; for (String col : updateColumns) { if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) { String value = col.substring(col.indexOf(':') + 1); col = col.substring(0, col.indexOf(':')); if ("@".equals(value)) { if (currCol.equalsIgnoreCase(col)) { counters.put(col, 0); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); } } else if (value.startsWith("%")) { if (currCol.equalsIgnoreCase(col)) { presetRefColumns.put(col, value.substring(1)); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); // System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1)); } } else if (!presetColumns.containsKey(col)) presetColumns.put(col, value); } if (currCol.equalsIgnoreCase(col)) { abort = true; updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++)); break; } } if (abort) continue; } if (first) { first = false; } else sbInsert.append(','); sbInsert.append(currCol); insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); } if (updateColumns.length > 0 && executeUpdatePhase) { sbUpdate.append("UPDATE ").append(table).append(" SET "); int counter = 0; for (String updateColumn : updateSetColumns.keySet()) { if (counter++ > 0) sbUpdate.append(','); sbUpdate.append(updateColumn).append("=?"); } sbUpdate.append(" WHERE "); boolean hasKeyColumn = false; for (String col : updateColumns) { if (!col.startsWith("KEY:")) continue; hasKeyColumn = true; String keyCol = col.substring(4); for (int i = 0; i < md.getColumnCount(); i++) { if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol)) continue; updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++)); sbUpdate.append(keyCol).append("=? AND "); break; } } if (!hasKeyColumn) throw new IllegalArgumentException("Update columns require a KEY!"); sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND " //"shift" clause indices for (String col : updateClauseColumns.keySet()) { GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col); ci.index += (updateSetIndex - 1); } } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(key); } sbInsert.append(")VALUES("); for (int i = 0; i < insertColumns.size(); i++) { if (i > 0) sbInsert.append(','); sbInsert.append('?'); } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(presetColumns.get(key)); } sbInsert.append(')'); if (DBG) { LOG.info("Insert statement:\n" + sbInsert.toString()); if (updateColumns.length > 0) LOG.info("Update statement:\n" + sbUpdate.toString()); } //build a map containing all nodes that require attributes //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data" final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5); for (String pElem : xpath.split("/")) { if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0)) continue; List<String> att = new ArrayList<String>(5); for (String pAtt : pElem.split("@")) { if (!(pAtt.indexOf('=') > 0)) continue; att.add(pAtt.substring(0, pAtt.indexOf('='))); } queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att); } final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString()); final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase ? stmt.getConnection().prepareStatement(sbUpdate.toString()) : null; try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; int counter; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); boolean insertMode = true; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { counter = 0; inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; } /** * {@inheritDoc} */ @Override public void processingInstruction(String target, String data) throws SAXException { if (target != null && target.startsWith("fx_")) { if (target.equals("fx_mode")) insertMode = "insert".equals(data); } else super.processingInstruction(target, data); } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { if (insertMode) LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath + "]"); else LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpath)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ private void pushPath(String qName, Attributes att) { if (att.getLength() > 0 && queryAttributes.containsKey(qName)) { String curr = qName + "["; boolean first = true; final List<String> attList = queryAttributes.get(qName); for (int i = 0; i < att.getLength(); i++) { if (!attList.contains(att.getQName(i))) continue; if (first) first = false; else curr += ','; curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'"; } curr += ']'; path.add(curr); } else path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpath)) { if (DBG) LOG.info("Insert [" + xpath + "]: [" + data + "]"); inTag = false; try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); } } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } /** * Process a column set * * @param columns the columns to process * @param ps prepared statement to use * @return if data other than <code>null</code> has been set * @throws SQLException on errors * @throws ParseException on date/time conversion errors */ private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps) throws SQLException, ParseException { boolean dataSet = false; for (String col : columns.keySet()) { ColumnInfo ci = columns.get(col); String value = data.get(col); if (insertMode && counters != null && counters.get(col) != null) { final int newVal = counters.get(col) - 1; value = String.valueOf(newVal); counters.put(col, newVal); // System.out.println("new value for " + col + ": " + newVal); } if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) { value = data.get(presetRefColumns.get(col)); // System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]"); } if (value == null) ps.setNull(ci.index, ci.columnType); else { dataSet = true; switch (ci.columnType) { case Types.BIGINT: case Types.NUMERIC: if (DBG) LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value)); ps.setBigDecimal(ci.index, new BigDecimal(value)); break; case java.sql.Types.DOUBLE: if (DBG) LOG.info("Double " + ci.index + "->" + Double.parseDouble(value)); ps.setDouble(ci.index, Double.parseDouble(value)); break; case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (DBG) LOG.info("Float " + ci.index + "->" + Float.parseFloat(value)); ps.setFloat(ci.index, Float.parseFloat(value)); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (DBG) LOG.info("Timestamp/Date " + ci.index + "->" + FxFormatUtils.getDateTimeFormat().parse(value)); ps.setTimestamp(ci.index, new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime())); break; case Types.TINYINT: case Types.SMALLINT: if (DBG) LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value)); ps.setInt(ci.index, Integer.valueOf(value)); break; case Types.INTEGER: case Types.DECIMAL: try { if (DBG) LOG.info("Long " + ci.index + "->" + Long.valueOf(value)); ps.setLong(ci.index, Long.valueOf(value)); } catch (NumberFormatException e) { //Fallback (temporary) for H2 if the reported long is a big decimal (tree...) ps.setBigDecimal(ci.index, new BigDecimal(value)); } break; case Types.BIT: case Types.CHAR: case Types.BOOLEAN: if (DBG) LOG.info("Boolean " + ci.index + "->" + value); if ("1".equals(value) || "true".equals(value)) ps.setBoolean(ci.index, true); else ps.setBoolean(ci.index, false); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: case Types.BINARY: ZipEntry bin = zip.getEntry(value); if (bin == null) { LOG.error("Failed to lookup binary [" + value + "]!"); ps.setNull(ci.index, ci.columnType); break; } try { ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize()); } catch (IOException e) { LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e); } break; case Types.CLOB: case Types.LONGVARCHAR: case Types.VARCHAR: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: if (DBG) LOG.info("String " + ci.index + "->" + value); ps.setString(ci.index, value); break; default: LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]"); } } } return dataSet; } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; handler.processingInstruction("fx_mode", "insert"); parser.parse(zip.getInputStream(ze), handler); if (updateColumns.length > 0 && executeUpdatePhase) { handler.processingInstruction("fx_mode", "update"); parser.parse(zip.getInputStream(ze), handler); } } finally { Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate); } }