Groovy Sql Paging Behavior

Question

I use the groovy.sql.Sql class to query the database and process the results. My problem is that the ResultSet can be very large; so big that I run the risk of running out of memory if I try to process the entire ResultSet at once. I know that the Sql.rows () method supports paging using offset parameters and maximum results, but I could not find a good example of how to use it (and I'm not sure if the search engine is what I am looking for).

Basically, here is what I am trying to do:

def endOfResultSet = false for(int x = 1; !endOfResultSet; x+=1000){ def result = sql.rows("Select * from table", x, 1000) processResult(result) endOfResultSet = result.size()!=1000 } 

My question is, is Groovy smart enough to reuse the same result set to invoke sql.rows("Select * from table", x, 1000) or if it will re-run the same statement in the database and then paging to where the offset begins.

Your help is appreciated, thanks!

Edit: what I'm trying to avoid triggers the same query in the database several times. I would like to run the request once, get the first 1000 lines, process them, get the next 1000 lines, etc ... until all the lines are processed.

Answer 1

I assume you saw this paging blog post ?

To answer your question, if we look at the code for the Sql class in Groovy, we will see that the code for rows(String,int,int) calls rows(String,int,int,null)

And the code for this:

  AbstractQueryCommand command = createQueryCommand(sql); ResultSet rs = null; try { rs = command.execute(); List<GroovyRowResult> result = asList(sql, rs, offset, maxRows, metaClosure); rs = null; return result; } finally { command.closeResources(rs); } 

So, as you can see, it gets the full ResultSet , then performs the steps inside the asList method, populating the List<GroovyRowResult> with the requested results.

Edit (after editing the question)

As I said in my comment below, I think you will need to write your own search query for the specific database that you are using ... For example, with MySQL , your previous query can be changed to:

 def result = sql.rows( "SELECT * FROM table LIMIT ${Sql.expand x}, 1000" ) 

Other databases will have different methods for this kind of thing ... I do not believe that there is a standard implementation

Answer 2

The answer above is incorrect. If you dig deeper, you will find that if the ResultSet is not TYPE_FORWARD_ONLY, then the server-side cursor is called for the absolute ResultSet method. Then maxRows are returned. If ResultSet is TYPE_FORWARD_ONLY, then ResultSet.next () is called by the number of delays, then maxRows is returned. The exact performance characteristics will depend on the underlying jdbc driver implementation, but usually you want to get a scrollable result set when using the paging function.

The result set is not reused between calls. It sounds like you want something like streaming, not swapping.

In addition, I wrote a patch, by the way.

http://jira.codehaus.org/browse/GROOVY-4622