Spring 将数据访问的样板代码抽象到模板类之中，我们可以直接使用模板类，从而简化了JDBC代码。
引入下面依赖，自动引入连接池 HikariCP 及 spring-jdbc
1
2
3
4
5
6
|
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.7.4</version>
</dependency>
|
JDBC 访问数据库方式
- JdbcTemplate是经典且最流行的 Spring JDBC 方法。这种“最低级别”的方法和所有其他方法都在幕后使用 JdbcTemplate。
- NamedParameterJdbcTemplate 包装 JdbcTemplate 以提供命名参数而不是传统的 JDBC ?占位符。当 SQL 语句有多个参数时，这种方法提供了更好的文档和易用性。
- SimpleJdbcInsert 及 SimpleJdbcCall 优化数据库元数据以限制必要配置的数量。这种方法简化了编码，因此您只需提供表或过程的名称，并提供与列名匹配的参数映射。这仅在数据库提供足够的元数据时才有效。如果数据库不提供此元数据，则必须提供参数的显式配置。
- RDBMS 对象（包括MappingSqlQuery、SqlUpdate和StoredProcedure）要求您在初始化数据访问层期间创建可重用和线程安全的对象。这种方法是在 JDO Query 之后建模的，您可以在其中定义查询字符串、声明参数并编译查询。一旦你这样做了， 就可以使用各种参数值多次调用execute(…)、update(…)和方法。findObject(…)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
server:
port: 8100
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?rewriteBatchedStatements=true
username: root
password: wang0804
hikari:
data-source-properties:
useConfigs: maxPerformance
rewriteBatchedStatements: true
jdbc:
template:
max-rows: 1000
sql:
init:
schema-locations: classpath*:schema-all.sql
username: root
password: wang0804
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
CREATE TABLE `user`
(
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
`balance` decimal(12, 2) NOT NULL DEFAULT '0.00' COMMENT '余额',
`sex` tinyint DEFAULT '-1' COMMENT '性别',
`phone` bigint DEFAULT NULL COMMENT '手机',
`province` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册地址：省',
`city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册地址：城市',
`country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册地址：县/区',
`status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否启用 0：否 1：是',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
实践
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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
@Repository
public class NamedParameterUserDao implements UserRepository {
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public NamedParameterUserDao(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
@Override
public int count() {
String sql = "select count(*) from user where sex = :sex";
Map<String, Integer> namedParameters =
Collections.singletonMap("sex", Gender.FEMALE.getValue());
return Optional.ofNullable(
this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class))
.orElse(0);
}
@Override
public int update(String sql, User user) {
// "update user set balance = :balance where id = :id"
return namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(user));
}
@Override
public int deleteById(Long id) {
SqlParameterSource namedParameters = new MapSqlParameterSource("id", id);
return namedParameterJdbcTemplate.update("delete from user where id = :id", namedParameters);
}
@Override
public List<User> findAll() {
return namedParameterJdbcTemplate.query("select * from user", USER_ROW_MAPPER);
}
@Override
public List<User> findByName(String name) {
MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
mapSqlParameterSource.addValue("name", "%" + name + "%");
return namedParameterJdbcTemplate.query(
"select * from user where name like :name", mapSqlParameterSource, USER_ROW_MAPPER);
}
@Override
public Optional<User> findById(Integer id) {
return namedParameterJdbcTemplate.queryForObject(
"select * from user where id = :id",
new MapSqlParameterSource("id", id),
(rs, rowNum) -> Optional.of(USER_ROW_MAPPER.mapRow(rs, rowNum)));
}
@Override
public String getNameById(Integer id) {
return namedParameterJdbcTemplate.queryForObject(
"select name from user where id = :id", new MapSqlParameterSource("id", id), String.class);
}
@Override
public int[] batchUpdate(String sql, List<User> users) {
// SqlParameterSourceUtils.createBatch(users)
return this.namedParameterJdbcTemplate.batchUpdate(
sql,
users.stream()
.map(
i ->
new BeanPropertySqlParameterSource(i) {
@Override
public Object getValue(String paramName) throws IllegalArgumentException {
Object value = super.getValue(paramName);
if (value instanceof Gender gender) {
return gender.getValue();
}
return value;
}
})
.toArray(SqlParameterSource[]::new));
}
@Override
public int[][] sectionBatchUpdate(String sql, Collection<User> users) {
final var sublist = new AtomicInteger();
var result = new ArrayList<int[]>();
var futures =
users.stream()
.collect(Collectors.groupingBy(t -> sublist.getAndIncrement() / Constant.BATCH_SIZE))
.values()
.stream()
.map(
sectionList ->
CompletableFuture.supplyAsync(() -> batchUpdate(sql, sectionList))
.whenComplete((r, e) -> result.add(r)))
.toArray(CompletableFuture[]::new);
CompletableFuture.allOf(futures).join();
return result.toArray(new int[0][]);
}
}
|
批量插入及更新
?rewriteBatchedStatements=true
useServerPrepStmts在默认情况下已经为false，即使将其更改为true也不会对批处理插入性能产生太大影响。
可以通过切换mysql日志记录(通过SET global general_log = 1)来观察它，该日志记录会将发送到mysql服务器的每个语句登录到一个文件中。
如果您确实需要速度，请使用LOAD DATA INFILE从一个用逗号分隔的文件中加载数据
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
|
public int[] batchUpdateUsingJdbcTemplate(List<Employee> employees) {
return jdbcTemplate.batchUpdate("INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1, employees.get(i).getId());
ps.setString(2, employees.get(i).getFirstName());
ps.setString(3, employees.get(i).getLastName());
ps.setString(4, employees.get(i).getAddress();
}
@Override
public int getBatchSize() {
return 50;
}
});
}
// If the batch is too big, we can split it by a smaller batch size.
public int[][] batchInsert(List<Book> books, int batchSize) {
int[][] updateCounts = jdbcTemplate.batchUpdate(
"insert into books (name, price) values(?,?)",
books,
batchSize,
new ParameterizedPreparedStatementSetter<Book>() {
public void setValues(PreparedStatement ps, Book argument) throws SQLException {
ps.setString(1, argument.getName());
ps.setBigDecimal(2, argument.getPrice());
}
});
return updateCounts;
}
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(employees.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
"INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName, :address)", batch);
return updateCounts;
|
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
|
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate namedJdbcTemplate;
@Value("${jdbc.batch_insert_size}")
private int batchSize;
private static final ExecutorService executor = Executors.newFixedThreadPool(10);
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void batchInsertAsync(List<User> users) throws InterruptedException, ExecutionException {
StopWatch timer = new StopWatch();
String sql = "INSERT INTO `USER` (USERNAME, PASSWORD, CREATEDTIME, UPDATEDTIME, USERTYPE, DATEOFBIRTH)"
+ " VALUES(?,?,?,?,?,?)";
final AtomicInteger sublists = new AtomicInteger();
CompletableFuture[] futures = users.stream()
.collect(Collectors.groupingBy(t -> sublists.getAndIncrement() / batchSize))
.values()
.stream()
.map(ul -> runBatchInsert(ul, sql))
.toArray(CompletableFuture[]::new);
CompletableFuture<Void> run = CompletableFuture.allOf(futures);
timer.start();
run.get();
timer.stop();
log.info("batchInsertAsync -> Total time in seconds: " + timer.getTotalTimeSeconds());
}
public CompletableFuture<Void> runBatchInsert(List<User> users, String sql) {
return CompletableFuture.runAsync(() -> {
jdbcTemplate.batchUpdate(sql, new UserBatchPreparedStatementSetter(users));
}, executor);
}
|
流式查询
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
|
jdbcTemplate.query(
con -> {
PreparedStatement preparedStatement =
con.prepareStatement(
"select * from user", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
preparedStatement.setFetchSize(Integer.MIN_VALUE);
preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
return preparedStatement;
},
rs -> {
while (rs.next()) {
System.out.println(USER_ROW_MAPPER.mapRow(rs, rs.getRow()));
}
});
Stream<User> userStream =
jdbcTemplate.queryForStream(
conn -> {
var ps =
conn.prepareStatement(
"SELECT * FROM user",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_FORWARD);
return ps;
},
USER_ROW_MAPPER);
RowMapper<User> USER_ROW_MAPPER =
(rs, rowNum) ->
new User()
.setId(rs.getInt("id"))
.setAge(rs.getInt("age"))
.setName(rs.getString("name"))
.setBalance(rs.getBigDecimal("balance"))
.setSex(Gender.getByValue(rs.getInt("sex")))
.setStatus(
EnumUtil.getBy(
Status.class,
i -> {
try {
return i.ordinal() == rs.getInt("status");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}))
.setCreateTime(rs.getObject("create_time", OffsetDateTime.class))
.setUpdateTime(rs.getObject("update_time", OffsetDateTime.class));
|
枚举
1
2
3
4
5
6
7
8
9
10
|
new BeanPropertySqlParameterSource(i) {
@Override
public Object getValue(String paramName) throws IllegalArgumentException {
Object value = super.getValue(paramName);
if (value instanceof Gender gender) {
return gender.getValue();
}
return value;
}
}
|
单元测试 @JdbcTest
- 在不使用 Spring Data JDBC 并且只需要 DataSource 的测试中使用
- @JdbcTest 创建一个 JdbcTemplate 用于测试内存嵌入式数据库设置
- 不扫描普通的 @ConfigurationProperties 和 @Component bean。
- 默认情况下，JDBC 为每个测试测试回滚
- 如果想使用实际的数据库，请使用 @AutoConfigureTestDatabase
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
@JdbcTest
@Sql({"schema.sql", "test-data.sql"})
@Transactional(propagation = Propagation.NOT_SUPPORTED)
class MyTransactionalTests {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void whenInjectInMemoryDataSource_thenReturnCorrectEmployeeCount() {
EmployeeDAO employeeDAO = new EmployeeDAO();
employeeDAO.setJdbcTemplate(jdbcTemplate);
assertEquals(4, employeeDAO.getCountOfEmployees());
}
}
|
附录