spring boot 整合Mybatis 完成增删改查_springbootmybatis增删改查

---

前言

使用 java 开发或者学习过程中，最避免不了的是连接和操作数据库，此次，学习了如何在spring boot中配置和使用Mybatis框架以及 PageHelper的使用 加快开发效率。

---

一、环境准备

开发工具：idea
接口测试工具：apipost7
数据库：mysql

二、操作步骤

1.建表

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `password` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL,
  `sex` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_user` VALUES (3, '张三', '123', 23, '女', '854296521@qq.com');
INSERT INTO `t_user` VALUES (4, 'admin', '123', 23, '女', '854296521@qq.com');
INSERT INTO `t_user` VALUES (5, 'weqwe', '123', 23, '女', '854296521@qq.com');
INSERT INTO `t_user` VALUES (6, '李四', '123123', 78, '男', '98984@qq.com');
INSERT INTO `t_user` VALUES (7, '四', '123', 78, '女', '4564@qq.com');
INSERT INTO `t_user` VALUES (8, '王五', '123456', 12, '男', '123@136.com');
INSERT INTO `t_user` VALUES (9, '张三', '1234567', 12, '男', '854296521@qq.com');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.创建 Spring Boot 工程

3.修改配置文件 application.yaml

spring:
  application:
    name: Springboot-Mybatis

  #mysql
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    name: defaultDataSource
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
    username: root
    password: 123456

server:
  port: 8080

#mybatis配置
mybatis:
  #mapper file location
  mapper-locations: classpath:com/cque/springbootmybatis/mapper/*.xml
  #entity package
  type-aliases-package: com.cque.springbootmybatis.entity
  configuration:
    # 开启下划线转化为驼峰规则
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

4.创建实体类等

/**
 * @author zhaoyuqi start
 * @create 2022-12-08 - 17:30
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
    private Long id;
    private String username;
    private String password;
    private Integer age;
    private String sex;
    private String email;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5.创建mapper

/**
 * @author zhaoyuqi start
 * @create 2022-12-08 - 17:37
 */
@Mapper
public interface UserMapper {
	//添加
    void insert(User user);
	//删除
    void delete(Long id);
	//修改
    void update(User user);
	//分页条件查询
    List<User> getPages(User user);

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

6.创建service

/**
 * @author zhaoyuqi start
 * @create 2022-12-08 - 17:48
 */
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
	//添加
    public void registerUser(User user) {
        userMapper.insert(user);
    }
	//删除
    public void deleteUser(Long id) {
        userMapper.delete(id);
    }
	//修改
    public void updateUser(User user) {
        userMapper.update(user);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

7.在启动类上加上@MapperScan 扫描Mapper

@SpringBootApplication
@MapperScan("com.cque.springbootmybatis.mapper")
public class SpringbootMybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisApplication.class, args);
    }

}
1
2
3
4
5
6
7
8
9

8.创建UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cque.springbootmybatis.mapper.UserMapper">
    <!--insert(User user)-->
    <insert id="insert">
        insert into t_user values(null ,#{username},#{password},#{age},#{sex},#{email})
    </insert>

    <!--delete(Integer id)-->
    <delete id="delete">
        delete from t_user where id = #{id}
    </delete>
    <!--update(User user)-->
    <update id="update">
        update t_user set username=#{username},password = #{password},age=#{age},sex=#{sex},email=#{email}
        where id = #{id}
    </update>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

值得注意的是：需要在application.yaml中写好Mapper文件位置

9.使用PageHelper实现分页

PageHelper 用于后端分页

1.导入依赖

        <!--spring boot pageHelper starter 用于分页-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.5</version>
        </dependency>
1
2
3
4
5
6

2.配置PageHelper（两种方式）

• 方式一：在application.yaml加入以下配置

#pagehelper plugins 用于分页
pagehelper:
  # 分页的数据来源
  helper-dialect: mysql
  # 启用合理化，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页
  reasonable: false
  # 支持通过 Mapper 接口参数来传递分页参数，默认值false
  support-methods-arguments: true
  #用于从对象中根据属性名取值，可以配置 pageNum，pageSize，count，pageSizeZero，reasonable，
  #不配置映射的用默认值，默认值为 pageNum=pageNum；pageSize=pageSize；count=countSql；reasonable=reasonable；
  params: count=countSql
1
2
3
4
5
6
7
8
9
10
11
12

• 方式二：创建配置类，交给spring管理

/**
 * @author zhaoyuqi start
 * @create 2022-12-09 - 12:18
 */

/*
 配置方式：除了用yaml配置之外还可以配置类
 pageHelper的配置方式二
 */
@Configuration
public class PagehelperConfigration {

    @Bean
    public PageHelper pageHelper(){
        PageHelper pageHelper = new PageHelper();
        Properties pro = new Properties();
        pro.setProperty("offsetAsPageNum","true");
        pro.setProperty("rowBoundsWithCount","true");
        pro.setProperty("reasonable","true");
        pageHelper.setProperties(pro);
        return pageHelper;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.在UserMapper.java中加入查询方法

	//用于分页条件查询
    List<User> getPages(User user);
1
2

4.在service实现分页方法

/**
     * 分页条件查询
     *
     * @param pageNum  当前页
     * @param pageSize 每一页显示条数
     * @param user     封装的查询条件
     * @return 返回分页后的数据
     */
    public List<User> listUser(Integer pageNum, Integer pageSize, User user) {
        /*
         startPage方法自动为Mpper.xml中的SQL，加上 limit pageNum,pageSize，在SQL中就不用写了limit了 排序也可通过startPage设置
         */
        PageHelper.startPage(pageNum, pageSize);
        List<User> list = userMapper.getPages(user);
        //可以进一步包装成更完整的数据 PageInfo 分页对象，方便前端展示，里面封装了数据total这些）
        //PageInfo<User> pageInfo = new PageInfo<>(list);
        return list;
    }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

10.封装全局返回结果类和返回状态类,方便接口测试

• Result.java

/**
 * 全局统一返回结果类
 */
@Data
public class Result<T> {

    private Integer code;

    private String message;

    private T data;

    public Result(){}

    protected static <T> Result<T> build(T data) {
        Result<T> result = new Result<T>();
        if (data != null)
            result.setData(data);
        return result;
    }

    public static <T> Result<T> build(T body, ResultCodeEnum resultCodeEnum) {
        Result<T> result = build(body);
        result.setCode(resultCodeEnum.getCode());
        result.setMessage(resultCodeEnum.getMessage());
        return result;
    }

    public static <T> Result<T> build(Integer code, String message) {
        Result<T> result = build(null);
        result.setCode(code);
        result.setMessage(message);
        return result;
    }

    public static<T> Result<T> ok(){
        return Result.ok(null);
    }

    /**
     * 操作成功
     * @param data
     * @param <T>
     * @return
     */
    public static<T> Result<T> ok(T data){
        Result<T> result = build(data);
        return build(data, ResultCodeEnum.SUCCESS);
    }

    public static<T> Result<T> fail(){
        return Result.fail(null);
    }

    /**
     * 操作失败
     * @param data
     * @param <T>
     * @return
     */
    public static<T> Result<T> fail(T data){
        Result<T> result = build(data);
        return build(data, ResultCodeEnum.FAIL);
    }

    public Result<T> message(String msg){
        this.setMessage(msg);
        return this;
    }

    public Result<T> code(Integer code){
        this.setCode(code);
        return this;
    }

    public boolean isOk() {
        if(this.getCode().intValue() == ResultCodeEnum.SUCCESS.getCode().intValue()) {
            return true;
        }
        return false;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

• ResultCodeEnum.java

/**
 * 统一返回结果状态信息类
 */
@Getter
public enum ResultCodeEnum {

    SUCCESS(20000,"成功"),
    FAIL(20001, "失败"),
    ;

    private Integer code;
    private String message;

    private ResultCodeEnum(Integer code, String message) {
        this.code = code;
        this.message = message;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

11.创建控制器UserController

/**
 * @author zhaoyuqi start
 * @create 2022-12-08 - 17:46
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @PostMapping
    public Result registerUser(@RequestBody User user){
        userService.registerUser(user);
        return Result.ok();
    }

    @DeleteMapping("{id}")
    public Result deleteUser(@PathVariable Long id){
        userService.deleteUser(id);
        return Result.ok();
    }

    @PutMapping
    public Result udpateUser(@RequestBody User user){
        userService.updateUser(user);
        return Result.ok();
    }

    @PostMapping("/list/{pageNum}/{pageSize}")
    public Result list(@PathVariable Integer pageNum,
                           @PathVariable Integer pageSize,
                           @RequestBody User user){
        List<User> users = userService.listUser(pageNum, pageSize, user);
        return Result.ok(users);

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

12.使用APIPOST测试接口

1. 测试添加

数据库：

2. 测试删除

数据库：

3. 测试修改

数据库：

4. 测试查询

---

三、总结

从数据库到实体类再到测试，一个完成的 spring boot 整合 mybatis 的demo演示完毕！