Spring的JDBC操作类
Spring 的 JDBC 操作类
在 Spring 中的 JDBC 操作主要集中在 spring-jdbc 包中，此包一共分为四个部分：
- core,JdbcTemplate 等相关核心接口和类
- datasource， 数据源相关的辅助类
- object, 将基本的 JDBC 操作封装成对象
- support， 错误码等其他辅助工具
JdbcTemplate 提供的功能
- query
- queryForObject
- queryForList
- update （插入、修改、删除）
- execute （通用）
SQL 批处理
- JdbcTemplate.batchUpdate() : BatchPreparedStatementSetter
- NamedParameterJdbcTemplate.batchUpdate() : SqlParameterSourceUtils.createBatch
引入依赖
1<?xml version="1.0" encoding="UTF-8"?>
2<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4 <modelVersion>4.0.0</modelVersion>
5 <parent>
6 <groupId>org.springframework.boot</groupId>
7 <artifactId>spring-boot-starter-parent</artifactId>
8 <version>2.1.2.RELEASE</version>
9 <relativePath/> <!-- lookup parent from repository -->
10 </parent>
11 <groupId>geektime.spring.data</groupId>
12 <artifactId>simple-jdbc-demo</artifactId>
13 <version>0.0.1-SNAPSHOT</version>
14 <name>simple-jdbc-demo</name>
15 <description>Demo project for Spring Boot</description>
16
17 <properties>
18 <java.version>1.8</java.version>
19 </properties>
20
21 <dependencies>
22 <dependency>
23 <groupId>org.springframework.boot</groupId>
24 <artifactId>spring-boot-starter-jdbc</artifactId>
25 </dependency>
26
27 <dependency>
28 <groupId>com.h2database</groupId>
29 <artifactId>h2</artifactId>
30 <scope>runtime</scope>
31 </dependency>
32 <dependency>
33 <groupId>org.projectlombok</groupId>
34 <artifactId>lombok</artifactId>
35 <optional>true</optional>
36 </dependency>
37 <dependency>
38 <groupId>org.springframework.boot</groupId>
39 <artifactId>spring-boot-starter-test</artifactId>
40 <scope>test</scope>
41 </dependency>
42 </dependencies>
43
44 <build>
45 <plugins>
46 <plugin>
47 <groupId>org.springframework.boot</groupId>
48 <artifactId>spring-boot-maven-plugin</artifactId>
49 </plugin>
50 </plugins>
51 </build>
52</project>
application.properties
1spring.output.ansi.enabled=ALWAYS
data.sql
1INSERT INTO FOO (BAR) VALUES ('aaa');
schema.sql
1CREATE TABLE FOO (ID INT IDENTITY, BAR VARCHAR(64));
Foo
1package geektime.spring.data.simplejdbcdemo;
2
3import lombok.Builder;
4import lombok.Data;
5
6@Data
7@Builder
8public class Foo {
9 private Long id;
10 private String bar;
11}
FooDao
1package geektime.spring.data.simplejdbcdemo;
2
3import lombok.extern.slf4j.Slf4j;
4import org.springframework.beans.factory.annotation.Autowired;
5import org.springframework.jdbc.core.JdbcTemplate;
6import org.springframework.jdbc.core.RowMapper;
7import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
8import org.springframework.stereotype.Repository;
9
10import java.sql.ResultSet;
11import java.sql.SQLException;
12import java.util.Arrays;
13import java.util.HashMap;
14import java.util.List;
15
16@Slf4j
17@Repository
18public class FooDao {
19 @Autowired
20 private JdbcTemplate jdbcTemplate;
21 @Autowired
22 private SimpleJdbcInsert simpleJdbcInsert;
23
24 /**
25 * 插入
26 * JdbcTemplate
27 * SimpleJdbcInsert
28 */
29 public void insertData() {
30 //使用jdbcTemplate.update进行插入操作
31 Arrays.asList("b", "c").forEach(bar -> {
32 jdbcTemplate.update("INSERT INTO FOO (BAR) VALUES (?)", bar);
33 });
34
35 //使用SimpleJdbcInsert进行插入操作
36 HashMap<String, String> row = new HashMap<>();
37 row.put("BAR", "d");
38 Number id = simpleJdbcInsert.executeAndReturnKey(row);
39 log.info("ID of d: {}", id.longValue());
40 }
41
42 /**
43 * 查询
44 * jdbcTemplate.queryForObject
45 * jdbcTemplate.queryForList
46 *
47 */
48 public void listData() {
49 //JdbcTemplate.queryForObject : 获取单条记录
50 log.info("Count: {}",
51 jdbcTemplate.queryForObject("SELECT COUNT(*) FROM FOO", Long.class));
52
53 //JdbcTemplate.queryForList : 获取多条记录，保存在List中
54 List<String> list = jdbcTemplate.queryForList("SELECT BAR FROM FOO", String.class);
55 list.forEach(s -> log.info("Bar: {}", s));
56
57 ////jdbcTemplate.query : 获取多条记录，并且映射成对象
58 List<Foo> fooList = jdbcTemplate.query("SELECT * FROM FOO", new RowMapper<Foo>() {
59 @Override
60 public Foo mapRow(ResultSet rs, int rowNum) throws SQLException {
61 return Foo.builder()
62 .id(rs.getLong(1))
63 .bar(rs.getString(2))
64 .build();
65 }
66 });
67 fooList.forEach(f -> log.info("Foo: {}", f));
68 }
69}
BatchFooDao（SQL 批处理）
1package geektime.spring.data.simplejdbcdemo;
2
3import org.springframework.beans.factory.annotation.Autowired;
4import org.springframework.jdbc.core.BatchPreparedStatementSetter;
5import org.springframework.jdbc.core.JdbcTemplate;
6import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
7import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
8import org.springframework.stereotype.Repository;
9
10import java.sql.PreparedStatement;
11import java.sql.SQLException;
12import java.util.ArrayList;
13import java.util.List;
14
15@Repository
16public class BatchFooDao {
17 @Autowired
18 private JdbcTemplate jdbcTemplate;
19 @Autowired
20 private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
21
22 public void batchInsert(List<Foo> fooList) {
23 //jdbcTemplate.batchUpdate()
24 //BatchPreparedStatementSetter 设置参数
25 jdbcTemplate.batchUpdate("INSERT INTO FOO (BAR) VALUES (?)",
26 new BatchPreparedStatementSetter() {
27 @Override
28 public void setValues(PreparedStatement ps, int i) throws SQLException {
29 Foo foo = fooList.get(i);
30 ps.setString(1, foo.getBar());
31 }
32
33 @Override
34 public int getBatchSize() {
35 return fooList.size();
36 }
37 });
38
39 // namedParameterJdbcTemplate.batchUpdate()
40 // SqlParameterSourceUtils 设置参数
41 List<Foo> list = new ArrayList<>();
42 list.add(Foo.builder().id(100L).bar("b-100").build());
43 list.add(Foo.builder().id(101L).bar("b-101").build());
44 namedParameterJdbcTemplate
45 .batchUpdate("INSERT INTO FOO (ID, BAR) VALUES (:id, :bar)",
46 SqlParameterSourceUtils.createBatch(list));
47 }
48}
SimpleJdbcDemoApplication 启动类
1package geektime.spring.data.simplejdbcdemo;
2
3import lombok.extern.slf4j.Slf4j;
4import org.springframework.beans.factory.annotation.Autowired;
5import org.springframework.boot.CommandLineRunner;
6import org.springframework.boot.SpringApplication;
7import org.springframework.boot.autoconfigure.SpringBootApplication;
8import org.springframework.context.annotation.Bean;
9import org.springframework.jdbc.core.JdbcTemplate;
10import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
11import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
12
13import javax.sql.DataSource;
14import java.util.ArrayList;
15import java.util.List;
16
17@SpringBootApplication
18@Slf4j
19public class SimpleJdbcDemoApplication implements CommandLineRunner {
20 @Autowired
21 private FooDao fooDao;
22 @Autowired
23 private BatchFooDao batchFooDao;
24
25 public static void main(String[] args) {
26 SpringApplication.run(SimpleJdbcDemoApplication.class, args);
27 }
28
29 /**
30 * 生成 SimpleJdbcInsert并且作用于 FOO 表, 并设置 ID 列为自增。
31 * @param jdbcTemplate
32 * @return
33 */
34 @Bean
35 @Autowired
36 public SimpleJdbcInsert simpleJdbcInsert(JdbcTemplate jdbcTemplate) {
37 return new SimpleJdbcInsert(jdbcTemplate)
38 .withTableName("FOO").usingGeneratedKeyColumns("ID");
39 }
40
41 /**
42 * 生成 NamedParameterJdbcTemplate
43 * @param dataSource
44 * @return
45 */
46 @Bean
47 @Autowired
48 public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
49 return new NamedParameterJdbcTemplate(dataSource);
50 }
51
52 @Override
53 public void run(String... args) throws Exception {
54 List<Foo> list = new ArrayList<>();
55 list.add(Foo.builder().id(1L).bar("b-0").build());
56 list.add(Foo.builder().id(2L).bar("b-1").build());
57
58 //fooDao.insertData();
59 batchFooDao.batchInsert(list);
60 fooDao.listData();
61 }
62}
控制台输出
12020-01-07 17:39:46.119 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Count: 5
22020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: aaa
32020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-0
42020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-1
52020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-100
62020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-101
72020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=1, bar=aaa)
82020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=2, bar=b-0)
92020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=3, bar=b-1)
102020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=100, bar=b-100)
112020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=101, bar=b-101)
122020-01-07 17:39:46.123 INFO 12296 --- [ Thread-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
132020-01-07 17:39:46.126 INFO 12296 --- [ Thread-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
14
15Process finished with exit code 0