MybatisPlus基本使用

MybatisPlus基本使用


官网

前言

在只使用mybatis框架作为DAO层开发时,会创建大量的Mapper.java接口、Mapper.xml配置,而且接口、配置中的分别的CRUD大致都一样,导致项目中的代码、文件很臃肿,难以维护。那有什么办法解决呢?


简介

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


mybatis与mybatis-plus的关系

MybatisPlus基本使用


mybatis-plus架构

MybatisPlus基本使用


mybatis与mybatis-plus优缺点对比

MyBatis

优点:

  • SQL语句可根据需求*编写,较为灵活

  • SQL与业务代码分离,易于阅读与维护

  • 提供动态SQL语句,可以根据需求灵活控制

缺点:

  • 简单的crud操作也必须提供对应SQL语句

  • 必须维护大量的xxxMapper.xml文件

  • 自身功能有限,要拓展只能依赖第三方插件(pagehelper)

MyBatis-plus 是在Mybatis的基础上进行二次开发,具有MyBatis所有功能,还弥补了mybatis的缺点

  • 提供无sql 的crud操作

  • 内置代码生成器,分页插件, 性能分析插件等

  • 提供功能丰富的条件构造器快速进行无sql开发


入门案例

  1. 搭建springboot项目
  2. 导入依赖包
<!-- mybatis-plus启动器 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.16</version>
    <scope>provided</scope>
</dependency>

<!-- druid连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>

