Hibernate ScrollableResults without transaction

Hibernate ORM
1

I see that I can iterate ScrollableResults without explicit transaction like the following:

EntityManager em = ......

ScrollableResults scrollableResults = em.unwrap(Session.class).createQuery("my select query").scroll();

    while (scrollableResults.next()){
        MyEntity entity= (MyEntity) scrollableResults.get(0);
        someCalculation(entity);
    }
    em.close();

Why does it works? do I need to wrap it in a transaction?

I also notice when I calling a query (via the same entity manager) in the middle of the while loop as above I get an error that the resultset is closed. Why? after all I managed to read the result set without transaction

2

We can’t help you unless you post a stacktrace. The reasons for a closed result set are various. Maybe you hit a transaction timeout? Or the connection was terminated. The exception should tell you more details.

3

Thank you for answer me.
As you can see I don’t use transaction so I really confuse how the ScrollableResults works without transaction.

If I use the following (Say I have message entity)

EntityManager em = ......
EntityManager em = ......

 ScrollableResults scrollableResults = em.unwrap(Session.class).createQuery("SELECT m FROM Message m").scroll();

    while (scrollableResults.next()){
        Message m= (Message) scrollableResults.get(0);
        log.info(m);
       Number total = em.createQuery("SELECT COUNT(m) FROM Message m").getSingleResult();
logger.info("total messages : "+total);

    }

    em.close();

The stack trace is

2021-06-25 22:35:33 [main] INFO org.hibernate.jpa.internal.util.LogHelper - HHH000204: Processing PersistenceUnitInfo [name: my-persistence-unit]

2021-06-25 22:35:33 [main] INFO org.hibernate.Version - HHH000412: Hibernate ORM core version 5.4.30.Final

2021-06-25 22:35:33 [main] INFO org.hibernate.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {5.1.2.Final}

2021-06-25 22:35:34 [main] WARN org.hibernate.orm.connections.pooling - HHH10001002: Using Hibernate built-in connection pool (not for production use!)

2021-06-25 22:35:34 [main] INFO org.hibernate.orm.connections.pooling - HHH10001005: using driver [org.postgresql.Driver] at URL [jdbc:postgresql://localhost:5432/testdb]

2021-06-25 22:35:34 [main] INFO org.hibernate.orm.connections.pooling - HHH10001001: Connection properties: {password=****, user=postgres}

2021-06-25 22:35:34 [main] INFO org.hibernate.orm.connections.pooling - HHH10001003: Autocommit mode: false

2021-06-25 22:35:34 [main] INFO org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000115: Hibernate connection pool size: 20 (min=1)

2021-06-25 22:35:35 [main] INFO org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL10Dialect

2021-06-25 22:35:36 [main] INFO org.hibernate.engine.transaction.jta.platform.internal.JtaPlatformInitiator - HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]

2021-06-25 22:35:36 [main] DEBUG org.hibernate.SQL - select message0_.id as id1_0_, message0_.text as text2_0_ from message message0_

2021-06-25 22:35:36 [main] DEBUG org.hibernate.SQL - select message0_.id as id1_0_, message0_.text as text2_0_ from message message0_

2021-06-25 22:35:36 [main] INFO com.example.Launcher - Message [id=3, text=I am message number 3]

2021-06-25 22:35:36 [main] DEBUG org.hibernate.SQL - select count(message0_.id) as col_0_0_ from message message0_

2021-06-25 22:35:36 [main] DEBUG org.hibernate.SQL - select count(message0_.id) as col_0_0_ from message message0_

2021-06-25 22:35:36 [main] INFO com.example.Launcher - total messages : 30

2021-06-25 22:35:36 [main] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 55000

2021-06-25 22:35:36 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - This ResultSet is closed.

Exception in thread “main” org.hibernate.exception.GenericJDBCException: could not advance using next()

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)

at org.hibernate.internal.ScrollableResultsImpl.convert(ScrollableResultsImpl.java:71)

at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:106)

at com.example.Launcher.main(Launcher.java:28)

Caused by: org.postgresql.util.PSQLException: This ResultSet is closed.

at org.postgresql.jdbc.PgResultSet.checkClosed(PgResultSet.java:2905)

Caused by: org.postgresql.util.PSQLException: This ResultSet is closed.

at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1924)

at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:101)

… 1 more

Task :Launcher.main() FAILED

4

You can debug into the close method to see when the result set is closed, but I am pretty sure there is an implicit transaction that is committed after every interaction which also somehow closes the resources. I’m pretty sure this happens in org.hibernate.internal.SessionImpl#afterOperation which will close open resources like the scrollable result set.