Spring的JDBC框架承擔了資源管理和異常處理的工作乱陡，將數(shù)據(jù)訪問的樣板代碼抽象到模板類之中浇揩，從而簡化了JDBC代碼，使我們只需編寫從數(shù)據(jù)庫讀寫數(shù)據(jù)所必需的代碼憨颠。
Spring為JDBC提供了三個模板類供選擇：
-
JdbcTemplate
最基本的Spring JDBC模板胳徽，支持簡單的JDBC數(shù)據(jù)訪問功能以及基于索引參數(shù)的查詢，上層接口為JdbcOperations
This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package. -
NamedParameterJdbcTemplate
使用該模板類進行查詢時可以將值以命名參數(shù)的形式綁定到sql中爽彤，而不是使用簡單的索引參數(shù)养盗，上層接口為NamedParameterJdbcOperations
Template class with a basic set of JDBC operations, allowing the useof named parameters rather than traditional '?' placeholders. -
SimpleJdbcTemplate
已廢棄
導入依賴
在 pom.xml 中添加對 JdbcTemplate 的依賴
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
連接數(shù)據(jù)庫
在application.yml中添加如下配置。值得注意的是适篙，Spring Boot默認會自動配置DataSource往核，它將優(yōu)先采用HikariCP連接池，如果沒有該依賴的情況則選取 Tomcat pooling DataSource 嚷节，如果前兩者都不可用最后選取 Commons DBCP2
聂儒。通過spring.datasource.type屬性可以指定其它種類的連接池
application.yml
spring:
application:
name: spring-boot-jdbc
datasource:
url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
username: root
password: mysql123
driver-class-name: com.mysql.jdbc.Driver
啟動項目，通過日志硫痰，可以看到默認情況下注入的是HikariDataSource
2018-07-07 15:28:37.925 INFO 2316 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Bean with name 'dataSource' has been autodetected for JMX exposure
2018-07-07 15:28:37.932 INFO 2316 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
2018-07-07 15:28:37.978 INFO 2316 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
具體編碼
表結(jié)構(gòu)
創(chuàng)建tb_user表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶id',
`username` varchar(64) NOT NULL COMMENT '用戶名',
`password` varchar(64) NOT NULL COMMENT '用戶密碼',
`birthday` date DEFAULT NULL COMMENT '用戶生日',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
BEGIN;
INSERT INTO `tb_user` VALUES (1, 'aaa', '123456', '1994-08-10');
INSERT INTO `tb_user` VALUES (2, 'bbb', '123456', '1996-07-25');
INSERT INTO `tb_user` VALUES (3, 'ccc', '123456', '2000-05-01');
INSERT INTO `tb_user` VALUES (4, 'ddd', '123456', '1997-05-10');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
實體類
package com.example.springbootjdbc.pojo;
import java.util.Date;
public class User {
/** 用戶id */
private Integer id;
/** 用戶名 */
private String username;
/** 用戶密碼 */
private String password;
/** 用戶生日 */
private Date birthday;
// setters and getters ...
}
Dao層
com.example.springbootjdbc.dao.IUserDao
package com.example.springbootjdbc.dao;
import com.example.springbootjdbc.pojo.User;
import java.util.List;
public interface IUserDao {
int save(User user);
int delete(Integer id);
int update(User user);
User findById(Integer id);
List<User> findAll();
List<User> findList(Integer page, Integer pageSize);
}
com.example.springbootjdbc.dao.impl.UserDaoImpl
package com.example.springbootjdbc.dao.impl;
import com.example.springbootjdbc.dao.IUserDao;
import com.example.springbootjdbc.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public class UserDaoImpl implements IUserDao {
private final JdbcTemplate jdbcTemplate;
@Autowired
public UserDaoImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public int save(User user) {
String sql = "INSERT INTO tb_user(username, password, birthday) VALUES(?, ?, ?)";
return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getBirthday());
}
@Override
public int delete(Integer id) {
String sql = "DELETE FROM tb_user WHERE id = ?";
return jdbcTemplate.update(sql, id);
}
@Override
public int update(User user) {
String sql = "UPDATE tb_user u SET u.username = ?, u.password = ?, u.birthday = ? WHERE u.id = ?";
return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getBirthday(), user.getId());
}
@Override
public User findById(Integer id) {
String sql = "SELECT * FROM tb_user WHERE id = ?";
return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
}
@Override
public List<User> findAll() {
String sql = "SELECT * FROM tb_user";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}
@Override
public List<User> findList(Integer page, Integer pageSize) {
String sql = "SELECT * FROM tb_user LIMIT ?, ?";
Integer offset = (page - 1) * pageSize;
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), offset, pageSize);
}
}
Service層
com.example.springbootjdbc.service.IUserService
package com.example.springbootjdbc.service;
import com.example.springbootjdbc.pojo.User;
import java.util.List;
public interface IUserService {
int save(User user);
int delete(Integer id);
int update(User user);
User findById(Integer id);
List<User> findAll();
List<User> findList(Integer page, Integer pageSize);
}
com.example.springbootjdbc.service.impl.UserServiceImpl
package com.example.springbootjdbc.service.impl;
import com.example.springbootjdbc.dao.IUserDao;
import com.example.springbootjdbc.pojo.User;
import com.example.springbootjdbc.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements IUserService {
private final IUserDao userDao;
@Autowired
public UserServiceImpl(IUserDao userDao) {
this.userDao = userDao;
}
@Override
public int save(User user) {
return userDao.save(user);
}
@Override
public int delete(Integer id) {
return userDao.delete(id);
}
@Override
public int update(User user) {
return userDao.update(user);
}
@Override
public User findById(Integer id) {
return userDao.findById(id);
}
@Override
public List<User> findAll() {
return userDao.findAll();
}
@Override
public List<User> findList(Integer page, Integer pageSize) {
return userDao.findList(page, pageSize);
}
}
Rest 接口
package com.example.springbootjdbc.controller;
import com.example.springbootjdbc.pojo.User;
import com.example.springbootjdbc.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
@RestController
@RequestMapping("/users")
public class UserController {
private final IUserService userService;
@Autowired
public UserController(IUserService userService) {
this.userService = userService;
}
@PostMapping("")
public String saveUser(@RequestBody User user) {
int count = userService.save(user);
return count > 0 ? "success" : "fail";
}
@DeleteMapping("/{id}")
public String deleteUser(@PathVariable Integer id) {
int count = userService.delete(id);
return count > 0 ? "success" : "fail";
}
@PutMapping("/{id}")
public String updateUser(@PathVariable Integer id, @RequestParam(value = "username", required = true) String username,
@RequestParam(value = "password", required = true) String password,
@RequestParam(value = "birthday", required = true) String birthday) throws ParseException {
User updateUser = new User();
updateUser.setId(id);
updateUser.setUsername(username);
updateUser.setPassword(password);
updateUser.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));
int count = userService.update(updateUser);
return count > 0 ? "success" : "fail";
}
@GetMapping("")
public List<User> findList(@RequestParam(value = "page", defaultValue = "1") Integer page,
@RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize) {
return userService.findList(page, pageSize);
}
@GetMapping("{id}")
public User findById(@PathVariable Integer id) {
return userService.findById(id);
}
}
