0

I am trying to use ScrollableCursor for loading all users of the system to memory. I have a code like this:

    Query findUsersQuery
            = entityManager.createNamedQuery(UserEntity.QUERY_ALL_USERS, UserEntity.class);
    findUsersQuery.setHint(QueryHints.SCROLLABLE_CURSOR, Boolean.TRUE);
    findUsersQuery.setHint(QueryHints.CURSOR_PAGE_SIZE, bulkSize);
    findUsersQuery.setMaxResults(maxInitialCacheSize);

    ScrollableCursor scrollableCursor = (ScrollableCursor) findUsersQuery.getSingleResult();
    int leftSize = scrollableCursor.size();
    while (scrollableCursor.hasNext()) {

        int nextSize = leftSize > bulkSize ? bulkSize : leftSize;
        List subscriberPrefs = scrollableCursor.next(nextSize);
        leftSize -= nextSize;
        //... Result list processing

    }

The query is: SELECT s FROM UserEntity u order by u.userId

Size of result table is 100, when scrollableCursor.next(nextSize); called, i get following exception:

org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLRecoverableException: Closed Resultset
Error Code: 17010
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.cursorRetrieveNextRow(DatabaseAccessor.java:447)[215:org.eclipse.persistence.core:2.4.1.v20121003-ad44345]
    at org.eclipse.persistence.queries.ScrollableCursor.retrieveNextObject(ScrollableCursor.java:563)[215:org.eclipse.persistence.core:2.4.1.v20121003-ad44345]
    at org.eclipse.persistence.queries.ScrollableCursor.loadNext(ScrollableCursor.java:411)[215:org.eclipse.persistence.core:2.4.1.v20121003-ad44345]
    at org.eclipse.persistence.queries.ScrollableCursor.next(ScrollableCursor.java:437)[215:org.eclipse.persistence.core:2.4.1.v20121003-ad44345]
    at org.eclipse.persistence.queries.ScrollableCursor.next(ScrollableCursor.java:459)[215:org.eclipse.persistence.core:2.4.1.v20121003-ad44345]

While calling next(10) the first row loaded normally and the exceptions occurs on second row. I don't have an idea why. Can anybody say how i can avoid this error?

Architect
  • 155
  • 2
  • 11
  • Is this a container managed persistence unit? Try wrapping it in a transaction if so. – Chris Aug 25 '15 at 15:21
  • Do you call next() inside the same "http request"? If not, your container probably closes the DB connection (And also the resultset) – André Schild Aug 26 '15 at 10:50
  • I call scrollableCursor.next(nextSize), it calls next() in cycle. All resultset processing done into one method. This operation done inside single transaction. – Architect Aug 26 '15 at 14:24

0 Answers0