Example usage for java.sql Statement setFetchSize

List of usage examples for java.sql Statement setFetchSize

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

Introduction

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

Prototype

void setFetchSize(int rows) throws SQLException;

Source Link

Document

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement.

Usage

From source file:edu.arizona.rice.kew.docsearch.dao.impl.IndexTableSearchHandler.java

@Override
public void run() {
    Connection conn = null;//from   w  ww . j av  a 2 s .c o  m
    ResultSet res = null;
    Statement stmt = null;
    try {
        long start = System.currentTimeMillis();
        conn = dataSource.getConnection();
        conn.setReadOnly(true);

        char dataTypeCode = DocumentSearchConstants.TYPE_CODE_BY_ATTRIBUTE_TABLE_MAP.get(indexTableName);
        stmt = conn.createStatement();
        stmt.setFetchSize(fetchSize);

        for (List<String> inlist : DocumentSearchUtils.getInLists(docids)) {
            res = stmt.executeQuery(getSqlSelect(inlist));

            while (res.next()) {
                String docid = res.getString(1);
                String keyCd = res.getString(2);
                String val = res.getString(3);

                if (StringUtils.isNotBlank(val)) {
                    List<DocumentAttribute> attlist = attributeMap.get(docid);

                    if (attlist == null) {
                        attributeMap.put(docid, attlist = new ArrayList<DocumentAttribute>());
                    }

                    attlist.add(buildDocumentAttribute(dataTypeCode, keyCd, res));
                }
            }
        }

        if (LOG.isDebugEnabled()) {
            LOG.debug("index table query [" + indexTableName + "] elapsed time(sec): "
                    + (System.currentTimeMillis() - start) / 1000);
        }
    }

    catch (Exception ex) {
        exception = ex;
    }

    finally {
        done = true;
        DocumentSearchUtils.closeDbObjects(conn, stmt, res);
    }
}

From source file:BQJDBC.QueryResultTest.QueryResultTest.java

@Test
public void QueryResultTest12() {
    int limitNum = 40000;
    final String sql = "SELECT weight_pounds  FROM publicdata:samples.natality LIMIT " + limitNum;

    this.logger.info("Test number: 12");
    this.logger.info("Running query:" + sql);

    java.sql.ResultSet Result = null;
    try {/*from   w  w w . jav a 2s. c  om*/
        Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stm.setFetchSize(1000);
        Result = stm.executeQuery(sql);
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail("SQLException" + e.toString());
    }
    Assert.assertNotNull(Result);
    try {/*
         int j = 0;
         for (int i = 0; i < limitNum-1; i++) {
         if(i%1000 == 0) {
             logger.debug("fetched 1k for the " + ++j + ". time");
         }
         Assert.assertTrue(Result.next());  
         }*/
        Result.absolute(limitNum);
        Assert.assertTrue(true);
    } catch (SQLException e) {
        e.printStackTrace();
        Assert.fail();
    }
}

From source file:net.antidot.semantic.rdf.rdb2rdf.dm.core.TupleExtractor.java

public HashMap<Key, Tuple> getReferencedTuples(DriverType driver, Tuple tuple)
        throws UnsupportedEncodingException {
    HashMap<Key, Tuple> result = new HashMap<Key, Tuple>();
    try {/*from  w  w  w  .  j  av a2  s. c o  m*/
        // Extract target name table
        HashSet<Key> referencedKeys = engine.getReferencedKeys(tuple);
        log.debug("[TupleExtractor:getReferencedTuples] Referenced keys : " + referencedKeys);
        Statement referencedStatement = conn.createStatement();
        referencedStatement.setFetchSize(0); // Default behaviour = no cursor mode
        Key primaryIsForeignKey = null;
        for (Key key : referencedKeys) {
            if (engine.isPrimaryKey(key, tuple)) {
                currentPrimaryIsForeignKey = getPrimaryIsForeignKey(key, tuple);
                if (currentPrimaryIsForeignKey == null)
                    throw new IllegalStateException(
                            "[TupleExtractor:getReferencedTuples] No primary-is-foreign key extracted whereas it's has been detected.");
                else
                    log.debug("[TupleExtractor:getReferencedTuples] Primary-is-foreign key detected : "
                            + primaryIsForeignKey);
            } else {

                String referencedTableName = engine.getReferencedTableName(key);
                extractReferencedSets(referencedTableName);
                String sqlQuery = engine.constructReferencedSQLQuery(driver, currentReferencedHeaderSet,
                        referencedTableName, key, tuple);
                reinitCurrentReferencedCursors();
                log.debug("[TupleExtractor:getReferencedTuples] Execute query : " + sqlQuery);
                ResultSet referencedValueSet = referencedStatement.executeQuery(sqlQuery);

                // Extract values in database
                if (referencedValueSet.next()) {
                    Tuple referencedTuple = engine.extractReferencedTupleFrom(referencedValueSet,
                            currentReferencedHeaderSet, currentReferencedPrimaryKeySet,
                            currentReferencedImportedKeySet, engine.getReferencedTableName(key), driver, null,
                            currentNbTuplesExtractedInTable);
                    if (referencedValueSet.next())
                        throw new IllegalStateException(
                                "[TupleExtractor:getReferencedTuples] Foreign key matches with one element and more, it's unconsistent.");
                    result.put(key, referencedTuple);
                }
            }
        }
    } catch (SQLException e) {
        log.error("[TupleExtractor:getReferencedTuples] Error SQL during extracting referenced tuples.");
        e.printStackTrace();
    }
    return result;
}

