Retrieving large result sets through JDBI failing...

Skip to first unread message

James Cubeta

Apr 2, 2021, 3:00:29 PM4/2/21
to jDBI
Hello everyone - 

I apologize in advance if this is a "newbie"-level question with a simple solution, but I've been able to find the solution on my own.

I am using JDBI to access & manage a Postgres database, and I am trying to perform a query that returns a large amount of data. I am trying to use ResultIterator to give me ability to page through the results vs loading them all into memory. 

For example purposes, here's basically what I'm doing - first, I have an interface defined that retrieves my data: 

public interface MyDatabase {
   @SqlQuery("select from customers where zipcode = :zipcode")
   public ResultIterator<Customer> getByZipCode(int zipcode);

And then from elsewhere in my code, I am trying to do this:

public void processCustomersByZip(int zip) { 
   try (ResultIterator<Customer> customers = myDb.getByZipCode(zip)) { 
      while (customers.hasNext()) { 
         Customer c =;
         // do stuff...

All of this is inside a SpringBoot application, so I'm using the following to create the instantiation of my MyDatabase interface: 

public class MyConfig { 

   DataSource dataSource;

   public MyDatabase getDatabase() { 
      Jdbi jdbi = Jdbi.create(dataSource);
      jdbi.installPlugin(new SqlObjectPlugin());
      return jdbi.onDemand(MyDatabase.class);

When I try to run my code, I get into the "try" but it eventually dies with a "Unable to advance result set" with a nested "This ResultSet is closed" exception out of the postgres jdbc driver. I never see a single record from the database.

I am hoping this is something simple that I've overlooked. I am using v3.18.0 of JDBI and v42.2.19 of the Postgres JDBC driver. 

Thank you in advance for any help.

Matthew Hall

Apr 7, 2021, 2:25:28 PM4/7/21
The problem is you are using an on-demand object. On-demand closes the connection and releases all database resources before returning. Try doing this inside a call to `jdbi.useExtension(MyDao.class, dao -> { ... })` instead of using an on-demand DAO. Or extension `GetHandle` in your DAO interface, and make the calls inside `dao.useHandle(h -> { ... })`



You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit

Matthew Hall

Apr 7, 2021, 2:28:37 PM4/7/21
Excuse me, I named the wrong interface. If you extend the `SqlObject` interface in your DAO, you can move your query and processing code inside a `useHandle(h -> { ... })` call, which will keep the result iterable open for you to process the result.
Reply all
Reply to author
0 new messages