This article will teach you how to get auto-incrementing IDs when using JdbcTemplate or NamedParameterJdbcTemplate.
Table (MYSQL)
Suppose the following data table is available.
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`balance` decimal(10,2) DEFAULT NULL,
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`enabled` tinyint unsigned NOT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`update_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='user';
|
As you can see, the id field is an auto-incrementing column.
JdbcTemplate
spring-jdbc provides the GeneratedKeyHolder object to get the auto-incremented ID value after data insertion.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
package io.springcloud.test;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.time.LocalDateTime;
import javax.sql.DataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import io.springboot.demo.DemoApplication;
import lombok.extern.slf4j.Slf4j;
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class, webEnvironment = WebEnvironment.RANDOM_PORT)
@Slf4j
public class DemoApplicationTest {
@Autowired
DataSource dataSource;
@Test
@Transactional
@Rollback(false)
public void test() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
// Create GeneratedKeyHolder object
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
String sql = "INSERT INTO `user`(`balance`, `create_at`, `enabled`, `name`, `update_at`) VALUES(?, ?, ?, ?, ?);";
// To insert data, you need to pre-compile the SQL and set up the data yourself.
int rowsAffected = jdbcTemplate.update(conn -> {
// Pre-compiling SQL
PreparedStatement preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// Set parameters
preparedStatement.setBigDecimal(1, new BigDecimal("15.88"));
preparedStatement.setObject(2, LocalDateTime.now());
preparedStatement.setBoolean(3, Boolean.TRUE);
preparedStatement.setString(4, "JdbcTemplate");
preparedStatement.setObject(5, LocalDateTime.now());
return preparedStatement;
}, generatedKeyHolder);
// Get auto-incremented ID
Integer id = generatedKeyHolder.getKey().intValue();
log.info("rowsAffected = {}, id={}", rowsAffected, id);
}
}
|
The output log is as follows, everything is OK.
1
|
2022-06-06 17:03:11.240 INFO 8964 --- [ main] io.springcloud.test.DemoApplicationTest : rowsAffected = 1, id=11
|
NamedParameterJdbcTemplate
The usage of NamedParameterJdbcTemplate is not much different from JdbcTemplate. But it supports using named parameters in SQL instead of ?, by this feature we can directly use objects or Map as parameters. Very friendly. In actual development, it is more recommended to use it.
The NamedParameterJdbcTemplate also provides more rich methods, you can refer to the documentation to learn more.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import io.springboot.demo.DemoApplication;
import lombok.extern.slf4j.Slf4j;
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class, webEnvironment = WebEnvironment.RANDOM_PORT)
@Slf4j
public class DemoApplicationTest {
@Autowired
DataSource dataSource;
@Test
@Transactional
@Rollback(false)
public void test() {
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
// The GeneratedKeyHolder object is used to get the auto-incrementing ID.
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
// SQL placeholders can use named parameters instead of "?".
String sql = "INSERT INTO `user`(`balance`, `create_at`, `enabled`, `name`, `update_at`) VALUES(:balance, :create_at, :enabled, :name, :update_at);";
// params
Map<String, Object> params = new HashMap<>();
params.put("balance", new BigDecimal("25.66"));
params.put("create_at", LocalDateTime.now());
params.put("enabled", Boolean.FALSE);
params.put("name", "NamedParameterJdbcTemplate");
params.put("update_at", LocalDateTime.now());
int rowsAffected = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(params), generatedKeyHolder);
Integer id = generatedKeyHolder.getKey().intValue();
log.info("rowsAffected = {}, id={}", rowsAffected, id);
}
}
|
The output log is as follows, everything is OK.
1
|
2022-06-06 17:10:19.167 INFO 12408 --- [ main] io.springcloud.test.DemoApplicationTest : rowsAffected = 1, id=12
|
Summary
Get the auto-incremented ID through the GeneratedKeyHolder object.
If you use JdbcTemplate, then you need to pre-compile the SQL and set the parameters yourself, which is more troublesome. It is more recommended to use NamedParameterJdbcTemplate.
GeneratedKeyHolder has some other methods, if you want to know more, you can refer to its documentation.
Finally, take a look at the 2 data we inserted.
1
2
3
4
5
6
7
8
|
mysql> select * from `user`;
+----+---------+---------------------+---------+----------------------------+---------------------+
| id | balance | create_at | enabled | name | update_at |
+----+---------+---------------------+---------+----------------------------+---------------------+
| 11 | 15.88 | 2022-06-06 17:03:11 | 1 | JdbcTemplate | 2022-06-06 17:03:11 |
| 12 | 25.66 | 2022-06-06 17:10:19 | 0 | NamedParameterJdbcTemplate | 2022-06-06 17:10:19 |
+----+---------+---------------------+---------+----------------------------+---------------------+
2 rows in set (0.00 sec)
|