Bulk/Batch insert using query DSL-SQL

According to my previous blog — Bulk/Batch insert using Spring Data JPA and Mysql database, we can understand that batch insert is not possible with JPA using Mysql as database. we have to either go with JdbcTemplate or query DSL-SQL

In this blog, let's try to implement query DSL-SQL

cover pic

Maven dependencies to be added

maven dependencies for query DSL-SQL

Code generation

we have to generate the class files that act as an abstraction of our database schema. There are two ways to generate code: The maven plugin or through code configurations.

code generation using maven

code generation using configuration

Let’s create an entity class tweets where we can store tweets

Entity class — Tweet.java

If you are using the maven plugin -> run mvn clean install. For
code, start the server. Now you can see the Qclass files were generated above the database schema you have.

the generated class file above the schema

Now let's get into the war zone. Let's create a service class that inserts a batch of records into the tweet table using query DSL-SQL. To do that follow the simple code below

Now if you run the above method, you will notice that the entire data gets inserted into the database using a single query as shown below.

log while running batch through query DSL-SQL

To see the below log you have to add the maven dependency — datasource-proxy-spring-boot-starter with properties — logging.level.net.ttddyy.dsproxy.listener=debug. This helps you to see the queries that are actually performed on our database.

Advantages of using query DSL-SQL

1. Type-safe code
2. Dynamic queries
3. Lesser time for code to reach production

Conclusion

we can able to batch insert the records using query DSL-SQL maven dependency on MySQL database. this is cool, right?

For GitHub code click here.