Example usage for java.sql ResultSet FETCH

Introduction

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

Prototype

int FETCH_FORWARD

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

Click Source Link

Document

The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();

    DatabaseMetaData md = conn.getMetaData();
    System.out.println(//from ww  w .j av  a  2  s.co  m
            "supportsResultSetHoldability - " + md.supportsResultSetHoldability(ResultSet.FETCH_FORWARD));

    conn.close();
}

From source file:com.dsclab.loader.export.DBClient.java

public DBClient(final String url) throws ClassNotFoundException, SQLException {
    Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
    try {/* w  w w . j  av  a  2s. c o  m*/
        con = DriverManager.getConnection(url);
        con.setAutoCommit(false);
        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(1000);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
        md = con.getMetaData();
    } catch (RuntimeException ex) {
        //LOG.error("Could not connect",ex);
    }
}

From source file:com.dsclab.loader.loader.DBClient.java

public DBClient(final String url, final String driverClass) throws ClassNotFoundException, SQLException {
    if (driverClass != null) {
        Class.forName(driverClass);
    }// www. ja  va 2 s  .  co m
    try {
        con = DriverManager.getConnection(url);
        con.setAutoCommit(false);
        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(1000);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
        md = con.getMetaData();
    } catch (RuntimeException ex) {
        //LOG.error("Could not connect",ex);
    }
}

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/*w ww . j  a v  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.yale.cs.hadoopdb.connector.AbstractDBRecordReader.java

/**
 * Method sets up a connection to a database and provides query optimization
 * parameters. Then it executes the query.
 *//*from www.ja v a2s  .  c o m*/
protected void setupDB(DBInputSplit split, JobConf conf) throws SQLException {

    try {
        startTime = System.currentTimeMillis();
        connection = getConnection(split);
        // Optimization options including specifying forward direction,
        // read-only cursor
        // and a default fetch size to prevent db cache overloading.
        statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        connection.setAutoCommit(false);
        statement.setFetchDirection(ResultSet.FETCH_FORWARD);
        statement.setFetchSize(conf.getInt(DBConst.DB_FETCH_SIZE, DBConst.SQL_DEFAULT_FETCH_SIZE));

        connTime = System.currentTimeMillis();

        String sql = prepareSqlQuery(getSqlQuery(), split, conf);

        LOG.info(sql);
        results = statement.executeQuery(sql);
        queryTime = System.currentTimeMillis();

    } catch (SQLException e) {

        try {
            if (results != null)
                results.close();
            if (statement != null)
                statement.close();
            if (connection != null)
                connection.close();
        } catch (SQLException ex) {
            LOG.info(ex, ex);
        }

        throw e;
    }
}

From source file:at.ac.univie.isc.asio.engine.sql.WebRowSetWriter.java

private void properties(final String statement) throws XMLStreamException {
    // @formatter:off
    xml.writeStartElement(WRS, "properties");
    tag("command", statement);
    tag("concurrency", ResultSet.CONCUR_UPDATABLE);
    tag("datasource", null);
    tag("escape-processing", Boolean.TRUE);
    tag("fetch-direction", ResultSet.FETCH_FORWARD);
    tag("fetch-size", 0);
    tag("isolation-level", Connection.TRANSACTION_NONE);
    emptyTag("key-columns");
    emptyTag("map");
    tag("max-field-size", 0);
    tag("max-rows", 0);
    tag("query-timeout", 0);
    tag("read-only", Boolean.TRUE);
    tag("rowset-type", "ResultSet.TYPE_SCROLL_INSENSITIVE"); // must be constant name !
    tag("show-deleted", Boolean.FALSE);
    emptyTag("table-name"); // <null /> would represent java null - but is invalid according to schema
    tag("url", null);
    emptySyncProvider();/*from  w  w  w . jav  a2s.c om*/
    xml.writeEndElement();
    // @formatter:on
}

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

/**
 * /*from   w w  w.  j  av  a 2s.  co m*/
 */
public void process() {
    //String sql = "SELECT collector_name FROM raw WHERE collector_name IS NOT NULL AND collector_name LIKE '%;%' limit 4000,1000";
    String sql = "SELECT collector_name FROM raw WHERE collector_name IS NOT NULL limit 4000,1000";
    try {
        Statement stmt = oldDBConn.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);

        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String str = rs.getString(1);
            System.out.println("\n" + str);
            parseForNames(str);
        }
        rs.close();
        stmt.close();

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

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

