How JDBC Statement.setFetchsize exactly works

Question

I want to implement this methods functionality in my custom JDBC driver. Which method from JDBC should I use to setMaxResults?

If my query results is 1000 rows and I have set the value for setFetchSize=100, then what will be the results? It will return only 100 row or after returning first 100 it will get next 100 from db until 1000 rows completed.

It is not clear to me what you are trying to achieve. I'd suggest you make your question explicit by incorporating the information you are linking to, and describing what you want to achieve instead of making us guess. It is also not clear if you are having problems with the BigQuery side of things or with the JDBC terminology. — Mark Rotteveel, May 25 '15 at 10:31
@ Mark Rotteveel I am developing JDBC driver for Bigquery and I want Implement SetMaxResults methods functionality in to JDBC driver. — V__, May 25 '15 at 13:32

Mark Rotteveel · Accepted Answer · 2015-05-26T08:20:14.143

From Statement.setFetchSize:

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

So if you have a total of 1000 rows and the fetch size is 100 (and assuming the driver doesn't ignore the hint or fetches an approximate nr), then this means that the driver will fetch all rows in 10 batches of 100 rows each. A batch of rows is fetched when there is no row available locally to fulfill a call to next(), this is done until all rows have been fetched or maxRows has been reached.

This results in something like:

Statement stmt = ...;
stmt.setFetchSize(100);
ResultSet rs = ...;
rs.next(); // Triggers fetch of 100 rows, positions on row 1 / 100
rs.next(); // Positions on row 2 / 100
// 3-99 rs.next();
rs.next(); // Positions on row 100 / 100
rs.next(); // Triggers fetch of 100 rows, positions on row 101 / 200
// etc

This is speaking ideally from the perspective of the JDBC specification. There are drivers that ignore the hint (eg MySQL abuses it to fetch row by row if you set it to Integer.MIN_VALUE and ignores all other values), and some databases will take the hint, but then return less (sometimes more) rows than requested.

I find the documentation of com.google.api.services.bigquery.model.QueryRequest.setMaxResults you link not entirely clear, but it seems to do the same thing.

How JDBC Statement.setFetchsize exactly works

1 Answers1

Linked