Example usage for java.sql Statement clearBatch

List of usage examples for java.sql Statement clearBatch

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. clearBatch

Introduction

In this page you can find the example usage for java.sql Statement clearBatch.

Prototype

void clearBatch() throws SQLException;

Source Link

Document

Empties this Statement object's current list of SQL commands.

Usage

From source file:com.mysql.stresstool.RunnableQueryInsertPCH.java

public void run() {

    BufferedReader d = null;/*from   w w w.  j  a  v a  2s  .  c  o m*/
    Connection conn = null;

    if (conn == null) {

        try {
            long execTime = 0;
            int pkStart = 0;
            int pkEnds = 0;
            int intDeleteInterval = 0;
            int intBlobInterval = 0;
            int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue();
            ThreadInfo thInfo;

            long threadTimeStart = System.currentTimeMillis();
            active = true;

            thInfo = new ThreadInfo();
            thInfo.setId(this.ID);
            thInfo.setType("insert");
            thInfo.setStatusActive(this.isActive());

            StressTool.setInfo(this.ID, thInfo);
            boolean lazy = false;
            int lazyInterval = 0;

            for (int repeat = 0; repeat <= repeatNumber; repeat++) {

                try {
                    if (conn != null && !conn.isClosed()) {
                        conn.close();
                    }
                    SoftReference sf = new SoftReference(
                            DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")));
                    conn = (Connection) sf.get();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                Statement stmt = null;
                //                ResultSet rs = null;
                //                ResultSet rs2 = null;

                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                stmt.execute("SET AUTOCOMMIT=0");
                ResultSet rs = null;
                int ServerId = 0;
                String query = null;
                ArrayList insert1 = null;
                ArrayList insert2 = null;
                int pk = 0;

                {
                    SoftReference sf = new SoftReference(
                            stmt.executeQuery("show global variables like 'SERVER_ID'"));
                    rs = (ResultSet) sf.get();
                }
                rs.next();
                ServerId = rs.getInt(2);

                if (repeat > 0 && lazyInterval < 500) {
                    lazy = true;
                    ++lazyInterval;
                } else {
                    lazy = false;
                    lazyInterval = 0;
                }

                intBlobInterval = StressTool.getNumberFromRandom(10).intValue();
                //               intBlobInterval++;
                //IMPLEMENTING lazy

                Vector v = null;
                {
                    SoftReference sf = new SoftReference(this.getTablesValues(lazy, ServerId));
                    v = (Vector) sf.get();
                }
                insert1 = (ArrayList<String>) v.get(0);
                insert2 = (ArrayList<String>) v.get(1);

                //                    System.out.println(insert1);
                //                    System.out.println(insert2);

                //                    pk = ((Integer) v.get(2)).intValue();

                int[] iLine = { 0, 0 };

                //                    pkStart = StressTool.getNumberFromRandom(2147483647).intValue();
                //                    pkEnds = StressTool.getNumberFromRandom(2147483647).intValue();

                try {

                    long timeStart = System.currentTimeMillis();

                    if (this.ignoreBinlog)
                        stmt.execute("SET sql_log_bin=0");
                    stmt.execute("SET GLOBAL max_allowed_packet=10737418");

                    if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE"))
                        stmt.execute("BEGIN");
                    else
                        stmt.execute("COMMIT");
                    //                                stmt.execute("SET TRANSACTION NAME 'TEST'");
                    {
                        Iterator<String> it = insert1.iterator();
                        while (it.hasNext()) {
                            stmt.addBatch(it.next());
                        }
                    }

                    if (!this.doSimplePk) {
                        //                      System.out.println("Blob insert value :" + intBlobInterval);
                        if (intBlobInterval > intBlobIntervalLimit) {
                            Iterator<String> it = insert2.iterator();
                            while (it.hasNext()) {
                                stmt.addBatch(it.next());
                            }
                            //                        intBlobInterval=0;

                        }
                    }

                    iLine = stmt.executeBatch();
                    stmt.clearBatch();
                    //                            System.out.println("Query1 = " + insert1);
                    //                            System.out.println("Query2 = " + insert2);
                    //                            stmt.execute("START TRANSACTION");
                    //                            stmt.execute(insert1);
                    //                            iLine = stmt.executeBatch();
                    //                            conn.commit();
                    long timeEnds = System.currentTimeMillis();
                    execTime = (timeEnds - timeStart);

                } catch (Exception sqle) {
                    conn.rollback();
                    if (StressTool.getErrorLogHandler() != null) {
                        StressTool.getErrorLogHandler().appendToFile(("FAILED QUERY1==" + insert1));
                        StressTool.getErrorLogHandler().appendToFile(("FAILED QUERY2==" + insert2));
                        StressTool.getErrorLogHandler().appendToFile(sqle.toString());

                    } else {
                        sqle.printStackTrace();
                        System.out.println("FAILED QUERY1==" + insert1);
                        System.out.println("FAILED QUERY2==" + insert2);
                        sqle.printStackTrace();
                        System.exit(1);
                    }
                    //conn.close();
                    //this.setJdbcUrl(jdbcUrl);
                    //System.out.println("Query Insert TH RE-INIZIALIZING");

                } finally {
                    //                           conn.commit();
                    stmt.execute("COMMIT");
                    rs.close();
                    stmt.close();
                    rs = null;
                    stmt = null;

                    //                            intDeleteInterval++;
                    if (doLog) {

                        System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " "
                                + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) ="
                                + execTime + " Running = " + repeat + " of " + repeatNumber + " to go ="
                                + (repeatNumber - repeat) + " Using Lazy=" + lazy);
                    }
                }
                thInfo.setExecutedLoops(repeat);
                if (sleepFor > 0 || this.getSleepWrite() > 0) {
                    if (this.getSleepWrite() > 0) {
                        Thread.sleep(getSleepWrite());
                    } else
                        Thread.sleep(sleepFor);
                }

                conn.close();
                conn = null;
            }

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);
            active = false;
            //                System.out.println("Query Insert TH = " + this.getID() + " COMPLETED!  TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000));

            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfo(this.ID, thInfo);
            return;

        } catch (Exception ex) {
            if (StressTool.getErrorLogHandler() != null) {
                StressTool.getErrorLogHandler().appendToFile(ex.toString() + "\n");
            } else
                ex.printStackTrace();

            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                if (StressTool.getErrorLogHandler() != null) {
                    StressTool.getErrorLogHandler().appendToFile(e.toString() + "\n");
                    conn = null;
                } else
                    e.printStackTrace();
            }
        }

    }

}

From source file:com.mysql.stresstool.RunnableQueryInsertDR.java

