Example usage for java.sql ResultSet TYPE_SCROLL_INSENSITIVE

List of usage examples for java.sql ResultSet TYPE_SCROLL_INSENSITIVE

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. TYPE_SCROLL_INSENSITIVE

Introduction

In this page you can find the example usage for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.

Prototype

int TYPE_SCROLL_INSENSITIVE

To view the source code for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.

Click Source Link

Document

The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.

Usage

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

protected void parseAndFixMultiLineAddresses() {
    String whereStr = " FROM address a WHERE Address like '%\r\n%'";
    String sql = "SELECT COUNT(*)" + whereStr;
    if (BasicSQLUtils.getCountAsInt(sql) < 1) {
        return;/*from w ww  . j a va2  s.co  m*/
    }

    sql = "SELECT AddressID, Address" + whereStr;

    Statement stmt = null;
    //PreparedStatement pStmt = null; 
    try {
        // pStmt = newDBConn.prepareStatement("UPDATE address SET Address=?, Address2=?, City=?, State=?, PostalCode=? WHERE AddressID = ?");
        stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery(sql);

        HashSet<Integer> hashSet = new HashSet<Integer>();
        while (rs.next()) {
            String[] toks = StringUtils.split(rs.getString(2), "\r\n");
            hashSet.add(toks.length);
        }
        rs.close();

        for (Integer i : (Integer[]) hashSet.toArray()) {
            System.out.println(i);
        }
        System.out.println();

    } catch (Exception ex) {

    }

}

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

/**
 * @param oldDBConn/*  w ww . ja v a 2  s .c  o m*/
 * @param newDBConn
 */
