Springboot - Mybatis Plus

Springboot - Mybatis Plus

---

---

1. Mybatis Plus

简介

• Mybatis-Plus(简称MP)是一个Mybatis的增强工具，在Mybatis的基础上只做增强不做改变，避免了我们重复CRUD语句。

1.1 快速入门

1. 创建工程，引入依赖

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.murphy</groupId>
    <artifactId>mybatis-plus-demo-quickstart</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <mybatisplus.version>3.3.2</mybatisplus.version>
        <skipTests>true</skipTests>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.version}</version>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</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.yml

spring:
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:h2:mem:test
    username: murphy
    password: xmf123123

logging:
  level:
    com.murphy: debug

yml配置简介

• 在Springboot中，推荐使用properties或者YAML文件来完成配置，但是对于较复杂的数据结构来说，YAML又远远优于properties。我们快速介绍YAML的常见语法格式。

先来看一个Springboot中的properties文件和对应YAML文件的对比：

# properties(示例来源于Springboot User guide):
environments.dev.url=http://dev.bar.com
environments.dev.name=Developer Setup
environments.prod.url=http://foo.bar.com
environments.prod.name=My Cool App
my.servers[0]=dev.bar.com
my.servers[1]=foo.bar.com

• 可以明显的看到，在处理层级关系的时候，properties需要使用大量的路径来描述层级（或者属性），比如environments.dev.url和environments.dev.name。其次，对于较为复杂的结构，比如数组（my.servers），写起来更为复杂。而对应的YAML格式文件就简单很多：

#YAML格式 environments:
      dev:
          url: http://dev.bar.com
          name: Developer Setup
      prod:
          url: http://foo.bar.com
          name: My Cool App
my: servers:
          - dev.bar.com
          - foo.bar.com

application.yml

# DataSource Config
spring:
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:h2:mem:test
    username: root
    password: test
# Logger Config
logging:
  level:
    com.lxs.quickstart: debug

数据库脚本文件/db/data-h2.sql和/db/schema-h2.sql

• h2数据库是一个基于内存的数据库，在JVM启动时，自动执行脚本加载相应的数据
• Springboot中使用h2数据库直接按照上面配置，配置schema表结构脚本和data数据脚本即可

注意：这里用户名密码可以省略不写，或者随意设定

启动类