<!-- mysql连接驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>
  1. 数据库创建表
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`admin` bit(1) DEFAULT NULL,
`dept_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1327139013313564674 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE `department` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表示部门编号,由两位数字所组成',
  `name` varchar(14) DEFAULT NULL COMMENT '部门名称,最多由14个字符所组成',
  `sn` varchar(13) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1. 创建实体类
@Getter
@Setter
@ToString
public class Employee {
    private Long id;
    private String username;
    private String password;
    private String email;
    private int age;
    private Boolean admin;
    private Long deptId;
}
  1. 创建mapper接口,继承BaseMapper
public interface EmployeeMapper extends BaseMapper<Employee> {
}
  1. 配置文件(application.properties)中配置连接四要素
#mysql
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis-plus?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  1. 启动类中扫描mapper接口
@MapperScan(basePackages = "mapper包路径")
  1. 新建测试类
@SpringBootTest
public class CRUDTest {

    @Autowired
    private EmployeeMapper employeeMapper;

    @Test
    public void testSave(){
        Employee employee = new Employee();
        employee.setUsername("xiaoming");
        employee.setPassword("123");
        employee.setAdmin(true);
        employee.setAge(17);
        employee.setEmail("xiaoming@qq.com");
        employee.setDeptId(1L);
        employeeMapper.insert(employee);
    }
}

好的,一个简单的mybatis-plus新增案例完成了,执行后,数据库增加一条数据。


提出问题

为什么EmployeeMapper中没有编写insert方法,但可以执行成功?

进入mapper继承的BaseMapper类中,发现里面有我们调用的方法,但方法中没有具体实现。

MybatisPlus基本使用

/**
* 插入一条记录
*
* @param entity 实体对象
*/
int insert(T entity);

为什么不写sql语句也能实现sql操作呢?

EmployeeMapper接口定义的时候需要明确指定操作的实体对象:Employee

public interface EmployeeMapper extends BaseMapper<Employee> {
}

mybatis-plus底层通过反射解析该实体类对象,获取实体类对象的类名、字段,作为拼接sql语句的表名和字段名。然后根据调用的方法,拼接出对应的sql语句。

注意:数据库的列名要和实体类的字段名一样,表名和实体类名一样

如果不一样怎么办?看看几个常用的注解!


常用注解

  • @TableName

描述:表名注解

作用:指定当前实体类映射哪张数据库表, 默认是跟实体类名一致

/*
	实体类中贴上@TableName(value = "employee")
	表示该实体类的类名映射到表名为employee的表
*/
@Getter
@Setter
@ToString
@TableName(value = "employee")
public class Employee {
    // ...
}
  • @TableId

描述:字段注解(主键)

作用:指定当前属性映射数据库表哪一列, 默认是跟属性名一致

/*
	在主键字段上贴@TableId(value = "id",type = IdType.AUTO)
	value:表示该字段名映射到表中的列名
	type:
		IdType.AUTO:表示该字段使用自增长
*/
@Getter
@Setter
@ToString
@TableName(value = "employee")
public class Employee {
    @TableId(value = "id",type = IdType.AUTO)
    private Long id;
    private String username;
    private String password;
    private String email;
    private int age;
    private Boolean admin;
    private Long deptId;
}
  • @TableField

描述:字段注解(非主键)

作用:指定当前属性映射数据库表哪一列, 默认是跟属性名一致

/*
	在字段上贴@TableField(value = "username",exist = false)
	value:表示该字段名映射到表中的列名
	exist:表示当前属性是否映射数据库列
*/
@Getter
@Setter
@ToString
@TableName(value = "employee")
public class Employee {
    @TableId(value = "id",type = IdType.AUTO)
    private Long id;
    @TableField(value = "username")
    private String name;
    private String password;
    private String email;
    private int age;
    // 当表中不存在此列时使用exist = false
    @TableField(value = "username",exist = false)
    private Boolean admin;
    private Long deptId;
}

注意:为了能演示效果,这里把username改成了name!!!


Mapper接口

配置文件配置sql日志

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl


所有方法中参数的T泛型类型是由EmployeeMapper继承的BaseMapper<Employee> 这里指定的。



方法

/**
 * 插入一条记录
 *
 * @param entity 实体对象
 */
int insert(T entity);

使用

@Test
public void testSave(){
    Employee employee = new Employee();
    employee.setName("xiaoming");
    employee.setPassword("123");
    employee.setAdmin(true);
    employee.setAge(17);
    employee.setEmail("xiaoming@qq.com");
    employee.setDeptId(1L);
    // INSERT INTO employee ( username, password, email, age, dept_id ) VALUES ( ?, ?, ?, ?, ? )
    employeeMapper.insert(employee);
}


方法

/**
 * 根据 ID 删除
 *
 * @param id 主键ID
 */
int deleteById(Serializable id);

/**
 * 根据 columnMap 条件,删除记录
 *
 * @param columnMap 表字段 map 对象
 */
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

/**
 * 根据 entity 条件,删除记录
 *
 * @param wrapper 实体对象封装操作类(可以为 null)
 */
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);

/**
 * 删除(根据ID 批量删除)
 *
 * @param idList 主键ID列表(不能为 null 以及 empty)
 */
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

使用

// 删除单条数据
// 根据id值删除数据
@Test
public void testDelete1() {
    // DELETE FROM employee WHERE id=?
    employeeMapper.deleteById(1);
}

// 删除多条数据
// 根据id集合批量删除数据
@Test
public void testDelete2() {
    // DELETE FROM employee WHERE id IN ( ? , ? )
    employeeMapper.deleteBatchIds(Arrays.asList(2, 3));
}

// 删除多条数据
// HashMap<列, 值>,批量匹配列上的值删除数据
// 拼接条件,多个用and拼接
@Test
public void testDelete3() {
    HashMap<String, Object> map = new HashMap<>();
    map.put("id", 4);
    map.put("username", "xiaoming");
    // DELETE FROM employee WHERE id = ? AND username = ?
    employeeMapper.deleteByMap(map);
}

// 删除多条数据
// Wrapper:只有两种类型,update和query
// 更新使用update,其余使用query
// 泛型指定操作表对应的实体类
// eq:拼接条件,多个用and拼接
@Test
public void testDelete4() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.eq("id", 5);
    wrapper.eq("username", "xiaoming");
    // DELETE FROM employee WHERE (id = ? AND username = ?)
    employeeMapper.delete(wrapper);
}


方法

/**
 * 根据 ID 修改
 *
 * @param entity 实体对象
 */
int updateById(@Param(Constants.ENTITY) T entity);

/**
 * 根据 whereEntity 条件,更新记录
 *
 * @param entity        实体对象 (set 条件值,可以为 null)
 * @param updateWrapper 实体对象封装操作类(可以为 null,里面的 entity 用于生成 where 语句)
 */
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);

注意:先把实体类的@TableField(exist = false)去掉,不然修改就为null了

使用

// 修改所有的字段
@Test
public void testUpdate1() {
    Employee employee = new Employee();
    employee.setId(6L);
    employee.setName("daming");
    employee.setPassword("123");
    employee.setAdmin(true);
    employee.setAge(17);
    employee.setEmail("daming@qq.com");
    employee.setDeptId(1L);
    // UPDATE employee SET username=?, password=?, email=?, age=?, dept_id=? WHERE id=?
    employeeMapper.updateById(employee);
}

// 根据id修改部分字段
@Test
public void testUpdate2() {
    Employee employee = new Employee();
    employee.setId(1L);
    employee.setName("xiaoming");
    
    // UPDATE employee SET username=?, age=? WHERE id=?
    // 除了age是基本类型,其他都是包装类型
    // 拼接sql时,所有非null 字段都进行set 拼接
    employeeMapper.updateById(employee);
}

// 使用updateById删除部分字段时出现问题:拼接sql时,所有非null 字段都进行set 拼接,有些没指定修改的字段也被修改了
// 解决办法有两个

// 解决修改部分字段遇到的问题,方式一
// 把实体类的字段修改成包装类
@Test
public void testUpdate3() {
    Employee employee = new Employee();
    employee.setId(1L);
    employee.setName("daming");
    // UPDATE employee SET username=? WHERE id=?
    employeeMapper.updateById(employee);
}

// 解决修改部分字段遇到的问题,方式二
// 使用update方法:
// 参数一:通过对象方式指定要更新列与数据
// 参数二:指定更新查询条件
// 这里参数一不使用,在wrapper中指定要修改的列和修改条件
@Test
public void testUpdate4() {
    UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
    wrapper.eq("id", 1L);
    wrapper.set("username", "xiaoming");
    // UPDATE employee SET username=? WHERE (id = ?)
    employeeMapper.update(null, wrapper);
}

// 使用参数一的话还是不能解决上述问题,只是实现多条件更新
@Test
public void testUpdate5() {
    Employee employee = new Employee();
    UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
    wrapper.eq("id", 1L);
    employee.setName("daming");
    employeeMapper.update(employee, wrapper);
    // UPDATE employee SET username=?, age=? WHERE (id = ?)
}


方法

/**
 * 根据 ID 查询
 *
 * @param id 主键ID
 */
T selectById(Serializable id);

/**
 * 查询(根据ID 批量查询)
 *
 * @param idList 主键ID列表(不能为 null 以及 empty)
 */
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

/**
 * 查询(根据 columnMap 条件)
 *
 * @param columnMap 表字段 map 对象
 */
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

/**
 * 根据 entity 条件,查询一条记录
 *
 * @param queryWrapper 实体对象封装操作类(可以为 null)
 */
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 Wrapper 条件,查询总记录数
 *
 * @param queryWrapper 实体对象封装操作类(可以为 null)
 */
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 entity 条件,查询全部记录
 *
 * @param queryWrapper 实体对象封装操作类(可以为 null)
 */
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 Wrapper 条件,查询全部记录
 *
 * @param queryWrapper 实体对象封装操作类(可以为 null)
 */
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 Wrapper 条件,查询全部记录
 * <p>注意: 只返回第一个字段的值</p>
 *
 * @param queryWrapper 实体对象封装操作类(可以为 null)
 */
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 entity 条件,查询全部记录(并翻页)
 *
 * @param page         分页查询条件(可以为 RowBounds.DEFAULT)
 * @param queryWrapper 实体对象封装操作类(可以为 null)
 */
<E extends IPage<T>> E selectPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 Wrapper 条件,查询全部记录(并翻页)
 *
 * @param page         分页查询条件
 * @param queryWrapper 实体对象封装操作类
 */
<E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

使用

// 单条查询
// 根据id查询数据
@Test
public void testSelect1() {
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE id=?
   employeeMapper.selectById(1L);
}

// 多条查询
// 根据id集合批量查询数据
@Test
public void testSelect2() {
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE id IN ( ? , ? )
    List<Employee> employees = employeeMapper.selectBatchIds(Arrays.asList(1L, 2L));
}

// 多条查询
// HashMap<列, 值>,批量匹配列上的值删除数据
// 拼接条件,多个用and拼接
@Test
public void testSelect3() {
    HashMap<String, Object> map = new HashMap<>();
    map.put("id", 1L);
    map.put("username", "admin");
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE id = ? AND username = ?
    List<Employee> employees = employeeMapper.selectByMap(map);
}

// 查询符合条件的数量
@Test
public void testSelect4() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.eq("id", 1L);
    wrapper.eq("username", "admin");
    // SELECT COUNT( 1 ) FROM employee WHERE (id = ? AND username = ?)
    Integer count = employeeMapper.selectCount(wrapper);
}

// 多条查询
// 根据条件查询
@Test
public void testSelect5() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.eq("username", "admin");
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (username = ?)
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}


// 多条查询
// 当查询出来的数据不能封装成对象时,可以使用selectMaps查询
@Test
public void testSelect6() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    // 没有条件查询所有
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee
    List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
    for (Map<String, Object> map : maps) {
        System.out.println(map);
    }
}
// 分页查询
// 先在启动类中配置mybatis-plus分页拦截器
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
    paginationInnerInterceptor.setOverflow(true); //合理化
    interceptor.addInnerInterceptor(paginationInnerInterceptor);
    return interceptor;
}

@Test
public void testSelect7() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    IPage<Employee> page = new Page<>(2,3);
    // 返回的值就是参数中的page
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee LIMIT ?
    employeeMapper.selectPage(page, wrapper);

    System.out.println("当前页:" + page.getCurrent());
    System.out.println("总页数:" + page.getPages());
    System.out.println("当前显示数据:" + page.getRecords());
    System.out.println("每页显示记录数:" + page.getSize());
    System.out.println("总条数:" + page.getTotal());
}

注意:

List<T> selectList(@Param(Constants.WRAPPER) Wrapper queryWrapper);

List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper queryWrapper);

开发选用:这两个所需参数一样,返回值不一样,一个返回List,一个返回Map。查询返回的数据无法封装到对象中时,就需要用Map封装,用不了List<T>去封装


条件构造器

条件构造器:可以理解为mybatis中的动态SQL或者理解为SQL语句片段<sql></sql>

看看条件构造器的继承体系

MybatisPlus基本使用

更新使用UpdateWrapper、LambdaUpdateWrapper,其余使用QueryWrapper、LambdaQueryWrapper


通过Wrappers获取条件构造器(列举部分,还有很多重载方法)

不要纠结它的格式,泛型写上需要被操作的实体类

Wrappers.<Employee>query();
Wrappers.<Employee>update();
Wrappers.<Employee>lambdaQuery();
Wrappers.<Employee>lambdaUpdate();

wapper介绍 :

  • Wrapper : 条件构造抽象类,最顶端父类,抽象类中提供4个方法西面贴源码展示
  • AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
  • AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
  • LambdaQueryWrapper :看名称也能明白就是用于Lambda语法使用的查询Wrapper
  • LambdaUpdateWrapper : Lambda 更新封装Wrapper
  • QueryWrapper : Entity 对象封装操作类,不是用lambda语法
  • UpdateWrapper : Update 条件封装,用于Entity对象更新操作

AbstractWrapper具有它们一些共同的条件方法

共同的条件方法


参数说明:

boolean condition:该条件是否加入最后生成的sql中

params : key为数据库字段名,value为字段值

null2IsNull : 为true则在mapvaluenull时调用 isNull 方法,为false时则忽略valuenull

filter : 过滤函数,是否允许字段传入比对条件中


allEq、eq、ne(是否等于)

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
    
allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) 
@Test
public void testSelect1() {
    QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
    Map<String, Object> map = new HashMap<>();
    map.put("id",2);
    map.put("username","xiaoming");
    map.put("email",null);
    wrapper.allEq(true,map,true);
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (id = ? AND email IS NULL AND username = ?)
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
eq(R column, Object val)
eq(boolean condition, R column, Object val)
ne(R column, Object val)
ne(boolean condition, R column, Object val)
@Test
public void testSelect2() {
    QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
    // eq
    // wrapper.eq(true,"username","xiaoming");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username = ?)
    
    // ne
    wrapper.ne(true,"username","dafei");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username <> ?)
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

gt、ge、lt、le(大小于)

gt(R column, Object val)
gt(boolean condition, R column, Object val)
ge(R column, Object val)
ge(boolean condition, R column, Object val)
lt(R column, Object val)
lt(boolean condition, R column, Object val)
le(R column, Object val)
le(boolean condition, R column, Object val)
@Test
public void testSelect3() {
    QueryWrapper<Employee> wrapper = Wrappers.query();
    // gt lt
    //  wrapper.gt(true,"age",10).lt(true,"age",30);
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (age > ? AND age < ?)
    
    // ge le
    wrapper.ge(true,"age",10).le(true,"age",30);
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (age >= ? AND age <= ?)
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

between、notBetween(范围查询)

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)
notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)
@Test
public void testSelect4(){
    QueryWrapper<Employee> wrapper = Wrappers.query();
    // between
    //wrapper.between(true,"age",20,30);
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (age BETWEEN ? AND ?)

    // notBetween
    wrapper.notBetween(true,"age",20,30);
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (age NOT BETWEEN ? AND ?)

    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

like、notLike、likeLeft、likeRight(模糊查询)

like(R column, Object val)
like(boolean condition, R column, Object val)
notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)
likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
@Test
public void testSelect5(){
    QueryWrapper<Employee> wrapper = Wrappers.query();

    // like
    //wrapper.like(true,"username","f");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE '%f%')

    // not like
    //wrapper.notLike(true,"username","f");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username NOT LIKE '%f%');

    // likeLeft
    //wrapper.likeLeft(true,"username","f");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE '%f');

    // likeRight
    wrapper.likeRight(true,"username","f");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE 'f%');

    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

isNull、isNotNull(是否为Null)

isNull(R column)
isNull(boolean condition, R column)
isNotNull(R column)
isNotNull(boolean condition, R column)
@Test
public void testSelect6(){
    QueryWrapper<Employee> wrapper = Wrappers.query();
    // isNull
    //wrapper.isNull(true,"email");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (email IS NULL)

    // isNotNull
    wrapper.isNotNull(true,"email");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (email IS NOT NULL)

    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

in、notIn、inSql、notInSql(是否包含)

in 和 notIn 是用于字段范围

inSql 和 notInSql 是用于子查询

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)
in(R column, Object... values)
in(boolean condition, R column, Object... values)
notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)
notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)
inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)
notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)
@Test
public void testSelect7(){
    QueryWrapper<Employee> wrapper = Wrappers.query();
    // in
    //wrapper.in("id",1,2,3);
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id IN (?,?,?))

    // notIn
    //wrapper.notIn("id",1,2,3);
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id NOT IN (?,?,?))

    // inSql
    //wrapper.inSql("id","select id from employee where age > 20 and age < 40");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id IN (select id from employee where age > 20 and age < 40))

    // inSql
    wrapper.notInSql("id","select id from employee where age > 20 and age < 40");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id NOT IN (select id from employee where age > 20 and age < 40))

    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

groupBy、having(分组、过滤)

having:参数一是条件语句,参数二是条件语句中所需值

条件语句使用{?}作占位符,与参数二顺序对应

groupBy(R... columns)
groupBy(boolean condition, R... columns)
having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)
@Test
public void testSelect8(){
    QueryWrapper<Employee> wrapper = Wrappers.query();

    // groupBy
    //wrapper.groupBy("dept_id");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee GROUP BY dept_id;
    wrapper.groupBy("dept_id").select("username","count(1) count").having("count > {0} and count < {1}",1,5);
    // SELECT username,count(1) count FROM employee GROUP BY dept_id HAVING count > 1 and count < 5;

    List<Employee> employeeList = employeeMapper.selectList(wrapper);

}

orderByAsc、orderByDesc、orderBy (排序)

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)
@Test
public void testSelect9() {
    QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
    // 根据age升序
    wrapper.orderByAsc("age");
    // 根据id降序
    wrapper.orderByDesc("id");
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id DESC
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
orderBy(boolean condition, boolean isAsc, R... columns)

isAsc:true表示升序(Asc),false表示降序(Desc)

@Test
public void testSelect10() {
    QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
    wrapper.orderBy(true,true,"age","id");
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
    
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

当对多个列进行同一种排序时可使用orderBy统一指定,不需要一个列使用一个orderByAscorderByDesc


func

func 方法(主要方便在出现if…else下调用不同方法能不断链)

func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)

or、and(或与条件)

or()
or(boolean condition)
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)
and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)
@Test
public void testSelect11(){
    QueryWrapper<Employee> wrapper = Wrappers.query();

    // or
    //wrapper.eq("id",1).or().eq("id",2).or().eq("id",3);
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id = 1 OR id = 2 OR id = 3);

    //and用于解决a and (b or c)问题
    
    // and
    // w:QueryWrapper对象
    wrapper.like("username","李").and(
            w -> w.ge("age",20).
                    or().
                    le("age",30)
    );
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE '%李%' AND (age >= 20 OR age <= 30));

    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

nested(嵌套)

nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)
@Test
public void testSelect12(){
    QueryWrapper<Employee> wrapper = Wrappers.query();
    wrapper.eq("username","xiaoming").nested(
            w -> w.gt("age",10).or().lt("age",20)
    );
    // 正常嵌套 不带 AND 或者 OR,也可以解决上面or出现的问题
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (username = ? AND (age > ? OR age < ?))
    List<Employee> employeeList = employeeMapper.selectList(wrapper);

}

apply(拼接 sql)

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

注意事项:

该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!

@Test
public void testSelect13() {
    QueryWrapper<Employee> wrapper = Wrappers.query();
    wrapper.apply("age > {0} and age < {1}", "18", "28");
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (age > ? and age < ?)
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

last(sql拼接)

last(String lastSql)
last(boolean condition, String lastSql)

无视优化规则直接拼接到 sql 的最后

注意事项:

只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

@Test
public void testSelect14() {
    QueryWrapper<Employee> wrapper = Wrappers.query();
    wrapper.last(true,"limit 1,2");
    // SELECT id,username AS name,password,email,age,admin,dept_id FROM employee limit 1,2
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

exists、notExists(是否存在)

EXISTS 指定一个子查询,检测 行 的存在。

语法:EXISTS(子查询)

参数: 子查询 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)

结果类型: 如果子查询包含行,则返回 TRUE ,否则返回 FLASE

  • 在子查询中使用 NULL 仍然返回结果集
    select * from employee where exists(select null)
    等同于: select * from employee

  • 比较使用 EXISTS 和 IN 的查询。注意两个查询返回相同的结果。

    select * from employee where exists(select name from department where name=“财务部”)

    select * from employee where dept_id in(select id from department)

    EXISTS:子查询只要存在行,就查询employee 表的所有数据

    In:子查询存在什么dept_id ,就查询对应的那条数据出来,employee 与department需要有关联的列

  • 比较使用 EXISTS 和 = ANY 的查询。注意两个查询返回相同的结果。

    SELECT * FROM employee where dept_id in (SELECT id from department)
    SELECT * FROM employee where dept_id = any(SELECT id from department)

    结果一样的,any里面只能使用子查询,in里面还可以使用(1,2,3)数组

exists(String existsSql)
exists(boolean condition, String existsSql)
notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)
@Test
public void testSelect15() {
    QueryWrapper<Employee> wrapper = Wrappers.query();

    // exists
    //wrapper.exists("select id from employee where age = 19");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (EXISTS (select id from employee where age = 19))

    // not Exists
    wrapper.notExists("select id from employee where age = 19");
    // SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (NOT EXISTS (select id from employee where age = 19))

    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

开发选用

EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。


独有的条件方法


更新(设置 SET 部分 SQL)

set
set(String column, Object val)
set(boolean condition, String column, Object val)
@Test
public void testUpdate16() {
    UpdateWrapper<Employee> updateWrapper = Wrappers.update();
    updateWrapper.set(true,"age",18).eq("id",3);
    // UPDATE employee SET age=? WHERE (id = ?)
    employeeMapper.update(null,updateWrapper);
}
setSql(参数写Sql)
setSql(String sql)
setSql(boolean condition, String sql)
@Test
public void testUpdate17() {
    UpdateWrapper<Employee> updateWrapper = Wrappers.update();
    updateWrapper.setSql(true,"age = 18 where id = 3");
    // UPDATE employee SET age = 18 where id = 3
    employeeMapper.update(null,updateWrapper);
}

查询(设置查询字段)

select

说明:

方法分为两类.
第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper内的entity属性有值! 这两类方法重复调用以最后一次为准

select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)
@Test
public void testSelect18() {
    QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
    // 查询指定的列
    wrapper.select("id","username", "age");
    // SELECT id,username,age FROM employee
    List<Employee> employeeList = employeeMapper.selectList(wrapper);
}

到此,上面所有的条件构造器使用的都是普通的Wrapper,没有使用过Lambda类型的Wrapper

下面使用Lambda实现set和select两个条件方法

@Test
public void testUpdate19(){
    LambdaUpdateWrapper<Employee> wrapper = Wrappers.lambdaUpdate();
    wrapper.eq(Employee::getId,1).set(Employee::getName,"xiaoming");
    // UPDATE employee SET username=? WHERE (id = ?)
    employeeMapper.update(null,wrapper);
}
@Test
public void testSelect20(){
    LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery();
    wrapper.select(Employee::getId,Employee::getName,Employee::getAge);
    // SELECT id,username,age AS name FROM employee
    List<Employee> employee = employeeMapper.selectList(wrapper);
}

开发选用

一般会使用Lambda类型去实现,因为使用Employee::getId比使用"id"更容易发现编写错误,当Employee::getId使用错误时,编译阶段就能发现,而"id"要等到运行期才能发现

自定义SQL

XML

其实和使用mybatis时没有两样

public interface EmployeeMapper extends BaseMapper<Employee> {
    // 在Mapper接口中自定义方法
    List<Employee> listUseXml();
}
<?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.hao.mybatisplus.mapper.EmployeeMapper">
    <resultMap id="BaseResultMap" type="com.hao.mybatisplus.domain.Employee">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="username" property="name"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
        <result column="admin" property="admin" jdbcType="BIT"/>
        <result column="dept_id" property="deptId" jdbcType="BIGINT"/>
    </resultMap>

    <select id="listUseXml" resultMap="BaseResultMap">
        select * from employee
    </select>
</mapper>
@Test
public void testSelect(){
    List<Employee> employeeList = employeeMapper.listByXmlSingle();
}

注解

单表查询的时候还是很方便的,但要连表时就得很恶心了

public interface EmployeeMapper extends BaseMapper<Employee> {
    @Select(value = "select * from employee")
    List<Employee> listUseAnnotation();
}
@Test
public void testSelect2(){
    List<Employee> employeeList = employeeMapper.listUseAnnotation();
    System.out.println(employeeList);
}

Service接口

为什么要使用Mybatis-plus的Service接口呢?

还是和传统Mapper接口一样的原因,简单的CRUD还是重复又重复的写。

接下来看看怎么写Mybatis-plus的Service接口吧!


编写一个Service接口,继承IService<T>接口,泛型添上要被操作的实体类

public interface IEmployeeService extends IService<Employee> {
}

编写子类继承Service接口,再继承一个ServiceImpl<M, T>

参数一:要被操作的实体类Mapper接口

参数二:要被操作的实体类

@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {
}

这样就编写好Mybatis-plus的Service接口了


// 调用mybatis-plus封装好的list方法
@Test
public void testSelect2(){
    LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery();
    wrapper.select(Employee::getId,Employee::getName);
    // SELECT id,username AS name FROM employee
    List<Employee> employeeList = employeeService.list(wrapper);
}

进入list方法中

/**
 * 查询列表
 *
 * @param queryWrapper 实体对象封装操作类 {@link com.baomidou.mybatisplus.core.conditions.query.QueryWrapper}
 */
default List<T> list(Wrapper<T> queryWrapper) {
    return getBaseMapper().selectList(queryWrapper);
}

getBaseMapper():其实就是我们之前service实现类继承的ServiceImpl<EmployeeMapper, Employee> 类中的EmployeeMapper

selectList(queryWrapper):其实就是mapper接口中的方法

最终得出:service接口其实只是对mapper接口进行了再一次的封装


上面讲到一个分页的实现,现在在Service接口中实现。

Service接口没有提供合适我们的分页方法,我们需要自定义方法。

先自定义一个类封装分页所需参数。

@Getter
@Setter
public class EmployeeQuery{
    // 当前页
    private Integer currentPage = 1;
    // 每页条数
    private Integer pageSize = 1;
}
public interface IEmployeeService extends IService<Employee> {
    IPage<Employee> queryPage(EmployeeQuery employeeQuery);
}
@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {
    @Override
    public IPage<Employee> queryPage(EmployeeQuery employeeQuery) {
        IPage<Employee> objectIPage = new Page<>(employeeQuery.getCurrentPage(),employeeQuery.getPageSize());
        // 1.<E extends IPage<T>> E selectPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
        // IPage<Employee> page = super.getBaseMapper().selectPage(objectIPage, null);
        // 2.不要Wrapper(条件)的分页
        return super.page(objectIPage);
    }
}
@Test
public void testSelect(){
    EmployeeQuery employeeQuery = new EmployeeQuery();
    employeeQuery.setCurrentPage(2);
    employeeQuery.setPageSize(3);
    // 调用自定义service分页方法
    IPage<Employee> pages =  employeeService.queryPage(employeeQuery);
}

上一篇:JWT原理


下一篇:Java笔记(day12)