Spring repository method which are returning Java 8 stream doesn't close JDBC connection

Question

I have a Spring data repository:

@Repository
interface SomeRepository extends CrudRepository<Entity, Long> {
    Stream<Entity> streamBySmth(String userId);
}

I am calling that method in some Spring bean:

@Scheduled(fixedRate = 10000)
private void someMethod(){
    someRepository.streamBySmth("smth").forEach(this::callSomeMethod);
}

I am using MySQL database. And when I am running application after some successful method invocations it throws an exception:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08001
o.h.engine.jdbc.spi.SqlExceptionHelper   : Could not create connection to database server.
o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task.

org.springframework.dao.DataAccessResourceFailureException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

It seems, that connection was not closed properly by Spring. If I have changed method return value to List from Stream it works correctly.

UPDATE: Spring Boot version is 1.4.1.RELEASE

Unrepair

Have you tried calling close on the stream? – Alfonzoalford

I have tried, it was not helped. Nevertheless, it is not correct behavior to close Stream manually. – Unrepair

I'm not very familiar with returning a Stream from a repository, but a Stream is lazy, so how would Spring otherwise know when to close the connection? Stream is an AutoClosable as well, so you could use it in a try-with-resources clause. – Alfonzoalford

Streams has ending operations, that means that you can not do anything else with Stream after them. forEach is one of such operations. – Unrepair

They have terminating operations, but that doesn't close the stream. See here: #37660372 – Alfonzoalford

Answer 1

As the reference documentation clearly states, Streams need to be used with a try-with-resources block.

Also, make sure you keep a (read-only) transaction open for the time of the consumption of the stream by annotating the surrounding method with @Transactional. Otherwise the default settings apply and the resources are attempted to be freed on repository method return.

@Transactional
public void someMethod() {

  try (Stream<User> stream = repository.findAllByCustomQueryAndStream()) {
    stream.forEach(…);
  } 
}

Perpetrate

It looks reasonable but it does not help us. I still have same error after several requests. – Unrepair

Unfortunately, it does not help again. I have tried to use @Transactional(readOnly = true) and also i have tried to .close() streams. Now it runs successfully 100 counts and fails. As i understand it means that new connection are opened on every request. Can it be a Spring bug? – Unrepair

Can you provide a sample reproducing your problem? There's a ticket with a problem described that sounds related here but that can be solved by following what I described above. – Perpetrate

Answer 2

Using @Transactional(readOnly = true) and public access modifier will solve the issue. Any other access modifier will not work.

Burdine