Streaming large result sets with MySQL

by

Only setting the fetch size is not the correct approach. The javadoc of Statement#setFetchSize() already states the following:

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database

The driver is actually free to apply or ignore the hint. Some drivers ignore it, some drivers apply it directly, some drivers need more parameters. The MySQL JDBC driver falls in the last category. If you check the MySQL JDBC driver documentation, you’ll see the following information (scroll about 2/3 down until header ResultSet):

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Please read the entire section of the document, it describes the caveats of this approach as well. Here’s a relevant cite:

There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

(…)

If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

If that doesn’t fix the OutOfMemoryError (not Exception), then the problem is likely that you’re storing all the data in Java’s memory instead of processing it immediately as soon as the data comes in. This would require more changes in your code, maybe a complete rewrite. I’ve answered similar question before here.

More Related Contents:

  1. Using env variable in Spring Boot’s application.properties
  2. Unable to get spring boot to automatically create database schema
  3. No Dialect mapping for JDBC type: 1111
  4. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure Software caused connection abort: recv failed [duplicate]
  5. When are connections returned to the connection pool with Spring JPA (Hibernate) Entity Manager?
  6. Table ‘DBNAME.hibernate_sequence’ doesn’t exist
  7. How to use Spring Boot with MySQL database and JPA?
  8. How to setup Hibernate to read/write to different datasources?
  9. Filter invoke twice when register as Spring bean
  10. Spring MVC @PathVariable getting truncated
  11. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure [duplicate]
  12. Connect to remote MySQL database through SSH using Java
  13. Spring 4 – addResourceHandlers not resolving the static resources
  14. How to set spring active profiles with maven profiles
  15. How to conditionally enable or disable scheduled jobs in Spring?
  16. How do I get a HttpServletRequest in my spring beans?
  17. Spring hibernate template when to use and why?
  18. Spring Prototype scoped bean in a singleton
  19. Spring 3.1 JSON date format
  20. Populate JFreechart TimeSeriesCollection from Mysql DB?
  21. Spring + Hibernate : a different object with the same identifier value was already associated with the session
  22. How to run bootRun with spring profile via gradle task
  23. Which is best data type for phone number in MySQL and what should Java type mapping for it be?
  24. no suitable HttpMessageConverter found for response type
  25. Testing @Scheduled in spring
  26. java.lang.NoSuchMethodError: javax.persistence.spi.PersistenceUnitInfo.getValidationMode()Ljavax/persistence/ValidationMode;
  27. java.lang.NoSuchMethodError: org.springframework.core.annotation.AnnotationUtils.isInJavaLangAnnotationPackage(Ljava/lang/annotation/Annotation;)Z
  28. Spring Controller to handle all requests not matched by other Controllers
  29. Embedding mysql in java desktop application
  30. Spring Integration manually start/stop channel adapter via control bus

Leave a Comment