Spring Jdbc Batch Insert Generated Keys
Spring JDBC FAQ: How do I retrieve the auto-generated database key for a serial field (also known as an autoincrement field in MySQL) after I perform a SQL INSERT using Spring JDBC? (I'm not phrasing that well, but by this question I mean the value of the primary key for the record I just inserted. In Spring we can persist and load large objects by using the JdbcTemplate. These large objects are called BLOBs (Binary Large Object) for binary data and CLOBs (Character Large Object) for character data. 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. For a programmer getting auto generated id of a newly inserted row in table is a little bit tricky. In this page we will learn how the spring provides an easy way to get that. Spring provides KeyHolder which helps to get auto generated key. KeyHolder is supported by JDBC 3.0. Execute the insert using the values passed in and return the generated key. This requires that the name of the columns with auto generated keys have been specified. This method will always return a KeyHolder but the caller must verify that it actually contains the generated keys. Is there a way to get all the generated keys of a batch insert? Is there a way to get all the generated keys of a batch insert? Batch insert generated keys #487. Closed bardam opened this issue Jan 27, 2016 7 comments. It's not possible for now because JDBC Batch API returns only an array of update count.
The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. The main value of this feature is to provide a way to make IDENTITY values available to an application that is updating a database table without a requiring a query and a second round-trip to the server.
Because SQL Server doesn't support pseudo columns for identifiers, updates that have to use the auto-generated key feature must operate against a table that contains an IDENTITY column. SQL Server allows only a single IDENTITY column per table. The result set that is returned by getGeneratedKeys method of the SQLServerStatement class will have only one column, with the returned column name of GENERATED_KEYS. If generated keys are requested on a table that has no IDENTITY column, the JDBC driver will return a null result set.
As an example, create the following table in the sample database:
In the following example, an open connection to the sample database is passed in to the function, an SQL statement is constructed that will add data to the table, and then the statement is run and the IDENTITY column value is displayed.
See also
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.
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
/ms-office-2013-product-key-generator-online.html. We are going to insert data into MySQL table, so we need to create a table in MySQL server under database roytuts.
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.
Creating Main Class
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:Batch • JdbcTemplate • Spring JdbcTemplate