Spring JDBC simplifies database operations by providing an abstraction layer over traditional JDBC. In this blog post, we’ll walk through a practical example of using Spring JDBC to insert a record into a database and obtain the generated key for the newly inserted record.
First, let’s look at the complete code snippet:
package org.kodejava.spring.jdbc;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import java.math.BigInteger;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Month;
public class InsertDemoObtainsKey {
public static void main(String[] args) {
String insert = "INSERT INTO record (title, release_date, artist_id, label_id, created) VALUES (?, ?, ?, ?, ?)";
JdbcTemplate template = new JdbcTemplate(new DriverManagerDataSource("jdbc:mysql://localhost/musicdb", "root", ""));
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(con -> {
PreparedStatement ps = con.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1, "Let It Be");
ps.setDate(2, Date.valueOf(LocalDate.of(1970, Month.MAY, 8)));
ps.setLong(3, 1L);
ps.setLong(4, 1L);
ps.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
return ps;
}, keyHolder);
BigInteger key = (BigInteger) keyHolder.getKeys();
System.out.println("key = " + key);
}
}
This code demonstrates how to insert a record into a database table and retrieve the generated key using Spring’s JdbcTemplate.
DataSource Configuration
JdbcTemplate template = new JdbcTemplate(new DriverManagerDataSource("jdbc:mysql://localhost/musicdb", "root", ""));
Here, we create a JdbcTemplate instance, providing it with a DriverManagerDataSource. This data source points to our MySQL database named musicdb, with the username root and an empty password.
SQL Insert Statement
String insert = "INSERT INTO record (title, release_date, artist_id, label_id, created) VALUES (?, ?, ?, ?, ?)";
We define our SQL insert statement with placeholders for the values to be inserted. The table record is assumed to have columns title, release_date, artist_id, label_id, and created.
KeyHolder Initialization
KeyHolder keyHolder = new GeneratedKeyHolder();
A KeyHolder is used to hold the generated keys. In this case, we’re using a GeneratedKeyHolder.
Executing the Update
template.update(con -> {
PreparedStatement ps = con.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1, "Let It Be");
ps.setDate(2, Date.valueOf(LocalDate.of(1970, Month.MAY, 8)));
ps.setLong(3, 1L);
ps.setLong(4, 1L);
ps.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
return ps;
}, keyHolder);
We use the JdbcTemplate‘s update method, which takes two parameters: a PreparedStatementCreator and a KeyHolder. The PreparedStatementCreator is implemented using a lambda expression that sets the values for the placeholders in our SQL insert statement.
Retrieving the Generated Key
BigInteger key = (BigInteger) keyHolder.getKeys();
System.out.println("key = " + key);
After the update operation, we retrieve the generated key from the KeyHolder. The key is then printed to the console.
Summary
Using Spring JDBC simplifies the process of interacting with the database. In this example, we demonstrated how to insert a record into a MySQL database and retrieve the generated key using JdbcTemplate. This approach ensures clean and manageable code while leveraging the power of Spring’s abstraction over traditional JDBC.
Feel free to experiment with this code, adjusting the database connection details and SQL statements to fit your requirements. Happy coding!
Maven Dependencies
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>6.1.10</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.4.0</version>
</dependency>
</dependencies>
