整合MyBatis

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。非常灵活，可定制化程度高！

简单介绍

MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

准备工作

MySQL创建一个biubiu的数据库 创建一张表 tb_user，插入一些测试数据

CREATE TABLE `tb_user`  (
    `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
    `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
    `nickname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
    `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
    `gender` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
    `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像',
    `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电话',
    `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
    `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
    `created_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
    `updated_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10001 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

INSERT INTO `tb_user` VALUES (10003, '刘备', NULL, NULL, '男', NULL, NULL, NULL, NULL, '2021-08-11 16:54:04', NULL);
INSERT INTO `tb_user` VALUES (10004, '孙尚香', NULL, NULL, '女', NULL, NULL, NULL, NULL, '2021-08-11 16:54:04', NULL);

创建项目

创建一个springboot-mybatis-demo的web项目，如图

开始集成

1. 引入相关依赖 mybatis-spring-boot-starter mysql-connector-java 最终的pom.xml文件

<!--web启动器-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--MyBatis框架-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${mybatis.version}</version>
</dependency>
<!--MySQL驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

2. 实体类

在写之前推荐一个工具，这里有个 代码生成器网站 (opens new window) 推荐使用!

UserEntity 对应数据库的表 biubiu.tb_user

@Data
public class UserEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    private Long userId;
    private String name;
    private String nickname;
    private Integer age;
    private String gender;
    private String avatar;
    private String phone;
    private String address;
    private String remark;
    private Date createdTime;
    private Date updatedTime;
}

3. 数据交互层接口 UserMapper(增删改查)

public interface UserMapper {

    int insert(UserEntity user);

    int delete(int id);

    int update(UserEntity user);

    UserEntity load(int id);

    List<UserEntity> pageList(int pageNum, int pageSize);

    int pageListCount(int pageNum, int pageSize);
}

4. 接口对应的UserMapper.xml文件

MyBatis提供两种写sql的方式，注解和xml，我个人觉得xml比较好，把代码和sql分开，便于维护。有时候sql写的复杂了注解方式有局限性，我习惯上使用xml方式。IDEA有个插件叫MyBatisX，可以从mapper到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.biubiu.mb.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.biubiu.mb.entity.UserEntity">
        <result column="user_id" property="userId"/>
        <result column="name" property="name"/>
        <result column="nickname" property="nickname"/>
        <result column="age" property="age"/>
        <result column="remark" property="remark"/>
        <result column="created_time" property="createdTime"/>
        <result column="updated_time" property="updatedTime"/>
    </resultMap>

    <sql id="Base_Column_List">
        user_id,name,nickname,age,gender,avatar,phone,address,remark,created_time,updated_time
    </sql>

    <insert id="insert" useGeneratedKeys="true" keyColumn="user_id" keyProperty="userId"
            parameterType="com.biubiu.mb.entity.UserEntity">
        INSERT INTO tb_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != name and '' != name">
                name,
            </if>
            <if test="null != nickname and '' != nickname">
                nickname,
            </if>
            <if test="null != createdTime and '' != createdTime">
                created_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != name and '' != name">
                #{name},
            </if>
            <if test="null != nickname and '' != nickname">
                #{nickname},
            </if>
            <if test="null != createdTime and '' != createdTime">
                #{createdTime},
            </if>
        </trim>
    </insert>

    <delete id="delete">
        DELETE
        FROM tb_user
        WHERE user_id = #{userId}
    </delete>

    <update id="update" parameterType="com.biubiu.mb.entity.UserEntity">
        UPDATE tb_user
        <set>
            <if test="null != userId and '' != userId">user_id = #{userId},</if>
            <if test="null != name and '' != name">name = #{name},</if>
            <if test="null != nickname and '' != nickname">nickname = #{nickname},</if>
            <if test="null != age and '' != age">age = #{age},</if>
            <if test="null != createdTime and '' != createdTime">created_time = #{createdTime},</if>
            <if test="null != updatedTime and '' != updatedTime">updated_time = #{updatedTime}</if>
        </set>
        WHERE user_id = #{userId}
    </update>


    <select id="load" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_user
        WHERE user_id = #{userId}
    </select>

    <select id="pageList" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_user
        LIMIT #{pageNum}, #{pageSize}
    </select>

    <select id="pageListCount" resultType="java.lang.Integer">
        SELECT count(1)
        FROM tb_user
    </select>

