Performance insert by JPA Spring-boot with Oracle database

Question

the first thanks for your time.

I am trying to insert data to the database by JPA(spring-boot), the project is using Oracle.

Currently, Insert 5000 record, it takes a long time with repository.save(...) or repository.saveAll(...).

I tried batch_size, but it is not working(looks like it is not working for oracle ?).

Code config:

            Properties properties = new Properties();
            properties.setProperty("hibernate.ddl-auto", "none");
            properties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");
            properties.setProperty("hibernate.show_sql", "true");
            properties.put("hibernate.jdbc.batch_size", 5);
            properties.put("hibernate.order_inserts", true);
            properties.put("hibernate.order_updates", true);
            setJpaProperties(properties);

I create sql query to insert several rows at one time execute statement.

INSERT ALL INTO table(...)...

I hope there is a better and more efficient way

So, can you give me any solution?

Thankyou so much!!!!

Answer 1

How about: batch_size : 1000 when entity count is 1000, then :repository.saveAndFlush(); then call the next batch.

Another method can be call the EntityManager persist directly in the batch saving. like:

public int saveDemoEntities(List<DemoEntity> DemoEntities) {
    long start = System.currentTimeMillis();
    int count = 0;
    for (DemoEntities o : DemoEntities) {
        entityManager.persist(o);
        count++;
        if (count % BATCH_COUNT == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
    entityManager.flush();
    entityManager.clear();
    return count;
}