search

spring-projects / spring-data-jpa

Simplifies the development of creating a JPA-based data access layer.
https://spring.io/projects/spring-data-jpa/
Apache License 2.0
3k stars 1.41k forks source link

Add note about driver behaviour for streaming query results [DATAJPA-1007] #1346

Open spring-projects-issues opened 7 years ago

spring-projects-issues commented 7 years ago

Robert Hunt opened DATAJPA-1007 and commented

It would be useful to add a note about JDBC driver behaviour in relation to streaming results. I know it goes beyond the scope/control of Spring Data but someone might assume that by using a Stream return type that the ResultSet is being fetched in a streaming nature from the database.

In the case of MySQL this is certainly not the case as a @QueryHint needs to be included as a signal to the underlying driver (see: http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html)

Just a small note as a hint to the reader that they may need to investigate the behaviour of their specific drivers in regard to memory-efficient fetching would be useful

Reference URL: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-streaming

spring-projects-issues commented 7 years ago

Oliver Drotbohm commented

That's a great suggestion. We might even do better here and find a way to set the hint automatically if we detect MySQL to be the database being talked to. I agree that it might be a too low-level thing to do ourselves, but maybe the Stream support in the recent Hibernate versions might already take care of that for us. Definitely worth exploring.

In either case, adding a note to the reference documentation doesn't hurt :)

spring-projects-issues commented 7 years ago

Robert Hunt commented

I have actually just come across a situation where it would be nice to have this feature auto-enabled based on the driver. It turns out that the org.h2.Driver doesn't like it if you add a @QueryHint which sets the fetch size to Integer.MIN_VALUE, it actually throws an exception. This causes some of our tests to fail as we make use of H2 for some integration tests, I couldn't find any simple workaround but I have written an Aspect which goes and removes specific annotations from query methods, it's not pretty but it is a workaround for the moment:

https://gist.github.com/roberthunt/78392cbca46eeb33c803ce0294f1e847