How JDBC Statement.setFetchsize exactly works

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.

Answer

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.