Batch Inserts Via EntityManager in MySQL
Description: Batch inserts via EntityManager in MySQL. This way you can easily control the flush() and clear() of the persistence context (1st level cache). This is not possible via SpringBoot, saveAll(Iterable<S> entities). Another advantage is that you can call persist() instead of merge() - this is used behind the scene by the SpringBoot saveAll(Iterable<S> entities) and save(S entity).
Key points:
- in application.properties set spring.jpa.properties.hibernate.jdbc.batch_size
- in application.properties set spring.jpa.properties.hibernate.generate_statistics (just to check that batching is working)
- in application.properties set JDBC URL with rewriteBatchedStatements=true (optimization for MySQL)
- in application.properties set JDBC URL with cachePrepStmts=true (enable caching and is useful if you decide to set prepStmtCacheSize, prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
- in application.properties set JDBC URL with useServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
- in entity, use the assigned generator since MySQL IDENTITY will cause batching to be disabled
- in DAO, flush and clear the persistence context from time to time. This way you avoid to "overwhelm" the persistence context.
Output example:
