Retrieving large result sets through JDBI failing...

[James Cubeta]

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 {

   @FetchSize(1000)

   @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:

@Transaction

public void processCustomersByZip(int zip) { 

   try (ResultIterator<Customer> customers = myDb.getByZipCode(zip)) { 

      while (customers.hasNext()) { 

         Customer c = customers.next();

         // do stuff...

      }

   }

}

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

@Configuration

public class MyConfig { 

   @Autowired

   DataSource dataSource;

   @Bean

   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.

James

[Matthew Hall]

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 -> { ... })`

Regards,

-Matt

--
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 jdbi+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/15f1517f-ddf4-40f6-b4da-8814ff5b7f89n%40googlegroups.com.

[Matthew Hall]

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.