public void run() {

    BufferedReader d = null;//from  ww w .  j  ava2 s  .  c om
    Connection conn = null;

    try {
        if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) {
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test");
        } else
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"));
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    if (conn != null) {

        try {

            Statement stmt = null;
            //                ResultSet rs = null;
            //                ResultSet rs2 = null;

            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt.execute("SET AUTOCOMMIT=0");
            long execTime = 0;
            int pkStart = 0;
            int pkEnds = 0;
            int intDeleteInterval = 0;
            int intBlobInterval = 0;
            int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue();
            ThreadInfo thInfo;

            long threadTimeStart = System.currentTimeMillis();
            active = true;

            thInfo = new ThreadInfo();
            thInfo.setId(this.ID);
            thInfo.setType("insert");
            thInfo.setStatusActive(this.isActive());

            StressTool.setInfo(this.ID, thInfo);
            boolean lazy = false;
            int lazyInterval = 0;

            populateLocalInfo(conn);

            for (int repeat = 0; repeat <= repeatNumber; repeat++) {
                String query = null;
                ArrayList insert1 = null;
                ArrayList insert2 = null;
                int pk = 0;

                if (repeat > 0 && lazyInterval < 500) {
                    lazy = true;
                    ++lazyInterval;
                } else {
                    lazy = false;
                    lazyInterval = 0;
                }

                intBlobInterval++;
                //IMPLEMENTING lazy
                Vector v = this.getTablesValues(lazy);

                insert1 = (ArrayList<String>) v.get(0);
                insert2 = (ArrayList<String>) v.get(1);

                //                    System.out.println(insert1);
                //                    System.out.println(insert2);

                //                    pk = ((Integer) v.get(2)).intValue();

                int[] iLine = { 0, 0 };

                //                    pkStart = StressTool.getNumberFromRandom(2147483647).intValue();
                //                    pkEnds = StressTool.getNumberFromRandom(2147483647).intValue();

                try {

                    long timeStart = System.currentTimeMillis();

                    if (this.ignoreBinlog)
                        stmt.execute("SET sql_log_bin=0");
                    stmt.execute("SET GLOBAL max_allowed_packet=1073741824");

                    if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE"))
                        stmt.execute("BEGIN");
                    else
                        stmt.execute("COMMIT");
                    //                                stmt.execute("SET TRANSACTION NAME 'TEST'");
                    {
                        Iterator<String> it = insert1.iterator();
                        while (it.hasNext()) {
                            stmt.addBatch(it.next());
                        }
                    }

                    if (!this.doSimplePk) {
                        if (intBlobInterval > intBlobIntervalLimit) {
                            Iterator<String> it = insert2.iterator();
                            while (it.hasNext()) {
                                stmt.addBatch(it.next());
                            }
                            intBlobInterval = 0;

                        }
                    }
                    if (debug) {
                        System.out.println("Thread " + thInfo.getId() + " Executing loop "
                                + thInfo.getExecutedLoops() + " QUERY1==" + insert1);
                        System.out.println("Thread " + thInfo.getId() + " Executing loop "
                                + thInfo.getExecutedLoops() + " QUERY2==" + insert2);

                    }

                    iLine = stmt.executeBatch();
                    stmt.clearBatch();
                    //                            System.out.println("Query1 = " + insert1);
                    //                            System.out.println("Query2 = " + insert2);
                    //                            stmt.execute("START TRANSACTION");
                    //                            stmt.execute(insert1);
                    //                            iLine = stmt.executeBatch();
                    //                            conn.commit();
                    long timeEnds = System.currentTimeMillis();
                    execTime = (timeEnds - timeStart);

                } catch (Exception sqle) {
                    conn.rollback();
                    System.out.println("FAILED QUERY1==" + insert1);
                    System.out.println("FAILED QUERY2==" + insert2);
                    sqle.printStackTrace();
                    System.exit(1);
                    //conn.close();
                    //this.setJdbcUrl(jdbcUrl);
                    //System.out.println("Query Insert TH RE-INIZIALIZING");

                } finally {
                    //                           conn.commit();
                    stmt.execute("COMMIT");
                    //                            intDeleteInterval++;
                    if (doLog) {

                        System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " "
                                + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) ="
                                + execTime + " Running = " + repeat + " of " + repeatNumber + " to go ="
                                + (repeatNumber - repeat) + " Using Lazy=" + lazy);
                    }
                }
                thInfo.setExecutedLoops(repeat);
                if (sleepFor > 0 || this.getSleepWrite() > 0) {
                    if (this.getSleepWrite() > 0) {
                        Thread.sleep(getSleepWrite());
                    } else
                        Thread.sleep(sleepFor);
                }

            }

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);
            active = false;
            //                System.out.println("Query Insert TH = " + this.getID() + " COMPLETED!  TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000));

            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfo(this.ID, thInfo);
            return;

        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}

From source file:com.intellectualcrafters.plot.database.SQLManager.java

/**
 * Create tables/*from   w w  w. j  av a2  s  . com*/
 *
 * @throws SQLException
 */
@Override
public void createTables(final String database, final boolean add_constraint) throws SQLException {
    final boolean mysql = database.equals("mysql");
    final Statement stmt = connection.createStatement();
    if (mysql) {
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot` ("
                + "`id` INT(11) NOT NULL AUTO_INCREMENT," + "`plot_id_x` INT(11) NOT NULL,"
                + "`plot_id_z` INT(11) NOT NULL," + "`owner` VARCHAR(45) NOT NULL,"
                + "`world` VARCHAR(45) NOT NULL," + "`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,"
                + "PRIMARY KEY (`id`)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0");
        stmt.addBatch(
                "CREATE TABLE IF NOT EXISTS `" + prefix + "plot_denied` (" + "`plot_plot_id` INT(11) NOT NULL,"
                        + "`user_uuid` VARCHAR(40) NOT NULL" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
        stmt.addBatch(
                "CREATE TABLE IF NOT EXISTS `" + prefix + "plot_helpers` (" + "`plot_plot_id` INT(11) NOT NULL,"
                        + "`user_uuid` VARCHAR(40) NOT NULL" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_comments` ("
                + "`plot_plot_id` INT(11) NOT NULL," + "`comment` VARCHAR(40) NOT NULL,"
                + "`tier` INT(11) NOT NULL," + "`sender` VARCHAR(40) NOT NULL"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
        stmt.addBatch(
                "CREATE TABLE IF NOT EXISTS `" + prefix + "plot_trusted` (" + "`plot_plot_id` INT(11) NOT NULL,"
                        + "`user_uuid` VARCHAR(40) NOT NULL" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_settings` ("
                + "  `plot_plot_id` INT(11) NOT NULL," + "  `biome` VARCHAR(45) DEFAULT 'FOREST',"
                + "  `rain` INT(1) DEFAULT 0," + "  `custom_time` TINYINT(1) DEFAULT '0',"
                + "  `time` INT(11) DEFAULT '8000'," + "  `deny_entry` TINYINT(1) DEFAULT '0',"
                + "  `alias` VARCHAR(50) DEFAULT NULL," + "  `flags` VARCHAR(512) DEFAULT NULL,"
                + "  `merged` INT(11) DEFAULT NULL," + "  `position` VARCHAR(50) NOT NULL DEFAULT 'DEFAULT',"
                + "  PRIMARY KEY (`plot_plot_id`)," + "  UNIQUE KEY `unique_alias` (`alias`)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix
                + "plot_ratings` ( `plot_plot_id` INT(11) NOT NULL, `rating` INT(2) NOT NULL, `player` VARCHAR(40) NOT NULL, PRIMARY KEY(`plot_plot_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        if (add_constraint) {
            stmt.addBatch("ALTER TABLE `" + prefix + "plot_settings` ADD CONSTRAINT `" + prefix
                    + "plot_settings_ibfk_1` FOREIGN KEY (`plot_plot_id`) REFERENCES `" + prefix
                    + "plot` (`id`) ON DELETE CASCADE");
        }

    } else {
        stmt.addBatch(
                "CREATE TABLE IF NOT EXISTS `" + prefix + "plot` (" + "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
                        + "`plot_id_x` INT(11) NOT NULL," + "`plot_id_z` INT(11) NOT NULL,"
                        + "`owner` VARCHAR(45) NOT NULL," + "`world` VARCHAR(45) NOT NULL,"
                        + "`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP)");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_denied` ("
                + "`plot_plot_id` INT(11) NOT NULL," + "`user_uuid` VARCHAR(40) NOT NULL" + ")");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_helpers` ("
                + "`plot_plot_id` INT(11) NOT NULL," + "`user_uuid` VARCHAR(40) NOT NULL" + ")");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_trusted` ("
                + "`plot_plot_id` INT(11) NOT NULL," + "`user_uuid` VARCHAR(40) NOT NULL" + ")");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_comments` ("
                + "`plot_plot_id` INT(11) NOT NULL," + "`comment` VARCHAR(40) NOT NULL,"
                + "`tier` INT(11) NOT NULL," + "`sender` VARCHAR(40) NOT NULL" + ")");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix + "plot_settings` ("
                + "  `plot_plot_id` INT(11) NOT NULL," + "  `biome` VARCHAR(45) DEFAULT 'FOREST',"
                + "  `rain` INT(1) DEFAULT 0," + "  `custom_time` TINYINT(1) DEFAULT '0',"
                + "  `time` INT(11) DEFAULT '8000'," + "  `deny_entry` TINYINT(1) DEFAULT '0',"
                + "  `alias` VARCHAR(50) DEFAULT NULL," + "  `flags` VARCHAR(512) DEFAULT NULL,"
                + "  `merged` INT(11) DEFAULT NULL," + "  `position` VARCHAR(50) NOT NULL DEFAULT 'DEFAULT',"
                + "  PRIMARY KEY (`plot_plot_id`)" + ")");
        stmt.addBatch("CREATE TABLE IF NOT EXISTS `" + prefix
                + "plot_ratings` (`plot_plot_id` INT(11) NOT NULL, `rating` INT(2) NOT NULL, `player` VARCHAR(40) NOT NULL, PRIMARY KEY(`plot_plot_id`))");
    }
    stmt.executeBatch();
    stmt.clearBatch();
    stmt.close();
}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * @param collId/*  w w  w .  j  a v a  2s.c  om*/
 * @param autoNumId
 */
protected void joinCollectionAndAutoNum(final Integer collId, final Integer autoNumId) {
    try {
        Statement updateStatement = newDBConn.createStatement();
        strBuf.setLength(0);
        strBuf.append("INSERT INTO autonumsch_coll (CollectionID, AutoNumberingSchemeID) VALUES (");
        strBuf.append(collId + ",");
        strBuf.append(autoNumId.toString());
        strBuf.append(")");

        log.debug(strBuf.toString());

        updateStatement.executeUpdate(strBuf.toString());
        updateStatement.clearBatch();
        updateStatement.close();
        updateStatement = null;

    } catch (SQLException ex) {
        ex.printStackTrace();
        showError(ex.getMessage());
    }
}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the CollectionObject Physical records and CollectionObjectCatalog Records into
 * the new schema Preparation table./*  w  w  w . ja va 2s .co  m*/
 * @return true if no errors
 */
