returning Iterable from fluent interface with a cursor

[Michael Kay]

Hi,

I'm trying to return an Iterator<> using the fluent interface so that I can process large amounts of data in chunks. I'm using PostgreSQL. I know you need to set AutoCommit to false to use a cursor or use a transaction (which sets AutoCommit to false internally) but I don't want this to effect all connections in my application and therefore want to reset it afterwards. If I try to reset AutoCommit in a finally (or use a transaction) I get an error such as:

PSQLException: ERROR: portal C_2 does not exist

This is due to the connection being closed before the Iterator has finished fetching. The basic code looks like:

public Iterator<Stuff> getAll() {
    try (Handle handler = jdbi.open()) {
        return handler.createQuery("SELECT stuff FROM thing")
                .setFetchSize(200000)
                .map(new StuffMapper())
                .iterator();
    }
}

How can I use a Cursor with an Iterator using the fluent interface?

I'm sure i'm missing something simple. I've tried:

• Setting AutoCommit to false and then setting it to true in a finally
• Wrapping this in an inTransaction
• Returning a Stream instead of an Iterator and using StreamSupport to hook into onClose and trying to set it there. Connection was already closed :(
• Using a StatementCustomizer and trying to do it on cleanUp

As you can see, my efforts are getting more and more desperate :)

Thanks if anyone can help,

 

[Matthew Hall]

The iterator you're returning is actually a ResultIterable over a JDBI ResultSet, and requires a connection. However you're creating the Handle in a try-with-resources block--this explicitly closes the connection before the method returns. So at that point the Iterable it useless.

You need to consume the Iterable inside your try block.

If your results are small enough, you could do query.map(mapper).list().iterator(), which would eagerly load the mapped results into memory.

-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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Matthew Hall]

Replace all "Iterable" with "Iterator" in my last message. Recent PRs have scrambled my brains.. :)

[Michael Kay]

Thanks for the fast response. Very awesome to get such fast feedback.

The reason I wanted to use an Iterator was so that I didn't fetch all rows up front as there are lots of them and I wanted to separate the concerns of DB access (in a Repository) to processing the data (in a Jersey Resource).

I'm guessing this is not possible? It seems like the perfect Use Case for an Iterator though :)

Cheers again,

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.

[Matthew Hall]

If you want to use the iterator, you have to leave the handle open. There's really no way around it. Sorry.

-Matt

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Michael Kay]

No Problems. I might have ended up doing something crazy. I pass a Consumer lambda to my getAll and my method acts on that. Not the nicest but it works and keeps my separation of concerns.

    public void getAll(Consumer<Iterator<Stuff>> consumer) {
        jdbi.useTransaction((h, s) -> consumer.accept(
                h.createQuery("SELECT thing FROM stuff")
                        .setFetchSize(200000)
                        .map(new StuffMapper())
                        .fetchForward()
                        .iterator()
        ));
    }

And call it like

return output -> {
            try (JsonGenerator jsonGenerator = ObjectMapperProvider.Mapper.getFactory().createGenerator(output, JsonEncoding.UTF8)) {

                jsonGenerator.writeStartObject();

                repository.getAll(stuff -> stuff.forEachRemaining(c -> {
                    try {

                        jsonGenerator.writeFieldName(c.getStuffName().toString());
                        jsonGenerator.writeString(c.getStuffValue().toString());

                    } catch (IOException e) {
                        throw new RuntimeException("Problem reading stuff", e);
                    }
                }));


                jsonGenerator.writeEndObject();


            } finally {
                output.flush();
                output.close();
            }
        };

Just in case anyone has the same problem and would like a solution, no matter how crazy.

[Matthew Hall]

Nice.

Alternatively your `getAll` method could take a `Consumer<Stuff>`, and could call `.iterator().forEachRemaining(consumer)`, which would simplify usage at the call site. At that point you could rename the method to e.g. `forEach`.

Regards,

-Matt

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Michael Kay]

Even nicer :)

You received this message because you are subscribed to a topic in the Google Groups "jDBI" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jdbi/c2xHDeA-AZk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jdbi+uns...@googlegroups.com.