</mapper>

5. service层，包括接口和实现类

• UserService接口

public interface UserService {

    public boolean insert(UserEntity user);

    public boolean delete(int id);

    public boolean update(UserEntity user);

    public UserEntity load(int id);

    public Map<String, Object> pageList(int pageNum, int pageSize);
}

• UserServiceImpl实现类

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    @Override
    public boolean insert(UserEntity user) {
        // valid
        if (user == null) {
            throw new RuntimeException("必要参数缺失");
        }

        int insert = userMapper.insert(user);
        return insert > 0;
    }

    @Override
    public boolean delete(int id) {
        int ret = userMapper.delete(id);
        return ret > 0;
    }

    @Override
    public boolean update(UserEntity user) {
        int ret = userMapper.update(user);
        return ret > 0;
    }

    @Override
    public UserEntity load(int id) {
        return userMapper.load(id);
    }

    @Override
    public Map<String, Object> pageList(int pageNum, int pageSize) {
        List<UserEntity> pageList = userMapper.pageList(pageNum, pageSize);
        int totalCount = userMapper.pageListCount(pageNum, pageSize);

        // result
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("pageList", pageList);
        result.put("totalCount", totalCount);

        return result;
    }
}

6. UserController

@RestController
@RequestMapping("/api/users")
public class UserController {

    @Resource
    private UserService userService;

    /**
     * 新增
     */
    @RequestMapping("/insert")
    public Object insert(UserEntity user) {
        return userService.insert(user);
    }

    /**
     * 刪除
     */
    @RequestMapping("/delete")
    public Object delete(int id) {
        return userService.delete(id);
    }

    /**
     * 更新
     */
    @RequestMapping("/update")
    public Object update(UserEntity user) {
        return userService.update(user);
    }

    /**
     * 查询 根据主键 id 查询
     */
    @RequestMapping("/load")
    public Object load(int id) {
        return userService.load(id);
    }

    /**
     * 查询 分页查询
     */
    @RequestMapping("/pageList")
    public Map<String, Object> pageList(@RequestParam(required = false, defaultValue = "0") int pageNum,
                                        @RequestParam(required = false, defaultValue = "10") int pageSize) {
        return userService.pageList(pageNum, pageSize);
    }
}

配置数据库

上面写了一系列代码，唉，差点累死了，接下来轻松多了！

我们写了一些列类，下面开始连接数据库，以及MyBatis的xml文件位置。注意修改数据库连接字符串以及用户名密码！

application.properties

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/biubiu?useUnicode=true&characterEncoding=UTF-8&useSSL=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root

#MyBatis的xml文件，UserMapper.xml
mybatis.mapper-locations=classpath:mapper/*Mapper.xml

最后一步，在配置下mapper接口扫描，在启动类上加上一个注解@MapperScan("com.biubiu.mb.mapper")

@MapperScan("com.biubiu.mb.mapper")
@SpringBootApplication
public class MyBatisApplication {

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

}

开始测试

费了九牛二虎之力，终于到了验收成果的时候，有些小激动，废话不多说，开始干！

打开postman神器，开始一个一个测试我们的增删改查

• 查询10007的用户

http://localhost:8080/api/users/load?id=10007

• 分页查询

http://localhost:8080/api/users/pageList?pageNum=0&pageSize=5

• 插入一个人

好家伙看起来成功了，我们查一下数据库，果真成功了

成功了，那我再用接口查一把

• 修改下这个人

数据库看眼，是否成功

• 删除把

问题排除

由于我写的时候太粗心把好几个单词打错了，导致了几个Bug...要细心细心细心呀！！！

1. 错误1 pagenum小写了，和接口参数里对不上也会报错

2. 错误2 自动生成Id我全都写成了id，这里应该是UserEntity里的UserId和数据库字段user_id

3. 错误3 也是和UserEntity里的UserId不对应造成的

源代码

本文对应代码 (opens new window)