public boolean convertLoanRecords(final boolean doingGifts) {
    String newTableName = doingGifts ? "gift" : "loan";
    setIdentityInsertONCommandForSQLServer(newDBConn, newTableName, BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, newTableName, BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    if (getNumRecords(oldDBConn, "loan") == 0) {
        return true;
    }

    String[] ignoredFields = { "SpecialConditions", "AddressOfRecordID", "DateReceived", "ReceivedComments",
            "PurposeOfLoan", "OverdueNotiSetDate", "IsFinancialResponsibility", "Version", "CreatedByAgentID",
            "IsFinancialResponsibility", "SrcTaxonomy", "SrcGeography", "CollectionMemberID", "PurposeOfGift",
            "IsFinancialResponsibility", "SpecialConditions", "ReceivedComments", "AddressOfRecordID" };

    Hashtable<String, Boolean> fieldToSkip = new Hashtable<String, Boolean>();
    for (String nm : ignoredFields) {
        fieldToSkip.put(nm, true);
    }

    IdTableMapper loanIdMapper = (IdTableMapper) idMapperMgr.get(newTableName,
            doingGifts ? "GiftID" : "LoanID");
    try {
        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = getFieldNamesFromSchema(oldDBConn, "loan");

        StringBuilder sql = new StringBuilder("SELECT ");
        sql.append(buildSelectFieldList(oldFieldNames, "loan"));
        sql.append(" FROM loan WHERE loan.Category = ");
        sql.append(doingGifts ? "1" : "0");
        sql.append(" ORDER BY loan.LoanID");
        log.info(sql);

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, newTableName);
        log.info("Number of Fields in New " + newTableName + " " + newFieldMetaData.size());
        String sqlStr = sql.toString();

        if (doingGifts && loanIdMapper == null) {
            StringBuilder mapSQL = new StringBuilder("SELECT LoanID FROM loan WHERE loan.Category = ");
            mapSQL.append(doingGifts ? "1" : "0");
            mapSQL.append(" ORDER BY loan.LoanID");
            log.info(mapSQL.toString());

            BasicSQLUtils.deleteAllRecordsFromTable(oldDBConn, "gift_GiftID",
                    BasicSQLUtils.myDestinationServerType);
            loanIdMapper = new IdTableMapper(newTableName, "GiftID", mapSQL.toString(), false, false);
            idMapperMgr.addMapper(loanIdMapper);
            loanIdMapper.mapAllIdsWithSQL();
        }

        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        for (String name : oldFieldNames) {
            oldNameIndex.put(name, inx++);
        }

        Map<String, String> colNewToOldMap = doingGifts
                ? createFieldNameMap(new String[] { "GiftNumber", "LoanNumber", "GiftDate", "LoanDate",
                        "IsCurrent", "Current", "IsClosed", "Closed" })
                : createFieldNameMap(new String[] { "IsCurrent", "Current", "IsClosed", "Closed", });

        log.info(sqlStr);
        ResultSet rs = stmt.executeQuery(sqlStr);

        if (hasFrame) {
            if (rs.last()) {
                setProcess(0, rs.getRow());
                rs.first();

            } else {
                rs.close();
                stmt.close();
                return true;
            }
        } else {
            if (!rs.first()) {
                rs.close();
                stmt.close();
                return true;
            }
        }

        PartialDateConv partialDateConv = new PartialDateConv();

        int lastEditedByInx = oldNameIndex.get("LastEditedBy");

        int count = 0;
        do {
            partialDateConv.nullAll();

            str.setLength(0);
            StringBuffer fieldList = new StringBuffer();
            fieldList.append("( ");
            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if ((i > 0) && (i < newFieldMetaData.size())) {
                    fieldList.append(", ");
                }
                String newFieldName = newFieldMetaData.get(i).getName();
                fieldList.append(newFieldName);
            }

            fieldList.append(")");

            str.append("INSERT INTO " + newTableName + " " + fieldList + " VALUES (");
            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0) {
                    str.append(", ");
                }

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldID = rs.getInt(1);
                    Integer newID = loanIdMapper.get(oldID);
                    if (newID != null) {
                        str.append(getStrValue(newID));
                    } else {
                        log.error(newTableName + " Old/New ID problem [" + oldID + "][" + newID + "]");
                    }

                } else if (newFieldName.equals("Version")) // User/Security changes
                {
                    str.append("0");

                } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes
                {
                    str.append(getCreatorAgentId(null));

                } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes
                {
                    String lastEditedByStr = rs.getString(lastEditedByInx);
                    str.append(getModifiedByAgentId(lastEditedByStr));

                } else if (fieldToSkip.get(newFieldName) != null) {
                    str.append("NULL");

                } else if (newFieldName.equals("DisciplineID")) // User/Security changes
                {
                    str.append(curDisciplineID);

                } else if (newFieldName.equals("DivisionID")) // User/Security changes
                {
                    str.append(curDivisionID);

                } else {
                    Integer index = null;
                    String oldMappedColName = colNewToOldMap.get(newFieldName);
                    if (oldMappedColName != null) {
                        index = oldNameIndex.get(oldMappedColName);

                    } else {
                        index = oldNameIndex.get(newFieldName);
                        oldMappedColName = newFieldName;
                    }

                    Object data;
                    if (index == null) {
                        String msg = "convertLoanRecords - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            stmt.close();
                        //                            throw new RuntimeException(msg);
                        data = null;
                    } else {

                        data = rs.getObject(index);
                    }
                    if (data != null) {
                        int idInx = newFieldName.lastIndexOf("ID");
                        if (idMapperMgr != null && idInx > -1) {
                            IdMapperIFace idMapper = idMapperMgr.get("loan", oldMappedColName);
                            if (idMapper != null) {
                                data = idMapper.get(rs.getInt(index));
                            } else {
                                log.error("No Map for [" + "loan" + "][" + oldMappedColName + "]");
                            }
                        }
                    }

                    // hack for ??bug?? found in Sp5 that inserted null values in
                    // timestampmodified field of determination table?
                    BasicSQLUtils.fixTimestamps(newFieldName, newFieldMetaData.get(i).getType(), data, str);
                }
            }
            str.append(")");

            if (hasFrame) {
                if (count % 500 == 0) {
                    setProcess(count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info("Loan/Gifts Records: " + count);
                }
            }

            try {
                //log.debug(str.toString());
                Statement updateStatement = newDBConn.createStatement();
                // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                updateStatement.executeUpdate(str.toString());
                updateStatement.clearBatch();
                updateStatement.close();
                updateStatement = null;

            } catch (SQLException e) {
                log.error("Count: " + count);
                log.error("Exception on insert: " + str.toString());
                e.printStackTrace();
                log.error(e);
                rs.close();
                stmt.close();
                throw new RuntimeException(e);
            }

            count++;
            // if (count > 10) break;
        } while (rs.next());

        if (hasFrame) {
            setProcess(count);
        } else {
            log.info("Processed Loan/Gift " + count + " records.");
        }
        rs.close();

        stmt.close();

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        throw new RuntimeException(e);
    }

    setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType);

    return true;
}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the LoanPhysicalObjects.
 * @return true if no errors//from   w  w w . j  a va2 s  .c o  m
 */
