Large result on PostgreSQL query with Spring JPA/EclipseLink

Question

it is known that PostgreSQL queries expecting large result sets are best executed setting auto-commit off and ResultSet.TYPE_FORWARD_ONLY (see here). However, how can I realize this using Spring JPA together with EclipseLink? Has anyone experience with this (especially setting auto-commit off)?

Cheers, Steffen

score 1 · Answer 1 · answered Mar 14 '11 at 22:10

1

The JPA provider is supposed to handle low level connection details like the ones you are talking about. In JPA you can limit the number of rows returned by calling the setMaxResults() method on the Query interface. You can then use the setFirstResult() to page through the result set at runtime. The Postgres dialect implementation in EclipseLink is responsible for doing the best it can to make the communication with the postgres as effective as possible.

http://download.oracle.com/javaee/5/api/javax/persistence/Query.html

answered Mar 14 '11 at 22:10

ams

60,316
68
200
288

Is this really the standard way to do it? If I compare to java.sql.ResultSet, I can define a fetch size there which is automatically accounted for when iterating over the result set. Do I really need to do that (more or less) manually (although it's simple) when using Spring JPA? – Steffen Mar 17 '11 at 00:20

score 0 · Answer 2 · answered Feb 26 '15 at 03:22

The following seems to be working for me. Critical piece seems to be QueryHints.JDBC_FETCH_SIZE. Also, not caching result data helps a lot.

Query q = em.createNamedQuery("name");
q.setHint(QueryHints.CURSOR, HintValues.TRUE);
q.setHint(QueryHints.CURSOR_PAGE_SIZE, 1000);
q.setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE);
q.setHint(QueryHints.JDBC_FETCH_SIZE, 1000);

CursoredStream cursor = (CursoredStream)q.getSingleResult();
while(cursor.hasNext()) {
    MyObject o = (MyObject)cursor.next();
    // do something wit data
 }

score 0 · Answer 3 · answered Mar 14 '11 at 22:14

0

autocommit off is the default mode when you're using JPA. Else, you couldn't implement transactions. I'm pretty sure that result sets are forward only by default as well.

answered Mar 14 '11 at 22:14

JB Nizet

678,734
91
1,224
1,255

Large result on PostgreSQL query with Spring JPA/EclipseLink

3 Answers3