SpringBoot集成MyBatisPlus以及MyBatisPlus的简单使用「学习笔记」

本文链接：https://blog.csdn.net/qq_38288606/article/details/102701278

此篇文章的重点是MyBatis-Plus的简单使用，至于在使用过程中的用到的其它方法或技术将不作说明，可自行研究 ~~~

一、需求

使用Mybatis-Plus实现对用户的CRUD操作

二、说明

（1）Mybatis-Plus并没有提供单独的jar包，而是通过Maven（或者gradle）来管理jar依赖。
（2）Mybatis-Plus是基于Spring框架实现的，因此使用Mybatis-Plus，必须导入Spring相关依赖。

三、配置步骤说明

（1）搭建环境（创建项目、导入包）
（2）配置Mybaits-Plus（基于SpringBoot实现）
（3）编写测试代码

四、准备工作

创建一个Maven项目

在项目的pom.xml文件中引入Maven JAR包（引入最新JAR包，体验最新功能）

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.2.0</version>
</dependency>

注意：下面这段必须要配置，否则会有问题（亦可通过其它方法替代以下代码）

<build>
	<resources>
		<!--
		maven项目中src源代码下的xml等资源文件编译进classes文件夹，
		注意：如果没有这个，它会自动搜索resources下是否有mapper.xml文件，
		如果没有就会报org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.pet.mapper.PetMapper.selectByPrimaryKey
		-->
		<resource>
			<directory>src/main/java</directory>
			<includes>
				<include>**/*.xml</include>
			</includes>
		</resource>
		<!-- 将resources目录下的配置文件编译进classes文件 -->
		<resource>
			<directory>src/main/resources</directory>
		</resource>
	</resources>
</build>

定义MyBatisPlus入口

/**
 * Created by michael on 2019-03-15.
 */
@EnableTransactionManagement
@Configuration
@MapperScan({"michael.spica.*.mapper", "michael.spica.*.service"})// 设置mapper接口的扫描包
public class MyBatisPlusConfig {

    @Bean
    public GlobalConfig globalConfig() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
        return globalConfig;
    }

    /**
     * mybatis-plus 分页插件
     *
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
//        return new PaginationInterceptor();
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setDialectType("mysql");
        return paginationInterceptor;
    }
}

/**
 * Created by michael on 2019-03-15.
 */
@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    /**
     * 新增时自动填充
     *
     * @param metaObject
     */
    @Override
    public void insertFill(MetaObject metaObject) {
        log.debug("start insert fill......");
        // 获取到需要被填充的字段值
        Object createTime = this.getFieldValByName("createTime", metaObject);
        if (null == createTime) {
            this.setFieldValByName("createTime", LocalDateTime.now(), metaObject);
        }
        Object creator = this.getFieldValByName("creator", metaObject);
        if (null == creator) {
            this.setFieldValByName("creator", 1, metaObject);
        }

        Object lastModifyTime = this.getFieldValByName("lastModifyTime", metaObject);
        if (null == lastModifyTime) {
            this.setFieldValByName("lastModifyTime", LocalDateTime.now(), metaObject);
        }
        Object modifier = this.getFieldValByName("modifier", metaObject);
        if (null == modifier) {
            this.setFieldValByName("modifier", 1, metaObject);
        }
    }

    /**
     * 更新时自动填充
     *
     * @param metaObject
     */
    @Override
    public void updateFill(MetaObject metaObject) {
        log.debug("start update fill......");
        this.setFieldValByName("lastModifyTime", LocalDateTime.now(), metaObject);
        this.setFieldValByName("modifier", 2, metaObject);
    }
}

/**
 * Created by michael on 2019-03-17.
 */
@Component
public class MyBatisPlusSqlInject extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        /**
         * 创建注入器，添加自己的方法
         */
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new DeleteAllMethod());
        return methodList;
    }

    class DeleteAllMethod extends AbstractMethod {
        @Override
        public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
            // 得到待解析sql的模板
            SqlMethod sqlMethod = SqlMethod.DELETE_ALL;
            // 执行的sql
            String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName());
            // 利用语言驱动和配置信息，table元数据，和刚才得到的sql方法模板得到sqlSource
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
            // 最后添加到mybatis的configuration里的mappedStatements中
            return super.addDeleteMappedStatement(mapperClass, sqlMethod.getMethod(), sqlSource);
        }
    }
}

/**
 * Created by michael on 2019-03-18.
 */
