下面列出了java.sql.ResultSet#setFetchSize ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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;
}
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;
}
/**
* 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;
}
}
}
@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
}
}
}
@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
}
}
}
@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());
}
@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);
}
@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
}
}
}
/**
* 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();
}
@NoWarning("SQL_BAD_RESULTSET_ACCESS")
void notBug(ResultSet any) throws SQLException {
any.setFetchSize(0);
}
/**
* 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();
}
/**
* 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();
}