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
引入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>geektime.spring.data</groupId>
<artifactId>simple-jdbc-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>simple-jdbc-demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.properties
spring.output.ansi.enabled=ALWAYS
data.sql
INSERT INTO FOO (BAR) VALUES ('aaa');
schema.sql
CREATE TABLE FOO (ID INT IDENTITY, BAR VARCHAR(64));
Foo
package geektime.spring.data.simplejdbcdemo;
import lombok.Builder;
import lombok.Data;
@Data
@Builder
public class Foo {
private Long id;
private String bar;
}
FooDao
package geektime.spring.data.simplejdbcdemo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
@Slf4j
@Repository
public class FooDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private SimpleJdbcInsert simpleJdbcInsert;
/**
* 插入
* JdbcTemplate
* SimpleJdbcInsert
*/
public void insertData() {
//使用jdbcTemplate.update进行插入操作
Arrays.asList("b", "c").forEach(bar -> {
jdbcTemplate.update("INSERT INTO FOO (BAR) VALUES (?)", bar);
});
//使用SimpleJdbcInsert进行插入操作
HashMap<String, String> row = new HashMap<>();
row.put("BAR", "d");
Number id = simpleJdbcInsert.executeAndReturnKey(row);
log.info("ID of d: {}", id.longValue());
}
/**
* 查询
* jdbcTemplate.queryForObject
* jdbcTemplate.queryForList
*
*/
public void listData() {
//JdbcTemplate.queryForObject : 获取单条记录
log.info("Count: {}",
jdbcTemplate.queryForObject("SELECT COUNT(*) FROM FOO", Long.class));
//JdbcTemplate.queryForList : 获取多条记录，保存在List中
List<String> list = jdbcTemplate.queryForList("SELECT BAR FROM FOO", String.class);
list.forEach(s -> log.info("Bar: {}", s));
////jdbcTemplate.query : 获取多条记录，并且映射成对象
List<Foo> fooList = jdbcTemplate.query("SELECT * FROM FOO", new RowMapper<Foo>() {
@Override
public Foo mapRow(ResultSet rs, int rowNum) throws SQLException {
return Foo.builder()
.id(rs.getLong(1))
.bar(rs.getString(2))
.build();
}
});
fooList.forEach(f -> log.info("Foo: {}", f));
}
}
BatchFooDao（SQL 批处理）
package geektime.spring.data.simplejdbcdemo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Repository
public class BatchFooDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void batchInsert(List<Foo> fooList) {
//jdbcTemplate.batchUpdate()
//BatchPreparedStatementSetter 设置参数
jdbcTemplate.batchUpdate("INSERT INTO FOO (BAR) VALUES (?)",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Foo foo = fooList.get(i);
ps.setString(1, foo.getBar());
}
@Override
public int getBatchSize() {
return fooList.size();
}
});
// namedParameterJdbcTemplate.batchUpdate()
// SqlParameterSourceUtils 设置参数
List<Foo> list = new ArrayList<>();
list.add(Foo.builder().id(100L).bar("b-100").build());
list.add(Foo.builder().id(101L).bar("b-101").build());
namedParameterJdbcTemplate
.batchUpdate("INSERT INTO FOO (ID, BAR) VALUES (:id, :bar)",
SqlParameterSourceUtils.createBatch(list));
}
}
SimpleJdbcDemoApplication 启动类
package geektime.spring.data.simplejdbcdemo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
@SpringBootApplication
@Slf4j
public class SimpleJdbcDemoApplication implements CommandLineRunner {
@Autowired
private FooDao fooDao;
@Autowired
private BatchFooDao batchFooDao;
public static void main(String[] args) {
SpringApplication.run(SimpleJdbcDemoApplication.class, args);
}
/**
* 生成 SimpleJdbcInsert并且作用于 FOO 表, 并设置 ID 列为自增。
* @param jdbcTemplate
* @return
*/
@Bean
@Autowired
public SimpleJdbcInsert simpleJdbcInsert(JdbcTemplate jdbcTemplate) {
return new SimpleJdbcInsert(jdbcTemplate)
.withTableName("FOO").usingGeneratedKeyColumns("ID");
}
/**
* 生成 NamedParameterJdbcTemplate
* @param dataSource
* @return
*/
@Bean
@Autowired
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
@Override
public void run(String... args) throws Exception {
List<Foo> list = new ArrayList<>();
list.add(Foo.builder().id(1L).bar("b-0").build());
list.add(Foo.builder().id(2L).bar("b-1").build());
//fooDao.insertData();
batchFooDao.batchInsert(list);
fooDao.listData();
}
}
控制台输出
2020-01-07 17:39:46.119 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Count: 5
2020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: aaa
2020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-0
2020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-1
2020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-100
2020-01-07 17:39:46.120 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Bar: b-101
2020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=1, bar=aaa)
2020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=2, bar=b-0)
2020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=3, bar=b-1)
2020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=100, bar=b-100)
2020-01-07 17:39:46.121 INFO 12296 --- [ main] g.spring.data.simplejdbcdemo.FooDao : Foo: Foo(id=101, bar=b-101)
2020-01-07 17:39:46.123 INFO 12296 --- [ Thread-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2020-01-07 17:39:46.126 INFO 12296 --- [ Thread-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Process finished with exit code 0