public boolean convertGiftPreparations() {
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType);
    setIdentityInsertONCommandForSQLServer(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    if (getNumRecords(oldDBConn, "loanphysicalobject") == 0) {
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "giftpreparation",
                BasicSQLUtils.myDestinationServerType);
        return true;
    }

    Integer recCount = getCount(oldDBConn, "SELECT count(*) FROM loan WHERE Category = 1 ORDER BY LoanID");
    if (recCount == null || recCount == 0) {
        return true;
    }

    // This mapping is used by Gifts
    IdMapperIFace giftsIdMapper = IdMapperMgr.getInstance().get("gift", "GiftID");
    //if (shouldCreateMapTables)
    //{
    //   giftsIdMapper.mapAllIdsWithSQL();
    //}

    // This mapping is used by Gifts Preps

    IdMapperIFace giftPrepsIdMapper = IdMapperMgr.getInstance().get("giftphysicalobject", "id");

    TableWriter tblWriter = convLogger.getWriter("convertGiftPreparations.html", "Gift Preparations");
    TimeLogger timeLogger = new TimeLogger();

    try {
        Map<String, String> colNewToOldMap = createFieldNameMap(
                new String[] { "PreparationID", "PhysicalObjectID" });

        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("SELECT ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "loanphysicalobject");

        sql.append(buildSelectFieldList(names, "loanphysicalobject"));
        oldFieldNames.addAll(names);

        sql.append(
                " FROM loanphysicalobject INNER JOIN loan ON loanphysicalobject.LoanID = loan.LoanID WHERE loan.Category = 1 ORDER BY loanphysicalobject.LoanPhysicalObjectID");

        log.info(sql);

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "giftpreparation");

        log.info("Number of Fields in New giftpreparation " + newFieldMetaData.size());
        String sqlStr = sql.toString();

        colNewToOldMap.put("GiftID", "LoanID");

        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        for (String name : oldFieldNames) {
            oldNameIndex.put(name, inx++);
        }

        String tableName = "loanphysicalobject";

        //int quantityIndex   = oldNameIndex.get("Quantity");
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");
        int loanPhysIdIndex = oldNameIndex.get("LoanPhysicalObjectID");

        log.info(sqlStr);
        ResultSet rs = stmt.executeQuery(sqlStr);

        if (hasFrame) {
            if (rs.last()) {
                setProcess(0, rs.getRow());
                rs.first();

            } else {
                rs.close();
                stmt.close();
                return true;
            }
        } else {
            if (!rs.first()) {
                rs.close();
                stmt.close();
                return true;
            }
        }

        String insertStmtStr = null;

        int count = 0;
        do {
            //int quantity         = getIntValue(rs, quantityIndex);
            String lastEditedBy = rs.getString(lastEditedByInx);

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO giftpreparation " + fieldList + " VALUES (";
            }
            str.append(insertStmtStr);

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0)
                    str.append(", ");

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldId = rs.getInt(loanPhysIdIndex);
                    Integer newID = giftPrepsIdMapper.get(oldId);
                    if (newID != null) {
                        str.append(getStrValue(newID));
                    } else {
                        String msg = String.format(
                                "loanPhysIdIndex: %d; Old Id: %d could be mapped to a new ID. (This was a fatal error).",
                                loanPhysIdIndex, oldId);
                        tblWriter.logError(msg);
                        log.error(msg);
                        return false;
                    }

                } else if (newFieldName.equals("ReceivedComments")) {
                    str.append("NULL");

                } else if (newFieldName.equalsIgnoreCase("Version")) {
                    str.append("0");

                } else if (newFieldName.equalsIgnoreCase("DisciplineID")) {
                    str.append(getDisciplineId());

                } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) {
                    str.append(getModifiedByAgentId(lastEditedBy));

                } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) {
                    str.append(getCreatorAgentId(null));

                } else {
                    Integer index = null;
                    String oldMappedColName = colNewToOldMap.get(newFieldName);
                    if (oldMappedColName != null) {
                        index = oldNameIndex.get(oldMappedColName);

                    } else {
                        index = oldNameIndex.get(newFieldName);
                        oldMappedColName = newFieldName;
                    }

                    Object data;

                    if (index == null) {
                        String msg = "convertGiftPreparations - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            stmt.close();
                        //                            tblWriter.logError(msg);
                        //                            showError(msg);
                        //                            throw new RuntimeException(msg);
                        data = null;
                    } else {
                        data = rs.getObject(index);
                    }
                    if (data != null) {
                        if (newFieldName.equalsIgnoreCase("GiftID")) {
                            data = giftsIdMapper.get((Integer) data);

                        } else {
                            int idInx = newFieldName.lastIndexOf("ID");
                            if (idMapperMgr != null && idInx > -1) {
                                IdMapperIFace idMapper = idMapperMgr.get(tableName, oldMappedColName);
                                if (idMapper != null) {
                                    Integer oldId = rs.getInt(index);
                                    data = idMapper.get(oldId);
                                    if (data == null) {
                                        String msg = "No Map ID for [" + tableName + "][" + oldMappedColName
                                                + "] for ID[" + oldId + "]";
                                        log.error(msg);
                                        tblWriter.logError(msg);
                                    }
                                } else {
                                    String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]";
                                    log.error(msg);
                                    tblWriter.logError(msg);
                                }
                            }
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));
                }
            }
            str.append(")");

            if (hasFrame) {
                if (count % 500 == 0) {
                    setProcess(count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info("LoanPreparation Records: " + count);
                }
            }

            try {
                Statement updateStatement = newDBConn.createStatement();
                if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                    removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                }
                // log.debug("executring: " + str.toString());
                // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                updateStatement.executeUpdate(str.toString());
                updateStatement.clearBatch();
                updateStatement.close();
                updateStatement = null;

            } catch (SQLException e) {
                log.error("Count: " + count);
                e.printStackTrace();
                log.error(e);
                rs.close();
                stmt.close();
                throw new RuntimeException(e);
            }

            count++;
            // if (count > 10) break;
        } while (rs.next());

        if (hasFrame) {
            setProcess(count);
            log.info("Processed LoanPreparation " + count + " records.");
        } else {
            log.info("Processed LoanPreparation " + count + " records.");
        }
        rs.close();
        stmt.close();

        tblWriter.log(String.format("Determinations Processing Time: %s", timeLogger.end()));

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
                BasicSQLUtils.myDestinationServerType);
        throw new RuntimeException(e);
    }
    log.info("Done processing LoanPhysicalObject");
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
            BasicSQLUtils.myDestinationServerType);

    tblWriter.close();
    return true;

}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Convert all the biological attributes to Collection Object Attributes. Each old record may
 * end up being multiple records in the new schema. This will first figure out which columns in
 * the old schema were used and only map those columns to the new database.<br>
 * <br>/*from  w w w .j a  v  a  2 s. c  om*/
 * It also will use the old name if there is not mapping for it. The old name is converted from
 * lower/upper case to be space separated where each part of the name starts with a capital
 * letter.
 * 
 * @param discipline the Discipline
 * @param colToNameMap a mape for old names to new names
 * @param typeMap a map for changing the type of the data (meaning an old value may be a boolean
 *            stored in a float)
 * @return true for success
 */