@Override
public void process(final int type, final int options) {
    calcMaxScore();/*from  ww w . ja  v  a 2 s .com*/

    String gbifSQL = "SELECT DISTINCT id, catalogue_number, genus, species, subspecies, latitude, longitude, country, state_province, collector_name, locality, year, month, day, collector_num ";

    String fromClause1a = "FROM raw WHERE collector_num LIKE ? AND year = ? AND genus = ?";
    String fromClause1b = "FROM raw WHERE collector_num IS NULL AND year = ? AND genus = ?";
    //String fromClause2  = "FROM raw WHERE collector_num IS NULL AND year = ? AND month = ? AND genus = ? AND id <> ?";

    //                        1       2           3        4           5         6          7         8           9               10          11       12    13    14      15
    String postSQL = "FROM raw WHERE collector_num IS NOT NULL GROUP BY collector_num, year, genus";
    String srcSQL = "SELECT id, catalogue_number, genus, species, subspecies, latitude, longitude, country, state_province, collector_name, locality, year, month, day, collector_num "
            + postSQL + " ORDER BY collector_num";

    String grphashSQL = "SELECT name FROM group_hash";

    String gbifgbifInsert = "INSERT INTO gbifgbif (reltype, score, GBIFID, SNIBID) VALUES (?,?,?,?)";

    Statement stmt = null;
    PreparedStatement gStmt1a = null;
    PreparedStatement gStmt1b = null;
    //PreparedStatement gStmt2  = null;
    PreparedStatement gsStmt = null;

    Object[] refRow = new Object[NUM_FIELDS];
    Object[] cmpRow = new Object[NUM_FIELDS];

    long totalRecs = BasicSQLUtils.getCount(dbSrcConn, "SELECT COUNT(*) FROM group_hash");
    long procRecs = 0;
    long startTime = System.currentTimeMillis();
    int secsThreshold = 0;

    String blank = "X?";

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

        gStmt1a = dbGBIFConn.prepareStatement(gbifSQL + fromClause1a);
        gStmt1b = dbGBIFConn.prepareStatement(gbifSQL + fromClause1b);

        //gStmt2 = dbGBIFConn.prepareStatement(gbifSQL + fromClause2);
        gsStmt = dbDstConn.prepareStatement(gbifgbifInsert);

        stmt = dbSrcConn.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);

        System.out.println("Starting Query... " + totalRecs);
        pw.println("Starting Query... " + totalRecs);
        System.out.flush();
        pw.flush();

        HashSet<Integer> idHash = new HashSet<Integer>();
        int writeCnt = 0;
        ResultSet rs = stmt.executeQuery(grphashSQL);

        System.out
                .println(String.format("Starting Processing... Total Records %d  Max Score: %d  Threshold: %d",
                        totalRecs, maxScore, thresholdScore));
        pw.println(String.format("Starting Processing... Total Records %d  Max Score: %d  Threshold: %d",
                totalRecs, maxScore, thresholdScore));
        System.out.flush();
        pw.flush();

        Vector<Object[]> group = new Vector<Object[]>();
        ArrayList<Integer> ids = new ArrayList<Integer>();
        while (rs.next()) {
            String[] tokens = StringUtils.split(rs.getString(1), '_');

            String colNum = tokens[0].trim();
            String year = tokens[1].trim();
            String genus = tokens[2].trim();

            if (StringUtils.isEmpty(colNum) || colNum.equals(blank))
                colNum = null;
            if (StringUtils.isEmpty(year) || year.equals(blank))
                year = null;
            if (StringUtils.isEmpty(genus) || genus.equals(blank))
                genus = null;

            PreparedStatement gStmt1;
            if (colNum != null) {
                gStmt1 = gStmt1a;
                gStmt1.setString(1, "%" + colNum + "%");
            } else {
                gStmt1 = gStmt1b;
                gStmt1.setString(1, null);
            }
            gStmt1.setString(2, year);
            gStmt1.setString(3, genus);
            ResultSet gRS = gStmt1.executeQuery();

            ids.clear();
            int maxNonNullTot = -1;
            int maxNonNullInx = -1;
            int inx = 0;
            while (gRS.next()) {

                Object[] row = getRow();
                int cnt = fillRowWithScore(row, gRS);
                if (cnt > maxNonNullTot) {
                    maxNonNullInx = inx;
                    maxNonNullTot = cnt;
                }
                group.add(row);
                ids.add(gRS.getInt(1));
                inx++;
            }
            gRS.close();

            if (inx < 2) {
                for (Object[] r : group) {
                    recycleRow(r);
                }
                group.clear();
                continue;
            }

            System.arraycopy(group.get(maxNonNullInx), 0, refRow, 0, refRow.length);

            Integer srcId = ids.get(maxNonNullInx);

            for (int i = 0; i < group.size(); i++) {
                if (i != maxNonNullInx) {
                    int score = score(refRow, group.get(i));

                    if (score > thresholdScore) {
                        writeCnt++;

                        int gbifID = ids.get(i);
                        gsStmt.setInt(1, 1); // reltype
                        gsStmt.setInt(2, score); // score
                        gsStmt.setInt(3, gbifID);
                        gsStmt.setInt(4, srcId);
                        gsStmt.executeUpdate();

                        idHash.add(gbifID);
                    }
                }
            }

            idHash.clear();

            for (Object[] r : group) {
                recycleRow(r);
            }
            group.clear();

            if (gStmt1 == gStmt1b) {
                continue;
            }

            gStmt1 = gStmt1b;
            gStmt1.setString(1, year);
            gStmt1.setString(2, genus);

            gRS = gStmt1.executeQuery();
            while (gRS.next()) {
                fillRowWithScore(cmpRow, gRS);

                int gbifID = gRS.getInt(1);
                if (gbifID == srcId)
                    continue;

                int score = score(refRow, cmpRow);

                if (score > thresholdScore) {
                    writeCnt++;
                    gsStmt.setInt(1, 1); // reltype
                    gsStmt.setInt(2, score); // score
                    gsStmt.setInt(3, gbifID);
                    gsStmt.setInt(4, srcId);
                    gsStmt.executeUpdate();
                }
            }
            gRS.close();

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

                double timePerRecord = (elapsedTime / procRecs);

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

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

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

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

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (gStmt1a != null) {
                gStmt1a.close();
            }
            if (gStmt1b != null) {
                gStmt1b.close();
            }
            /*if (gStmt2 != null)
            {
            gStmt2.close();
            }*/
        } catch (Exception ex) {

        }
    }
    System.out.println("Done.");
    pw.println("Done.");
    pw.flush();
    pw.close();
}

