List of usage examples for java.sql PreparedStatement setObject
void setObject(int parameterIndex, Object x) throws SQLException;
Sets the value of the designated parameter using the given object.
From source file:org.apache.cocoon.util.JDBCTypeConversions.java
/** * Set the Statement column so that the results are mapped correctly. * * @param statement the prepared statement * @param position the position of the column * @param value the value of the column/*from w w w .ja va 2 s .c om*/ */ public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception { if (value instanceof String) { value = ((String) value).trim(); } if (typeObject == null) { throw new SQLException("Can't set column because the type is unrecognized"); } if (value == null) { /** If the value is null, set the column value null and return **/ statement.setNull(position, typeObject.intValue()); return; } if ("".equals(value)) { switch (typeObject.intValue()) { case Types.CHAR: case Types.CLOB: case Types.VARCHAR: /** If the value is an empty string and the column is a string type, we can continue **/ break; default: /** If the value is an empty string and the column is something else, we treat it as a null value **/ statement.setNull(position, typeObject.intValue()); return; } } File file = null; int length = -1; InputStream asciiStream = null; //System.out.println("========================================================================"); //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue()); switch (typeObject.intValue()) { case Types.CLOB: //System.out.println("CLOB"); Clob clob = null; if (value instanceof Clob) { clob = (Clob) value; } else if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; clob = new ClobHelper(asciiStream, length); } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new ByteArrayInputStream(asciiText.getBytes()); length = asciiText.length(); clob = new ClobHelper(asciiStream, length); } statement.setClob(position, clob); break; case Types.CHAR: // simple large object, e.g. Informix's TEXT //System.out.println("CHAR"); if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes())); length = asciiText.length(); } statement.setAsciiStream(position, asciiStream, length); break; case Types.BIGINT: //System.out.println("BIGINT"); BigDecimal bd = null; if (value instanceof BigDecimal) { bd = (BigDecimal) value; } else if (value instanceof Number) { bd = BigDecimal.valueOf(((Number) value).longValue()); } else { bd = new BigDecimal(value.toString()); } statement.setBigDecimal(position, bd); break; case Types.TINYINT: //System.out.println("TINYINT"); Byte b = null; if (value instanceof Byte) { b = (Byte) value; } else if (value instanceof Number) { b = new Byte(((Number) value).byteValue()); } else { b = new Byte(value.toString()); } statement.setByte(position, b.byteValue()); break; case Types.DATE: //System.out.println("DATE"); Date d = null; if (value instanceof Date) { d = (Date) value; } else if (value instanceof java.util.Date) { d = new Date(((java.util.Date) value).getTime()); } else if (value instanceof Calendar) { d = new Date(((Calendar) value).getTime().getTime()); } else { d = Date.valueOf(value.toString()); } statement.setDate(position, d); break; case Types.DOUBLE: //System.out.println("DOUBLE"); double db; if (value instanceof Number) { db = (((Number) value).doubleValue()); } else { db = Double.parseDouble(value.toString()); } statement.setDouble(position, db); break; case Types.FLOAT: //System.out.println("FLOAT"); float f; if (value instanceof Number) { f = (((Number) value).floatValue()); } else { f = Float.parseFloat(value.toString()); } statement.setFloat(position, f); break; case Types.NUMERIC: //System.out.println("NUMERIC"); long l; if (value instanceof Number) { l = (((Number) value).longValue()); } else { l = Long.parseLong(value.toString()); } statement.setLong(position, l); break; case Types.SMALLINT: //System.out.println("SMALLINT"); Short s = null; if (value instanceof Short) { s = (Short) value; } else if (value instanceof Number) { s = new Short(((Number) value).shortValue()); } else { s = new Short(value.toString()); } statement.setShort(position, s.shortValue()); break; case Types.TIME: //System.out.println("TIME"); Time t = null; if (value instanceof Time) { t = (Time) value; } else if (value instanceof java.util.Date) { t = new Time(((java.util.Date) value).getTime()); } else { t = Time.valueOf(value.toString()); } statement.setTime(position, t); break; case Types.TIMESTAMP: //System.out.println("TIMESTAMP"); Timestamp ts = null; if (value instanceof Time) { ts = (Timestamp) value; } else if (value instanceof java.util.Date) { ts = new Timestamp(((java.util.Date) value).getTime()); } else { ts = Timestamp.valueOf(value.toString()); } statement.setTimestamp(position, ts); break; case Types.ARRAY: //System.out.println("ARRAY"); statement.setArray(position, (Array) value); // no way to convert string to array break; case Types.STRUCT: //System.out.println("STRUCT"); case Types.OTHER: //System.out.println("OTHER"); statement.setObject(position, value); break; case Types.LONGVARBINARY: //System.out.println("LONGVARBINARY"); statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime())); break; case Types.VARCHAR: //System.out.println("VARCHAR"); statement.setString(position, value.toString()); break; case Types.BLOB: //System.out.println("BLOB"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else { Blob blob = null; if (value instanceof Blob) { blob = (Blob) value; } else if (value instanceof File) { file = (File) value; blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof String) { file = new File((String) value); blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof Part) { Part anyFile = (Part) value; blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize()); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); statement.setBlob(position, blob); } break; case Types.VARBINARY: //System.out.println("VARBINARY"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else if (value instanceof Part) { statement.setBinaryStream(position, ((Part) value).getInputStream(), ((Part) value).getSize()); } else { if (value instanceof File) { file = (File) value; } else if (value instanceof String) { file = new File((String) value); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); FileInputStream input = new FileInputStream(file); statement.setBinaryStream(position, input, (int) file.length()); } break; case Types.INTEGER: //System.out.println("INTEGER"); Integer i = null; if (value instanceof Integer) { i = (Integer) value; } else if (value instanceof Number) { i = new Integer(((Number) value).intValue()); } else { i = new Integer(value.toString()); } statement.setInt(position, i.intValue()); break; case Types.BIT: //System.out.println("BIT"); Boolean bo = null; if (value instanceof Boolean) { bo = (Boolean) value; } else if (value instanceof Number) { bo = BooleanUtils.toBooleanObject(((Number) value).intValue() == 1); } else { bo = BooleanUtils.toBooleanObject(value.toString()); } statement.setBoolean(position, bo.booleanValue()); break; default: //System.out.println("default"); throw new SQLException("Impossible exception - invalid type "); } //System.out.println("========================================================================"); }
From source file:com.pari.nm.utils.db.InventoryDBHelper.java
private static void insertClisOptimized(int nodeId, String[] clis, IDSPDeviceInfoProvider device, JobParameters jobParameters) {/*from w w w. j av a2 s . co m*/ Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)"); for (String cli : clis) { DSPCLICommandOutput output = device.getCLI(cli); if (output != null) { String outputText = output.getOutput(); if (outputText == null) { outputText = output.getErrors(); } if (outputText == null) { continue; } if (outputText.startsWith(cli)) { outputText = outputText.substring(cli.length()); } ps.clearParameters(); ps.setInt(1, nodeId); ps.setString(2, cli); ps.setObject(3, CompressionUtils.getCompressedString(outputText)); ps.executeUpdate(); CommandResult result = new CommandResult(); result.setOperation(Operation.UPDATE); result.setOperatedBy(jobParameters.getUserName()); result.setJobId(jobParameters.getParentJobId()); result.setInventoryJobId(jobParameters.getJobId()); result.setCli(cli); result.setDeviceId((long) nodeId); result.setOutput(CompressionUtils.getCompressedString(outputText)); HistoryManager.getInstance().createRecord(result); } } } catch (Exception ex) { logger.error("Exception while saving CLIs for node: " + nodeId, ex); } finally { if (ps != null) { try { ps.close(); } catch (Throwable t) { } } if (c != null) { DBHelper.releaseConnection(c); } } }
From source file:com.pari.nm.utils.db.InventoryDBHelper.java
public static boolean migrateShowCommandsInternal(Set<String> toBeRemoved) throws Exception { Connection c = null;/*from w w w . j av a 2 s . co m*/ ResultSet rs = null; PreparedStatement ps = null; int curNodeId = -1; String curCli = null; StringBuilder curOp = new StringBuilder(); // logger.info("Looking to see if we need to migrate from old show command tables to new one."); boolean deleteAll = true; try { c = DBHelper.getConnection(); ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)"); rs = DBHelper.executeQuery("select * from show_commands order by device_id,cli,output_part"); int count = 0; while (rs != null && rs.next()) { int newDev = rs.getInt("device_id"); String newCli = rs.getString("cli"); if (newDev != curNodeId || !newCli.equals(curCli)) { try { ps.setInt(1, curNodeId); ps.setString(2, curCli); ps.setObject(3, curOp.toString().getBytes()); curOp = new StringBuilder(); ps.executeUpdate(); toBeRemoved.add(curNodeId + ":" + curCli); } catch (Exception ex) { deleteAll = false; logger.trace("Exception while inserting into new table", ex); } } curOp.append(rs.getString("out_put")); curNodeId = newDev; curCli = newCli; count++; } logger.info("Done migrating from old show_commands table to new table. Migrated " + count + " rows"); } catch (Exception ee) { deleteAll = false; throw ee; } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { } finally { try { if (ps != null) { ps.close(); } } catch (Exception ex) { } finally { try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception ex) { } } } } return deleteAll; }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public boolean getPopulatedDataAndInsert(EDI322Bean eb, String boescUserId, String userType, File file, List<String> finalErrorList, Map<Integer, Object> fileTransStatus) throws Exception { logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId + " userType ::" + userType); String status = ""; logger.info("----- ISA ............"); String gsHeader = ""; String gsControl = ""; String st_control = ""; String n7Header = ""; String q5Header = ""; String equip_prefix = ""; String equip_number = ""; String w2Header = ""; String r4Header = ""; String r13Header = ""; String n9Header = ""; String eventType = ""; String port_qual = ""; String iana_splc = ""; QueryRunner qrun = new QueryRunner(getDataSource()); logger.info("----- GE ............"); for (int i = 0; i < eb.getListGSDetails().size(); i++) { gsHeader = eb.getListGSDetails().get(i).getHeaderDetails(); gsControl = eb.getListGSDetails().get(i).getGroupControlNumber(); logger.info("gsControl ::" + gsControl + " gsHeader ::" + gsHeader); int startIndex = i + 1; logger.info("----- ST & SE ............"); for (int a = 0; a < eb.getSTDetailsMap().get(startIndex).size(); a++) { Connection conn = getConnection(); conn.setAutoCommit(false);/*from w w w .j av a2s . c o m*/ PreparedStatement pstmt = null; ResultSet rs = null; StringBuilder sbQuery = new StringBuilder( "INSERT INTO BOESC_TRAN_SET (ISA_HEADER, GS_HEADER, INPUT_TYPE, SENDER_ID, SENDER_TYPE, "); sbQuery.append( " ISA_DATETIME, GS_CONTROL, ST_CONTROL, EVENT_TYPE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, IEP_DOT, PORT_QUAL, IANA_SPLC, "); sbQuery.append(" POOL_ID, POOL_NAME, Q5_SEG, N7_SEG, W2_SEG, R4_SEG, N9_SEG, R13_SEG, "); if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) { sbQuery.append(" RECEIVER_ID, REC_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) { sbQuery.append(" MRV_ID, MRV_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) { sbQuery.append(" FO_ID, FO_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_SU)) { } try { status = ""; int changedIndex = a + 1; //very important Variable if (fileTransStatus != null && fileTransStatus.size() > 0) { logger.info("-------------------- changedIndex ::" + changedIndex + " fileTransStatus.get(startIndex) ::" + fileTransStatus.get(changedIndex)); if (fileTransStatus.get(changedIndex) == null) { status = GlobalVariables.STATUS_PENDING; } else { status = GlobalVariables.STATUS_REJECTED; } } else { status = GlobalVariables.STATUS_PENDING; } r13Header = ""; r4Header = ""; n9Header = ""; port_qual = ""; iana_splc = ""; GIERInfoDetails gierInfo = null; st_control = eb.getSTDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber(); logger.info(" st_control :" + st_control); /*String transactionControlNumberSE = eb.getSEDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber(); logger.info(" transactionControlNumberSE :"+transactionControlNumberSE );*/ logger.info("----- N7 ............"); for (int q = 0; q < eb.getN7DetailsMap().get(startIndex).get(changedIndex).size(); q++) { n7Header = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); logger.info("n7Header ::" + n7Header); equip_prefix = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q) .getEquipmentInitial(); equip_number = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q) .getEquipmentNumber(); logger.info("equip_prefix ::" + equip_prefix); logger.info("equip_number ::" + equip_number); equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix; equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number; gierInfo = getDVIRAdditionaldetails(equip_prefix, equip_number); //logger.info("gierInfo ::"+gierInfo); } logger.info("----- Q5 ............"); for (int q = 0; q < eb.getQ5DetailsMap().get(startIndex).get(changedIndex).size(); q++) { q5Header = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); eventType = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getStatusCode(); logger.info("q5Header ::" + q5Header + " eventType ::" + eventType); } logger.info("----- W2 ............"); for (int q = 0; q < eb.getW2DetailsMap().get(startIndex).get(changedIndex).size(); q++) { w2Header = eb.getW2DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); logger.info("w2Header ::" + w2Header); } logger.info("----- R4 ............"); String tempR4Header = ""; String tempPort_qual = ""; String tempIana_splc = ""; for (int q = 0; q < eb.getR4DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempR4Header = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); tempPort_qual = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getLocationQualifier(); tempIana_splc = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getLocationIdentifier(); r4Header = r4Header + GlobalVariables.FIELD_SEPARATOR + tempR4Header; port_qual = port_qual + GlobalVariables.FIELD_SEPARATOR + tempPort_qual; iana_splc = iana_splc + GlobalVariables.FIELD_SEPARATOR + tempIana_splc; logger.info("r4Header ::" + r4Header + " port_qual:: " + port_qual + " iana_splc ::" + iana_splc); } r4Header = r4Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r4Header.substring(1) : r4Header; port_qual = port_qual.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? port_qual.substring(1) : port_qual; iana_splc = iana_splc.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? iana_splc.substring(1) : iana_splc; logger.info("----- R13 ............"); String tempR13Header = ""; for (int q = 0; q < eb.getR13DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempR13Header = eb.getR13DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); r13Header = r13Header + GlobalVariables.FIELD_SEPARATOR + tempR13Header; logger.info("r13Header ::" + r13Header); } r13Header = r13Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r13Header.substring(1) : r13Header; logger.info("----- N9 ............"); String tempN9Header = ""; for (int q = 0; q < eb.getN9DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempN9Header = eb.getN9DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); n9Header = n9Header + GlobalVariables.FIELD_SEPARATOR + tempN9Header; logger.info("n9Header ::" + n9Header); } n9Header = n9Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? n9Header.substring(1) : n9Header; sbQuery.append( " , CREATED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); if (gierInfo == null) { gierInfo = new GIERInfoDetails(); //this situation happen when all segment are missing except : ISA,SE,ST,GE,GS,IEA } equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix; equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number; pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, eb.getISADetails().getHeaderDetails()); pstmt.setString(2, gsHeader); pstmt.setString(3, GlobalVariables.INPUT_TYPE_BOESC_322); pstmt.setString(4, eb.getISADetails().getInterchangeSenderId()); pstmt.setString(5, userType); pstmt.setString(6, eb.getISADetails().getInterchangeDate()); pstmt.setString(7, gsControl); pstmt.setString(8, st_control); pstmt.setString(9, eventType); pstmt.setString(10, equip_prefix); pstmt.setString(11, equip_number); pstmt.setString(12, equip_prefix + equip_number); pstmt.setString(13, gierInfo.getCompanySCACCode() == null ? "" : gierInfo.getCompanySCACCode()); pstmt.setString(14, gierInfo.getUsDotNumber() == null ? "" : gierInfo.getUsDotNumber()); pstmt.setString(15, port_qual); pstmt.setString(16, iana_splc); pstmt.setString(17, gierInfo.getChassisPoolId() == null ? "" : gierInfo.getChassisPoolId()); pstmt.setString(18, gierInfo.getChassisPoolName() == null ? "" : gierInfo.getChassisPoolName()); pstmt.setString(19, q5Header); pstmt.setString(20, n7Header); pstmt.setString(21, w2Header); pstmt.setString(22, r4Header); pstmt.setString(23, n9Header); pstmt.setString(24, r13Header); pstmt.setString(25, boescUserId); pstmt.setString(26, status); pstmt.setObject(27, DateTimeFormater.getSqlSysTimestamp()); logger.info("query :: " + sbQuery.toString()); int dbStat = 0; int boescKey = 0; dbStat = pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); if (dbStat != 0) { if (rs != null) { while (rs.next()) { boescKey = rs.getInt(1); logger.info("boescKey: " + boescKey); } } conn.commit(); } else { conn.rollback(); } if (boescKey != 0) { //Update BOESC_UNIQUE_NO : using business logic String sql = "UPDATE BOESC_TRAN_SET SET BOESC_UNIQUE_NO = ? WHERE BOESC_TRAN_ID = ? "; qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(boescKey, "BOESC-"), boescKey }); logger.info("Record Inserted successfully for BOESC..." + file.getName()); return true; } else { logger.error("Failure Data insertion in BOESC.."); } } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex1) { logger.error("Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage()); ex1.printStackTrace(); throw ex1; } catch (Exception e) { logger.error("Caught SQL exception in finally block " + e.getMessage()); e.printStackTrace(); throw e; } } } } return false; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param oldTaxonomyTypeID/*from w ww .j av a 2 s.c om*/ * @param newTaxonomyTypeID * @param kingdom * @param curColObjTypeID * @param taxonTypeName */ private void duplicateTaxonTree(final int oldTaxonomyTypeID, final int newTaxonomyTypeID, final int kingdom, final int curColObjTypeID, final String taxonTypeName) { try { String dateStr = "2011-06-30 00:00:00"; String postFix = Integer.toString(oldTaxonomyTypeID); System.out.println("----------------------------------------"); System.out.println( String.format("Copying 'taxonomicunittype' Old TaxonomyTypeID %d to New TaxonomyTypeID %d\n", oldTaxonomyTypeID, newTaxonomyTypeID)); IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn); TableDuplicator taxonomicunittypeDup = new TableDuplicator(oldDBConn, "taxonomicunittype", "TaxonomicUnitTypeID", "TaxonomyTypeID = " + oldTaxonomyTypeID, "RankID", postFix); taxonomicunittypeDup.initialize(); taxonomicunittypeDup.duplicate(); TableDuplicator taxonnameDup = new TableDuplicator(oldDBConn, "taxonname", "TaxonNameID", "TaxonomyTypeID = " + oldTaxonomyTypeID, "RankID", postFix); taxonnameDup.initialize(); taxonnameDup.duplicate(); String sql = String.format( "INSERT INTO taxonomytype (TaxonomyTypeID, KingdomID, TaxonomyTypeName, TreeInfoUpToDate) VALUES(%d, %d, '%s', 1)", newTaxonomyTypeID, kingdom, taxonTypeName); System.out.println(sql); BasicSQLUtils.update(oldDBConn, sql); sql = String.format("UPDATE taxonomicunittype SET TaxonomyTypeID = %d WHERE TaxonomicUnitTypeID = ?", newTaxonomyTypeID); System.out.println(sql); PreparedStatement pStmt = oldDBConn.prepareStatement(sql); Statement stmt = oldDBConn.createStatement(); sql = "SELECT NewID FROM " + taxonomicunittypeDup.getMapperName(); System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { pStmt.setInt(1, rs.getInt(1)); System.out.println(String.format( "UPDATE taxonomicunittype SET TaxonomyTypeID = %d WHERE TaxonomicUnitTypeID = %d", newTaxonomyTypeID, rs.getInt(1))); pStmt.executeUpdate(); } rs.close(); pStmt.close(); int cnt = 0; pStmt = oldDBConn.prepareStatement(String .format("UPDATE taxonname SET TaxonomyTypeID = %d WHERE TaxonNameID = ?", newTaxonomyTypeID)); sql = "SELECT NewID FROM " + taxonnameDup.getMapperName(); System.out.println(sql); rs = stmt.executeQuery(sql); while (rs.next()) { pStmt.setInt(1, rs.getInt(1)); pStmt.executeUpdate(); cnt++; if (cnt % 2000 == 0) { System.out.println(cnt); break; } } rs.close(); pStmt.close(); IdMapperIFace taxonMapper = taxonnameDup.getMapper(); IdMapperIFace taxUnitTypeMapper = taxonomicunittypeDup.getMapper(); cnt = 0; pStmt = oldDBConn.prepareStatement( "UPDATE taxonname SET ParentTaxonNameID=?, AcceptedID=?, TaxonomicUnitTypeID=? WHERE TaxonNameID = ?"); rs = stmt.executeQuery( "SELECT TaxonNameID, ParentTaxonNameID, AcceptedID, TaxonomicUnitTypeID FROM taxonname WHERE TaxonomyTypeID = " + newTaxonomyTypeID); while (rs.next()) { int taxonId = rs.getInt(1); Integer parentId = rs.getInt(2); Integer acceptId = rs.getInt(3); Integer tutId = rs.getInt(4); if (parentId == null || parentId == 0) { pStmt.setObject(1, null); } else { Integer newId = taxonMapper.get(parentId); if (newId != null) { pStmt.setInt(1, newId); } else { pStmt.setObject(1, null); } } if (acceptId == null || acceptId == 0) { pStmt.setObject(2, null); } else { Integer newId = taxonMapper.get(acceptId); if (newId != null) { pStmt.setInt(2, newId); } else { pStmt.setObject(2, null); } } pStmt.setInt(3, taxUnitTypeMapper.get(tutId)); pStmt.setInt(4, taxonId); pStmt.executeUpdate(); cnt++; if (cnt % 2000 == 0) { System.out.println(cnt); break; } } rs.close(); pStmt.close(); stmt.close(); taxonomicunittypeDup.cleanup(); taxonnameDup.cleanup(); sql = String.format( "SELECT CollectionTaxonomyTypesID FROM collectiontaxonomytypes WHERE BiologicalObjectTypeID = %d AND TaxonomyTypeID = %d", curColObjTypeID, oldTaxonomyTypeID); System.out.println(sql); int collectiontaxonomytypesID = BasicSQLUtils.getCountAsInt(oldDBConn, sql); sql = String.format( "UPDATE collectiontaxonomytypes SET TaxonomyTypeID = %d WHERE CollectionTaxonomyTypesID = %d", newTaxonomyTypeID, collectiontaxonomytypesID); //sql = String.format("INSERT INTO collectiontaxonomytypes (CollectionTaxonomyTypesID,CollectionID,BiologicalObjectTypeID,TaxonomyTypeID,TimestampModified,TimestampCreated,LastEditedBy,DisplaySubSpecificTaxaLevelIndicators) " + // "VALUES(%d, %d, %d, %d, '%s', '%s', 'db', 1)", newColTaxTypeID, 0, newColObjTypeID, newTaxonomyTypeID, dateStr, dateStr); System.out.println(sql); BasicSQLUtils.update(oldDBConn, sql); } catch (SQLException ex) { ex.printStackTrace(); } }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the Determinations./*from w w w . j a va2 s . c om*/ * @return true if no errors */ public boolean convertDeterminationRecords() { TableWriter tblWriter = convLogger.getWriter("convertDeterminations.html", "Determinations"); setIdentityInsertONCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); // automatically closes the connection if (getNumRecords(oldDBConn, "determination") == 0) { return true; } TimeLogger timeLogger = new TimeLogger(); String oldDetermination_Current = "Current"; String oldDetermination_Date = "Date"; /*if (BasicSQLUtils.mySourceServerType == BasicSQLUtils.SERVERTYPE.MySQL) { oldDetermination_Date = "Date1"; oldDetermination_Current = "IsCurrent"; }*/ Map<String, String> colNewToOldMap = createFieldNameMap( new String[] { "CollectionObjectID", "BiologicalObjectID", // meg is this right? "IsCurrent", oldDetermination_Current, "DeterminedDate", oldDetermination_Date, // want to change over to DateField TODO Meg!!! "TaxonID", "TaxonNameID" }); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("SELECT "); List<String> names = getFieldNamesFromSchema(oldDBConn, "determination"); sql.append(buildSelectFieldList(names, "determination")); oldFieldNames.addAll(names); sql.append( ", cc.CatalogSeriesID AS CatSeriesID FROM determination Inner Join collectionobjectcatalog AS cc ON determination.BiologicalObjectID = cc.CollectionObjectCatalogID"); log.info(sql); if (BasicSQLUtils.mySourceServerType == BasicSQLUtils.SERVERTYPE.MS_SQLServer) { log.debug("FIXING select statement to run against SQL Server......."); log.debug("old string: " + sql.toString()); String currentSQL = sql.toString(); currentSQL = currentSQL.replaceAll("Current", "[" + "Current" + "]"); log.debug("new string: " + currentSQL); sql = new StringBuilder(currentSQL); } oldFieldNames.add("CatSeriesID"); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "determination"); log.info("Number of Fields in New Determination " + newFieldMetaData.size()); String sqlStr = sql.toString(); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); } String tableName = "determination"; //int isCurrentInx = oldNameIndex.get(oldDetermination_Current) + 1; log.info(sqlStr); System.err.println(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); if (hasFrame) { if (rs.last()) { setProcess(0, rs.getRow()); rs.first(); } else { rs.close(); stmt.close(); return true; } } else { if (!rs.first()) { rs.close(); stmt.close(); return true; } } PartialDateConv partialDateConv = new PartialDateConv(); IdMapperIFace detIdMapper = IdMapperMgr.getInstance().get("determination", "DeterminationID"); IdMapperIFace colObjIdMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID"); IdMapperIFace colObjCatIdMapper = idMapperMgr.get("collectionobject", "CollectionObjectID"); Integer catSeriesIdInx = oldNameIndex.get("CatSeriesID"); Integer oldRecIDInx = oldNameIndex.get("DeterminationID"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); Integer detDateInx = oldNameIndex.get("Date"); System.err.println("catSeriesIdInx: " + catSeriesIdInx); HashMap<String, Integer> nameToInxHash = new HashMap<String, Integer>(); StringBuffer fieldList = new StringBuffer(); StringBuilder insertQuesDB = new StringBuilder(); for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) { fieldList.append(','); insertQuesDB.append(','); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName); insertQuesDB.append('?'); nameToInxHash.put(newFieldName, (i + 1)); System.out.println(newFieldName + " " + (i + 1)); } String insertStmtStr = "INSERT INTO determination (" + fieldList + ") VALUES (" + insertQuesDB.toString() + ')'; log.debug(insertStmtStr); PreparedStatement pStmt = newDBConn.prepareStatement(insertStmtStr); int count = 0; do { partialDateConv.nullAll(); String lastEditedBy = rs.getString(lastEditedByInx); Integer catSeriesId = rs.getInt(catSeriesIdInx); if (catSeriesId != null && rs.wasNull()) { String msg = String.format( "Error - The Determination had a null CatalogSeries DeterminationID %d; it's CollectionObjectID: %d", rs.getInt(1), rs.getInt(6)); log.error(msg); tblWriter.logError(msg); //if (rs.next()) //{ continue; //} //break; } Vector<Integer> collectionIdList = catSeriesToNewCollectionID.get(catSeriesId); if (collectionIdList == null) { //Integer colObjId = rs.getInt(idIndex); throw new RuntimeException("There are no Collections mapped to CatSeriesId[" + catSeriesId + "] (converting Determinations)"); } if (collectionIdList.size() > 1) { UIRegistry.showError( "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now."); } Integer collectionId = collectionIdList.get(0); if (collectionId == null) { throw new RuntimeException("CollectionId is null when mapped from CatSeriesId"); } this.curCollectionID = collectionId; boolean isError = false; for (int i = 0; i < newFieldMetaData.size(); i++) { String newFieldName = newFieldMetaData.get(i).getName(); int fldInx = nameToInxHash.get(newFieldName); if (i == 0) { Integer recId = rs.getInt(oldRecIDInx); Integer newId = detIdMapper.get(recId); if (newId != null) { pStmt.setInt(fldInx, newId); } else { String msg = String.format("Error - Unable to map old id %d to new Id", recId); log.error(msg); tblWriter.logError(msg); isError = true; continue; } } else if (newFieldName.equals("Version")) // User/Security changes { pStmt.setInt(fldInx, 0); } else if (newFieldName.equals("DeterminedDate")) { //System.out.println("["+rs.getObject(detDateInx)+"]"); if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (!partialDateConv.isNull()) { int len = partialDateConv.getDateStr().length(); if (len == 12) { String tsStr = partialDateConv.getDateStr().length() == 12 ? partialDateConv.getDateStr().substring(1, 11) : partialDateConv.getDateStr(); pStmt.setString(fldInx, tsStr); } else { if (!partialDateConv.getDateStr().equals("NULL")) log.error("Determined Date was in error[" + partialDateConv.getDateStr() + "]"); pStmt.setObject(fldInx, null); } } else { pStmt.setObject(fldInx, null); } /* if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (isNotEmpty(partialDateConv.getDateStr())) { try { Date tsDate = sdf.parse(partialDateConv.getDateStr()); pStmt.setTimestamp(fldInx, new Timestamp(tsDate.getTime())); } catch (ParseException e) { e.printStackTrace(); pStmt.setObject(fldInx, null); } } else { pStmt.setObject(fldInx, null); } */ } else if (newFieldName.equals("DeterminedDatePrecision")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (partialDateConv.getPartial() != null) { if (partialDateConv.getPartial().length() > 1) { pStmt.setInt(fldInx, 1); } else { pStmt.setInt(fldInx, Integer.parseInt(partialDateConv.getPartial())); } } else { pStmt.setInt(fldInx, 1); } } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes { Integer agentId = getCreatorAgentId(null); pStmt.setInt(fldInx, agentId); } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes { Integer agentId = getModifiedByAgentId(lastEditedBy); pStmt.setInt(fldInx, agentId); } else if (newFieldName.equals("Qualifier") || newFieldName.equals("SubSpQualifier") || newFieldName.equals("VarQualifier") || newFieldName.equals("Addendum") || newFieldName.equals("AlternateName") || newFieldName.equals("NameUsage") || newFieldName.equals("GUID") || newFieldName.equals("PreferredTaxonID")) { pStmt.setObject(fldInx, null); } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes { pStmt.setInt(fldInx, getCollectionMemberId()); } else { Integer index = null; String oldMappedColName = colNewToOldMap.get(newFieldName); if (oldMappedColName != null) { index = oldNameIndex.get(oldMappedColName); } else { index = oldNameIndex.get(newFieldName); oldMappedColName = newFieldName; } Object data; if (index == null) { String msg = "convertDeterminationRecords - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // stmt.close(); // tblWriter.logError(msg); // throw new RuntimeException(msg); data = null; } else { data = rs.getObject(index); } if (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { Integer oldId = (Integer) data; IdMapperIFace idMapper; if (oldMappedColName.equals("BiologicalObjectID")) { data = colObjIdMapper.get(oldId); if (data == null) { data = colObjCatIdMapper.get(oldId); } } else { idMapper = idMapperMgr.get(tableName, oldMappedColName); if (idMapper != null) { data = idMapper.get(oldId); } else { String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]"; log.error(msg); tblWriter.logError(msg); isError = true; break; } } if (data == null) { String msg = "The determination with recordID[" + rs.getInt(oldRecIDInx) + "] could not find a mapping for record ID[" + oldId + "] for Old Field[" + oldMappedColName + "]"; log.debug(msg); tblWriter.logError(msg); tblWriter.log(ConvertVerifier.dumpSQL(oldDBConn, "SELECT * FROM determination WHERE DeterminationId = " + rs.getInt(oldRecIDInx))); if (isValueRequired(tableName, newFieldName)) { msg = "For table[" + tableName + "] the field [" + newFieldName + "] is null and can't be. Old value[" + oldId + "]"; log.error(msg); tblWriter.logError(msg); } isError = true; break; } } } //fixTimestamps(newFieldName, newFieldMetaData.get(i).getType(), data, str); FieldMetaData fldMetaData = newFieldMetaData.get(i); if (fldMetaData == null) { String msg = "For table[" + tableName + "] the field [" + newFieldName + "] FieldMeataDate was null for index[" + i + "]"; log.error(msg); tblWriter.logError(msg); } else { //System.out.println(fldMetaData.getName()+" "+fldMetaData.getSqlType()+" "+fldMetaData.getType()); BasicSQLUtils.setData(pStmt, newFieldMetaData.get(i).getSqlType(), fldInx, data); } } } if (hasFrame) { if (count % 500 == 0) { setProcess(count); } } else { if (count % 2000 == 0) { log.info("Determination Records: " + count); } } if (!isError) { try { if (pStmt.executeUpdate() != 1) { log.error("Count: " + count); log.error("Error inserting record."); } } catch (SQLException e) { log.error("Count: " + count); e.printStackTrace(); log.error(e); rs.close(); stmt.close(); showError(e.toString()); throw new RuntimeException(e); } } count++; // if (count > 10) break; } while (rs.next()); pStmt.close(); if (hasFrame) { setProcess(count); } else { log.info("Processed Determination " + count + " records."); } rs.close(); stmt.close(); tblWriter.log(String.format("Determination Processing Time: %s", timeLogger.end())); tblWriter.close(); } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); return true; }
From source file:ca.sqlpower.matchmaker.address.AddressPool.java
/** * Inserts and updates the contents of the result table with the * {@link AddressResult} contents in this {@link AddressPool}. * AddressResults that are marked as {@link StorageState#DIRTY} are assumed * to be already in the database and are updated. AddressResults that are * marked as {@link StorageState#NEW} are assumed to be new entries that do * no yet exist in the database and are inserted. * /* ww w . j av a 2 s. c o m*/ * It is worth noting that at the moment, new Address results won't have an * output street number yet (since they have not been validated yet) but a * {@link NullPointerException} gets thrown if we try to insert a null * Integer, so for the time being, I've set the 'null' steet number to be * -1, since I don't believe there's anyone with a negative street number, * but if I'm wrong, this will have to be changed. * * @throws SQLException * @throws SQLObjectException */ public void store(Logger engineLogger, boolean useBatchExecute, boolean debug) throws SQLException, SQLObjectException { setStarted(true); setFinished(false); setCancelled(false); setProgress(0); List<AddressResult> dirtyAddresses = new ArrayList<AddressResult>(); List<AddressResult> deleteAddresses = new ArrayList<AddressResult>(); List<AddressResult> newAddresses = new ArrayList<AddressResult>(); for (List<Object> key : addresses.keySet()) { AddressResult result = addresses.get(key); if (result.getStorageState() == StorageState.DELETE) { deleteAddresses.add(result); } else if (result.getStorageState() == StorageState.DIRTY) { dirtyAddresses.add(result); } else if (result.getStorageState() == StorageState.NEW) { newAddresses.add(result); } } setJobSize(deleteAddresses.size() + dirtyAddresses.size() + newAddresses.size()); engineLogger.debug("# of Delete Address Records:" + deleteAddresses.size()); engineLogger.debug("# of Dirty Address Records:" + dirtyAddresses.size()); engineLogger.debug("# of New Address Records:" + newAddresses.size()); Connection con = null; PreparedStatement ps = null; Statement stmt = null; StringBuilder sql = null; AddressResult result = null; try { con = project.createResultTableConnection(); con.setAutoCommit(false); boolean useBatchUpdates = useBatchExecute && con.getMetaData().supportsBatchUpdates(); SQLTable resultTable = project.getResultTable(); int keySize = project.getSourceTableIndex().getChildCount(); if (deleteAddresses.size() > 0) { stmt = con.createStatement(); for (AddressResult currentResult : deleteAddresses) { sql = new StringBuilder("DELETE FROM "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" WHERE "); int j = 0; for (Object keyValue : currentResult.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); } j++; } engineLogger.debug("Preparing the following address result to be deleted: " + currentResult); engineLogger.debug("Executing statement " + sql); stmt.execute(sql.toString()); incrementProgress(); } if (stmt != null) stmt.close(); stmt = null; } Map<String, Integer> columnMetaData = this.getColumnMetaData(engineLogger, resultTable); /* For backward compatibility, see if old column names are being used. * NOTE: the database may return column names as upper case. */ boolean usingNewNames = true; if (columnMetaData.containsKey(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME)) { usingNewNames = false; } engineLogger.debug("Using new shorter names? " + usingNewNames); if (dirtyAddresses.size() > 0) { //First, create and UPDATE PreparedStatement to update dirty records sql = new StringBuilder(); sql.append("UPDATE "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" SET "); sql.append(INPUT_ADDRESS_LINE1).append("=?, "); // 1 sql.append(INPUT_ADDRESS_LINE2).append("=?, "); // 2 sql.append(INPUT_MUNICIPALITY).append("=?, "); // 3 sql.append(INPUT_PROVINCE).append("=?, "); // 4 sql.append(INPUT_COUNTRY).append("=?, "); // 5 sql.append(INPUT_POSTAL_CODE).append("=?, "); // 6 sql.append(OUTPUT_COUNTRY).append("=?, "); // 7 if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } sql.append(OUTPUT_DIRECTION_PREFIX).append("=?, "); // 10 sql.append(OUTPUT_FAILED_PARSING_STRING).append("=?, "); // 11 sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append("=?, "); // 12 sql.append(OUTPUT_LOCK_BOX_NUMBER).append("=?, "); // 13 sql.append(OUTPUT_LOCK_BOX_TYPE).append("=?, "); // 14 sql.append(OUTPUT_MUNICIPALITY).append("=?, "); // 15 sql.append(OUTPUT_POSTAL_CODE).append("=?, "); // 16 sql.append(OUTPUT_PROVINCE).append("=?, "); // 17 sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append("=?, "); // 18 sql.append(OUTPUT_RURAL_ROUTE_TYPE).append("=?, "); // 19 sql.append(OUTPUT_STREET_DIRECTION).append("=?, "); // 20 sql.append(OUTPUT_STREET_NAME).append("=?, "); // 21 sql.append(OUTPUT_STREET_NUMBER).append("=?, "); // 22 sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append("=?, "); // 23 if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } sql.append(OUTPUT_STREET_TYPE).append("=?, "); // 24 sql.append(OUTPUT_STREET_TYPE_PREFIX).append("=?, "); // 25 sql.append(OUTPUT_SUITE).append("=?, "); // 26 sql.append(OUTPUT_SUITE_PREFIX).append("=?, "); // 27 sql.append(OUTPUT_SUITE_TYPE).append("=?, "); // 28 sql.append(OUTPUT_TYPE).append("=?, "); // 29 sql.append(OUTPUT_UNPARSED_ADDRESS).append("=?, "); // 30 sql.append(OUTPUT_URBAN_BEFORE_RURAL).append("=?, "); // 31 sql.append(OUTPUT_VALID).append("=? "); // 32 sql.append("WHERE "); String baseStatement = sql.toString(); int batchCount = 0; for (int i = 0; i < dirtyAddresses.size(); i++) { sql = new StringBuilder(baseStatement); result = dirtyAddresses.get(i); int j = 0; // I really wish there was a better way to handle this, // but unfortunately in SQL, <column> = null and <column> is // null are not the same thing, and you usually want 'is // null' Why they couldn't just use '= null' is beyond me. // Otherwise, we could just use a single prepared statement // for all the records. The main reason we had to switch // back to using prepared statements is because different RDBMS // platforms handle Booleans differently (some support // boolean explicitly, others use an integer (1 or 0) for (Object keyValue : result.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); // 18+ } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); // 18+ } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); // 18+ } j++; } ps = con.prepareStatement(sql.toString()); Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(1, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(2, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(3, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(4, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(5, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(6, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(7, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(8, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(9, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(10, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(11, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(12, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(13, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(14, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(15, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(16, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(17, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(18, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(19, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(20, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(21, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(22, Types.INTEGER); } else { ps.setInt(22, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(23, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(24, Types.BOOLEAN); } else { ps.setBoolean(24, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(25, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(26, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(27, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(28, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(29, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(30, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(31, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(32, Types.BOOLEAN); } else { ps.setBoolean(32, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(33, result.isValid()); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (newAddresses.size() > 0) { //Next, let's meke an INSERT PreparedStatement to insert new records sql = new StringBuilder(); sql.append("INSERT INTO "); appendFullyQualifiedTableName(sql, resultTable); sql.append("("); for (int i = 0; i < keySize; i++) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(i).append(", "); } sql.append(INPUT_ADDRESS_LINE1).append(", "); sql.append(INPUT_ADDRESS_LINE2).append(", "); sql.append(INPUT_MUNICIPALITY).append(", "); sql.append(INPUT_PROVINCE).append(", "); sql.append(INPUT_COUNTRY).append(", "); sql.append(INPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_COUNTRY).append(", "); if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } sql.append(OUTPUT_DIRECTION_PREFIX).append(", "); sql.append(OUTPUT_FAILED_PARSING_STRING).append(", "); sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append(", "); sql.append(OUTPUT_LOCK_BOX_NUMBER).append(", "); sql.append(OUTPUT_LOCK_BOX_TYPE).append(", "); sql.append(OUTPUT_MUNICIPALITY).append(", "); sql.append(OUTPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_PROVINCE).append(", "); sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append(", "); sql.append(OUTPUT_RURAL_ROUTE_TYPE).append(", "); sql.append(OUTPUT_STREET_DIRECTION).append(", "); sql.append(OUTPUT_STREET_NAME).append(", "); sql.append(OUTPUT_STREET_NUMBER).append(", "); sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append(", "); if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } sql.append(OUTPUT_STREET_TYPE).append(", "); sql.append(OUTPUT_STREET_TYPE_PREFIX).append(", "); sql.append(OUTPUT_SUITE).append(", "); sql.append(OUTPUT_SUITE_PREFIX).append(", "); sql.append(OUTPUT_SUITE_TYPE).append(", "); sql.append(OUTPUT_TYPE).append(", "); sql.append(OUTPUT_UNPARSED_ADDRESS).append(", "); sql.append(OUTPUT_URBAN_BEFORE_RURAL).append(", "); sql.append(OUTPUT_VALID).append(")"); sql.append("VALUES("); for (int i = 0; i < keySize; i++) { sql.append("?, "); } sql.append( "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); ps = con.prepareStatement(sql.toString()); int batchCount = 0; for (int i = 0; i < newAddresses.size(); i++) { result = newAddresses.get(i); int j = 1; for (Object keyValue : result.getKeyValues()) { ps.setObject(j, keyValue); j++; engineLogger.debug("Setting key value " + j + " to " + keyValue); } Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(j, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(j + 1, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(j + 2, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(j + 3, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(j + 4, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(j + 5, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 6, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(j + 7, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(j + 8, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(j + 9, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(j + 10, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(j + 11, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(j + 12, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(j + 13, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(j + 14, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(j + 15, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(j + 16, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(j + 17, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(j + 18, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(j + 19, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(j + 20, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(j + 21, Types.INTEGER); } else { ps.setInt(j + 21, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(j + 22, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(j + 23, Types.BOOLEAN); } else { ps.setBoolean(j + 23, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 24, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(j + 25, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 26, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(j + 27, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(j + 28, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(j + 29, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(j + 30, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(j + 31, Types.BOOLEAN); } else { ps.setBoolean(j + 31, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(j + 32, result.isValid()); engineLogger.debug("Preparing the following address to be inserted: " + result); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (debug) { engineLogger.debug("Rolling back changes"); con.rollback(); } else { engineLogger.debug("Committing changes"); con.commit(); } for (AddressResult ar : addresses.values()) { ar.markClean(); } } catch (Exception ex) { try { con.rollback(); } catch (SQLException sqlEx) { engineLogger.error("Error while rolling back. " + "Suppressing this exception to prevent it from overshadowing the orginal exception.", sqlEx); } throw new RuntimeException("Unexpected exception while storing address validation results.\n" + "SQL statement: " + ((sql == null) ? "null" : sql.toString()) + "\n" + "Current result: " + ((result == null) ? "null" : "Input Address:\n" + result.getInputAddress() + "\n" + "Output Address:\n" + result.getOutputAddress()), ex); } finally { setFinished(true); if (ps != null) try { ps.close(); } catch (SQLException e) { engineLogger.error("Error while closing PreparedStatement", e); } if (stmt != null) try { stmt.close(); } catch (SQLException e) { engineLogger.error("Error while closing Statement", e); } if (con != null) try { con.close(); } catch (SQLException e) { engineLogger.error("Error while closing Connection", e); } } }