public boolean convertBiologicalAttrs(final Discipline discipline,
        @SuppressWarnings("unused") final Map<String, String> colToNameMap, final Map<String, Short> typeMap) {
    AttributeIFace.FieldType[] attrTypes = { AttributeIFace.FieldType.IntegerType,
            AttributeIFace.FieldType.FloatType, AttributeIFace.FieldType.DoubleType,
            AttributeIFace.FieldType.BooleanType, AttributeIFace.FieldType.StringType,
            // AttributeIFace.FieldType.MemoType
    };

    Session localSession = HibernateUtil.getCurrentSession();

    deleteAllRecordsFromTable(newDBConn, "collectionobjectattr", BasicSQLUtils.myDestinationServerType);
    deleteAllRecordsFromTable(newDBConn, "attributedef", BasicSQLUtils.myDestinationServerType);

    try {
        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);

        // grab the field and their type from the old schema
        List<FieldMetaData> oldFieldMetaData = new ArrayList<FieldMetaData>();
        Map<String, FieldMetaData> oldFieldMetaDataMap = getFieldMetaDataFromSchemaHash(oldDBConn,
                "biologicalobjectattributes");

        // create maps to figure which columns where used
        List<String> columnsInUse = new ArrayList<String>();
        Map<String, AttributeDef> attrDefs = new Hashtable<String, AttributeDef>();

        List<Integer> counts = new ArrayList<Integer>();

        int totalCount = 0;

        for (FieldMetaData md : oldFieldMetaData) {
            // Skip these fields
            if (md.getName().indexOf("ID") == -1 && md.getName().indexOf("Timestamp") == -1
                    && md.getName().indexOf("LastEditedBy") == -1) {
                oldFieldMetaDataMap.put(md.getName(), md); // add to map for later

                // log.info(convertColumnName(md.getName())+" "+ md.getType());
                String sqlStr = "select count(" + md.getName() + ") from biologicalobjectattributes where "
                        + md.getName() + " is not null";
                ResultSet rs = stmt.executeQuery(sqlStr);
                if (rs.first() && rs.getInt(1) > 0) {
                    int rowCount = rs.getInt(1);
                    totalCount += rowCount;
                    counts.add(rowCount);

                    log.info(md.getName() + " has " + rowCount + " rows of values");

                    columnsInUse.add(md.getName());
                    AttributeDef attrDef = new AttributeDef();

                    String newName = convertColumnName(md.getName());
                    attrDef.setFieldName(newName);
                    log.debug("mapping[" + newName + "][" + md.getName() + "]");

                    // newNameToOldNameMap.put(newName, md.getName());

                    short dataType = -1;
                    if (typeMap != null) {
                        Short type = typeMap.get(md.getName());
                        if (type == null) {
                            dataType = type;
                        }
                    }

                    if (dataType == -1) {
                        dataType = getDataType(md.getName(), md.getType()).getType();
                    }

                    attrDef.setDataType(dataType);
                    attrDef.setDiscipline(discipline);
                    attrDef.setTableType(GenericDBConversion.TableType.CollectionObject.getType());
                    attrDef.setTimestampCreated(now);

                    attrDefs.put(md.getName(), attrDef);

                    try {
                        HibernateUtil.beginTransaction();
                        localSession.save(attrDef);
                        HibernateUtil.commitTransaction();

                    } catch (Exception e) {
                        log.error("******* " + e);
                        HibernateUtil.rollbackTransaction();
                        throw new RuntimeException(e);
                    }

                }
                rs.close();
            }
        } // for
        log.info("Total Number of Attrs: " + totalCount);

        // Now that we know which columns are being used we can start the conversion process

        log.info("biologicalobjectattributes columns in use: " + columnsInUse.size());
        if (columnsInUse.size() > 0) {
            int inx = 0;
            StringBuilder str = new StringBuilder("select BiologicalObjectAttributesID");
            for (String name : columnsInUse) {
                str.append(", ");
                str.append(name);
                inx++;
            }

            str.append(" from biologicalobjectattributes order by BiologicalObjectAttributesID");
            log.info("sql: " + str.toString());
            ResultSet rs = stmt.executeQuery(str.toString());

            int[] countVerify = new int[counts.size()];
            for (int i = 0; i < countVerify.length; i++) {
                countVerify[i] = 0;
            }
            boolean useHibernate = false;
            StringBuilder strBufInner = new StringBuilder();
            int recordCount = 0;
            while (rs.next()) {

                if (useHibernate) {
                    Criteria criteria = localSession.createCriteria(CollectionObject.class);
                    criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
                    criteria.add(Restrictions.eq("collectionObjectId", rs.getInt(1)));
                    List<?> list = criteria.list();
                    if (list.size() == 0) {
                        log.error("**** Can't find the CollectionObject " + rs.getInt(1));
                    } else {
                        CollectionObject colObj = (CollectionObject) list.get(0);

                        inx = 2; // skip the first column (the ID)
                        for (String name : columnsInUse) {
                            AttributeDef attrDef = attrDefs.get(name); // the needed
                                                                       // AttributeDef by name
                            FieldMetaData md = oldFieldMetaDataMap.get(name);

                            // Create the new Collection Object Attribute
                            CollectionObjectAttr colObjAttr = new CollectionObjectAttr();
                            colObjAttr.setCollectionObject(colObj);
                            colObjAttr.setDefinition(attrDef);
                            colObjAttr.setTimestampCreated(now);

                            // String oldName = newNameToOldNameMap.get(attrDef.getFieldName());
                            // log.debug("["+attrDef.getFieldName()+"]["+oldName+"]");

                            // log.debug(inx+" "+attrTypes[attrDef.getDataType()]+"
                            // "+md.getName()+" "+md.getType());
                            setData(rs, inx, attrTypes[attrDef.getDataType()], md, colObjAttr);

                            HibernateUtil.beginTransaction();
                            localSession.save(colObjAttr);
                            HibernateUtil.commitTransaction();

                            inx++;
                            if (recordCount % 2000 == 0) {
                                log.info("CollectionObjectAttr Records Processed: " + recordCount);
                            }
                            recordCount++;
                        } // for
                          // log.info("Done - CollectionObjectAttr Records Processed:
                          // "+recordCount);
                    }
                } else {
                    inx = 2; // skip the first column (the ID)
                    for (String name : columnsInUse) {
                        AttributeDef attrDef = attrDefs.get(name); // the needed AttributeDef
                                                                   // by name
                        FieldMetaData md = oldFieldMetaDataMap.get(name);

                        if (rs.getObject(inx) != null) {
                            Integer newRecId = (Integer) getMappedId(rs.getInt(1), "biologicalobjectattributes",
                                    "BiologicalObjectAttributesID");

                            Object data = getData(rs, inx, attrTypes[attrDef.getDataType()], md);
                            boolean isStr = data instanceof String;

                            countVerify[inx - 2]++;

                            strBufInner.setLength(0);
                            strBufInner.append("INSERT INTO collectionobjectattr VALUES (");
                            strBufInner.append("NULL");// Integer.toString(recordCount));
                            strBufInner.append(",");
                            strBufInner.append(getStrValue(isStr ? data : null));
                            strBufInner.append(",");
                            strBufInner.append(getStrValue(isStr ? null : data));
                            strBufInner.append(",");
                            strBufInner.append(getStrValue(now));
                            strBufInner.append(",");
                            strBufInner.append(getStrValue(now));
                            strBufInner.append(",");
                            strBufInner.append(newRecId.intValue());
                            strBufInner.append(",");
                            strBufInner.append(getStrValue(attrDef.getAttributeDefId()));
                            strBufInner.append(")");

                            try {
                                Statement updateStatement = newDBConn.createStatement();
                                // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                                removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                                if (false) {
                                    log.debug(strBufInner.toString());
                                }
                                updateStatement.executeUpdate(strBufInner.toString());
                                updateStatement.clearBatch();
                                updateStatement.close();
                                updateStatement = null;

                            } catch (SQLException e) {
                                log.error(strBufInner.toString());
                                log.error("Count: " + recordCount);
                                e.printStackTrace();
                                log.error(e);
                                throw new RuntimeException(e);
                            }

                            if (recordCount % 2000 == 0) {
                                log.info("CollectionObjectAttr Records Processed: " + recordCount);
                            }
                            recordCount++;
                        }
                        inx++;
                    } // for
                } // if
            } // while
            rs.close();
            stmt.close();

            log.info("Count Verification:");
            for (int i = 0; i < counts.size(); i++) {
                log.info(columnsInUse.get(i) + " [" + counts.get(i) + "][" + countVerify[i] + "] "
                        + (counts.get(i) - countVerify[i]));
            }
        }

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        throw new RuntimeException(e);
    }
    return true;
}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * @return/*from w w w .  ja v a 2 s  .  c  om*/
 */
