sql server - Behaviour of setFetchSize() in JDBC - Stack Overflow

admin管理员组

文章数量:1287886

I am fetching data from SQL Server database through JDBC. My SQL Server database is on remote machine and I connects the machine through a VPN.

I want to fetch the data in pages hence I used setFetchSize() method

Below is my code

  preparedStatement = getStatement(connection, sqlStatement);
  int pageSize = 10;
  preparedStatement.setFetchSize(pageSize);
  resultSet = statement.executeQuery();
   while (resultSet.next()) {
      int id = resultSet.getInt("id");
      String name = resultSet.getString("name");
      System.out.println("ID: " + id + ", Name: " + name);
   }

I believe JDBC fetches the data into chunks of 10 rows once 10 rows iterates then fetches next 10

to verify this

1. I put a break point in while statement
2. Once code execution stopped while processing first row I disconnect the VPN so now there is no database connection between JDBC and MS-SQL database
3. Continued the code execution

Expected Behaviour:

JDBC should throw connection error after 10 rows

Actual behaviour

JDBC proceeded all 50 rows

What goes wrong here? Do I misunderstood setFetchSize() method

I am fetching data from SQL Server database through JDBC. My SQL Server database is on remote machine and I connects the machine through a VPN.

I want to fetch the data in pages hence I used setFetchSize() method

Below is my code

  preparedStatement = getStatement(connection, sqlStatement);
  int pageSize = 10;
  preparedStatement.setFetchSize(pageSize);
  resultSet = statement.executeQuery();
   while (resultSet.next()) {
      int id = resultSet.getInt("id");
      String name = resultSet.getString("name");
      System.out.println("ID: " + id + ", Name: " + name);
   }

I believe JDBC fetches the data into chunks of 10 rows once 10 rows iterates then fetches next 10

to verify this

1. I put a break point in while statement
2. Once code execution stopped while processing first row I disconnect the VPN so now there is no database connection between JDBC and MS-SQL database
3. Continued the code execution

Expected Behaviour:

JDBC should throw connection error after 10 rows

Actual behaviour

JDBC proceeded all 50 rows

What goes wrong here? Do I misunderstood setFetchSize() method

• sql-server
• jdbc
• pagination

Share Improve this question Follow edited Feb 22 at 21:39 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Feb 22 at 19:00 Prasad ParabPrasad Parab 49611 gold badge99 silver badges3131 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I want to fetch the data in pages

This is probably wrong, why do you want that?

It's not likely SQL Server will read the whole resultset in memory anyway, if you're thinking about that part.

Anyways, for SQL Server this setting only has effect if your select uses server cursor, which isn't applicable for your kind of fetch. For example updateable resultsets makes use of this, or resultsets which aren't forward only but scrollable.

You can also force server cursors with selectMethod=cursor in your JDBC script, but you have to know what you're doing :) I usually go with selectMethod=direct and responseBuffering=adaptive for responsive resultsets

本文标签： sql serverBehaviour of setFetchSize() in JDBCStack Overflow