Java Code Examples for java.sql.ResultSet#setFetchSize()

The following examples show how to use java.sql.ResultSet#setFetchSize() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: JdbcAdapter.java    From nano-framework with Apache License 2.0 6 votes vote down vote up 
public Result executeQuery(final String sql, final Connection conn) throws SQLException {
    Assert.notNull(conn);
    long start = System.currentTimeMillis();
    Result result = null;
    ResultSet rs = null;
    Statement stmt = null;

    try {
        stmt = getStatement(conn);
        stmt.setQueryTimeout(60);
        rs = stmt.executeQuery(sql);
        rs.setFetchSize(rs.getRow());
        result = ResultSupport.toResult(rs);
    } finally {
        close(rs, stmt);
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("[ Execute Query SQL ]: {} cost [ {}ms ]", sql, System.currentTimeMillis() - start);
        }
    }

    return result;
}
Example 2
Source File: JdbcAdapter.java    From nano-framework with Apache License 2.0 6 votes vote down vote up 
public Result executeQuery(final String sql, final List<Object> values, final Connection conn) throws SQLException {
    Assert.notNull(conn);
    final long start = System.currentTimeMillis();
    Result result = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
        pstmt = getPreparedStmt(conn, sql, values);
        pstmt.setQueryTimeout(60);
        rs = pstmt.executeQuery();
        rs.setFetchSize(rs.getRow());
        result = ResultSupport.toResult(rs);
    } finally {
        close(rs, pstmt);
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("[ Execute Query SQL ]: {} [cost {}ms ]", sql, System.currentTimeMillis() - start);
            LOGGER.debug("[ Execute Parameter ]: {}", JSON.toJSONString(values, SerializerFeature.WriteDateUseDateFormat));
        }
    }

    return result;
}
Example 3
Source File: DbConnectionManager.java    From Openfire with Apache License 2.0 6 votes vote down vote up 
/**
 * Sets the number of rows that the JDBC driver should buffer at a time.
 * The operation is automatically bypassed if Jive knows that the
 * the JDBC driver or database doesn't support it.
 *
 * @param rs the ResultSet to set the fetch size for.
 * @param fetchSize the fetchSize.
 */