public boolean convertLoanPreparations() {
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType);
    setIdentityInsertONCommandForSQLServer(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    if (getNumRecords(oldDBConn, "loanphysicalobject") == 0) {
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "loanpreparation",
                BasicSQLUtils.myDestinationServerType);
        return true;
    }

    Integer recCount = getCount(oldDBConn, "SELECT count(*) FROM loan WHERE Category = 0 ORDER BY LoanID");
    if (recCount == null || recCount == 0) {
        return true;
    }

    TableWriter tblWriter = convLogger.getWriter("convertLoanPreparations.html", "Loan Preparations");

    IdTableMapper loanPrepsMapper = (IdTableMapper) IdMapperMgr.getInstance().get("loanphysicalobject",
            "LoanPhysicalObjectID");
    if (loanPrepsMapper == null) {
        String msg = "LoanPrepsMapper not found. (This was a fatal error).";
        tblWriter.logError(msg);
        log.error(msg);
        return false;
    }

    TimeLogger timeLogger = new TimeLogger();

    try {
        Map<String, String> colNewToOldMap = createFieldNameMap(
                new String[] { "PreparationID", "PhysicalObjectID", });

        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("SELECT ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "loanphysicalobject");

        sql.append(buildSelectFieldList(names, "loanphysicalobject"));
        oldFieldNames.addAll(names);

        sql.append(
                " FROM loanphysicalobject INNER JOIN loan ON loanphysicalobject.LoanID = loan.LoanID WHERE loan.Category = 0");

        log.info(sql);

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "loanpreparation");

        log.info("Number of Fields in New loanpreparation " + newFieldMetaData.size());
        String sqlStr = sql.toString();

        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        for (String name : oldFieldNames) {
            oldNameIndex.put(name, inx++);
        }

        String tableName = "loanphysicalobject";

        int loanPhysIdIndex = oldNameIndex.get("LoanPhysicalObjectID");
        int quantityIndex = oldNameIndex.get("Quantity");
        int quantityRetIndex = oldNameIndex.get("QuantityReturned");
        int quantityResIndex = oldNameIndex.get("QuantityResolved");
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");

        log.info(sqlStr);
        ResultSet rs = stmt.executeQuery(sqlStr);

        if (hasFrame) {
            if (rs.last()) {
                setProcess(0, rs.getRow());
                rs.first();

            } else {
                rs.close();
                stmt.close();
                return true;
            }
        } else {
            if (!rs.first()) {
                rs.close();
                stmt.close();
                return true;
            }
        }

        String insertStmtStr = null;

        int count = 0;
        do {
            boolean skipInsert = false;

            int quantity = getIntValue(rs, quantityIndex);
            int quantityResolved = getIntValue(rs, quantityResIndex);
            int quantityReturned = getIntValue(rs, quantityRetIndex);
            Boolean isResolved = quantityReturned == quantity || quantityResolved == quantity;
            String lastEditedBy = rs.getString(lastEditedByInx);

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO loanpreparation " + fieldList + " VALUES (";
            }

            str.append(insertStmtStr);

            /*int    loanPhysId = rs.getInt(loanPhysIdIndex);
            String loanNumber = BasicSQLUtils.querySingleObj(oldDBConn, "SELECT LoanNumber FROM loan l INNER JOIN loanphysicalobject lp ON l.LoanID = lp.LoanID WHERE LoanPhysicalObjectID = "+loanPhysId);
            if (loanNumber != null && loanNumber.equals("25"))
            {
            debug = true;
            System.out.println("-------------------------------------\n"+loanPhysId);
            }*/

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0)
                    str.append(", ");

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldId = rs.getInt(loanPhysIdIndex);
                    Integer newID = loanPrepsMapper.get(oldId);
                    if (newID != null) {
                        str.append(getStrValue(newID));
                    } else {
                        String msg = String.format(
                                "loanPhysIdIndex: %d; Old Id: %d could be mapped to a new ID. (This was a fatal error).",
                                loanPhysIdIndex, oldId);
                        tblWriter.logError(msg);
                        log.error(msg);
                        return false;
                    }

                } else if (newFieldName.equals("ReceivedComments")) {
                    str.append("NULL");

                } else if (newFieldName.equals("IsResolved")) {
                    str.append(getStrValue(isResolved));

                } else if (newFieldName.equalsIgnoreCase("Version")) {
                    str.append("0");

                } else if (newFieldName.equalsIgnoreCase("DisciplineID")) {
                    str.append(getDisciplineId());

                } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) {
                    str.append(getModifiedByAgentId(lastEditedBy));

                } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) {
                    str.append(getCreatorAgentId(null));

                } else {
                    Integer index = null;
                    String oldMappedColName = colNewToOldMap.get(newFieldName);
                    if (oldMappedColName != null) {
                        index = oldNameIndex.get(oldMappedColName);

                    } else {
                        index = oldNameIndex.get(newFieldName);
                        oldMappedColName = newFieldName;
                    }

                    Object data;
                    if (index == null) {
                        String msg = "convertLoanPreparations - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            stmt.close();
                        //                            tblWriter.logError(msg);
                        //                            showError(msg);
                        //                            throw new RuntimeException(msg);
                        data = null;
                    } else {
                        data = rs.getObject(index);
                    }
                    if (data != null) {
                        int idInx = newFieldName.lastIndexOf("ID");
                        if (idMapperMgr != null && idInx > -1) {
                            IdMapperIFace idMapper = idMapperMgr.get(tableName, oldMappedColName);
                            if (idMapper != null) {
                                Integer oldId = rs.getInt(index);
                                data = idMapper.get(oldId);
                                if (data == null) {
                                    String msg = "No Map ID for [" + tableName + "][" + oldMappedColName
                                            + "] for ID[" + oldId + "]";
                                    log.error(msg);
                                    tblWriter.logError(msg);
                                    skipInsert = true;
                                }
                            } else {
                                String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]";
                                log.error(msg);
                                tblWriter.logError(msg);
                                skipInsert = true;
                            }
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));
                }
            }
            str.append(")");

            if (hasFrame) {
                if (count % 500 == 0) {
                    setProcess(count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info("LoanPreparation Records: " + count);
                }
            }

            try {
                if (!skipInsert) {
                    Statement updateStatement = newDBConn.createStatement();
                    if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                        removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                    }
                    // log.debug("executring: " + str.toString());
                    // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                    updateStatement.executeUpdate(str.toString());
                    updateStatement.clearBatch();
                    updateStatement.close();
                    updateStatement = null;
                }

            } catch (SQLException e) {
                log.error("Count: " + count);
                e.printStackTrace();
                log.error(e);
                rs.close();
                stmt.close();
                throw new RuntimeException(e);
            }

            count++;
            // if (count > 10) break;
        } while (rs.next());

        if (hasFrame) {
            setProcess(count);
            log.info("Processed LoanPreparation " + count + " records.");
        } else {
            log.info("Processed LoanPreparation " + count + " records.");
        }
        rs.close();

        stmt.close();

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
                BasicSQLUtils.myDestinationServerType);
        throw new RuntimeException(e);
    }
    log.info("Done processing LoanPhysicalObject");
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
            BasicSQLUtils.myDestinationServerType);

    //tblWriter.log(String.format("Loan Preps Processing Time: %s", timeLogger.end()));
    tblWriter.close();

    return true;

}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the CollectionObject and CollectionObjectCatalog Records into the new schema
 * CollectionObject table. All "logical" records are moved to the CollectionObject table and all
 * "physical" records are moved to the Preparation table.
 * @return true if no errors/*  w  ww . j  av a  2 s. co  m*/
 */
