?精通 Spring Boot 系列文（10）

Spring Boot 使用 JdbcTemplate

JdbcTemplate 主要為數(shù)據(jù)訪問提供了模板方案，將數(shù)據(jù)庫存取的工作進(jìn)行簡化。

案例：訪問數(shù)據(jù)

1）編輯 pom.xml 文件

<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.0modelVersion>

????<groupId>com.nxgroupId>
????<artifactId>springbootdataartifactId>
????<version>1.0-SNAPSHOTversion>

????<parent>
????????<groupId>org.springframework.bootgroupId>
????????<artifactId>spring-boot-starter-parentartifactId>
????????<version>2.2.6.RELEASEversion>
????????<relativePath/>
????parent>

????<properties>
????????<project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
????????<project.reporting.outputEncoding>UTF-8project.reporting.outputEncoding>
????????<java.version>1.8java.version>
????properties>

????<dependencies>

????????
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-webartifactId>
????????dependency>

????????
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-thymeleafartifactId>
????????dependency>

????????
????????<dependency>
????????????<groupId>mysqlgroupId>
????????????<artifactId>mysql-connector-javaartifactId>
????????dependency>

????????
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-jdbcartifactId>
????????dependency>

????????<dependency>
????????????<groupId>junitgroupId>
????????????<artifactId>junitartifactId>
????????????<scope>testscope>
????????dependency>

????dependencies>
project>

2）編輯 application.properties 文件

####################
###?數(shù)據(jù)源信息配置?###
####################
#?數(shù)據(jù)庫地址
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdata?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
#?用戶名
spring.datasource.username=root
#?密碼
spring.datasource.password=1234
#?數(shù)據(jù)庫驅(qū)動(dòng)
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
#?指定連接池中最大的活躍連接數(shù).
spring.datasource.max-active=20
#?指定連接池最大的空閑連接數(shù)量.
spring.datasource.max-idle=8
#?指定必須保持連接的最小值
spring.datasource.min-idle=8
#?指定啟動(dòng)連接池時(shí)，初始建立的連接數(shù)量
spring.datasource.initial-size=10

3）創(chuàng)建 User 持久類

public?class?User?implements?Serializable{

????private?static?final?long?serialVersionUID?=?1L;

????private?int?id?;
????private?String?loginName?;
????private?String?username?;
????private?String?password;

????//?setXxx?和?getXxx?方法
}

4）創(chuàng)建 UserRepository 數(shù)據(jù)訪問接口