public static void setFetchSize(ResultSet rs, int fetchSize) {
    if (isFetchSizeSupported()) {
        try {
            rs.setFetchSize(fetchSize);
        }
        catch (Throwable t) {
            // Ignore. Exception may happen if the driver doesn't support
            // this operation and we didn't set meta-data correctly.
            // However, it is a good idea to update the meta-data so that
            // we don't have to incur the cost of catching an exception
            // each time.
            Log.error("Disabling JDBC method rs.setFetchSize(fetchSize).", t);
            fetchSizeSupported = false;
        }
    }
}
Example 4
Source File: FirebirdSpecific.java    From CloverETL-Engine with GNU Lesser General Public License v2.1 6 votes vote down vote up 
@Override
public void optimizeResultSet(ResultSet resultSet,
		OperationType operationType) {

	switch (operationType){
	case READ:
		try {
			resultSet.setFetchDirection(ResultSet.FETCH_FORWARD);
			// SQLite driver MUST HAVE fetch size set to 0 - otherwise it limits number of results returned
			resultSet.setFetchSize(0);
		} catch(SQLException ex) {
			//TODO: for now, do nothing
		}
	}

}
Example 5
Source File: SQLiteSpecific.java    From CloverETL-Engine with GNU Lesser General Public License v2.1 6 votes vote down vote up 
@Override
public void optimizeResultSet(ResultSet resultSet,
		OperationType operationType) {

	switch (operationType){
	case READ:
		try {
			resultSet.setFetchDirection(ResultSet.FETCH_FORWARD);
			// SQLite driver MUST HAVE fetch size set to 0 - otherwise it limits number of results returned
			resultSet.setFetchSize(0);
		} catch(SQLException ex) {
			//TODO: for now, do nothing
		}
	}

}
Example 6
Source File: ResultSetLiveTest.java    From tutorials with MIT License 6 votes vote down vote up 
@Test
public void givenDbConnectionL_whenFetch_thenCorrect() throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    List<Employee> listOfEmployees = new ArrayList<Employee>();
    try {
        pstmt = dbConnection.prepareStatement("select * from employees", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pstmt.setFetchSize(1);
        rs = pstmt.executeQuery();
        rs.setFetchSize(1);
        while (rs.next()) {
            Employee employee = populateResultSet(rs);
            listOfEmployees.add(employee);
        }
    } catch (Exception e) {
        throw e;
    } finally {
        if (rs != null)
            rs.close();
        if (pstmt != null)
            pstmt.close();
    }

    assertEquals(2, listOfEmployees.size());
}
Example 7
Source File: AbstractResultSetAdapter.java    From sharding-jdbc-1.5.1 with Apache License 2.0 5 votes vote down vote up 
@Override
public final void setFetchSize(final int rows) throws SQLException {
    Collection<SQLException> exceptions = new LinkedList<>();
    for (ResultSet each : resultSets) {
        try {
            each.setFetchSize(rows);
        } catch (final SQLException ex) {
            exceptions.add(ex);
        }
    }
    throwSQLExceptionIfNecessary(exceptions);
}
Example 8
Source File: AbstractJdbcSpecific.java    From CloverETL-Engine with GNU Lesser General Public License v2.1 5 votes vote down vote up 
@Override
public void optimizeResultSet(ResultSet resultSet, OperationType operationType) {
	switch (operationType){
	case READ:
		try {
			resultSet.setFetchDirection(ResultSet.FETCH_FORWARD);
			resultSet.setFetchSize(DEFAULT_FETCH_SIZE);
		} catch(SQLException ex) {
			//TODO: for now, do nothing
		}
	}
}
Example 9
Source File: ScrollCursors2Test.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
/**
 * Tests for maxRow and fetchSize with scrollable cursors
 *
 *
 * @param maxRows
 *            The maxRows value to use
 * @param fetchSize
 *            The fetchSize value to use
 *
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
        throws SQLException {

    ResultSet rs;
    Statement s_i_r = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    s_i_r.setMaxRows(maxRows);

    // Execute query
    rs = s_i_r
            .executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
    rs.setFetchSize(fetchSize);

    // this should not affect the ResultSet because
    s_i_r.setMaxRows(2);
    if (maxRows == 0)
        maxRows = 15;
    assertNotNull(rs);

    // Start from before first
    // Iterate straight thru RS, expect only maxRows rows.
    for (int index = 1; index < maxRows + 1; index++) {
        assertTrue("rs.next() failed, index = " + index, rs.next());
        assertEquals(index, rs.getInt(1));

    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.next());

    // Start from first and verify maxRows
    assertTrue(rs.first());

    // Iterate forward thru RS, expect only (maxRows - 1) more rows.
    for (int index = 1; index < maxRows; index++) {
        assertTrue(rs.next());
        assertEquals(index + 1, rs.getInt(1));

    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.next());

    // Start from afterLast and verify maxRows
    rs.afterLast();
    // Iterate backwards thru RS, expect only (maxRows - 1) rows.
    for (int index = 1; index < maxRows + 1; index++) {
        assertTrue(rs.previous());
        assertEquals(maxRows - index + 1, rs.getInt(1));
    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.previous());

    // Start from last and verify maxRows
    assertTrue(rs.last());

    // Iterate backwards thru RS, expect only (maxRows - 1) more rows.
    for (int index = 1; index < maxRows; index++) {
        assertTrue(rs.previous());
        assertEquals((maxRows - index), rs.getInt(1));

    }
    // We should not see another row (only 5, not 6)
    assertFalse(rs.previous());
    rs.last();
    int rows = rs.getRow();

    rs.absolute(rows / 2);
    assertFalse(rs.relative(-1 * (rows)));
    assertTrue(rs.isBeforeFirst());

    rs.absolute(rows / 2);
    assertFalse(rs.relative(rows));
    assertTrue(rs.isAfterLast());
    rs.absolute(rows / 2);
    assertFalse("absolute(" + (rows + 1)
            + ") should return false, position outside of the resultSet",
            rs.absolute(rows + 1));

    rs.absolute(rows / 2);
    assertFalse(rs.absolute((-1) * (rows + 1)));

    assertTrue(rs.isBeforeFirst());

    rs.close();

}
Example 10
Source File: SQL_BAD_RESULTSET_ACCESS.java    From spotbugs with GNU Lesser General Public License v2.1 4 votes vote down vote up 
@NoWarning("SQL_BAD_RESULTSET_ACCESS")
void notBug(ResultSet any) throws SQLException {
    any.setFetchSize(0);
}
Example 11
Source File: ScrollCursors2Test.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up 
/**
 * Tests for maxRow and fetchSize with scrollable cursors
 *
 *
 * @param maxRows
 *            The maxRows value to use
 * @param fetchSize
 *            The fetchSize value to use
 *
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
        throws SQLException {

    ResultSet rs;
    Statement s_i_r = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    s_i_r.setMaxRows(maxRows);

    // Execute query
    rs = s_i_r
            .executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
    rs.setFetchSize(fetchSize);

    // this should not affect the ResultSet because
    s_i_r.setMaxRows(2);
    if (maxRows == 0)
        maxRows = 15;
    assertNotNull(rs);

    // Start from before first
    // Iterate straight thru RS, expect only maxRows rows.
    for (int index = 1; index < maxRows + 1; index++) {
        assertTrue("rs.next() failed, index = " + index, rs.next());
        assertEquals(index, rs.getInt(1));

    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.next());

    // Start from first and verify maxRows
    assertTrue(rs.first());

    // Iterate forward thru RS, expect only (maxRows - 1) more rows.
    for (int index = 1; index < maxRows; index++) {
        assertTrue(rs.next());
        assertEquals(index + 1, rs.getInt(1));

    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.next());

    // Start from afterLast and verify maxRows
    rs.afterLast();
    // Iterate backwards thru RS, expect only (maxRows - 1) rows.
    for (int index = 1; index < maxRows + 1; index++) {
        assertTrue(rs.previous());
        assertEquals(maxRows - index + 1, rs.getInt(1));
    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.previous());

    // Start from last and verify maxRows
    assertTrue(rs.last());

    // Iterate backwards thru RS, expect only (maxRows - 1) more rows.
    for (int index = 1; index < maxRows; index++) {
        assertTrue(rs.previous());
        assertEquals((maxRows - index), rs.getInt(1));

    }
    // We should not see another row (only 5, not 6)
    assertFalse(rs.previous());
    rs.last();
    int rows = rs.getRow();

    rs.absolute(rows / 2);
    assertFalse(rs.relative(-1 * (rows)));
    assertTrue(rs.isBeforeFirst());

    rs.absolute(rows / 2);
    assertFalse(rs.relative(rows));
    assertTrue(rs.isAfterLast());
    rs.absolute(rows / 2);
    assertFalse("absolute(" + (rows + 1)
            + ") should return false, position outside of the resultSet",
            rs.absolute(rows + 1));

    rs.absolute(rows / 2);
    assertFalse(rs.absolute((-1) * (rows + 1)));

    assertTrue(rs.isBeforeFirst());

    rs.close();

}
Example 12
Source File: ScrollCursors2Test.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up 
/**
 * Tests for maxRow and fetchSize with scrollable cursors
 * 
 * 
 * @param maxRows
 *            The maxRows value to use
 * @param fetchSize
 *            The fetchSize value to use
 * 
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
        throws SQLException {

    ResultSet rs;
    Statement s_i_r = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    s_i_r.setMaxRows(maxRows);

    // Execute query
    rs = s_i_r
            .executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
    rs.setFetchSize(fetchSize);

    // this should not affect the ResultSet because
    s_i_r.setMaxRows(2);
    if (maxRows == 0)
        maxRows = 15;
    assertNotNull(rs);

    // Start from before first
    // Iterate straight thru RS, expect only maxRows rows.
    for (int index = 1; index < maxRows + 1; index++) {
        assertTrue("rs.next() failed, index = " + index, rs.next());
        assertEquals(index, rs.getInt(1));

    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.next());

    // Start from first and verify maxRows
    assertTrue(rs.first());

    // Iterate forward thru RS, expect only (maxRows - 1) more rows.
    for (int index = 1; index < maxRows; index++) {
        assertTrue(rs.next());
        assertEquals(index + 1, rs.getInt(1));

    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.next());

    // Start from afterLast and verify maxRows
    rs.afterLast();
    // Iterate backwards thru RS, expect only (maxRows - 1) rows.
    for (int index = 1; index < maxRows + 1; index++) {
        assertTrue(rs.previous());
        assertEquals(maxRows - index + 1, rs.getInt(1));
    }
    // We should not see another row (only maxRows, not total)
    assertFalse(rs.previous());

    // Start from last and verify maxRows
    assertTrue(rs.last());

    // Iterate backwards thru RS, expect only (maxRows - 1) more rows.
    for (int index = 1; index < maxRows; index++) {
        assertTrue(rs.previous());
        assertEquals((maxRows - index), rs.getInt(1));

    }
    // We should not see another row (only 5, not 6)
    assertFalse(rs.previous());
    rs.last();
    int rows = rs.getRow();

    rs.absolute(rows / 2);
    assertFalse(rs.relative(-1 * (rows)));
    assertTrue(rs.isBeforeFirst());

    rs.absolute(rows / 2);
    assertFalse(rs.relative(rows));
    assertTrue(rs.isAfterLast());
    rs.absolute(rows / 2);
    assertFalse("absolute(" + (rows + 1)
            + ") should return false, position outside of the resultSet",
            rs.absolute(rows + 1));

    rs.absolute(rows / 2);
    assertFalse(rs.absolute((-1) * (rows + 1)));

    assertTrue(rs.isBeforeFirst());

    rs.close();

}