@Getter
@AllArgsConstructor
public enum SqlMethod {

    DELETE_ALL("deleteAll", "删除所有表数据", "<script>DELETE FROM %s</script>");

    private final String method;

    private final String desc;

    private final String sql;
}

建表(自动建表)

基类

/**
 * Created by michael on 2019-03-09.
 */
@Data
@MappedSuperclass
public class BasicEntity<T extends Model<?>> extends Model<T> {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @TableId(value = "id", type = IdType.AUTO)
    protected Integer id;

    @TableField(value = "create_time", fill = FieldFill.INSERT)// 执行新增操作
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    protected LocalDateTime createTime;

    @TableField(value = "last_modify_time", fill = FieldFill.INSERT_UPDATE)// 执行新增更新操作
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    protected LocalDateTime lastModifyTime;
}

用户实体对象

/**
 * Created by michael on 2019-03-15.
 */
@Data
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "tb_user")
@TableName("tb_user")
public class User extends BasicEntity<User> {

    @TableField(value = "name", condition = SqlCondition.LIKE)
    private String name;

    @TableField("age")
    private Integer age;

    @Email
    @TableField(value = "email")
    private String email;

    @Column(name = "phone_type", columnDefinition = "VARCHAR(255) COMMENT '电话类型'")
    private PhoneType phoneType;

    @Column(name = "gender", columnDefinition = "VARCHAR(255) COMMENT '性别'")
    private Gender gender = Gender.UNKNOWN;
}

1. 使用Mapper

MyBatis-Plus（简称：MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

1.1 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="michael.spica.core.mapper.UserMapper">
    <select id="selectAll" resultType="michael.spica.entity.mybatisplus.User">
        SELECT * FROM tb_user
    </select>
</mapper>

1.2 UserMapper.java

/**
 * Created by michael on 2019-03-15.
 */
public interface UserMapper extends BaseMapper<User> {

    /**
     * 查询所有用户信息
     *
     * @return
     */
    List<User> selectAll();

    /**
     * 删除所有表数据
     * SQL注入器 -> 封装自定义通用SQL
     *
     * @return
     */
    int deleteAll();

    /**
     * 插入多条数据
     * SQL注入器 -> 封装自定义通用SQL『现只支持MySQL』
     *
     * @param list
     * @return
     */
    int insertBatch(@Param(value = "items") Collection list);

    @Select("SELECT * FROM tb_user WHERE `name` = #{name}")
    List<User> selectByName(@Param("name") String name);
}

1.3 UserController.java

/**
 * Created by michael on 2019-03-15.
 */
@Slf4j
@RestController
@RequestMapping("/api/mybatisplus/user")
public class UserController {

	private static final List<User> userList = Lists.newArrayList(
	    User.builder().name("Jone").age(18).gender(Gender.FEMALE).phoneType(PhoneType.CMCC).email("test01@163.com").build(),
	    User.builder().name("Jack").age(18).gender(Gender.MALE).phoneType(PhoneType.CT).email("test02@163.com").build(),
	    User.builder().name("Jim").age(19).gender(Gender.MALE).phoneType(PhoneType.CUCC).email("test03@163.com").build(),
	    User.builder().name("Tom").age(19).gender(Gender.MALE).phoneType(PhoneType.CMCC).email("test04@163.com").build(),
	    User.builder().name("Sandy").age(20).gender(Gender.FEMALE).phoneType(PhoneType.CT).email("test05@163.com").build(),
	    User.builder().name("Billie").age(20).gender(Gender.MALE).phoneType(PhoneType.CMCC).email("test06@163.com").build(),
	    User.builder().name("Alice").age(21).gender(Gender.FEMALE).phoneType(PhoneType.CUCC).email("test07@163.com").build(),
	    User.builder().name("Lucy").age(21).gender(Gender.FEMALE).phoneType(PhoneType.CMCC).email("test08@163.com").build(),
	    User.builder().name("Jane").age(22).gender(Gender.FEMALE).phoneType(PhoneType.CT).email("test09@163.com").build(),
	    User.builder().name("Nancy").age(22).gender(Gender.FEMALE).phoneType(PhoneType.CMCC).email("test10@163.com").build()
    );

    @Autowired
    private UserMapper userMapper;

    private User selectById(Serializable id) {
        return userMapper.selectById(id);
    }

    private List<User> all() {
        return userMapper.selectAll();
    }

