limiting the size of a resultset - Breaking up large resultset into chunks

This is not a question, but some answers I have come up with in the past couple of days that I thought I'd share:

one option would be to use statement.setFetchSize(int) to limit the size of results returned -- call this prior to calling statement.executeQuery()
-------------------------------------------------
Otherwise, there are ways to control the resultset size via the SELECT statement as well, the exact way to do it depends on your database:
-----------------------------
Oracle:
select your results and explicitly add the rownum to the result, then select against that and use a between clause on the rownum, like this:
SELECT TALIAS.* FROM (SELECT ROWNUM AS RWN, <column-names> FROM (SELECT <column-names> FROM table_name WHERE <....> ORDER BY <...>

TALIAS) WHERE TALIAS.RWN BETWEEN 1 AND 15
plug in your column names and table name, where conditions, etc. TALIAS is an statement-scope alias for the inner SELECTion result.
just plug in different values for the BETWEEN numbers and you can control the size of the result set and your overall window to it....
-----------------------------
MySQL:
much easier, use the limit keyword at the end of your query:
All rows:
SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 0, -1
first 10 rows:
SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 10
next 20 rows:
SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 11, 20
LIMIT begin-index, number-to-return (-1 is all remaining rows, so LIMIT 0, -1 is the entire result set, while LIMIT 101, -1 would be all remaining results after the 100th)
---------------------------
HSQLDB:
also much easier, use the limit keyword at the beginning of your query, with no comma, and 0 in the second position means all remaining results:
all rows:
SELECT LIMIT 0 0 columnA, columnB FROM table_name WHERE columnA=condition
first 10 rows:
SELECT LIMIT 0 10 * FROM table_name WHERE columnA=condition
next 20 rows:
SELECT LIMIT 11 20 * FROM table_name WHERE ColumnA=condition
---------------------------
MSSQL (I think only applies to 2000, not 7):
TOP is similar to LIMIT for MySQL
SELECT TOP 100 * FROM table_name
you could simulate paging by subsequently calling:
SELECT TOP 200 * FROM table_name
and skipping over the first 100 rows in the resultset (not pretty but it would work)

If you have a single-valued key that you sort on, you can use TOP and a WHERE clause to paginate the result set:
SELECT TOP 100 * FROM table_name WHERE key_column > ?
and parameterize the ? with 0, 100, 200, etc.

If the key is a compound key, then you can select data into a temp table with an identity column, and then pick intervals from this one. This is similar to the Oracle way to do it.
I don't know MSSQL syntax well enough to give an example of how to select into a temp table while adding an auto-increment int column. If someone does know, please post an example of how to do it!
Once you have the temp table:
SELECT * FROM temp_table WHERE new_auto_inc_key_column BETWEEN start AND end
---------------------------
hope this helps -- I just ran into the same problem myself yesterday

in the Oracle example, the greater-than symbol followed by a right-parenthesis became a smiley face -- make sure you add a ')' before TALIAS

I'm going to add the keywords "ResultSet Paging" to this thread since that it what many people search on.
"Rubber Chicken",
Your name does not comply to the Javaranch Naming Policy found here. Accounts with invalid names get deleted, but you can edit your profile here
thanks,
Dave

Someone pointed this out to me for MSSQL 7 -- see how it flips the ORDER BY from DESC in the inner query to ASC in the 'final page' outer query -- pretty ingenious:

see also:
http://www.jguru.com/forums/view.jsp?EID=1060440

from the same resource as above, for Postgres: