Bulk Insert with H2 Database on Spring Boot

Question

I followed this example (How to do bulk (multi row) inserts with JpaRepository?) and created an h2-database example case. But bulk-insert doesn't work.

Model:

@Table(name = "user")
@Entity
public class User
{
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    private String name;
}

Repository:

public interface IUserRepository extends JpaRepository<User, Long>
{
}

Test:

@SpringBootApplication
public class SpringBootApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringBootApplication.class, args);
    }

    @Bean
    ApplicationRunner init(IUserRepository userRepository)
    {
        return args -> {
            User user = new User();
            user.setName("Test-1");

            User user1 = new User();
            user1.setName("Test-2");
            userRepository.saveAll(Arrays.asList(user, user1));
        };
    }

}

properties:

spring.datasource.url=jdbc:h2:~/test;TRACE_LEVEL_FIle=4
spring.jpa.hibernate.ddl-auto=create
spring.datasource.username=sa
spring.jpa.properties.hibernate.jdbc.batch_size=5

Output:

/*SQL #:1*/call next value for hibernate_sequence;
2021-02-28 15:13:35.110  INFO 43465 --- [           main] h2database                               
/*SQL l:58 #:1*/SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME=? {1: 'QUERY_TIMEOUT'};
2021-02-28 15:13:35.119  INFO 43465 --- [           main] h2database                               
/*SQL #:1*/call next value for hibernate_sequence;
2021-02-28 15:13:35.136  INFO 43465 --- [           main] h2database                               
/*SQL l:41 #:1*/insert into user (name, id) values (?, ?) {1: 'Test-1', 2: 1};
2021-02-28 15:13:35.137  INFO 43465 --- [           main] h2database                                
/*SQL l:41 #:1*/insert into user (name, id) values (?, ?) {1: 'Test-2', 2: 2};
2021-02-28 15:13:35.139  INFO 43465 --- [           main] h2database                               
/*SQL */COMMIT;
2021-02-28 15:13:35.139  INFO 43465 --- [           main] h2database                               
/*SQL */COMMIT;

How can I test bulk-insert in h2-database with using Spring-Boot? Or is it possible?

score 0 · Answer 1 · answered Feb 28 '21 at 12:39

0

It is not the spring boot here that is responsible for the bulk insert, it is hiberate (or the jpa provider you are using) and it just happens so that it runs within a spring but application.

Spring boot gives you the means and the configuration keys to configure hibernate, but essentialy it is hibernate you are configuring.

What would make the hibernate batch the inserts is:

    spring.jpa.properties.hibernate.jdbc.batch_size=5
    spring.jpa.properties.hibernate.order_inserts=true

for updates:

spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true

answered Feb 28 '21 at 12:39

Alexander Petrov

9,204
31
70

Alexander, thanks for your answer. But spring.jpa.properties.hibernate.order_inserts=true; is meaningful for multi-table insert and it's not meaningful for this question. spring.jpa.properties.hibernate.jdbc.batch_size=5; property has already implemented. – Sha Feb 28 '21 at 13:05
@Sha now I re-read your post and so you have actualy tried the spring.jpa.properties.hibernate.jdbc.batch_size . Can you try wrapping the method in a transaction. The crud methods already should have active transaction, but if it is not batching than it means something is making it to flush on each save. Try wrapping it in transaction just for the test. – Alexander Petrov Feb 28 '21 at 13:13
1

Oh it has been long time ago and I am starting to forget. Your batching is working, the fact that it is logging multiple statements in the log does not mean it is not doing the batching. Not every database supports SQL with one insert and many values. – Alexander Petrov Feb 28 '21 at 13:18
The best way to test that the batching is working is to flush in a middle of batch insert this way trigering all insert statements so far to be transfered to the server. – Alexander Petrov Feb 28 '21 at 13:19
2

Let me try to explain. If you throw a native query towards the database before the entity manager has flushed the query will not return a result. If it has flushed your native query will return a result. – Alexander Petrov Feb 28 '21 at 13:26

Bulk Insert with H2 Database on Spring Boot

1 Answers1