    @PostMapping("/insert/batch")
    @ResponseBody
    public R insertBatch() {
        userList.stream().forEach(user -> userMapper.insert(user));
        return R.ok(this.all());
    }

    @PostMapping("/insert/batch2")
    @ResponseBody
    public R insertBatch2() {
        userMapper.insertBatch(userList);// 测试成功，但不建议使用，因为为容易超出SQL限制
        return R.ok(this.all());
    }

    @PostMapping
    @ResponseBody
    public R insert(@RequestBody User entity) {
        log.info("添加信息={}", entity);
        userMapper.insert(entity);
        return R.ok(entity);
    }

    @GetMapping("/all")
    @ResponseBody
    public R selectAll() {
        log.debug("在UserMapper接口中写SQL查询......");
        return R.ok(this.all());
    }

    @GetMapping("/selectByName")
    @ResponseBody
    public R selectByName(String name) {
        List<User> userList = userMapper.selectByName(name);
        return R.ok(userList);
    }

    @GetMapping("/page")
    @ResponseBody
    public R page(@Param("current") Integer current, @Param("size") Integer size) {
        Page<User> page = new Page<>(current, size);
        IPage<User> pageResult = userMapper.selectPage(page,
                new QueryWrapper<User>()
                        .lambda()
                        .between(User::getAge, 18, 22)// 年龄在18~22
                        .like(User::getName, "a")// 名字中包含“a”
                        .orderByDesc(User::getId)// 按ID降序
        );
        return R.ok(pageResult);
    }

    @GetMapping("/selectList")
    @ResponseBody
    public R selectList() {
        List<User> userList = userMapper.selectList(null);
        return R.ok(userList);
    }

    @GetMapping("/like")
    @ResponseBody
    public R queryByLike(String nameKeyword) {
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.like("name", nameKeyword);// 查询名字中包含『keyword』的用户
        List<User> userList = userMapper.selectList(queryWrapper);
        return R.ok(userList);
    }

    @GetMapping("/le")
    @ResponseBody
    public R queryByLe(Integer age) {
        QueryWrapper<User> queryWrapper = new QueryWrapper(new User());
        queryWrapper.le("age", age); // 查询年龄小于等于『age』的用户
        List<User> userList = userMapper.selectList(queryWrapper);
        return R.ok(userList);
    }

    @GetMapping("/lambda")
    @ResponseBody
    public R queryByLambda(@Param("constructorNumber") Integer constructorNumber,
                           @Param("nameKeyword") String nameKeyword,
                           @Param("age") Integer age) {
        List<User> userList = new ArrayList<>();
        /**
         * Lambda条件构造器
         * MybatisPlus提供了4种方式创建lambda条件构造器，前三种分别是这样的
         */
        LambdaQueryWrapper<User> lambdaQueryWrapper = null;
        switch (constructorNumber) {
            case 1:
                lambdaQueryWrapper = new QueryWrapper<User>().lambda();
                lambdaQueryWrapper.lt(User::getAge, age); // 查询年龄小于『age』的用户
                userList = userMapper.selectList(lambdaQueryWrapper);
                break;
            case 2:
                lambdaQueryWrapper = new LambdaQueryWrapper<>();
                lambdaQueryWrapper.lt(User::getAge, age); // 查询年龄小于『age』的用户
                userList = userMapper.selectList(lambdaQueryWrapper);
                break;
            case 3:
                lambdaQueryWrapper = Wrappers.lambdaQuery();
                lambdaQueryWrapper.lt(User::getAge, age); // 查询年龄小于『age』的用户
                userList = userMapper.selectList(lambdaQueryWrapper);
                break;
            case 4:
                LambdaQueryChainWrapper<User> lambdaQueryChainWrapper = new LambdaQueryChainWrapper<>(userMapper)
                        .lt(User::getAge, age)// 查询年龄小于『age』的用户
                        .like(StrHelper.isNotEmpty(nameKeyword), User::getName, nameKeyword);// 名字中包含『nameKeyword』
                userList = lambdaQueryChainWrapper.list();
                break;
            default:
                break;
        }
        return R.ok(userList);
    }

    @GetMapping("/nesting")
    @ResponseBody
    public R queryByNesting(@Param("createTime") String createTime, @Param("nameKeyword") String nameKeyword) {
        log.debug("嵌套查询......");
        QueryWrapper<User> queryWrapper = new QueryWrapper(new User());
        queryWrapper
                .apply("date_format(create_time,'%Y-%m-%d')={0}", createTime)// 日期查询使用的是占位符的形式进行查询，目的就是为了防止SQL注入的风险
                .inSql("id", "select id from tb_user where name like '%" + nameKeyword + "%'");
        List<User> userList = userMapper.selectList(queryWrapper);
        return R.ok(userList);
    }

