CODEWITHSUNDEEP
sql-serverjdbcpagination
Prasad Parab
Prasad Parab

Reputation: 496

Behaviour of setFetchSize() in JDBC

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

Upvotes: 0

Views: 27

Answers (1)

siggemannen
siggemannen

Reputation: 9272

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

Upvotes: 0

Related Questions