What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause?

Question

What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause?

Answer 1

The SQL LIMIT will limit your SQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results.

The fetch size is the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet with next(). For example, you set the query fetch size to 100. When you retrieve the first row, the JDBC driver retrieves the first 100 rows (or all of them if fewer than 100 rows satisfy the query). When you retrieve the second row, the JDBC driver merely returns the row from local memory - it doesn't have to retrieve that row from the database. This feature improves performance by reducing the number of calls (which are frequently network transmissions) to the database.

So, even if setting the fetch size is translated by JDBC into a SQL LIMIT clause, the big difference with forcing a SQL query with LIMIT is that with JDBC, you're actually still able to browse all the results.