From source file:com.kumarvv.setl.core.Extractor.java

/**
 * extract data using sql definition/*from   w w  w . ja  v a  2  s .c o m*/
 *
 * @return
 */
boolean extractDataFromSql() {
    if (def.getExtract() == null || StringUtils.isEmpty(def.getExtract().getSql())) {
        Logger.info("extract config is missing. skipping extraction");
        return true;
    }
    String sql = def.getExtract().getSql();

    try (JdbcRowSet jrs = rowSetUtil.getRowSet(def.getFromDS())) {
        jrs.setCommand(sql);
        jrs.execute();
        jrs.setFetchDirection(ResultSet.FETCH_FORWARD);
        jrs.setFetchSize(100);

        ResultSetMetaData meta = jrs.getMetaData();
        initFromColumns(meta);
        parseData(jrs, meta);
        return true;
    } catch (Exception e) {
        Logger.error("error in extraction: {}", e.getMessage());
        Logger.debug(e);
        return false;
    }
}

From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java

@Test
public void testResultSetOptions() throws Exception {
    Statement statement = getConnection().createStatement();
    ResultSet rs = statement.executeQuery(SQL_EMPS);

    assertEquals(ResultSet.FETCH_FORWARD, rs.getFetchDirection());
    rs.setFetchDirection(ResultSet.FETCH_FORWARD);
    assertEquals(ResultSet.FETCH_FORWARD, rs.getFetchDirection());

    try {//from   w  w w  .j  a va2s  .co  m
        rs.setFetchDirection(ResultSet.FETCH_REVERSE);
        fail();
    } catch (SQLException ignore) {
    }

    assertEquals(ResultSet.FETCH_FORWARD, rs.getFetchDirection());

    rs.setFetchSize(100);
    assertEquals(100, rs.getFetchSize());

    rs.close();
    statement.close();
}

Example usage for java.sql ResultSet FETCH_FORWARD

Introduction

Prototype

Document

Usage