Use Stream return type in Spring Boot controller lead to "java.sql.SQLException: Closed Resultset: next"

Question

When streaming result from JpaRepository to Rest API, I have an exception "java.sql.SQLException: Closed Resultset: next"

I am Using Spring boot, Spring Data JPA and Oracle Database/JDBC

Repository

@Repository
public interface EntityRepository extends JpaRepository<Entity, Long> {

  @Transactional(readOnly = true)
  public Stream<Entity> findAllByFilter(String filter);
}

Service

@Service
public class EntityService {
  @Autowired private EntityRepository repository;

  @Transactional(readOnly = true)
  public Stream<Entity> getEntityByFilter(String filter) {
    return repository.findAllByFilter(filter);
  }
}

Controller

@RestController
@RequestMapping("/entities")
public class EntityController {

  @Autowired private EntityService service;

  @GetMapping(value = "/filter/{filter}")
  @Transactional(readOnly = true)
  public Stream<Entity> getEntitiesByFilter(@PathVariable("filter") String filter) {
    return service.getEntityByFilter(filter);
  }
}

Consuming the stream in the controller works, but returning it through HTTP cause

Caused by: java.sql.SQLException: Closed Resultset: next at oracle.jdbc.driver.InsensitiveScrollableResultSet.ensureOpen(InsensitiveScrollableResultSet.java:201) ~[ojdbc11-21.3.0.0.jar:21.3.0.0.0] at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:496) ~[ojdbc11-21.3.0.0.jar:21.3.0.0.0] at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar:?] at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:101) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final] at org.hibernate.query.internal.ScrollableResultsIterator.hasNext(ScrollableResultsIterator.java:33) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final] at java.util.Iterator.forEachRemaining(Iterator.java:132) ~[?:?]

Answer 1

I resolved my issue by using getResultStream of Query class and applying my filters with Predicates (instead of JpaRepository), the Stream to a HTTP client work well