    @GetMapping("/specified/column")
    @ResponseBody
    public R queryBySpecifiedColumn(String nameKeyword) {
        log.debug("查询指定部分列......");
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.select("name", "email").like("name", nameKeyword);
        List<User> userList = userMapper.selectList(queryWrapper);
        return R.ok(userList);
    }

    @GetMapping("/filter")
    @ResponseBody
    public R queryByFilter() {
        log.debug("使用过滤器查询指定列......");
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(User.class, info -> !info.getColumn().equals("create_time")
                && !info.getColumn().equals("last_modify_time")
                && !info.getColumn().equals("id"));
        List<User> userList = userMapper.selectList(queryWrapper);
        return R.ok(userList);
    }

    @GetMapping("/annotation")
    @ResponseBody
    public R queryByAnnotation(String nameKeyword) {
        log.debug("通过『数据库表对应的实体类中相应的属性标注注解』查询......");
        User user = new User();
        user.setName(nameKeyword);
        QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
        List<User> userList = userMapper.selectList(queryWrapper);
        return R.ok(userList);
    }

    @PutMapping("update")
    @ResponseBody
    public R update(@Param("id") Integer id, @Param("email") String email) {
        log.debug("根据主键ID将Email修改为指Email......");
        // where条件
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("id", id);
        // set值
        User user = new User();
        user.setEmail(email);
        userMapper.update(user, updateWrapper);
        return R.ok(this.selectById(id));
    }

    @PutMapping("/update2")
    @ResponseBody
    public R update2(@Param("id") Integer id, @Param("email") String email) {
        // TODO-MICHAEL: 2019-03-23 11:43:32 修改时，修改时间及修改人，不会自动填充『因为entity为空』
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("id", id).set("email", email);
        userMapper.update(null, updateWrapper);
        return R.ok(this.selectById(id));
    }

    @PutMapping("/update3")
    @ResponseBody
    public R update3(@Param("id") Integer id, @Param("email") String email) {
        User user = userMapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getId, id));
        user.setEmail(email);
        userMapper.updateById(user);
        return R.ok(user);
    }

    @PutMapping("/update4")
    @ResponseBody
    public R update4(@Param("id") Integer id, @Param("email") String email) {
        // TODO-MICHAEL: 2019-03-23 11:48:17 修改时，修改时间及修改人，不会自动填充『因为entity为空』
        LambdaUpdateWrapper<User> lambdaUpdateWrapper = Wrappers.lambdaUpdate();
        lambdaUpdateWrapper.eq(User::getId, id).set(User::getEmail, email);
        userMapper.update(null, lambdaUpdateWrapper);
        return R.ok(this.selectById(id));
    }

    @PutMapping("/update5")
    @ResponseBody
    public R update5(@RequestBody User entity) {
        // TODO-MICHAEL: 2019-03-23 11:49:15 修改时，修改时间及修改人，不会自动填充『因为entity为空』
        Integer id = entity.getId();

        LambdaUpdateWrapper<User> lambdaUpdateWrapper = Wrappers.lambdaUpdate();
        lambdaUpdateWrapper.eq(User::getId, id)
                .set(StrHelper.isNotEmpty(entity.getEmail()), User::getEmail, entity.getEmail())
                .set(StrHelper.isNotEmpty(entity.getName()), User::getName, entity.getName())
                .set(StrHelper.isNotEmpty(entity.getAge()), User::getAge, entity.getAge())
                .set(StrHelper.isNotEmpty(entity.getPhoneType()), User::getPhoneType, entity.getPhoneType())
                .set(StrHelper.isNotEmpty(entity.getGender()), User::getGender, entity.getGender());

        userMapper.update(null, lambdaUpdateWrapper);

        return R.ok(this.selectById(id));
    }

    @PutMapping("update/batch")
    @ResponseBody
    public R updateBatch() {
        // TODO: 2019-03-23 11:49:59 批量更新功能暂未实现
        return R.ok("");
    }

    @DeleteMapping("/{id}")
    public R delete(@PathVariable("id") Integer id) {
        userMapper.deleteById(id);
        return R.ok("");
    }

    @DeleteMapping("/delete/all")
    public R deleteAll() {
        userMapper.deleteAll();
        return R.ok("");
    }