@SpringBootApplication
@MapperScan("com.murphy.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

实体类

@Data
public class User {

    private Long id;
    private String name;
    private Integer age;
    private String email;

}

dao

/**
 * 接口类
 *
 * @author murphy
 */
public interface UserMapper extends BaseMapper<User> {

}

测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest extends TestCase {

    @Resource
    private UserMapper userMapper;

    @Test
    public void testSelectAll() {
        List<User> list = userMapper.selectList(null);
        list.forEach(System.out :: println);
        Assert.assertEquals(7,list.size());
    }
}

1.2 常用注解

• MyBatis-Plus提供了一些注解供我们在实体类和表信息出现不对应的时候使用。通过使用注解完成逻辑上匹配。

mybatis-plus注解策略配置

如果mysql自增主键注解策略设置如下

@TableId(type = IdType.AUTO)
private Long id;

默认主键策略

   /**
	* 采用雪花算法生成全局唯一主键 
	**/
	ASSIGN_ID(3),

排除实体类中非表字段

使用@TableField(exist = false)注解

@TableField(exist = false)
private Integer userId;

主键策略参考源码IdType

1.3 内置增删改查

测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest extends TestCase {

    @Resource
    private UserMapper userMapper;

    @Test
    public void testSelectAll() {
        List<User> list = userMapper.selectList(null);
        list.forEach(System.out :: println);
//        Assert.assertEquals(6,list.size());
    }

    @Test
    public void testInsert() {
        User user = new User();
        user.setName("murphy");
        user.setAge(20);
        user.setEmail("murphy@163.com");
        Assert.assertTrue(userMapper.insert(user) > 0);
        userMapper.selectList(null).forEach(System.out :: println);
    }

    @Test
    public void testDelete() {
        // 主键删除
        userMapper.deleteById(3l);
        userMapper.selectList(null).forEach(System.out :: println);

        // 批量删除：方式一
        userMapper.delete(new QueryWrapper<User>().like("name","J"));
        userMapper.selectList(null).forEach(System.out :: println);

        // 批量删除：方式二
        userMapper.delete(Wrappers.<User>query().like("name","J"));
        userMapper.selectList(null).forEach(System.out :: println);

        // 批量删除：方式三
        userMapper.delete(Wrappers.<User>query().lambda().like(User::getName,"J"));
        userMapper.selectList(null).forEach(System.out :: println);
    }

    @Test
    public void testUpdate() {
        // 基本修改
        userMapper.updateById(new User().setId(1l).setName("murphy"));
        userMapper.selectList(null).forEach(System.out :: println);

        // 批量修改：方式一
        userMapper.update(null,Wrappers.<User>update().set("email","murphy@163.com").like("name","J"));
        userMapper.selectList(null).forEach(System.out :: println);

        // 批量修改：方式二
        userMapper.update(new User().setEmail("murphy@163.com"),Wrappers.<User>update().like("name","J"));
        userMapper.selectList(null).forEach(System.out :: println);
    }

    @Test
    public void testDySelect() {
        // 基本查询
        System.out.println(userMapper.selectOne(Wrappers.<User>query().eq("name","Tom")));

        // 投影查询
        userMapper.selectList(new QueryWrapper<User>().select("id","name")).forEach(user -> {
            System.out.println(user);
        });
    }
}

1.4 分页

1.4.1 内置分页

/**
 * mp配置
 *
 * @author murphy
 */
@Configuration
public class MybatisPlusConfig {
    /**
     * 分页查询
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        // 开启 count 的 join 优化，只针对 left join！
        return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
    }
}

优化left join count场景

在一对一join操作时，也存在优化可能，看下面sql

select u.id,ua.account from user u left join user_account ua on u.id=ua.uid 
# 本来生成的count语句像这样
select count(1) from (select u.id,ua.account from user u left join user_account ua on u.id=ua.uid)

这时候分页查count时，其实可以去掉left join直查user，因为user与user_account是 1 对 1 关系，如下：

查count:
select count(1) from user u
查记录:
select u.id,ua.account from user u left join user_account ua on u.id=ua.uid limit 0,50

测试

@Test
public void testPage() {
    // 参数1：页数  -  参数2：每页行数
    IPage<User> page = new Page<>(1, 6);
    // 总行数
    IPage<User> pr = userMapper.selectPage(page, Wrappers.<User>query());
    System.out.println("总行数 = " + pr.getTotal());
    System.out.println("总页数 = " + pr.getPages());
    System.out.println("每页行数 = " + pr.getSize());

    pr.getRecords().forEach(user -> System.out.println(user));
}

1.4.2 自定义xml分页

application.yml配置文件

# 配置mybatis-plus
mybatis-plus:
  # 别名搜索
  type-aliases-package: com.murphy.entity
  # 加载映射文件
  mapper-locations: classpath:/mapper/*.xml

UserMapper接口

/**
 * 接口类
 *
 * @author murphy
 */
public interface UserMapper extends BaseMapper<User> {

    /**
     * 如果映射的接口方法有2个参数需要@Param定义参数名，定义参数名后，映射文件中使用p.属性 c.属性，具体访问
     * @param page
     * @param conditioin
     * @return
     */
    public IPage<User> selectUserByPage(@Param("p") IPage<User> page, @Param("c") User conditioin);
}

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.murphy.mapper.UserMapper">

    <sql id="selectSql">
        SELECT *
        FROM user
    </sql>

    <select id="selectUserByPage" resultType="user">
        <include refid="selectSql"></include>
        <where>
            <if test="c.age !=null">
                age = #{c.age}
            </if>
            <if test="c.email !=null">
                and email like '%${c.email}%'
            </if>
        </where>
    </select>
</mapper>

测试

@Test
public void testXmlPage() {
    IPage<User> page = new Page<>(1, 6);

    // 条件对象
    User user = new User();
    user.setAge(331);
    user.setEmail("test4");

    IPage<User> pr = userMapper.selectUserByPage(page, user);
    System.out.println("总行数 = " + pr.getTotal());
    System.out.println("总页数 = " + pr.getPages());
    System.out.println("每页行数 = " + pr.getSize());

    pr.getRecords().forEach(user1 -> System.out.println(user1));
}

1.4.3 pageHelper 分页

引入pageHelper依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.11</version>
</dependency>

mybatis plus整合pageHelper的配置类

/**
 * mp配置
 *
 * @author murphy
 */
@Configuration
public class MybatisPlusConfig {
    /**
     * 分页查询
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        // 开启 count 的 join 优化，只针对 left join！
        return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
    }

    /**
     * 两个分页插件都配置，不会冲突
     * pagehelper分页插件
     */
    @Bean
    public PageInterceptor pageInterceptor() {
        return new PageInterceptor();
    }
}

映射文件

<select id="selectUserByPage2" resultType="user">
    <include refid="selectSql"></include>
    <where>
        <if test="age !=null">
            age = #{age}
        </if>
        <if test="email !=null">
            and email like '%${email}%'
        </if>
    </where>
</select>

测试

@Test
public void testPageHelper() {
    // 条件对象
    User user = new User();
    user.setAge(331);
    user.setEmail("test4");

    PageInfo<User> pageInfo = PageHelper.startPage(2, 5).doSelectPageInfo(() -> {
        // 自定义映射文件
//            userMapper.selectUserByPage2(user);
        // 使用MP内置方法
        userMapper.selectList(Wrappers.<User>query());
    });

    pageInfo.getList().forEach(System.out :: println);

    System.out.println("总行数=" + pageInfo.getTotal());
    System.out.println("当前页=" + pageInfo.getPageNum());
    System.out.println("每页行数=" + pageInfo.getPageSize());
    System.out.println("总页数=" + pageInfo.getPages());
    System.out.println("起始行数=" + pageInfo.getStartRow());
    System.out.println("是第一页=" + pageInfo.isIsFirstPage());
    System.out.println("是最后页=" + pageInfo.isIsLastPage());
    System.out.println("还有下一页=" + pageInfo.isHasNextPage());
    System.out.println("还有上一页=" + pageInfo.isHasPreviousPage());
    System.out.println("页码列表" + Arrays.toString(pageInfo.getNavigatepageNums()));
}