1

I've been trying to enable Batch Inserts using Spring Boots JPA with a MySQL database

In my application.properties:

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
spring.datasource.url=jdbc:mysql://localhost:3306/demo?reconnect=true&rewriteBatchedStatements=true
spring.jpa.hibernate.jdbc.batch_size=500
spring.jpa.hibernate.order_inserts=true
spring.datasource.username=demo_user
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

I have tried using both CrudRepository.save(Iterable s) and have tried using a custom repository (which i've seen in another answer):

@Component
public class BulkRepository<T extends Identifiable> {// Identifiable just has a getId method

    private static final int BATCH_SIZE = 500;

    private final EntityManager entityManager;

    @Autowired
    public BulkRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public <S extends T> List<S> saveInBatch(Iterable<S> value) {
        List<S> entities = Lists.newArrayList(value);
        List<S> saved = new ArrayList<>(entities.size());
        int i = 0;
        for (S t : entities) {
            saved.add(persistOrMerge(t));
            i++;
            if (i % BATCH_SIZE == 0) {
                // Flush a batch of inserts and release memory.
                entityManager.flush();
                entityManager.clear();
            }
        }

        return saved;
    }

    private <S extends T> S persistOrMerge(S t) {
        if (t.getId() == null) {
            entityManager.persist(t);
            return t;
        } else {
            return entityManager.merge(t);
        }
    }
}

When I enable the general query log in MySql with both the above it shows each insert as seperate:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

SELECT 
    event_time,
    user_Host,
    CONVERT(argument USING utf8) as query
FROM mysql.general_log
order by event_time desc
;

2017-12-22 12:31:48.290334 demo_user[demo_user] @ localhost [127.0.0.1] insert into demo.test_table (id, string) values (1, 'value1')

2017-12-22 12:31:48.288328 demo_user[demo_user] @ localhost [127.0.0.1] insert into demo.test_table (id, string) values (2, 'value2')

Is there something I'm missing? At the moment it is looking like I'll need to bypass Spring JPA and use raw SQL for this.

Community
  • 1
  • 1
Eduardo
  • 6,900
  • 17
  • 77
  • 121
  • No it isn't... The JDBC driver is rewriting the batch statements not hibernate in this case. See https://stackoverflow.com/questions/21530112/no-matter-what-i-cant-batch-mysql-insert-statements-in-hibernate/21587949#21587949 – M. Deinum Dec 22 '17 at 14:07
  • @M.Deinum Thats not how they appear in the MySQL log though (which is direct from the database itself) – Eduardo Dec 22 '17 at 14:13
  • To me it looks like what you posted is the application log not the mysql log. Nonetheless the answer is in the same question. – M. Deinum Dec 22 '17 at 14:15
  • @M.Deinum The log is from running the query above it direct via MySql workbench – Eduardo Dec 22 '17 at 14:17
  • I think the question is different, I cant call `Transaction t = session.beginTransaction();` like they are using as my transactionals are managed by `@Transactional`. I get an exception if I try use both within the same application. – Eduardo Dec 22 '17 at 14:19
  • Your `spring.jpa.hibernate` properties are pretty much useless. It should be `spring.jpa.properties.hibernate....` – M. Deinum Dec 22 '17 at 14:19
  • No the question isn't different, how a transaction is started doesn't matter. The fact is that hibernate will always log single queries. – M. Deinum Dec 22 '17 at 14:20
  • @M.Deinum as mentioned, that is not the hibernate log (i'm aware that always logs single queries). It is the result of running the SQL query in my question above. – Eduardo Dec 22 '17 at 14:21
  • Always nice if people skip your comments -> Your `spring.jpa.hibernate properties` are pretty much useless. It should be `spring.jpa.properties.hibernate` – M. Deinum Dec 22 '17 at 14:24
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161819/discussion-between-edd-and-m-deinum). – Eduardo Dec 22 '17 at 14:35

0 Answers0