From source file:com.appeligo.amazon.ProgramIndexer.java

protected void addNewPrograms() throws SQLException, IOException {
    Connection conn = getConnection();

    Statement s = null;
    ResultSet rs = null;//w  ww.j av  a  2s. c om
    try {
        if (log.isInfoEnabled()) {
            log.info("Querying epg database for programs...");
        }
        s = conn.createStatement();
        s.setFetchSize(fetchSize);
        //            rs = s.executeQuery("select program_id, title from programs");
        rs = s.executeQuery("select program_id, title from programs limit 4 offset 14");

        if (log.isInfoEnabled()) {
            log.info("Traversing program list and adding all missing programs.");
        }
        int count = 0;
        int addedCount = 0;
        while (rs.next()) {
            int i = 1;
            String programId = rs.getString(i++);
            String title = rs.getString(i++);

            if (!containsProgram(programId)) {
                //it's not already in the index
                if (addProgram(programId, title)) {
                    addedCount++;
                } else {
                    addMarkerProgram(programId);
                }
            }
            count++;
        }
        if (log.isInfoEnabled()) {
            log.info(count + " programs were evaluated.");
        }
        if (log.isInfoEnabled()) {
            log.info(addedCount + " programs were added.");
        }

    } finally {
        close(rs);
        close(s);
    }

}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.MySqlEngine.java

@Override
public synchronized ResultIterator iterator(String query) throws DatabaseEngineException {
    List<Map<String, ResultColumn>> res = new ArrayList<>();
    Statement stmt = null;

    try {//from   w w w .  j  a  va  2 s  .  c o  m
        getConnection();
        stmt = conn.createStatement(TYPE_FORWARD_ONLY, CONCUR_READ_ONLY);
        stmt.setFetchSize(properties.getFetchSize());

        return createResultIterator(stmt, query);

    } catch (final Exception e) {
        throw new DatabaseEngineException("Error querying", e);
    }
}

From source file:com.alibaba.wasp.jdbc.TestJdbcStatement.java

@Test
public void testStatement() throws SQLException, IOException, InterruptedException {
    Statement stat = conn.createStatement();

    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability());
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability());
    // ignored//from   w  w  w. j  av  a 2  s  . c o  m
    stat.setCursorName("x");
    // fixed return value
    assertEquals(stat.getFetchDirection(), ResultSet.FETCH_FORWARD);
    // ignored
    stat.setFetchDirection(ResultSet.FETCH_REVERSE);
    // ignored
    stat.setMaxFieldSize(100);

    assertEquals(conf.getInt(FConstants.WASP_JDBC_FETCHSIZE, FConstants.DEFAULT_WASP_JDBC_FETCHSIZE),
            stat.getFetchSize());
    stat.setFetchSize(10);
    assertEquals(10, stat.getFetchSize());
    stat.setFetchSize(0);
    assertEquals(conf.getInt(FConstants.WASP_JDBC_FETCHSIZE, FConstants.DEFAULT_WASP_JDBC_FETCHSIZE),
            stat.getFetchSize());
    assertEquals(ResultSet.TYPE_FORWARD_ONLY, stat.getResultSetType());
    Statement stat2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY,
            ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, stat2.getResultSetType());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, stat2.getResultSetHoldability());
    assertEquals(ResultSet.CONCUR_READ_ONLY, stat2.getResultSetConcurrency());
    assertEquals(0, stat.getMaxFieldSize());
    assertTrue(!((JdbcStatement) stat2).isClosed());
    stat2.close();
    assertTrue(((JdbcStatement) stat2).isClosed());

    ResultSet rs;
    int count;
    boolean result;

    stat.execute("CREATE TABLE TEST {REQUIRED INT64 ID;" + "REQUIRED STRING VALUE; }PRIMARY KEY(ID), "
            + "ENTITY GROUP ROOT,ENTITY GROUP KEY(ID);");

    TEST_UTIL.waitTableEnabled(Bytes.toBytes("TEST"), 5000);

    ResultInHBasePrinter.printMETA(conf, LOG);
    ResultInHBasePrinter.printFMETA(conf, LOG);
    ResultInHBasePrinter.printTable("test", "WASP_ENTITY_TEST", conf, LOG);

    conn.getTypeMap();

    // this method should not throw an exception - if not supported, this
    // calls are ignored

    assertEquals(ResultSet.CONCUR_READ_ONLY, stat.getResultSetConcurrency());

    // stat.cancel();
    stat.setQueryTimeout(10);
    assertTrue(stat.getQueryTimeout() == 10);
    stat.setQueryTimeout(0);
    assertTrue(stat.getQueryTimeout() == 0);
    // assertThrows(SQLErrorCode.INVALID_VALUE_2, stat).setQueryTimeout(-1);
    assertTrue(stat.getQueryTimeout() == 0);
    trace("executeUpdate");
    count = stat.executeUpdate("INSERT INTO TEST (ID,VALUE) VALUES (1,'Hello')");
    assertEquals(1, count);
    count = stat.executeUpdate("INSERT INTO TEST (VALUE,ID) VALUES ('JDBC',2)");
    assertEquals(1, count);
    count = stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE ID=1");
    assertEquals(1, count);

    count = stat.executeUpdate("DELETE FROM TEST WHERE ID=-1");
    assertEquals(0, count);
    count = stat.executeUpdate("DELETE FROM TEST WHERE ID=1");
    assertEquals(1, count);
    count = stat.executeUpdate("DELETE FROM TEST WHERE ID=2");
    assertEquals(1, count);

    result = stat.execute("INSERT INTO TEST(ID,VALUE) VALUES(1,'Hello')");
    assertTrue(!result);
    result = stat.execute("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
    assertTrue(!result);
    result = stat.execute("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
    assertTrue(!result);
    result = stat.execute("DELETE FROM TEST WHERE ID=1");
    assertTrue(!result);
    result = stat.execute("DELETE FROM TEST WHERE ID=2");
    assertTrue(!result);
    result = stat.execute("DELETE FROM TEST WHERE ID=3");
    assertTrue(!result);

    // getMoreResults
    rs = stat.executeQuery("SELECT ID,VALUE FROM TEST WHERE ID=1");
    assertFalse(stat.getMoreResults());
    assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next();
    assertTrue(stat.getUpdateCount() == -1);
    count = stat.executeUpdate("DELETE FROM TEST WHERE ID=1");
    assertFalse(stat.getMoreResults());
    assertTrue(stat.getUpdateCount() == -1);

    WaspAdmin admin = new WaspAdmin(TEST_UTIL.getConfiguration());
    admin.disableTable("TEST");
    stat.execute("DROP TABLE TEST");
    admin.waitTableNotLocked("TEST".getBytes());
    stat.executeUpdate("DROP TABLE IF EXISTS TEST");

    assertTrue(stat.getWarnings() == null);
    stat.clearWarnings();
    assertTrue(stat.getWarnings() == null);
    assertTrue(conn == stat.getConnection());

    admin.close();
    stat.close();
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java

/**
 * //w w w . j  a  v a2 s  .co  m
 */
public void processNullKingdom() {
    PrintWriter pw = null;
    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

    System.out.println("----------------------- Searching NULL ----------------------- ");

    String gbifWhereStr = "FROM raw WHERE kingdom IS NULL";

    long startTime = System.currentTimeMillis();

    String cntGBIFSQL = "SELECT COUNT(*) " + gbifWhereStr;// + " LIMIT 0,1000";
    String gbifSQL = gbifSQLBase + gbifWhereStr;

    System.out.println(cntGBIFSQL);

    long totalRecs = BasicSQLUtils.getCount(srcConn, cntGBIFSQL);
    long procRecs = 0;
    int secsThreshold = 0;

    String msg = String.format("Query: %8.2f secs", (double) (System.currentTimeMillis() - startTime) / 1000.0);
    System.out.println(msg);
    pw.println(msg);
    pw.flush();

    startTime = System.currentTimeMillis();

    Statement gStmt = null;
    PreparedStatement pStmt = null;

    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

        pStmt = dstConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        ResultSet rs = gStmt.executeQuery(gbifSQL);
        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            String genus = rs.getString(16);
            if (genus == null)
                continue;

            String species = rs.getString(17);

            if (isPlant(colStmtGN, colStmtGNSP, genus, species)
                    || isPlant(colDstStmtGN, colDstStmtGNSP, genus, species)) {

                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    Object obj = rs.getObject(i);
                    pStmt.setObject(i, obj);
                }

                try {
                    pStmt.executeUpdate();

                } catch (Exception ex) {
                    System.err.println("For Old ID[" + rs.getObject(1) + "]");
                    ex.printStackTrace();
                    pw.print("For Old ID[" + rs.getObject(1) + "] " + ex.getMessage());
                    pw.flush();
                }

                procRecs++;
                if (procRecs % 10000 == 0) {
                    long endTime = System.currentTimeMillis();
                    long elapsedTime = endTime - startTime;

                    double avergeTime = (double) elapsedTime / (double) procRecs;

                    double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs
                            - procRecs) / HRS;

                    int seconds = (int) (elapsedTime / 60000.0);
                    if (secsThreshold != seconds) {
                        secsThreshold = seconds;

                        msg = String.format(
                                "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                                ((double) (elapsedTime)) / HRS, avergeTime,
                                100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                        System.out.println(msg);
                        pw.println(msg);
                        pw.flush();
                    }
                }
            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java

/**
 * //w  w  w  .  ja  v  a 2 s  .com
 */
public void processNonNullNonPlantKingdom() {
    PrintWriter pw = null;
    try {
        pw = new PrintWriter("gbif_plants_from_nonnull.log");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

    System.out.println("----------------------- Search non-Plantae ----------------------- ");

    String gbifWhereStr = "FROM raw WHERE kingdom = '%s'";

    Vector<String> nonPlantKingdoms = new Vector<String>();
    String sqlStr = "SELECT * FROM (select kingdom, count(kingdom) as cnt from plants.raw WHERE kingdom is not null AND NOT (lower(kingdom) like '%plant%') group by kingdom) T1 ORDER BY cnt desc;";
    for (Object[] obj : BasicSQLUtils.query(sqlStr)) {
        String kingdom = (String) obj[0];
        Integer count = (Integer) obj[1];

        System.out.println(kingdom + " " + count);
        pw.println(kingdom + " " + count);
        if (!StringUtils.contains(kingdom.toLowerCase(), "plant")) {
            nonPlantKingdoms.add(kingdom);
        }
    }

    long startTime = System.currentTimeMillis();

    for (String kingdom : nonPlantKingdoms) {
        String where = String.format(gbifWhereStr, kingdom);

        String cntGBIFSQL = "SELECT COUNT(*) " + where;
        String gbifSQL = gbifSQLBase + where;

        System.out.println(cntGBIFSQL);

        long totalRecs = BasicSQLUtils.getCount(srcConn, cntGBIFSQL);
        long procRecs = 0;
        int secsThreshold = 0;

        String msg = String.format("Query: %8.2f secs",
                (double) (System.currentTimeMillis() - startTime) / 1000.0);
        System.out.println(msg);
        pw.println(msg);
        pw.flush();

        startTime = System.currentTimeMillis();

        Statement gStmt = null;
        PreparedStatement pStmt = null;

        try {
            pStmt = dstConn.prepareStatement(pSQL);

            System.out.println("Total Records: " + totalRecs);
            pw.println("Total Records: " + totalRecs);
            pw.flush();

            gStmt = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            gStmt.setFetchSize(Integer.MIN_VALUE);

            ResultSet rs = gStmt.executeQuery(gbifSQL);
            ResultSetMetaData rsmd = rs.getMetaData();

            while (rs.next()) {
                String genus = rs.getString(16);
                if (genus == null)
                    continue;

                String species = rs.getString(17);

                if (isPlant(colStmtGN, colStmtGNSP, genus, species)
                        || isPlant(colDstStmtGN, colDstStmtGNSP, genus, species)) {

                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        Object obj = rs.getObject(i);
                        pStmt.setObject(i, obj);
                    }

                    try {
                        pStmt.executeUpdate();

                    } catch (Exception ex) {
                        System.err.println("For Old ID[" + rs.getObject(1) + "]");
                        ex.printStackTrace();
                        pw.print("For Old ID[" + rs.getObject(1) + "] " + ex.getMessage());
                        pw.flush();
                    }

                    procRecs++;
                    if (procRecs % 10000 == 0) {
                        long endTime = System.currentTimeMillis();
                        long elapsedTime = endTime - startTime;

                        double avergeTime = (double) elapsedTime / (double) procRecs;

                        double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs
                                - procRecs) / HRS;

                        int seconds = (int) (elapsedTime / 60000.0);
                        if (secsThreshold != seconds) {
                            secsThreshold = seconds;

                            msg = String.format(
                                    "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                                    ((double) (elapsedTime)) / HRS, avergeTime,
                                    100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                            System.out.println(msg);
                            pw.println(msg);
                            pw.flush();
                        }
                    }
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (gStmt != null) {
                    gStmt.close();
                }
                if (pStmt != null) {
                    pStmt.close();
                }
                pw.close();

            } catch (Exception ex) {

            }
        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");

}

From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java

/**
 * /*from   w w  w  . java  2 s .  c o m*/
 */
public void process() throws SQLException {
    buildTags = new BuildTags();
    buildTags.setDbConn(dbConn);
    buildTags.setDbConn2(dbConn);
    buildTags.initialPrepareStatements();

    BasicSQLUtils.setDBConnection(dbConn);

    IdMapperMgr idMapperMgr = IdMapperMgr.getInstance();
    idMapperMgr.setDBs(srcDBConn2, dbConn);

    geoStmt1 = dbConn.prepareStatement(
            "SELECT GeographyID FROM geography WHERE RankID = ? AND ParentID = ? AND LOWER(Abbrev) = ?");
    geoStmt2 = dbConn
            .prepareStatement("SELECT GeographyID FROM geography WHERE RankID = ? AND LOWER(Abbrev) = ?");
    agentStmt = dbConn
            .prepareStatement("SELECT AgentID FROM agent WHERE LOWER(FirstName) = ? AND LOWER(LastName) = ?");
    tagStmt = dbConn.prepareStatement(
            "SELECT CollectionObjectID FROM collectionobject WHERE CollectionID = 4 AND LOWER(FieldNumber) = ?");

    BasicSQLUtils.update(srcDBConn, "UPDATE recovery SET r_date = null WHERE r_date = '0000-00-00'");

    boolean doTags = true;
    if (doTags) {
        int divId = 2;
        int dspId = 3;
        int colId = 32768;

        String sql = "SELECT tagid, " + "r_city, r_state, r_zip, r_country, r_date, r_lat, r_long, "
                + "reporter_first, reporter_last, reporter_city, reporter_state, reporter_country, reporter_zip, "
                + "dir, dist, gender, "
                + "t_first, t_middle, t_last, t_city, t_state, t_country, t_postalcode, t_org, t_lat, t_long, t_date FROM recovery ORDER BY recovid ASC";

        Statement stmt = srcDBConn.createStatement();
        stmt.setFetchSize(Integer.MIN_VALUE);

        log.debug("Querying for Tags...");
        ResultSet rs = stmt.executeQuery(sql);
        int cnt = 0;
        log.debug("Done querying for Tags...");

        Calendar cal = Calendar.getInstance();
        Timestamp ts = new Timestamp(cal.getTime().getTime());

        String common = "TimestampCreated, Version, CreatedByAgentID";
        String coStr = String.format(
                "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Text2, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?)",
                common);
        String ceStr = String.format(
                "INSERT INTO collectingevent (StartDate, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?)",
                common);
        String lcStr = String.format(
                "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",
                common);
        String clStr = String.format(
                "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)",
                common);
        String rlStr = String.format(
                "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)",
                common);
        String agStr = String
                .format("INSERT INTO agent (AgentType, FirstName, LastName, %s) VALUES(?,?,?,?,?,?)", common);
        String adStr = String.format(
                "INSERT INTO address (City, State, PostalCode, Country, AgentID, %s) VALUES(?,?,?,?, ?,?,?,?)",
                common);

        String lcUpdateStr = "UPDATE locality SET Latitude1=?, Longitude1=?, SrcLatLongUnit=?, Lat1text=?, Long1text=?, LatLongType=? WHERE LocalityID = ?";
        String lcStr2 = "SELECT LocalityID FROM locality WHERE LocalityName LIKE ? AND LocalityName LIKE ?";

        PreparedStatement coStmt = dbConn.prepareStatement(coStr);
        PreparedStatement ceStmt = dbConn.prepareStatement(ceStr);
        PreparedStatement lcStmt = dbConn.prepareStatement(lcStr);
        PreparedStatement clStmt = dbConn.prepareStatement(clStr);
        PreparedStatement rlStmt = dbConn.prepareStatement(rlStr);
        PreparedStatement agStmt = dbConn.prepareStatement(agStr);
        PreparedStatement adStmt = dbConn.prepareStatement(adStr);
        PreparedStatement lcUpStmt = dbConn.prepareStatement(lcUpdateStr);
        PreparedStatement lcStmt2 = dbConn.prepareStatement(lcStr2);

        int recNum = 1;
        while (rs.next()) {
            String tag = rs.getString(1);

            String city = rs.getString(2);
            String state = rs.getString(3);
            String zip = rs.getString(4);
            String country = rs.getString(5);
            Date date = rs.getDate(6);

            double lat = rs.getDouble(7);
            boolean isLatNull = rs.wasNull();

            double lon = rs.getDouble(8);
            boolean isLonNull = rs.wasNull();

            String dir = rs.getString(9);
            String dist = rs.getString(10);
            String gender = rs.getString(11);

            String rep_first = rs.getString(12);
            String rep_last = rs.getString(13);
            String rep_city = rs.getString(14);
            String rep_state = rs.getString(15);
            String rep_country = rs.getString(16);
            String rep_zip = rs.getString(17);

            String t_first = rs.getString(18);
            //String t_middle    = rs.getString(19);
            String t_last = rs.getString(20);
            String t_city = rs.getString(21);
            String t_state = rs.getString(22);
            String t_country = rs.getString(23);
            String t_zip = rs.getString(24);
            //String t_org       = rs.getString(25);

            double t_lat = rs.getDouble(26);
            boolean isTLatNull = rs.wasNull();

            double t_lon = rs.getDouble(27);
            boolean isTLonNull = rs.wasNull();

            //String oldState = state;

            city = condense(rep_city, t_city, city);
            state = condense(rep_state, state, t_state);
            country = condense(rep_country, country, t_country);
            zip = condense(rep_zip, zip, t_zip);
            rep_first = condense(rep_first, t_first);
            rep_last = condense(rep_last, t_last);

            /*boolean debug = ((rep_state != null && rep_state.equals("IA")) || 
            (t_state != null && t_state.equals("IA")) || 
            (oldState != null && oldState.equals("IA")));
                    
            if (debug && (state == null || !state.equals("IA")))
            {
            System.out.println("ouch");
            }*/

            if (rep_first != null && rep_first.length() > 50) {
                rep_first = rep_first.substring(0, 50);
            }

            lat = isLatNull && !isTLatNull ? t_lat : lat;
            lon = isLonNull && !isTLonNull ? t_lon : lon;

            try {
                // (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID
                Integer geoId = buildTags.getGeography(country, state, null);

                // Latitude varies between -90 and 90, and Longitude between -180 and 180.
                if (lat < -90.0 || lat > 90.0) {
                    lcStmt.setObject(1, null);
                    lcStmt.setObject(4, null);
                } else {
                    lcStmt.setDouble(1, lat);
                    lcStmt.setString(4, Double.toString(lat));

                    lcUpStmt.setDouble(1, lat);
                    lcUpStmt.setString(4, Double.toString(lat));
                }

                if (lon < -180.0 || lon > 180.0) {
                    lcStmt.setObject(2, null);
                    lcStmt.setObject(5, null);
                } else {
                    lcStmt.setDouble(2, lon);
                    lcStmt.setString(5, Double.toString(lon));

                    lcUpStmt.setDouble(2, lon);
                    lcUpStmt.setString(5, Double.toString(lon));
                }

                String locName = null;
                String fullName = null;

                Integer locId = null;
                geoId = buildTags.getGeography(country, state, null);
                if (geoId != null) {
                    fullName = geoFullNameHash.get(geoId);
                    if (fullName == null) {
                        fullName = BasicSQLUtils
                                .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId);
                        geoFullNameHash.put(geoId, fullName);
                    }

                    if (StringUtils.isNotEmpty(city)) {
                        locName = city + ", " + fullName;
                    } else {
                        locName = fullName;
                    }
                    locId = localityHash.get(locName);
                    if (locId == null) {
                        lcStmt2.setString(1, "%" + city);
                        lcStmt2.setString(2, country + "%");
                        ResultSet lcRS = lcStmt2.executeQuery();
                        if (lcRS.next()) {
                            locId = lcRS.getInt(1);
                            if (!lcRS.wasNull()) {
                                localityHash.put(locName, locId);
                            }
                        }
                        lcRS.close();
                    }

                } else {
                    //unknown++;
                    fullName = "Unknown";
                    locName = buildTags.buildLocalityName(city, fullName);
                    geoId = 27507; // Unknown
                    locId = localityHash.get(locName);
                    //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]");
                }

                if (locId == null) {
                    lcStmt.setByte(3, (byte) 0);
                    lcStmt.setString(6, "Point");
                    lcStmt.setInt(7, dspId);
                    lcStmt.setString(8, getLocalityName(country, state, null, city));
                    lcStmt.setObject(9, geoId);
                    lcStmt.setTimestamp(10, ts);
                    lcStmt.setInt(11, 1);
                    lcStmt.setInt(12, 1);
                    lcStmt.executeUpdate();
                    locId = BasicSQLUtils.getInsertedId(lcStmt);

                } else if (!isLatNull && !isLonNull) {
                    int count = BasicSQLUtils.getCountAsInt(
                            "SELECT COUNT(*) FROM locality WHERE Latitude1 IS NULL AND Longitude1 IS NULL AND LocalityID = "
                                    + locId);
                    if (count == 1) {
                        lcUpStmt.setByte(3, (byte) 0);
                        lcUpStmt.setString(6, "Point");
                        lcUpStmt.setInt(7, locId);
                        lcUpStmt.executeUpdate();
                    }
                }

                // (StartDate, Method, DisciplineID, LocalityID
                ceStmt.setDate(1, date);
                ceStmt.setInt(2, dspId);
                ceStmt.setInt(3, locId);
                ceStmt.setTimestamp(4, ts);
                ceStmt.setInt(5, 1);
                ceStmt.setInt(6, 1);
                ceStmt.executeUpdate();
                Integer ceId = BasicSQLUtils.getInsertedId(ceStmt);

                //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId
                coStmt.setString(1, String.format("%09d", recNum++));
                coStmt.setString(2, tag);
                coStmt.setString(3, gender);
                coStmt.setString(4, dir);
                coStmt.setString(5, dist);
                coStmt.setInt(6, colId);
                coStmt.setInt(7, colId);
                coStmt.setInt(8, ceId);
                coStmt.setTimestamp(9, ts);
                coStmt.setInt(10, 1);
                coStmt.setInt(11, 1);
                coStmt.executeUpdate();
                //Integer coId = BasicSQLUtils.getInsertedId(coStmt);

                Integer agentId = getAgentId(agentStmt, rep_first, rep_last);
                if (agentId == null) {
                    agStmt.setInt(1, 0);
                    agStmt.setString(2, rep_first);
                    agStmt.setString(3, rep_last);
                    agStmt.setTimestamp(4, ts);
                    agStmt.setInt(5, 1);
                    agStmt.setInt(6, 1);
                    agStmt.executeUpdate();
                    agentId = BasicSQLUtils.getInsertedId(agStmt);

                    if (agentId != null) {
                        adStmt.setString(1, rep_city);
                        adStmt.setString(2, rep_state);
                        adStmt.setString(3, rep_zip);
                        adStmt.setString(4, rep_country);
                        adStmt.setInt(5, agentId);
                        adStmt.setTimestamp(6, ts);
                        adStmt.setInt(7, 1);
                        adStmt.setInt(8, 1);
                        adStmt.executeUpdate();
                    } else {
                        log.error("agentId is null after being created: " + rep_first + ", " + rep_last);
                    }
                }

                // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID
                clStmt.setInt(1, 0);
                clStmt.setBoolean(2, true);
                clStmt.setInt(3, ceId);
                clStmt.setInt(4, divId);
                clStmt.setInt(5, agentId);
                clStmt.setTimestamp(6, ts);
                clStmt.setInt(7, 1);
                clStmt.setInt(8, 1);
                clStmt.executeUpdate();

            } catch (Exception ex) {
                log.debug(recNum + " tag[" + tag + "]");
                ex.printStackTrace();
            }

            cnt++;
            if (cnt % 100 == 0) {
                System.out.println("Col Obj: " + cnt);
            }
        }

        coStmt.close();
        ceStmt.close();
        lcStmt.close();
        clStmt.close();
        rlStmt.close();
        agStmt.close();
        adStmt.close();
        lcUpStmt.close();

        buildTags.cleanup();
    }
}

From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java

/**
 * //ww w. ja  v a  2s . c  o m
 */
public void process() throws SQLException {
    int dupAgents = 0;
    int dupLocality = 0;
    int unknown = 0;

    boolean doAll = false;

    BasicSQLUtils.setDBConnection(dbConn);

    boolean doTrim = false;
    if (doTrim || doAll) {
        String trimNamesSQL = "UPDATE tagger SET first=TRIM(first),last=TRIM(last),company=TRIM(company),address1=TRIM(address1),address2=TRIM(address2),city=TRIM(city),state=TRIM(state)";
        BasicSQLUtils.update(srcDBConn2, trimNamesSQL);

        String removeQuote = "UPDATE tagger SET first=SUBSTRING_INDEX(first, '\"', -1),last=SUBSTRING_INDEX(last, '\"', -1),company=SUBSTRING_INDEX(company, '\"', -1),address1=SUBSTRING_INDEX(address1, '\"', -1),"
                + "address2=SUBSTRING_INDEX(address2, '\"', -1),city=SUBSTRING_INDEX(city, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)";
        BasicSQLUtils.update(srcDBConn2, removeQuote);

        String trimNamesSQL2 = "UPDATE tag SET city=TRIM(city),county=TRIM(county),state=TRIM(state)";
        BasicSQLUtils.update(srcDBConn2, trimNamesSQL2);

        String removeQuote2 = "UPDATE tag SET city=SUBSTRING_INDEX(city, '\"', -1), county=SUBSTRING_INDEX(county, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)";
        BasicSQLUtils.update(srcDBConn2, removeQuote2);
    }

    IdMapperMgr idMapperMgr = IdMapperMgr.getInstance();
    idMapperMgr.setDBs(srcDBConn2, dbConn);

    IdHashMapper agentMapper;

    Division division = (Division) session.get(Division.class, 2);

    initialPrepareStatements();

    BasicSQLUtils.update(srcDBConn, "UPDATE tag SET `Date` = null WHERE Date = '0000-00-00'");

    //IdMapperMgr.setSkippingOldTableCheck(true);

    boolean doAgents = false;
    if (doAgents || doAll) {
        agentMapper = new IdTableMapper("agent", "AgentID", false, false);

        String sql = "SELECT first, last, company, address1, address2, city, state, country, zip, phone, fax, enail, tnum FROM tagger ORDER BY tnum";
        Statement stmt = srcDBConn.createStatement();
        stmt.setFetchSize(Integer.MIN_VALUE);

        log.debug("Querying for Agents...");
        ResultSet rs = stmt.executeQuery(sql);
        int cnt = 0;
        while (rs.next()) {
            String first = rs.getString(1);
            String last = rs.getString(2);
            String company = rs.getString(3);
            String addr1 = rs.getString(4);
            String addr2 = rs.getString(5);
            String city = rs.getString(6);
            String state = rs.getString(7);
            String country = rs.getString(8);
            String zip = rs.getString(9);
            String phone = rs.getString(10);
            String fax = rs.getString(11);
            String email = rs.getString(12);
            Integer oldId = rs.getInt(13);

            if (oldId == null) {
                log.error("Null primary Id: " + last + " " + first);
                continue;
            }

            Agent agent = getAgent(first, last, city, state);
            Integer agentId = null;
            if (agent == null) {
                agent = new Agent();
                agent.initialize();
                agent.setFirstName(first);
                agent.setLastName(last);
                agent.setEmail(email);
                agent.setRemarks(company);
                agent.setDivision(division);

                Address addr = new Address();
                addr.initialize();
                addr.setAddress(addr1);
                addr.setAddress2(addr2);
                addr.setCity(city);
                addr.setState(state);
                addr.setCountry(country);
                addr.setPostalCode(zip);
                addr.setPhone1(phone);
                addr.setFax(fax);

                agent.getAddresses().add(addr);
                addr.setAgent(agent);

                Transaction trans = null;
                try {
                    trans = session.beginTransaction();
                    session.saveOrUpdate(agent);
                    session.saveOrUpdate(addr);
                    trans.commit();

                    agentId = agent.getId();

                } catch (Exception ex) {
                    ex.printStackTrace();
                    try {
                        if (trans != null)
                            trans.rollback();
                    } catch (Exception ex2) {
                        ex2.printStackTrace();
                    }
                }
            } else {
                agentId = agent.getId();
                dupAgents++;
                //System.out.println("Found Agent: "+first+", "+last);
            }
            agentMapper.put(oldId, agentId);

            cnt++;
            if (cnt % 500 == 0) {
                System.out.println("Agents: " + cnt);
            }

            if (cnt % 400 == 0) {
                HibernateUtil.closeSession();
                session = HibernateUtil.getCurrentSession();
                hibSession = new HibernateDataProviderSession(session);
            }
        }
        rs.close();
        stmt.close();

        division = (Division) session.get(Division.class, 2);

    } else {
        //agentMapper = idMapperMgr.addTableMapper("agent", "AgentID", false);
        IdHashMapper.setEnableDelete(false);
        agentMapper = new IdTableMapper("agent", "AgentID", null, false, false);
    }

    System.out.println("Duplicated Agent: " + dupAgents);

    boolean doTags = true;
    if (doTags || doAll) {
        HashMap<String, Integer> localityHash = new HashMap<String, Integer>();
        HashMap<Integer, String> geoFullNameHash = new HashMap<Integer, String>();

        int divId = 2;
        int dspId = 3;
        int colId = 4;

        String sql = "SELECT t.tagid, t.`date`, t.wild, t.gender, t.city, t.county, t.state, t.country, t.zip, t.observations, t.lat, t.lon, t.sunangle, p.tnum "
                + "FROM tag AS t  Inner Join page AS p ON t.page = p.page ";
        Statement stmt = srcDBConn.createStatement();
        stmt.setFetchSize(Integer.MIN_VALUE);

        log.debug("Querying for Tags...");
        ResultSet rs = stmt.executeQuery(sql);
        int cnt = 0;
        log.debug("Done querying for Tags...");

        Calendar cal = Calendar.getInstance();
        Timestamp ts = new Timestamp(cal.getTime().getTime());

        String common = "TimestampCreated, Version, CreatedByAgentID";
        String coStr = String.format(
                "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?)",
                common);
        String ceStr = String.format(
                "INSERT INTO collectingevent (StartDate, Method, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?,?)",
                common);
        String lcStr = String.format(
                "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
                common);
        String clStr = String.format(
                "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)",
                common);

        PreparedStatement coStmt = dbConn.prepareStatement(coStr);
        PreparedStatement ceStmt = dbConn.prepareStatement(ceStr);
        PreparedStatement lcStmt = dbConn.prepareStatement(lcStr);
        PreparedStatement clStmt = dbConn.prepareStatement(clStr);

        int recNum = 1;
        while (rs.next()) {
            String tag = rs.getString(1);
            if (tag != null && tag.startsWith("ERR"))
                continue;

            Date date = rs.getDate(2);
            String wild = rs.getString(3);
            String gender = rs.getString(4);
            String city = rs.getString(5);
            String county = rs.getString(6);
            String state = rs.getString(7);
            String country = rs.getString(8);
            //String zip     = rs.getString(9);
            String obs = rs.getString(10);
            double lat = rs.getDouble(11);
            double lon = rs.getDouble(12);
            double angle = rs.getDouble(13);
            Integer taggerId = rs.getInt(14);

            String locName = null;
            String fullName = null;

            Integer locId = null;
            Integer geoId = getGeography(country, state, county);
            if (geoId != null) {
                //locName   = localityHash.get(geoId);
                fullName = geoFullNameHash.get(geoId);
                if (fullName == null) {
                    fullName = BasicSQLUtils
                            .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId);
                    geoFullNameHash.put(geoId, fullName);
                }

                if (StringUtils.isNotEmpty(city)) {
                    locName = city + ", " + fullName;
                } else {
                    locName = fullName;
                }
                locId = localityHash.get(locName);

            } else {
                unknown++;
                fullName = "Unknown";
                locName = buildLocalityName(city, fullName);
                geoId = 27507; // Unknown
                locId = localityHash.get(locName);
                //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]");
            }

            if (locId == null) {
                lcStmt.setDouble(1, lat);
                lcStmt.setDouble(2, lon);
                lcStmt.setByte(3, (byte) 0);
                lcStmt.setString(4, Double.toString(lat));
                lcStmt.setString(5, Double.toString(lon));
                lcStmt.setString(6, "Point");
                lcStmt.setInt(7, dspId);
                lcStmt.setDouble(8, angle);
                lcStmt.setString(9, locName);
                lcStmt.setObject(10, geoId);
                lcStmt.setTimestamp(11, ts);
                lcStmt.setInt(12, 1);
                lcStmt.setInt(13, 1);
                lcStmt.executeUpdate();
                locId = BasicSQLUtils.getInsertedId(lcStmt);

                localityHash.put(locName, locId);
            } else {
                dupLocality++;
            }

            // (StartDate, Method, DisciplineID, LocalityID
            ceStmt.setDate(1, date);
            ceStmt.setString(2, wild);
            ceStmt.setInt(3, dspId);
            ceStmt.setInt(4, locId);
            ceStmt.setTimestamp(5, ts);
            ceStmt.setInt(6, 1);
            ceStmt.setInt(7, 1);
            ceStmt.executeUpdate();
            Integer ceId = BasicSQLUtils.getInsertedId(ceStmt);

            //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId
            coStmt.setString(1, String.format("%09d", recNum++));
            coStmt.setString(2, tag);
            coStmt.setString(3, gender);
            coStmt.setString(4, obs);
            coStmt.setInt(5, colId);
            coStmt.setInt(6, colId);
            coStmt.setInt(7, ceId);
            coStmt.setTimestamp(8, ts);
            coStmt.setInt(9, 1);
            coStmt.setInt(10, 1);
            coStmt.executeUpdate();
            //Integer coId = BasicSQLUtils.getInsertedId(coStmt);

            //Integer coltrId = null;
            if (taggerId != null) {
                Integer agentId = agentMapper.get(taggerId);
                //System.out.println(agentId);
                if (agentId != null) {
                    // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID
                    clStmt.setInt(1, 0);
                    clStmt.setBoolean(2, true);
                    clStmt.setInt(3, ceId);
                    clStmt.setInt(4, divId);
                    clStmt.setInt(5, agentId);
                    clStmt.setTimestamp(6, ts);
                    clStmt.setInt(7, 1);
                    clStmt.setInt(8, 1);
                    clStmt.executeUpdate();
                    //coltrId = BasicSQLUtils.getInsertedId(clStmt);
                    //BasicSQLUtils.getInsertedId(clStmt);

                } else {
                    log.debug("Couldn't find Agent in DB for tagger id (tnum): " + taggerId + "  AgentID:: "
                            + agentId);
                }
            } else {
                log.debug("Couldn't find Mapped Id for tagger id (tnum): " + taggerId);
            }

            cnt++;
            if (cnt % 1000 == 0) {
                System.out.println("Col Obj: " + cnt);
            }
        }

        coStmt.close();
        ceStmt.close();
        lcStmt.close();
        clStmt.close();

        System.out.println("Duplicated Agent:      " + dupAgents);
        System.out.println("Duplicated Localities: " + dupLocality);
        System.out.println("Unknown Localities:    " + unknown);
        System.out.println("Localities:            "
                + BasicSQLUtils.getCountAsInt(dbConn, "SELECT COUNT(*) FROM locality"));
    }
}