2. AR(即：ActiveRecord)

/**
 * Created by michael on 2019-03-18.
 */
@Slf4j
@RestController
@RequestMapping("/api/mybatisplus/ar/member")
public class MemberController {

    @PostMapping
    @ResponseBody
    public R insert(@RequestBody Member entity) {
        log.debug("AR插入操作......");
        Member member = new Member();
        member.setName(entity.getName());
        member.setAge(entity.getAge());
        member.setRegisterTime(LocalDateTime.now());
        boolean insert = member.insert();
        log.debug("新增会员:{}", insert);
        return R.ok(member);
    }

    @PutMapping
    @ResponseBody
    public R update(@RequestBody Member entity) {
        log.debug("AR修改操作......");
        boolean update = entity.updateById();
        log.debug("修改会员:{}", update);
        return R.ok(entity.selectById(entity.getId()));
    }

    @GetMapping
    @ResponseBody
    public R select(@Param("type") Integer type, @Param("id") Integer id) {
        log.debug("AR查询操作......");
        Member member = new Member();
        switch (type) {
            case 1:
                // 1.根据id查询
                // 方式一
                member.setId(id);
                member = member.selectById();
                // 方式二
//                member = member.selectById(id);
                return R.ok(member);
            case 2:
                // 2.查询所有
                List<Member> memberList = member.selectAll();
                return R.ok(memberList);
            case 3:
                // 3.根据条件查询
                List<Member> memberList2 = member.selectList(new QueryWrapper<Member>().like("name", "刘"));
                return R.ok(memberList2);
            case 4:
                // 4.查询符合条件的总数
                int result = member.selectCount(new QueryWrapper<Member>().like("name", "刘"));
                return R.ok(result);
            default:
                return R.ok("");
        }
    }

    @DeleteMapping
    @ResponseBody
    public R delete(@Param("type") Integer type, @Param("id") Integer id) {
        log.debug("AR删除操作......");
        Member member = new Member();
        switch (type) {
            case 1:
                // 1.根据id删除数据
                // 方式一
                member.setId(id);
                boolean delete = member.deleteById();
                // 方式二
//                boolean delete = member.deleteById(id);
                return R.ok(delete);
            case 2:
                // 2.根据条件删除
                boolean result = member.delete(new QueryWrapper<Member>().eq("id", id));
                return R.ok(result);
            default:
                return R.ok("");
        }
    }

    /**
     * AR：ActiveRecord(活动记录)
     * 注：
     * 这个分页方法和BaseMapper提供的分页一样都是内存分页，并非物理分页，因为sql语句中没用limit，和BaseMapper的selectPage方法一样，配置了分页插件后就可以实现真正的物理分页。
     * AR的分页方法与BaseMapper提供的分页方法不同的是，BaseMapper的selectPage方法返回值是查询到的记录的list集合，而AR的selectPage方法返回的是page对象，该page对象封装了查询到的信息，可以通过getRecords方法获取信息。
     *
     * @param current
     * @param size
     * @return
     */
    @GetMapping("/page")
    @ResponseBody
    public R page(@Param("current") Integer current, @Param("size") Integer size) {
        log.debug("AR分页操作......");
        Member member = new Member();
        IPage<Member> page = member.selectPage(new Page<>(current, size), null);
        return R.ok(page);
    }

3.BaseMapper与IService的三种写法

3.1 普通写法

/**
 * Created by michael on 2019-04-15.
 */
@Service
public class UserService extends ServiceImpl<UserMapper, User> {

}

3.2 将`Mapper`直接写在`Service`里面(无需创建`Mapper`文件)

/**
 * Created by michael on 2019-04-15.
 */
@Service
public class UserService2 extends ServiceImpl<UserService2.UserMapper2, User> {

    public interface UserMapper2 extends BaseMapper<User> {

        @Select("SELECT * FROM tb_user WHERE `name` = #{name}")
        List<User> selectByName(@Param("name") String name);

        @Select("SELECT * FROM tb_user ORDER BY id DESC")
        List<User> selectUserList(IPage<User> page);
    }
}

3.3 通过`Service`暴露`Mapper`(需要创建对应的`Mapper`文件)

/**
 * Created by michael on 2019-04-15.
 */
@Service
public class UserService3 extends ServiceImpl<UserMapper, User> {

    public UserMapper userMapper3() {
        return this.baseMapper;
    }
}