@SuppressWarnings("cast")
public boolean convertCollectionObjects(final boolean useNumericCatNumbers, final boolean usePrefix) {
    final String ZEROES = "000000000";

    UIFieldFormatterIFace formatter0 = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumber");
    log.debug(formatter0);

    UIFieldFormatterIFace formatter = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumberNumeric");
    log.debug(formatter);

    DisciplineType dt;
    Discipline discipline = (Discipline) AppContextMgr.getInstance().getClassObject(Discipline.class);
    if (discipline != null) {
        System.out.println("discipline.getType()[" + discipline.getType() + "]");
        dt = DisciplineType.getDiscipline(discipline.getType());
    } else {
        Vector<Object[]> list = query(newDBConn, "SELECT Type FROM discipline");
        String typeStr = (String) list.get(0)[0];
        System.out.println("typeStr[" + typeStr + "]");
        dt = DisciplineType.getDiscipline(typeStr);
    }

    Pair<Integer, Boolean> objTypePair = dispToObjTypeHash.get(dt.getDisciplineType());
    if (objTypePair == null) {
        System.out.println("objTypePair is null dt[" + dt.getName() + "][" + dt.getTitle() + "]");

        for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) {
            Pair<Integer, Boolean> p = dispToObjTypeHash.get(key);
            System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]");
        }

    } else if (objTypePair.first == null) {
        System.out.println("objTypePair.first is null dt[" + dt + "]");

        for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) {
            Pair<Integer, Boolean> p = dispToObjTypeHash.get(key);
            System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]");
        }

    }
    //int objTypeId  = objTypePair.first;
    //boolean isEmbedded = objTypePair.second;

    idMapperMgr.dumpKeys();
    IdHashMapper colObjTaxonMapper = (IdHashMapper) idMapperMgr.get("ColObjCatToTaxonType".toLowerCase());
    IdHashMapper colObjAttrMapper = (IdHashMapper) idMapperMgr
            .get("biologicalobjectattributes_BiologicalObjectAttributesID");
    IdHashMapper colObjMapper = (IdHashMapper) idMapperMgr
            .get("collectionobjectcatalog_CollectionObjectCatalogID");

    colObjTaxonMapper.setShowLogErrors(false); // NOTE: TURN THIS ON FOR DEBUGGING or running new Databases through it
    colObjAttrMapper.setShowLogErrors(false);

    //IdHashMapper stratMapper    = (IdHashMapper)idMapperMgr.get("stratigraphy_StratigraphyID");
    //IdHashMapper stratGTPMapper = (IdHashMapper)idMapperMgr.get("stratigraphy_GeologicTimePeriodID");

    String[] fieldsToSkip = { "ContainerID", "ContainerItemID", "AltCatalogNumber", "GUID", "ContainerOwnerID",
            "RepositoryAgreementID", "GroupPermittedToView", // this may change when converting Specify 5.x
            "CollectionObjectID", "VisibilitySetBy", "ContainerOwnerID", "InventoryDate", "ObjectCondition",
            "Notifications", "ProjectNumber", "Restrictions", "YesNo3", "YesNo4", "YesNo5", "YesNo6",
            "FieldNotebookPageID", "ColObjAttributesID", "DNASequenceID", "AppraisalID", "TotalValue",
            "Description", "SGRStatus", "OCR", "ReservedText", "Text3" };

    HashSet<String> fieldsToSkipHash = new HashSet<String>();
    for (String fName : fieldsToSkip) {
        fieldsToSkipHash.add(fName);
    }

    TableWriter tblWriter = convLogger.getWriter("convertCollectionObjects.html", "Collection Objects");

    String msg = "colObjTaxonMapper: " + colObjTaxonMapper.size();
    log.info(msg);
    tblWriter.log(msg);

    setIdentityInsertONCommandForSQLServer(newDBConn, "collectionobject",
            BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    TreeSet<String> badSubNumberCatNumsSet = new TreeSet<String>();

    TimeLogger timeLogger = new TimeLogger();

    try {
        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("select ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "collectionobject");

        sql.append(buildSelectFieldList(names, "collectionobject"));
        sql.append(", ");
        oldFieldNames.addAll(names);

        names = getFieldNamesFromSchema(oldDBConn, "collectionobjectcatalog");
        sql.append(buildSelectFieldList(names, "collectionobjectcatalog"));
        oldFieldNames.addAll(names);

        String fromClause = " FROM collectionobject Inner Join collectionobjectcatalog ON "
                + "collectionobject.CollectionObjectID = collectionobjectcatalog.CollectionObjectCatalogID "
                + "WHERE (collectionobject.DerivedFromID IS NULL) AND collectionobjectcatalog.CollectionObjectCatalogID = ";
        sql.append(fromClause);

        log.info(sql);
        String sqlStr = sql.toString();

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "collectionobject");

        log.info("Number of Fields in New CollectionObject " + newFieldMetaData.size());

        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        log.info("---- Old Names ----");
        for (String name : oldFieldNames) {
            log.info("[" + name + "][" + inx + "]");
            oldNameIndex.put(name, inx++);
        }

        log.info("---- New Names ----");
        for (FieldMetaData fmd : newFieldMetaData) {
            log.info("[" + fmd.getName() + "]");
        }
        String tableName = "collectionobject";

        Statement newStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        newStmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rsLooping = newStmt.executeQuery(
                "SELECT OldID, NewID FROM collectionobjectcatalog_CollectionObjectCatalogID ORDER BY OldID");

        if (hasFrame) {
            if (rsLooping.last()) {
                setProcess(0, rsLooping.getRow());
                rsLooping.first();

            } else {
                rsLooping.close();
                stmt.close();
                return true;
            }
        } else {
            if (!rsLooping.first()) {
                rsLooping.close();
                stmt.close();
                return true;
            }
        }

        int boaCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM biologicalobjectattributes"); // ZZZ

        PartialDateConv partialDateConv = new PartialDateConv();

        Statement stmt2 = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt2.setFetchSize(Integer.MIN_VALUE);

        int catNumInx = oldNameIndex.get("CatalogNumber");
        int catDateInx = oldNameIndex.get("CatalogedDate");
        int catSeriesIdInx = oldNameIndex.get("CatalogSeriesID");
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");

        /*int     grpPrmtViewInx    = -1;
        Integer grpPrmtViewInxObj = oldNameIndex.get("GroupPermittedToView");
        if (grpPrmtViewInxObj != null)
        {
        grpPrmtViewInx = grpPrmtViewInxObj + 1;
        }*/

        Hashtable<Integer, CollectionInfo> oldCatSeriesIDToCollInfo = new Hashtable<Integer, CollectionInfo>();
        for (CollectionInfo ci : collectionInfoShortList) {
            if (ci.getCatSeriesId() != null) {
                oldCatSeriesIDToCollInfo.put(ci.getCatSeriesId(), ci);
            }
        }

        String insertStmtStr = null;

        /*String catIdTaxIdStrBase = "SELECT cc.CollectionObjectCatalogID, cc.CatalogSeriesID, ct.TaxonomyTypeID "
                                + "FROM collectionobjectcatalog AS cc "
                                + "Inner Join collectionobject AS co ON cc.CollectionObjectCatalogID = co.CollectionObjectID "
                                + "Inner Join collectiontaxonomytypes as ct ON co.CollectionObjectTypeID = ct.BiologicalObjectTypeID "
                                + "where cc.CollectionObjectCatalogID = ";*/

        int colObjAttrsNotMapped = 0;
        int count = 0;
        boolean skipRecord = false;
        do {
            String catSQL = sqlStr + rsLooping.getInt(1);
            //log.debug(catSQL);
            ResultSet rs = stmt.executeQuery(catSQL);
            if (!rs.next()) {
                log.error("Couldn't find CO with old  id[" + rsLooping.getInt(1) + "] " + catSQL);
                continue;
            }

            partialDateConv.nullAll();

            skipRecord = false;

            CollectionInfo collInfo = oldCatSeriesIDToCollInfo.get(rs.getInt(catSeriesIdInx));

            /*String catIdTaxIdStr = catIdTaxIdStrBase + rs.getInt(1);
            //log.info(catIdTaxIdStr);
                    
            ResultSet rs2 = stmt2.executeQuery(catIdTaxIdStr);
            if (!rs2.next())
            {
            log.info("QUERY failed to return results:\n"+catIdTaxIdStr+"\n");
            continue;
            }
            Integer catalogSeriesID = rs2.getInt(2);
            Integer taxonomyTypeID  = rs2.getInt(3);
            Integer newCatSeriesId  = collectionHash.get(catalogSeriesID + "_" + taxonomyTypeID);
            String  prefix          = prefixHash.get(catalogSeriesID + "_" + taxonomyTypeID);
            rs2.close();
                    
            if (newCatSeriesId == null)
            {
            msg = "Can't find " + catalogSeriesID + "_" + taxonomyTypeID;
            log.info(msg);
            tblWriter.logError(msg);
            continue;
            }*/

            /*if (false)
            {
            String stratGTPIdStr = "SELECT co.CollectionObjectID, ce.CollectingEventID, s.StratigraphyID, g.GeologicTimePeriodID FROM collectionobject co " +
                "LEFT JOIN collectingevent ce ON co.CollectingEventID = ce.CollectingEventID  " +
                "LEFT JOIN stratigraphy s ON ce.CollectingEventID = s.StratigraphyID  " +
                "LEFT JOIN geologictimeperiod g ON s.GeologicTimePeriodID = g.GeologicTimePeriodID  " +
                "WHERE co.CollectionObjectID  = " + rs.getInt(1);
            log.info(stratGTPIdStr);
            rs2 = stmt2.executeQuery(stratGTPIdStr);
                    
            Integer coId = null;
            Integer ceId = null;
            Integer stId = null;
            Integer gtpId = null;
            if (rs2.next())
            {
                coId = rs2.getInt(1);
                ceId = rs2.getInt(2);
                stId = rs2.getInt(3);
                gtpId = rs2.getInt(4);
            }
            rs2.close();
            }*/

            String catalogNumber = null;
            String colObjId = null;

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO collectionobject " + fieldList + "  VALUES (";
            }
            str.append(insertStmtStr);

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0) {
                    str.append(", ");
                }

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldColObjId = rs.getInt(1);
                    Integer newColObjId = colObjMapper.get(oldColObjId);

                    if (newColObjId == null) {
                        msg = "Couldn't find new ColObj Id for old [" + oldColObjId + "]";
                        tblWriter.logError(msg);
                        showError(msg);
                        throw new RuntimeException(msg);
                    }

                    colObjId = getStrValue(newColObjId);
                    if (contains(colObjId, '.')) {
                        String msgStr = String.format("CatalogNumber '%d' contains a decimal point.", colObjId);
                        log.debug(msgStr);
                        tblWriter.logError(msgStr);
                        skipRecord = true;
                        break;
                    }
                    str.append(colObjId);

                    if (useNumericCatNumbers) {
                        catalogNumber = rs.getString(catNumInx);

                        if (catalogNumber != null) {
                            int catNumInt = (int) Math.abs(rs.getDouble(catNumInx));
                            catalogNumber = Integer.toString(catNumInt);

                            if (catalogNumber.length() > 0 && catalogNumber.length() < ZEROES.length()) {
                                catalogNumber = "\"" + ZEROES.substring(catalogNumber.length()) + catalogNumber
                                        + "\"";

                            } else if (catalogNumber.length() > ZEROES.length()) {
                                showError(
                                        "Catalog Number[" + catalogNumber + "] is too long for formatter of 9");
                            }

                        } else {
                            String mssg = "Empty catalog number.";
                            log.debug(mssg);
                            //showError(msg);
                            tblWriter.logError(mssg);
                        }

                    } else {
                        String prefix = collInfo.getCatSeriesPrefix();

                        float catNum = rs.getFloat(catNumInx);
                        catalogNumber = "\"" + (usePrefix && isNotEmpty(prefix) ? (prefix + "-") : "")
                                + String.format("%9.0f", catNum).trim() + "\"";
                    }

                    int subNumber = rs.getInt(oldNameIndex.get("SubNumber"));
                    if (subNumber < 0 || rs.wasNull()) {
                        badSubNumberCatNumsSet.add(catalogNumber);

                        skipRecord = true;
                        //msg = "Collection Object is being skipped because SubNumber is less than zero CatalogNumber["+ catalogNumber + "]";
                        //log.error(msg);
                        //tblWriter.logError(msg);
                        //showError(msg);
                        break;
                    }

                } else if (fieldsToSkipHash.contains(newFieldName)) {
                    str.append("NULL");

                } else if (newFieldName.equals("CollectionID")) // User/Security changes
                {
                    str.append(collInfo.getCollectionId());

                } else if (newFieldName.equals("Version")) // User/Security changes
                {
                    str.append("0");

                } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes
                {
                    str.append(getCreatorAgentId(null));

                } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes
                {
                    String lastEditedByStr = rs.getString(lastEditedByInx);
                    str.append(getModifiedByAgentId(lastEditedByStr));

                } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes
                {
                    str.append(collInfo.getCollectionId());

                } else if (newFieldName.equals("PaleoContextID")) {
                    str.append("NULL");// newCatSeriesId);

                } else if (newFieldName.equals("CollectionObjectAttributeID")) // User/Security changes
                {
                    Object idObj = rs.getObject(1);
                    if (idObj != null) {
                        Integer coId = rs.getInt(1);
                        Integer newId = colObjAttrMapper.get(coId);
                        if (newId != null) {
                            str.append(getStrValue(newId));
                        } else {
                            if (boaCnt > 0)
                                colObjAttrsNotMapped++;
                            str.append("NULL");
                        }
                    } else {
                        str.append("NULL");
                    }

                } else if (newFieldName.equals("CatalogedDate")) {
                    if (partialDateConv.getDateStr() == null) {
                        getPartialDate(rs.getObject(catDateInx), partialDateConv);
                    }
                    str.append(partialDateConv.getDateStr());

                } else if (newFieldName.equals("CatalogedDatePrecision")) {
                    if (partialDateConv.getDateStr() == null) {
                        getPartialDate(rs.getObject(catDateInx), partialDateConv);
                    }
                    str.append(partialDateConv.getPartial());

                } else if (newFieldName.equals("CatalogedDateVerbatim")) {
                    if (partialDateConv.getDateStr() == null) {
                        getPartialDate(rs.getObject(catDateInx), partialDateConv);
                    }
                    str.append(partialDateConv.getVerbatim());

                } else if (newFieldName.equals("Availability")) {
                    str.append("NULL");

                } else if (newFieldName.equals("CatalogNumber")) {
                    str.append(catalogNumber);

                } else if (newFieldName.equals("Visibility")) // User/Security changes
                {
                    //str.append(grpPrmtViewInx > -1 ? rs.getObject(grpPrmtViewInx) : "NULL");
                    str.append("0");

                } else if (newFieldName.equals("VisibilitySetByID")) // User/Security changes
                {
                    str.append("NULL");

                } else if (newFieldName.equals("CountAmt")) {
                    Integer index = oldNameIndex.get("Count1");
                    if (index == null) {
                        index = oldNameIndex.get("Count");
                    }
                    Object countObj = rs.getObject(index);
                    if (countObj != null) {
                        str.append(getStrValue(countObj, newFieldMetaData.get(i).getType()));
                    } else {
                        str.append("NULL");
                    }

                } else {
                    Integer index = oldNameIndex.get(newFieldName);
                    Object data;
                    if (index == null) {
                        msg = "convertCollectionObjects - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            tblWriter.logError(msg);
                        //                            showError(msg);
                        data = null;
                        // for (String key : oldNameIndex.keySet())
                        // {
                        // log.info("["+key+"]["+oldNameIndex.get(key)+"]");
                        // }
                        //stmt.close();
                        //throw new RuntimeException(msg);
                    } else {

                        data = rs.getObject(index);
                    }
                    if (data != null) {
                        int idInx = newFieldName.lastIndexOf("ID");
                        if (idMapperMgr != null && idInx > -1) {
                            IdMapperIFace idMapper = idMapperMgr.get(tableName, newFieldName);
                            if (idMapper != null) {
                                Integer origValue = rs.getInt(index);
                                data = idMapper.get(origValue);
                                if (data == null) {
                                    msg = "No value [" + origValue + "] in map  [" + tableName + "]["
                                            + newFieldName + "]";
                                    log.error(msg);
                                    tblWriter.logError(msg);
                                    //showError(msg);
                                }
                            } else {
                                msg = "No Map for [" + tableName + "][" + newFieldName + "]";
                                log.error(msg);
                                tblWriter.logError(msg);
                                //showError(msg);
                            }
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));
                }
            }

            if (!skipRecord) {
                str.append(")");
                // log.info("\n"+str.toString());
                if (hasFrame) {
                    if (count % 500 == 0) {
                        setProcess(count);
                    }
                    if (count % 5000 == 0) {
                        log.info("CollectionObject Records: " + count);
                    }

                } else {
                    if (count % 2000 == 0) {
                        log.info("CollectionObject Records: " + count);
                    }
                }

                try {
                    Statement updateStatement = newDBConn.createStatement();
                    if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                        removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                    }
                    // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                    //if (count < 50) System.err.println(str.toString());

                    updateStatement.executeUpdate(str.toString());
                    updateStatement.clearBatch();
                    updateStatement.close();
                    updateStatement = null;

                } catch (SQLException e) {
                    log.error("Count: " + count);
                    log.error("Key: [" + colObjId + "][" + catalogNumber + "]");
                    log.error("SQL: " + str.toString());
                    e.printStackTrace();
                    log.error(e);
                    showError(e.getMessage());
                    rs.close();
                    stmt.close();
                    throw new RuntimeException(e);
                }

                count++;
            } else {
                tblWriter.logError("Skipping - CatNo:" + catalogNumber);
            }
            // if (count > 10) break;

            rs.close();

        } while (rsLooping.next());

        /*if (boaCnt > 0)
        {
        msg = "CollectionObjectAttributes not mapped: " + colObjAttrsNotMapped + " out of "+boaCnt;
        log.info(msg);
        tblWriter.logError(msg);
        }*/

        stmt2.close();

        if (hasFrame) {
            setProcess(count);
        } else {
            log.info("Processed CollectionObject " + count + " records.");
        }

        tblWriter.log(String.format("Collection Objects Processing Time: %s", timeLogger.end()));

        tblWriter.log("Processed CollectionObject " + count + " records.");
        rsLooping.close();
        newStmt.close();
        stmt.close();

        tblWriter.append(
                "<br><br><b>Catalog Numbers rejected because the SubNumber was NULL or less than Zero</b><br>");
        tblWriter.startTable();
        tblWriter.logHdr("Catalog Number");
        for (String catNum : badSubNumberCatNumsSet) {
            tblWriter.log(catNum);
        }
        tblWriter.endTable();

    } catch (SQLException e) {
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject",
                BasicSQLUtils.myDestinationServerType);
        e.printStackTrace();
        log.error(e);
        tblWriter.logError(e.getMessage());
        showError(e.getMessage());
        throw new RuntimeException(e);

    } finally {
        tblWriter.close();
    }
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject",
            BasicSQLUtils.myDestinationServerType);

    return true;
}

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Create a data type.//from ww  w.  j a v  a 2  s.  c  om
 * @param taxonomyTypeName the name
 * @return the ID (record id) of the data type
 */
