Spring Jdbc Batch Insert Generated Keys
So you will need to check if your driver actually supports it for batch updates. As indicated in the answer by Philip O., retrieval of generated keys is not supported with batch updates as documented in Oracle 12 JDBC Standards Support: You cannot combine auto-generated keys with batch update. I realize that generated keys are not returned in a batchUpdate even if multiple insert statements are executed for a table with an autoincrement column. Currently in JDBCTemplate the only way to get a generated key is to execute statements one at a time using the following method signature. Therefore, we'll use the JDBCTemplate update method which supports the retrieval of primary keys generated by the database. This method takes an instance of the PrepareStatementCreator interface as the first argument and the other argument is the KeyHolder.
Introduction
In this post we will see an example on batch insertion using Spring JdbcTemplate. We had seen the similar example using XML configuration previously but here we will create annotation based application. So we will see how we can insert a large data-set into a database at once using Spring JdbcTemplate.
Sometimes we need to insert or update large number of records in the database. It’s not a good idea to insert multiple records into database one by one in a traditional approach. It will hit the application’s performance. Spring provides batch operations with the help of JdbcTemplate, it inserts or updates records into database in one shot.
Spring Jdbc Batch Insert Generated Keys 2017
You may also like to read Batch insert using Spring Data JPA.
Prerequisites
Eclipse Neon, Java 1.8, Spring 5.1.8, Gradle 5.4.1, MySQL 8.0.17
Creating Project
Create a gradle based project in Eclipse. The project name is spring-jdbctemplate-batch-insertion.
Updating Build Script
The build.gradle file generated by Eclipse needs to be updated to include the required dependencies for our application.
The content of the build script is given below:
Configuring Database Properties
Create a file database.properties with the below content under src/main/resources folder to configure database properties for creating datasource.
Creating Table in MySQL
We are going to insert data into MySQL table, so we need to create a table in MySQL server under database roytuts.
Generate key for any software. Create a table called cd with below structure:
Creating DataSource
We need to create datasource in order to communicate with database and perform operations into database.
The below class creates datasource and JdbcTemplate beans so that we can use these two beans throughout the application wherever required.
Creating Model Class
We are using Java application to insert data. So we will map our database table with Java class attributes.
Therefore create a below model class – cd.
Creating DAO Class
We generally perform database operations in DAO layer.
The below DAO class defines a method for inserting records in batch. Ideally batch size should be more in real application.
Spring Jdbc Batch Insert
Creating Main Class
Spring Jdbc Batch Insert Generated Keys In Windows 10
We will create a class that has main method to test our application.
Generally DAO layer is injected into service layer but for our simple example we will inject into main class to test the application.
Testing the Application
Now if you run the above main class. You will see the below output in the console:
You will see that all records got inserted into the cd table in MySQL server under database roytuts.
The output is shown into the below image:
Source Code
Thanks for reading.
Tags: