第八章 数据库操作之整合Mybatis和事务讲解
08-1 Spring2.x持久化数据方式介绍
简介：介绍近几年常用的访问数据库的方式和优缺点
1、原始java访问数据库
开发流程麻烦
1、注册驱动/加载驱动
Class.forName("com.mysql.jdbc.Driver")
2、建立连接
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname","root","root");
3、创建Statement
4、执行SQL语句
5、处理结果集
6、关闭连接，释放资源
2、apache dbutils框架
比上一步简单点
官网:https://commons.apache.org/proper/commons-dbutils/
3、jpa框架
spring-data-jpa
jpa在复杂查询的时候性能不是很好
4、Hibernate 解释：ORM：对象关系映射Object Relational Mapping
企业大都喜欢使用hibernate
5、Mybatis框架
互联网行业通常使用mybatis
不提供对象和关系模型的直接映射,半ORM
08-2 SpringBoot2.x整合 Mybatis3.x注解实战
1.创建SpringBoot基础结构
第三方依赖包：
<?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 https://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.6.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.lcz</groupId>
<artifactId>spring_demo14</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring_demo14</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-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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>
2.application.properties
#mybatis.type-aliases-package=net.xdclass.base_project.domain
#可以自动识别
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/project_lcz?useUnicode=true&characterEncoding=utf-8
spring.datasource.username =root
spring.datasource.password =123
#spring.datasource.type =com.alibaba.druid.pool.DruidDataSource
3.目录结构
3.1 domain
User.java
package com.lcz.spring_demo14.domain;
import java.util.Date;
/**
* @author : codingchao
* @date : 2021-11-25 10:03
* @Description:
**/
public class User {
private int id;
private String name;
private String phone;
private int age;
private Date createTime;
public User(){
}
public User(int id, String name, String phone, int age, Date createTime) {
this.id = id;
this.name = name;
this.phone = phone;
this.age = age;
this.createTime = createTime;
}
public User(String name, String phone, int age, Date createTime) {
this.name = name;
this.phone = phone;
this.age = age;
this.createTime = createTime;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
JsonData.java：响应结果类
package com.lcz.spring_demo14.domain;
/**
* @author : codingchao
* @date : 2021-11-25 10:05
* @Description:
**/
import java.io.Serializable;
/**
* 响应结果类
*/
public class JsonData implements Serializable {
private Integer code; //状态码 0成功 1处理中 -1处理失败
private Object data; //数据
private String msg; //描述
public JsonData(){
}
public JsonData(Integer code, Object data,String msg) {
this.code = code;
this.msg = msg;
this.data = data;
}
// 成功，传入数据
public static JsonData buildSuccess() {
return new JsonData(0, null, null);
}
// 成功，传入数据
public static JsonData buildSuccess(Object data) {
return new JsonData(0, data, null);
}
// 失败，传入描述信息
public static JsonData buildError(String msg) {
return new JsonData(-1, null, msg);
}
// 失败，传入描述信息,状态码
public static JsonData buildError(String msg, Integer code) {
return new JsonData(code, null, msg);
}
// 成功，传入数据,及描述信息
public static JsonData buildSuccess(Object data, String msg) {
return new JsonData(0, data, msg);
}
// 成功，传入数据,及状态码
public static JsonData buildSuccess(Object data, int code) {
return new JsonData(code, data, null);
}
public Integer getCode() {
return code;
}
public void setCode(Integer code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
}
3.2 mapper
访问数据库表的接口
package com.lcz.spring_demo14.mapper;
/**
* @author : codingchao
* @date : 2021-11-25 10:10
* @Description:
**/
import com.lcz.spring_demo14.domain.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
/**
* 访问user数据表的mapper表
*/
public interface UserMapper {
//推荐使用#{}取值，不要用${},因为存在注入的风险
@Insert("INSERT INTO user(name,phone,create_time,age) VALUES(#{name}, #{phone}, #{createTime},#{age})")
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id") //keyProperty java对象的属性；keyColumn表示数据库的字段
int insert(User user);
}
3.3 service
UserService
package com.lcz.spring_demo14.service;
import com.lcz.spring_demo14.domain.User;
/**
* @author : codingchao
* @date : 2021-11-25 10:13
* @Description:
**/
public interface UserService {
public int add(User user);
}
impl/UserServiceImpl
package com.lcz.spring_demo14.service.impl;
import com.lcz.spring_demo14.domain.User;
import com.lcz.spring_demo14.mapper.UserMapper;
import com.lcz.spring_demo14.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @author : codingchao
* @date : 2021-11-25 10:14
* @Description:
**/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public int add(User user) {
userMapper.insert(user);
int id = user.getId();
return id;
}
}
3.4 controller
package com.lcz.spring_demo14.controller;
import com.lcz.spring_demo14.domain.JsonData;
import com.lcz.spring_demo14.domain.User;
import com.lcz.spring_demo14.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
/**
* @author : codingchao
* @date : 2021-11-25 10:20
* @Description:
**/
@RestController
@RequestMapping(value = "/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping(value = "/add")
public Object add(){
User user = new User();
user.setAge(18);
user.setCreateTime(new Date());
user.setName("lcz");
user.setPhone("1xxxxxxxx");
int id = userService.add(user);
return JsonData.buildSuccess();
}
}
3.5 启动类
package com.lcz.spring_demo14;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan("com.lcz.spring_demo14.mapper")
@SpringBootApplication
public class SpringDemo14Application {
public static void main(String[] args) {
SpringApplication.run(SpringDemo14Application.class, args);
}
}
3.6 sql创建表
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL COMMENT '名称',
`phone` varchar(16) DEFAULT NULL COMMENT '用户手机号',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`age` int(4) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
4.结果图
小结：：
SpringBoot2.x整合Mybatis3.x注解配置实战
1、使用starter, maven仓库地址：http://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter
2、加入依赖(可以用 http://start.spring.io/ 下载)
<!-- 引入starter-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
<scope>runtime</scope>
</dependency>
<!-- MySQL的JDBC驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 引入第三方数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
3、加入配置文件
#mybatis.type-aliases-package=net.xdclass.base_project.domain
#可以自动识别
#spring.datasource.driver-class-name =com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/movie?useUnicode=true&characterEncoding=utf-8
spring.datasource.username =root
spring.datasource.password =password
#如果不使用默认的数据源 （com.zaxxer.hikari.HikariDataSource）
spring.datasource.type =com.alibaba.druid.pool.DruidDataSource
加载配置，注入到sqlSessionFactory等都是springBoot帮我们完成
4、启动类增加mapper扫描
@MapperScan("net.xdclass.base_project.mapper")
技巧：保存对象，获取数据库自增id
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
4、开发mapper
参考语法 http://www.mybatis.org/mybatis-3/zh/java-api.html
5、sql脚本
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL COMMENT '名称',
`phone` varchar(16) DEFAULT NULL COMMENT '用户手机号',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`age` int(4) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
相关资料：
http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/#Configuration
https://github.com/mybatis/spring-boot-starter/tree/master/mybatis-spring-boot-samples
整合问题集合：
https://my.oschina.net/hxflar1314520/blog/1800035
https://blog.csdn.net/tingxuetage/article/details/80179772
08-3 SpringBoot2.x整合Mybatis3.x增删改查实操和控制台打印sql语句
在上一小结的基础上，继续增加删改查的操作。
1.application.properties
#mybatis.type-aliases-package=net.xdclass.base_project.domain
#可以自动识别
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/project_lcz?useUnicode=true&characterEncoding=utf-8
spring.datasource.username =root
spring.datasource.password =123
#spring.datasource.type =com.alibaba.druid.pool.DruidDataSource
#增加打印sql语句，一般用于本地开发测试
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
2.目录结构
2.1 mapper
UserMapper.java
package com.lcz.spring_demo14.mapper;
/**
* @author : codingchao
* @date : 2021-11-25 10:10
* @Description:
**/
import com.lcz.spring_demo14.domain.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* 访问user数据表的mapper表
*/
public interface UserMapper {
//推荐使用#{}取值，不要用${},因为存在注入的风险
@Insert("INSERT INTO user(name,phone,create_time,age) VALUES(#{name}, #{phone}, #{createTime},#{age})")
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id") //keyProperty java对象的属性；keyColumn表示数据库的字段
int insert(User user);
/**
* 根据id查找对象
* @param id
* @return
*/
@Select("select * from user where id=#{id}")
@Results({@Result(column = "create_time",property = "createTime")})
User findById(int id);
/**
* 查找全部
* @return
*/
@Select("SELECT * FROM user")
@Results({
@Result(column = "create_time",property = "createTime") //javaType = java.util.Date.class
})
List<User> getAll();
/**
* 功能描述：更新对象
* @param user
*/
@Update("UPDATE user SET name=#{name} WHERE id =#{id}")
void update(User user);
/**
* 功能描述：根据id删除用户
* @param userId
*/
@Delete("DELETE FROM user WHERE id =#{userId}")
void delete(int userId);
}
2.2 service
UserService
package com.lcz.spring_demo14.service;
import com.lcz.spring_demo14.domain.User;
import java.util.List;
/**
* @author : codingchao
* @date : 2021-11-25 10:13
* @Description:
**/
public interface UserService {
public int add(User user);
public User findById(int id);
public List<User> getAll();
public void update(User user);
public void delete(int id);
}
UserServiceImpl
package com.lcz.spring_demo14.service.impl;
import com.lcz.spring_demo14.domain.User;
import com.lcz.spring_demo14.mapper.UserMapper;
import com.lcz.spring_demo14.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author : codingchao
* @date : 2021-11-25 10:14
* @Description:
**/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public int add(User user) {
userMapper.insert(user);
int id = user.getId();
return id;
}
@Override
public User findById(int id) {
User user = userMapper.findById(id);
return user;
}
@Override
public List<User> getAll() {
List<User> list = userMapper.getAll();
return list;
}
@Override
public void update(User user) {
userMapper.update(user);
return;
}
@Override
public void delete(int id) {
userMapper.delete(id);
return;
}
}
3.结果图
插入语句insert
查询findall
查询findById
删除数据
更新数据update
小结：
讲解:SpringBoot2.x整合Mybatis3.x增删改查实操, 控制台打印sql语句
1、控制台打印sql语句
#增加打印sql语句，一般用于本地开发测试
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
2、增加mapper代码
@Select("SELECT * FROM user")
@Results({
@Result(column = "create_time",property = "createTime") //javaType = java.util.Date.class
})
List<User> getAll();
@Select("SELECT * FROM user WHERE id = #{id}")
@Results({
@Result(column = "create_time",property = "createTime")
})
User findById(Long id);
@Update("UPDATE user SET name=#{name} WHERE id =#{id}")
void update(User user);
@Delete("DELETE FROM user WHERE id =#{userId}")
void delete(Long userId);
3、增加API
@GetMapping("find_all")
public Object findAll(){
return JsonData.buildSuccess(userMapper.getAll());
}
@GetMapping("find_by_Id")
public Object findById(long id){
return JsonData.buildSuccess(userMapper.findById(id));
}
@GetMapping("del_by_id")
public Object delById(long id){
userMapper.delete(id);
return JsonData.buildSuccess();
}
@GetMapping("update")
public Object update(String name,int id){
User user = new User();
user.setName(name);
user.setId(id);
userMapper.update(user);
return JsonData.buildSuccess();
}
08-4 事务介绍和常见的隔离级别，传播行为
简介：讲解什么是数据库事务，常见的隔离级别和传播行为
1、介绍什么是事务，单机事务，分布式事务处理等
2、讲解场景的隔离级别
Serializable： 最严格，串行处理，消耗资源大
Repeatable Read：保证了一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据
Read Committed：大多数主流数据库的默认事务等级
Read Uncommitted：保证了读取过程中不会读取到非法数据。
3、讲解常见的传播行为
PROPAGATION_REQUIRED--支持当前事务，如果当前没有事务，就新建一个事务,最常见的选择。
PROPAGATION_SUPPORTS--支持当前事务，如果当前没有事务，就以非事务方式执行。
PROPAGATION_MANDATORY--支持当前事务，如果当前没有事务，就抛出异常。
PROPAGATION_REQUIRES_NEW--新建事务，如果当前存在事务，把当前事务挂起, 两个事务之间没有关系，一个异常，一个提交，不会同时回滚
PROPAGATION_NOT_SUPPORTED--以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
PROPAGATION_NEVER--以非事务方式执行，如果当前存在事务，则抛出异常
08-5 SpringBoot整合mybaits之事务处理实战
简介：SpringBoot整合Mybatis之事务处理实战
1、service逻辑引入事务 @Transantional(propagation=Propagation.REQUIRED)
2、service代码
@Override
@Transactional
public int addAccount() {
User user = new User();
user.setAge(9);
user.setCreateTime(new Date());
user.setName("事务测试");
user.setPhone("000121212");
userMapper.insert(user);
int a = 1/0;
return user.getId();
}