Max Max - 1 month ago 11
Groovy Question

Setting fetchSize in groovy Sql

I am using groovy sql (2.4.4). But I could not figure out how to set fetchSize and scroll the results.

I use

sql.setResultSetType(ResultSet.TYPE_FORWARD_ONLY)
but queries with
sql.rows(sqlQuery)
returns all the data at the same time and this is really slow. I want to fetch the data like we do in normal jdbc queries.

Setting maxRows is not really an option because I have to query too many times, but fetchSize would be a good option.

In groovy sql documentation(http://docs.groovy-lang.org/2.4.4/html/api/groovy/sql/Sql.html) I see that:


A facade over Java's normal JDBC APIs providing greatly simplified resource management and result set handling.


But no information given about fetching.
How can I set fetchSize ?

Thank You!

Answer

For fetchSize, this seems to be the only option,

An example here,

 sql.withStatement { stmt -> stmt.fetchSize = 50 }

For maxResult, there are few options available,

  • use overloaded rows() method that takes a maxRows param, or
  • you could use, asList() method that takes a maxRows param, or
  • use eachRow() that takes a maxRows param

NB: There are quite a number of overloaded methods named rows(..), asList(..), and eachRow(..).