public int createDataType() {
    int dataTypeId = -1;
    String dataTypeName = "Biota";//getStandardDataTypeName(taxonomyTypeName);
    if (dataTypeName == null) {
        return dataTypeId;
    }

    try {
        if (dataTypeNameToIds.get(dataTypeName) == null) {
            /*
             * describe datatype;
             * +------------+-------------+------+-----+---------+----------------+ | Field |
             * Type | Null | Key | Default | Extra |
             * +------------+-------------+------+-----+---------+----------------+ | DataTypeID |
             * int(11) | NO | PRI | | auto_increment | | Name | varchar(50) | YES | | | |
             * +------------+-------------+------+-----+---------+----------------+
             */

            Statement updateStatement = newDBConn.createStatement();
            // String updateStr = "INSERT INTO datatype (DataTypeID, TimestampCreated,
            // TimestampModified, LastEditedBy, Name) VALUES
            // (null,'"+nowStr+"','"+nowStr+"',NULL,'"+dataTypeName+"')";
            // Meg removed explicit insert of null value into DataTypeID, it was failing on SQL
            // Server
            // String updateStr = "INSERT INTO datatype ( TimestampCreated, TimestampModified,
            // Name, CreatedByAgentID, ModifiedByAgentID) VALUES
            // ('"+nowStr+"','"+nowStr+"','"+dataTypeName+"',"+getCreatorAgentId(null)+","+getModifiedByAgentId(null)+")";
            String updateStr = "INSERT INTO datatype ( TimestampCreated, TimestampModified, Name, Version, CreatedByAgentID, ModifiedByAgentID) "
                    + "VALUES ('" + nowStr + "','" + nowStr + "','" + dataTypeName + "', 0, "
                    + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + ")";
            updateStatement.executeUpdate(updateStr);
            updateStatement.clearBatch();
            updateStatement.close();
            updateStatement = null;

            dataTypeId = getHighestId(newDBConn, "DataTypeID", "datatype");
            log.info("Created new datatype[" + dataTypeName + "]");

            dataTypeNameToIds.put(dataTypeName, dataTypeId);

        } else {
            dataTypeId = dataTypeNameToIds.get(dataTypeName);
            log.info("Reusing new datatype[" + dataTypeName + "]");
        }
    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        showError(e.getMessage());
        throw new RuntimeException(e);
    }
    return dataTypeId;
}