public void doSetDisciplineIntoCEs(final Connection oldDBConn, final Connection newDBConn) {
    //ProgressFrame frame = conversion.getFrame();

    //IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdMapperIFace ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID",
            false);

    HashMap<Integer, Integer> catSeriesToDisciplineHash = new HashMap<Integer, Integer>();
    for (CollectionInfo ci : CollectionInfo.getCollectionInfoList()) {
        catSeriesToDisciplineHash.put(ci.getCatSeriesId(), ci.getDisciplineId());
    }

    //catSeriesToDisciplineHash.put(0, 3);
    //catSeriesToDisciplineHash.put(-568842536, 7);

    String sql = "SELECT csd.CatalogSeriesID, ce.CollectingEventID FROM catalogseriesdefinition AS csd "
            + "Inner Join collectingevent AS ce ON csd.ObjectTypeID = ce.BiologicalObjectTypeCollectedID";

    PreparedStatement pStmt = null;
    Statement stmt = null;
    try {
        pStmt = newDBConn
                .prepareStatement("UPDATE collectingevent SET DisciplineID=? WHERE CollectingEventID=?");
        int totalCnt = BasicSQLUtils.getNumRecords(oldDBConn, "collectingevent");

        stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs = stmt.executeQuery(sql);
        if (frame != null) {
            frame.setProcess(0, totalCnt);
            frame.setDesc("Setting Discipline Ids in CollectingEvents");
        }

        int count = 0;
        while (rs.next()) {
            int catSerId = rs.getInt(1);
            int id = rs.getInt(2);

            Integer dispId = catSeriesToDisciplineHash.get(catSerId);
            if (dispId != null) {
                Integer newId = ceMapper.get(id);
                if (newId != null) {
                    pStmt.setInt(1, dispId);
                    pStmt.setInt(2, newId);
                    pStmt.executeUpdate();

                } else {
                    System.err.println(String.format("Unable to map oldId %d", id));
                }
            } else {
                System.err.println(String.format("Unable to map Cat Series %d to a discipline", catSerId));
            }

            count++;
            if (count % 1000 == 0) {
                if (frame != null) {
                    frame.setProcess(count);
                } else {
                    log.info(String.format("CE Records: %d / %d", count, totalCnt));
                }
            }
        }
        rs.close();

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

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

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

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

/**
 * Negative Test for cursor repositioning to start of resultset
 * Verify unsupported JDBC resultset attributes
 * @throws Exception//from  ww  w.j  ava  2s. c o  m
 */
@Test
public void testUnsupportedFetchTypes() throws Exception {
    try {
        con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        fail("createStatement with TYPE_SCROLL_SENSITIVE should fail");
    } catch (SQLException e) {
        assertEquals("HYC00", e.getSQLState().trim());
    }

    try {
        con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        fail("createStatement with CONCUR_UPDATABLE should fail");
    } catch (SQLException e) {
        assertEquals("HYC00", e.getSQLState().trim());
    }
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

/**
 * Read the results locally. Then reset the read position to start and read the
 * rows again verify that we get the same results next time.
 * @param sqlStmt - SQL statement to execute
 * @param colName - columns name to read
 * @param oneRowOnly -  read and compare only one row from the resultset
 * @throws Exception//ww  w .  j  a  v  a2s  .c  o m
 */
private void execFetchFirst(String sqlStmt, String colName, boolean oneRowOnly) throws Exception {
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet res = stmt.executeQuery(sqlStmt);

    List<String> results = new ArrayList<String>();
    assertTrue(res.isBeforeFirst());
    int rowNum = 0;
    while (res.next()) {
        results.add(res.getString(colName));
        assertEquals(++rowNum, res.getRow());
        assertFalse(res.isBeforeFirst());
        if (oneRowOnly) {
            break;
        }
    }
    // reposition at the begining
    res.beforeFirst();
    assertTrue(res.isBeforeFirst());
    rowNum = 0;
    while (res.next()) {
        // compare the results fetched last time
        assertEquals(results.get(rowNum++), res.getString(colName));
        assertEquals(rowNum, res.getRow());
        assertFalse(res.isBeforeFirst());
        if (oneRowOnly) {
            break;
        }
    }
}

From source file:net.starschema.clouddb.jdbc.BQDatabaseMetadata.java

/**
 * <p>/*  w w  w . ja  v  a 2s .  c  o  m*/
 * <h1>Implementation Details:</h1><br>
 * Returns false
 * </p>
 */
@Override
public boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException {
    if (ResultSet.TYPE_FORWARD_ONLY == type) {
        if (ResultSet.CONCUR_READ_ONLY == concurrency) {
            return true;
        }
        if (ResultSet.CONCUR_UPDATABLE == concurrency) {
            return false;
        }
    }

    if (ResultSet.TYPE_SCROLL_INSENSITIVE == type) {
        if (ResultSet.CONCUR_READ_ONLY == concurrency) {
            return true;
        }
        if (ResultSet.CONCUR_UPDATABLE == concurrency) {
            return false;
        }
    }

    if (ResultSet.TYPE_SCROLL_SENSITIVE == type) {
        if (ResultSet.CONCUR_READ_ONLY == concurrency) {
            return true;
        }
        if (ResultSet.CONCUR_UPDATABLE == concurrency) {
            return false;
        }
    }
    return false;
}

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

/**
 * @param oldDBConn//from w  ww .  j  av  a2 s  .c  o m
 * @param newDBConn
 */
public void doSetDisciplineIntoLocalities(final Connection oldDBConn, final Connection newDBConn) {
    TableWriter tblWriter = convLogger.getWriter("LocalityDisciplines.html",
            "Setting Discipline into Localities");
    setTblWriter(tblWriter);
    IdHashMapper.setTblWriter(tblWriter);

    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdMapperIFace ceMapper = IdMapperMgr.getInstance().addTableMapper("locality", "LocalityID", false);

    HashMap<Integer, Integer> catSeriesToDisciplineHash = new HashMap<Integer, Integer>();
    for (CollectionInfo ci : CollectionInfo.getCollectionInfoList()) {
        catSeriesToDisciplineHash.put(ci.getCatSeriesId(), ci.getDisciplineId());
    }

    catSeriesToDisciplineHash.put(0, 3);
    catSeriesToDisciplineHash.put(-568842536, 7);

    String sql = " SELECT l.LocalityName, l.LocalityID FROM locality AS l Left Join collectingevent AS ce ON l.LocalityID = ce.LocalityID WHERE ce.CollectingEventID IS NULL";
    Vector<Object[]> rows = BasicSQLUtils.query(oldDBConn, sql);
    tblWriter.println(String.format("Unused Localities in the Sp5 database: %d<br>", rows.size()));
    if (rows.size() > 0) {
        tblWriter.startTable();
        tblWriter.logHdr("Id", "Locality Name");
        for (Object[] row : rows) {
            tblWriter.logObjRow(row);
        }
        tblWriter.endTable();
    }

    HashSet<Integer> sharedLocDifObjTypeSet = new HashSet<Integer>();
    int numSharedLocaltiesDifObjTypes = 0;

    // Find the Localities that are being shared.
    sql = " SELECT * FROM (SELECT l.LocalityID, COUNT(l.LocalityID) cnt, l.LocalityName FROM locality AS l Left Join collectingevent AS ce ON l.LocalityID = ce.LocalityID WHERE ce.CollectingEventID IS NOT NULL GROUP BY l.LocalityID) T1 WHERE cnt > 1";
    rows = BasicSQLUtils.query(oldDBConn, sql);
    tblWriter.println(String.format("Localities being Shared: %d<br>", rows.size()));
    tblWriter.println("Shared Localities with different ObjectTypes<br>");
    if (rows.size() > 0) {
        tblWriter.startTable();
        tblWriter.logHdr("Id", "Count", "Locality Name");
        for (Object[] row : rows) {
            Integer localityId = (Integer) row[0];
            sql = String.format(
                    "SELECT COUNT(*) FROM (SELECT ce.BiologicalObjectTypeCollectedID, COUNT(ce.BiologicalObjectTypeCollectedID) "
                            + "FROM locality AS l Left Join collectingevent AS ce ON l.LocalityID = ce.LocalityID "
                            + "WHERE l.LocalityID = %d GROUP BY ce.BiologicalObjectTypeCollectedID) T1",
                    localityId);
            int count = BasicSQLUtils.getCountAsInt(oldDBConn, sql);
            if (count > 1) {
                tblWriter.logObjRow(row);
                numSharedLocaltiesDifObjTypes++;
                sharedLocDifObjTypeSet.add(localityId);
            }
        }
        tblWriter.endTable();
    }
    tblWriter.println(String.format("Number of Shared Localities with different ObjectTypes: %d<br>",
            numSharedLocaltiesDifObjTypes));

    sql = "SELECT csd.CatalogSeriesID, l.LocalityID FROM locality AS l Left Join collectingevent AS ce ON l.LocalityID = ce.LocalityID "
            + "Inner Join catalogseriesdefinition AS csd ON ce.BiologicalObjectTypeCollectedID = csd.ObjectTypeID WHERE ce.CollectingEventID IS NOT NULL "
            + "GROUP BY l.LocalityID";

    PreparedStatement pStmt = null;
    Statement stmt = null;
    try {
        pStmt = newDBConn.prepareStatement("UPDATE locality SET DisciplineID=? WHERE LocalityID=?");
        int totalCnt = BasicSQLUtils.getNumRecords(oldDBConn, "locality");

        stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs = stmt.executeQuery(sql);
        if (frame != null) {
            frame.setProcess(0, totalCnt);
            frame.setDesc("Setting Discipline Ids in Locality");
        }

        int count = 0;
        while (rs.next()) {
            int catSerId = rs.getInt(1);
            int id = rs.getInt(2);

            if (sharedLocDifObjTypeSet.contains(id)) {
                continue;
            }

            Integer dispId = catSeriesToDisciplineHash.get(catSerId);
            if (dispId != null) {
                Integer newId = ceMapper.get(id);
                if (newId != null) {
                    pStmt.setInt(1, dispId);
                    pStmt.setInt(2, newId);
                    pStmt.executeUpdate();

                } else {
                    System.err.println(String.format("Unable to map oldId %d", id));
                }
            } else {
                System.err.println(String.format("Unable to map Cat Series %d to a discipline", catSerId));
            }

            count++;
            if (count % 1000 == 0) {
                if (frame != null) {
                    frame.setProcess(count);
                } else {
                    log.info(String.format("CE Records: %d / %d", count, totalCnt));
                }
            }
        }
        rs.close();

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

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

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

From source file:net.starschema.clouddb.jdbc.BQDatabaseMetadata.java

/**
 * <p>//w  w w  .j  a  v a 2 s. c om
 * <h1>Implementation Details:</h1><br>
 * Our resultset is scroll insensitive
 * </p>
 *
 * @return TYPE_SCROLL_INSENSITIVE
 */
@Override
public boolean supportsResultSetType(int type) throws SQLException {
    if (type == java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE)
        return true;
    if (type == ResultSet.TYPE_FORWARD_ONLY)
        return true;
    return false;
}

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

/**
 * Returns the number of records in a table
 * @param connection db connection//from w ww  .j a va 2 s .com
 * @param tableName the name of the table
 * @return the number of records in a table
 */
public static int getNumRecords(final Connection connection, final String tableName) {
    try {
        Integer count = 0;
        Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = cntStmt.executeQuery("select count(*) from " + tableName);
        if (rs.first()) {
            count = rs.getInt(1);
            if (count == null) {
                return -1;
            }
        }
        rs.close();
        cntStmt.close();

        return count;

    } catch (SQLException ex) {
        log.error(ex);
        ex.printStackTrace();
        edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
    }
    return -1;
}

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

/**
 * Returns a count of the records query by SQL passed in.
 * @param sql the SQL with a 'count(?)' 
 * @return the number of records or zero
 */// w ww  .jav  a  2  s.c  o  m
public static int getNumRecords(final String sql, final Connection conn) {
    Statement cntStmt = null;
    try {
        int count = 0;

        if (conn != null) {
            cntStmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (cntStmt != null) {
                ResultSet rs = cntStmt.executeQuery(sql);
                if (rs.first()) {
                    count = rs.getInt(1);
                }
                rs.close();
            }
            cntStmt.close();
        }
        return count;

    } catch (SQLException ex) {
        edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
        log.error(ex);

    } finally {
        try {
            if (cntStmt != null) {
                cntStmt.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    return 0;
}

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

/**
 * Returns the last ID that was inserted into the database
 * @param connection db connection//from w  w w.j a v  a2s  .c om
 * @param tableName the name of the table
 * @param idColName primary key column name
 * @return the last ID that was inserted into the database
 */
public static int getHighestId(final Connection connection, final String idColName, final String tableName) {
    try {
        Statement cntStmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = cntStmt
                .executeQuery("select " + idColName + " from " + tableName + " order by " + idColName + " asc");
        int id = 0;
        if (rs.last()) {
            id = rs.getInt(1);
        } else {
            id = 1;
        }
        rs.close();
        cntStmt.close();

        return id;

    } catch (SQLException ex) {
        edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
        log.error(ex);
    }
    return -1;
}