CODEWITHSUNDEEP
springpostgresqljpaeclipselink
Steffen
Steffen

Reputation: 31

Large result on PostgreSQL query with Spring JPA/EclipseLink

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

Upvotes: 2

Views: 1843

Answers (3)

maximdim
maximdim

Reputation: 8169

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
 }

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 692261

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.

Upvotes: 0

ams
ams

Reputation: 62792

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

Upvotes: 1

Related Questions