it is known that PostgreSQL queries expecting larg开发者_如何学编程e 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
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
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.
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
}
精彩评论