@Repository
public?class?UserRepository?{

????//?注入JdbcTemplate模板對象
????@Resource
????private?JdbcTemplate?jdbcTemplate;

????//?插入數(shù)據(jù)
????public?int?insertUser(){
????????String?sql?=?"insert?into?tb_user(login_name?,username?,passWord)?"
????????????????+?"values?(?,?,?),(?,?,?),(?,?,?)";
????????Object[]?args?=?new?Object[]{"s1","小黃","123456","s2","小綠","123456"
????????????????,"s3","小紅","123456"};
????????//?參數(shù)1：插入數(shù)據(jù)的sql語句?
????????//?參數(shù)2:?對應(yīng)sql語句中占位符?的參數(shù)
????????return?jdbcTemplate.update(sql,?args);
????}

????//?根據(jù)userName查詢數(shù)據(jù)
????public?User?selectByUsername(String?username)?{
????????String?sql?=?"select?*?from?tb_user?where?username?=??";
????????RowMapper?rowMapper?=?new?BeanPropertyRowMapper<>(User.class);
????????User?user?=?jdbcTemplate.queryForObject(sql,?new?Object[]?{?username?},?rowMapper);
????????return?user;
????}

????//?根據(jù)id查詢數(shù)據(jù)
????public?User?findUserById(int?id)?{
????????String?sql?=?"select?*?from?tb_user?where?id=?";
????????RowMapper?rowMapper?=?new?BeanPropertyRowMapper<>(User.class);
????????return?jdbcTemplate.queryForObject(sql,?new?Object[]?{?id?},?rowMapper);
????}

????//?查詢所有數(shù)據(jù)
????public?List?findAll()?{
????????String?sql?=?"select?*?from?tb_user";
????????//?申明結(jié)果集的映射rowMapper，將結(jié)果集的數(shù)據(jù)映射成User對象數(shù)據(jù)
????????RowMapper?rowMapper?=?new?BeanPropertyRowMapper<>(User.class);
????????return?jdbcTemplate.query(sql,?rowMapper);
????}

????//?根據(jù)id刪除數(shù)據(jù)
????public?void?delete(final?Integer?id)?{
????????String?sql?=?"delete?from?tb_user?where?id=?";
????????jdbcTemplate.update(sql,?new?Object[]?{?id?});
????}

????//?修改數(shù)據(jù)
????public?void?update(final?User?user)?{
????????String?sql?=?"update?tb_user?set?username=?,?login_name=??where?id=?";
????????jdbcTemplate.update(sql,
????????????new?Object[]?{?user.getUsername(),?user.getLoginName(),?user.getId()});
????}

????//?插入數(shù)據(jù)獲取被插入數(shù)據(jù)的主鍵
????public?User?insertGetKey(User?user)?{
????????String?sql?=?"insert?into?tb_user(username,login_name,password)?values(?,?,?)";
????????//?定義插入數(shù)據(jù)后獲取主鍵的對象
????????KeyHolder?holder?=?new?GeneratedKeyHolder();
????????jdbcTemplate.update(new?PreparedStatementCreator()?{
????????????@Override
????????????public?PreparedStatement?createPreparedStatement(Connection?connection)?throws?SQLException?{
????????????????//?插入數(shù)據(jù)后，將被插入數(shù)據(jù)的主鍵返回回來
????????????????PreparedStatement?ps?=?connection.prepareStatement(sql,?Statement.RETURN_GENERATED_KEYS);
????????????????ps.setString(1,?user.getUsername());
????????????????ps.setString(2,?user.getLoginName());
????????????????ps.setString(3,?user.getPassword());
????????????????return?ps;
????????????}
????????},?holder);
????????//?獲取被插入數(shù)據(jù)庫的主鍵?然后注入到user對象中去
????????int?newUserId?=?holder.getKey().intValue();
????????user.setId(newUserId);
????????return?user;
????}
}

5）創(chuàng)建 UserService 業(yè)務(wù)層類

@Service
public?class?UserService?{

????//?注入U(xiǎn)serRepository
????@Resource
????private?UserRepository?userRepository;

????public?int?insertUser(){
????????return?userRepository.insertUser();
????}

????public?User?selectByUsername(String?username){
????????return?userRepository.selectByUsername(username);
????}

????public?List?findAll(){
????????return?userRepository.findAll();
????}

????public?User?insertGetKey(User?user)?{
????????return?userRepository.insertGetKey(user);
????}

????public?void?update(User?user)?{
????????userRepository.update(user);
????}

????public?void?delete(Integer?id)?{
????????userRepository.delete(id);
????}
}

6）定義 UserController 控制器類

@RestController
@RequestMapping("/user")
public?class?UserController?{
????@Resource
????private?UserService?userService;

????@RequestMapping("/insertUser")
????public?String?insertUser(){
????????return?"插入數(shù)據(jù)["+userService.insertUser()+"]條";
????}

????@RequestMapping("/insertGetKey")
????public?User?insertGetKey(User?user)?{
????????return?userService.insertGetKey(user);
????}

????@RequestMapping("/selectByUsername")
????public?User?selectByUserNm(String?username){
????????return?userService.selectByUsername(username);
????}

????@RequestMapping("/findAll")
????public?List?findAll(){
????????return?userService.findAll();
????}

????@RequestMapping("/update")
????public?void?update(User?user)?{
????????userService.update(user);
????}

????@RequestMapping("/delete")
????public?void?delete(Integer?id)?{
????????userService.delete(id);
????}
}

7）測試

http://localhost:8080/user/insertUser