快速上手MyBatis的增强版MyBatis-Plus

2023-10-17 12:08:52

什么是Mybatis-Plus

是一个 MyBatis (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变

MyBatis Puls 优点 :

无需提供sql 增删改查操作
内置代码生成器分页插件等
提供功能丰富的条件构造器快速无sql开发
做一些中小项目开发效率快

缺点 :

大项目分布式项目效率慢

快速入门

导入相关依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

进行四要素配置

spring.datasource.url=jdbc:mysql://localhost:3306/mp?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 配置slq打印日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

创建mapper，继承BaseMapper<.T>接口，无需写映射文件

@Repository
public interface EmployeeMapper extends BaseMapper<Employee> {
}

创建自定义service接口，继承IService<.T>接口

创建service实现类，继承ServiceImpl<实体对象对应mapper接口，实体类对象>，并且实现自定义接口

public interface IEmployeeService extends IService<Employee> {
}

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

在启动容器时需进行包扫描

@MapperScan(basePackages = "cn.kjcoder.mp.mapper")

测试

@SpringBootTest
public class CrudTest {
    @Autowired
    private EmployeeMapper employeeMapper;
    @Test
    public void selectList(){
        List<Employee> employees = employeeMapper.selectList(null);
        System.out.println(employees);
    }
}

代码结构：

public interface UserMapper extends BaseMapper<User>{
    
}

public interface IUserService extends IService<User>{
    
}

public class UserServiceImpl extends ServiceImpl<UserMapper,User > implements IUserService{
    
}

思考

★问题1:EmployeeMapper并没有定义crud方法，为什么在测试类可以直接用呢?*

EmployeeMapper 继承了BaseMapper接口，自然可以使用父类BaseMapper接口定义crud方法了

★项目中并没有写crud sql语句，为什么可以执行crud 操作呢?

★mybatis-plus原理

mybatis-plus 自动拼接了sql，观察发现，表名跟mapper接口操作实体对象类名一样，列名跟mapper接口操作的实体对象属性名一样，那么可以使用java中的内省机制和反射，将实体对象的类解析得到类名做表名，属性字段做列名，将得到的表名和列名自动拼接成sql语句并注入到mybatis容器中

常用注解

@TableName(xxxx)：作用在类上，当表名与类名不一致时用于映射当前类对应的表名，默认跟实体类名一致

@TableId(value="id", type= IdType.AUTO)：作用在类的字段上，指定类型为自增长id策略

@TableField(value="ename")：作用在类的字段上，当类中的属性名与表中的字段名称不一致，用此注解进行映射

@TableField(exist = false):作用在类的字段上，表示当前的属性不参与sql拼接和列的映射

打印日志

方式一：

logging.level.cn.kjcoder.mp.mapper=debug

方式二：

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

Mapper接口方法

保存

insert(domain)

/**
 * 插入一条记录
 * INSERT INTO employee ( name, password, email, age, admin, dept_id ) VALUES ( ?, ?, ?, ?, ?, ? )
 */
Employee employee = new Employee();
employee.setName("lisa");
employee.setPassword("123455");
employee.setEmail("lisa@qq.com");
employee.setAge(20);
employee.setDeptId(2L);
employee.setAdmin(0);
employeeMapper.insert(employee);

更新

updateById(domain)：如果要更新的domain对象属性值为null，该属性值不参与sql拼接，如果是非null字段，mybatis-plus认为有值,就参与sql拼接

解决方案：

将基本数据类型改为包装类型

/**
 * 将id=1用户名字修改为hkj
 * UPDATE employee SET name=?, age=?, admin=? WHERE id=?
 */
Employee employee = new Employee();
employee.setId(1L);
employee.setName("gd");
employeeMapper.updateById(employee);

先查询，在设置，最后再更新

/**
 * UPDATE employee SET name=?, password=?, email=?, age=?, admin=?, dept_id=? WHERE id=?
 */
Employee employee = employeeMapper.selectById(1L);
employee.setName("hkj");
employeeMapper.updateById(employee);

使用update(null，wrapper)方法

update(null,Wrapper)：使用自定义update方式的sql语句进行部分字段更新

/**
 * 更新name=hkj员工年龄为18岁
 * UPDATE employee SET age=? WHERE (name = ?)
 */
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("name","hkj");
wrapper.set("age",18);
employeeMapper.update(null,wrapper);

***选用问题：***如果更新条件是id，并且是更新所有字段，使用updateById

如果只需更新部分字段，使用update

saveOrUpdate(domain,UpdateWapper):
当是主键自动生成的数据，一定要写UpdateWrapper，不然你必然是一直插入！完全不会进行更新，因为默认是用id查询的，当saveOrUpdate不使用条件构造器时，会先做根据主键查询，如果查出来的结果为0，那么就执行插入操作，如果查出来的结果不为0，则执行更新操作。

删除

deleteById(Serializable id)

/**
 * 删除id=21的员工信息
 * DELETE FROM employee WHERE id=?
 */
employeeMapper.deleteById(21L);

deleteBatchIds(Collection)：批量删除，类似sql语句中的in条件

/**
 * 删除id=1， id=2的员工信息
 * DELETE FROM employee WHERE id IN ( ? , ? )
 */
employeeMapper.deleteBatchIds(Arrays.asList(1L,2L));

deleteByMap(Map)：删除指定属性值的对象，map中的key为数据库字段名,value为字段值

/**
 * 删除name=xiaofei并且age=0的员工信息
 * DELETE FROM employee WHERE name = ? AND age = ?
 */
Map<String,Object> map = new HashMap<>();
map.put("name","xiaofei");
map.put("age",0);
employeeMapper.deleteByMap(map);

delete(Wrapper)：Wrapper用于指定要删除的条件

/**
 * 删除name=李六明并且age=25的员工信息
 * DELETE FROM employee WHERE (name = ? AND age = ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","李六明").eq("age",25);
employeeMapper.delete(wrapper);

查询

selectById(Serializable id)

/**
 * 查询id=3的员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id=?
 */
Employee employee = employeeMapper.selectById(3L);

selectBatchIds(Collection):批量查询，类似sql语句中的in条件

/**
 *查询id=3，id=4的员工信息
 *  SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id IN ( ? , ? )
 */
List<Employee> employees = employeeMapper.selectBatchIds(Arrays.asList(3L, 4L));

selectByMap(Map):查询指定属性值的对象，map中的key是列名，value是对应的值

/**
 * 查询name=yoonas， age=18的员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE name = ? AND age = ?
 */
Map<String,Object> map = new HashMap<>();
map.put("name", "yoonas");
map.put("age",18);
List<Employee> employees = employeeMapper.selectByMap(map);

selectCount(Wrapper)

/**
  *  查询满足条件的所有的员工个数
  *  SELECT COUNT( 1 ) FROM employee WHERE (age = ?)
  */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//employeeMapper.selectCount(null);
wrapper.eq("age",18);
Integer count = employeeMapper.selectCount(wrapper);

selectList(Wrapper)

/**
 *  查询满足条件的所有的员工信息， 返回List<Employee>
 *  SELECT id,name,password,email,age,admin,dept_id FROM employee
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//employeeMapper.selectList(null);
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);

selectMaps(Wrapper)：返回值为List<Map<String, Object>>，如果查询的数据无法封装成对象就使用该方法，类似查询的sql中含有group by…

/**
 * 查询满足条件的所有的员工信息， 返回List<Map<String, Object>>
 *  SELECT id,name,password,email,age,admin,dept_id FROM employee
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.out::println);

selectOne

/**
 * 查询name为yoonas的记录
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","yoonas");
Employee employee = employeeMapper.selectOne(wrapper);

分页

selectPage(Page,Wrapper)

要想实现分页效果，需要在启动类中配置分页拦截器

//分页拦截器
@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 testSelectPage(){
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    //参数1：当前页， 参数2：每页显示条数
    IPage<Employee> page = new Page<>(2,3);
    employeeMapper.selectPage(page,wrapper);
    System.out.println("当前页：" + page.getCurrent());
    System.out.println("每页显示条数：" + page.getSize());
    System.out.println("总页数：" + page.getPages());
    System.out.println("总数：" + page.getTotal());
    System.out.println("当前页数据：" + page.getRecords());
}

编写分页逻辑代码

//service
@Override
public IPage<Employee> query(QueryObject qo) {
    IPage<Employee> page = new Page<>(qo.getCurrentPage(),qo.getPageSize());
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    return super.page(page,wrapper);
}

//controller
@Test
public void testQuery(){
    QueryObject qo = new QueryObject();
    qo.setCurrentPage(2);
    qo.setPageSize(3);
    IPage<Employee> page = employeeService.query(qo);
    System.out.println("当前页：" + page.getCurrent());
    System.out.println("总页数：" + page.getPages());
    System.out.println("每页显示条数：" + page.getSize());
    System.out.println("总记录数：" + page.getTotal());
    System.out.println("当前页显示记录：" + page.getRecords());
}

条件构造器

可以简单理解为用于生成mybatis动态sql或sql语句片段(<sql.><./sql>)

更新操作

UpdateWrapper方式：

set:

/**
 * 将id=3的员工age改为18， 如果传入username变量值不等于null或者“”，修改为员工name为username变量值
 * UPDATE employee SET age=?,name=? WHERE (id = ?)
 */
String username = "angle";
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id", 3L);
wrapper.set("age",18);
if(StringUtils.hasLength(username)){
    wrapper.set("name",username);
}
employeeMapper.update(null,wrapper);

setSql:

/**
 * 将id=3的用户name改为yoona
 * UPDATE employee SET name = 'yoona' WHERE (id = ?)
 */
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id",3L);
wrapper.setSql("name = 'yoona'");
employeeMapper.update(null,wrapper);

LambdaUpdateWrapper方式：

/**
 * 将id=3的用户name改为hkj
 * UPDATE employee SET name=? WHERE (id = ?)
 */
LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(Employee::getId,3);
wrapper.set(Employee::getName,"hkj");
employeeMapper.update(null,wrapper);

查询操作

/** 普通查询
 * 查询name=dafei， age=18的员工
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE name = ? AND age = ?
 */
Map<String,Object> map = new HashMap<>();
map.put("name", "dafei");
map.put("age",18);
List<Employee> employees = employeeMapper.selectByMap(map);

/** QueryWrapper查询
 * 查询name=dafei， age=18的员工
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ? AND age = ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name", "dafei");
wrapper.eq("age",18);
List<Employee> employees = employeeMapper.selectList(wrapper);

/** LambdaQueryWrapper查询
 * 查询name=dafei， age=18的员工
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ? AND age = ?)
 */
LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(Employee::getName,"dafei").eq(Employee::getAge,18);
List<Employee> employees = employeeMapper.selectList(wrapper);

高級查询

列投影

/**列投影
 * ：查询所有员工， 返回员工name， age列
 * SELECT name,age FROM employee
 */
ueryWrapper<Employee> wrapper = new QueryWrapper<>();
//wrapper.select("name,age");
wrapper.select("name","age");
List<Employee> employees = employeeMapper.selectList(wrapper);

/**列投影
 * 查询所有员工， 返回员工表中以h字母开头的列
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select(Employee.class,columnValue->columnValue.getProperty().startsWith("h"));
List<Employee> employees = employeeMapper.selectList(wrapper);

排序

orderByAsc/orderByDesc：

/**排序orderByAsc/orderByDesc
 * 查询所有员工信息按age正(降)序排， 如果age一样， 按id正(降)序排
 * SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
 * SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age DESC,id DESC
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//wrapper.orderByAsc("age","id");
wrapper.orderByDesc("age","id");
List<Employee> employees = employeeMapper.selectList(wrapper);

orderBy：

/**排序orderBy
 * 查询所有员工信息按age正序排， 如果age一样， 按id正序排
 * SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//参数一 排序开关，true表示执行排序，false反之 参数二 是否正序排 参数三 要进行排序的列
wrapper.orderBy(true,false,"age","id");
List<Employee> employees = employeeMapper.selectList(wrapper);

分组查询

groupBy：

/**分组查询groupBy
 * 以部门id进行分组查询，查每个部门员工个数
 * 由于返回的列名无法通过对象封装，因此只能用selectMaps
 * SELECT dept_id,count(id) count FROM employee GROUP BY dept_id
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id");
wrapper.select("dept_id","count(id) count");
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.out::println);

having：

/**分组查询having
 * 以部门id进行分组查询，查每个部门员工个数， 将大于3人的部门过滤出来
 * SELECT dept_id,count(id) count FROM employee GROUP BY dept_id HAVING count > 3
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id")
    .select("dept_id","count(id) count")
    .having("count > 3");
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.out::println);

条件查询

等于、不等于

/**条件查询:不等于
 * 查询name != hkj员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name <> ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.ne("name","hkj");
List<Employee> employees = employeeMapper.selectList(wrapper);

比较运算符gt、ge、lt、le

/**条件查询:比较运算符gt、ge、lt、le
* 查询age 大于18岁员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (age > ?)
*/
ueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.gt("age",18);
List<Employee> employees = employeeMapper.selectList(wrapper);

between/notBetween:包含临界值

/**条件查询:between/notBetween:包含临界值
     * 查询年龄小于18或者大于30岁的员工信息
     * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (age NOT BETWEEN ? AND ?)
     */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.notBetween("age",18,30);
List<Employee> employees = employeeMapper.selectList(wrapper);

isNull/isNotNull

/**条件查询:isNull/isNotNull
 * 查询admin为null 员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (admin IS NULL)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.isNull("admin");
List<Employee> employees = employeeMapper.selectList(wrapper);

in/notIn/inSql/notInSql

/**条件查询:in/notIn/inSql/notInSql
 * 查询id为3， 6 的员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (id IN (?,?))
 * 查询id不为3， 6 的员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (id NOT IN (3,6))
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.in("id",3L,6L);
List<Employee> employees = employeeMapper.selectList(wrapper);
----------------------------------------------------------------
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.notInSql("id","3,6");
List<Employee> employees = employeeMapper.selectList(wrapper);

模糊查询

/**条件查询:模糊查询
 * 查询name中含有hkj字样的员工
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","hkj");
List<Employee> employees = employeeMapper.selectList(wrapper);

/**条件查询:模糊查询
 * 查询姓王的员工信息
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ?) 吴%
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.likeRight("name","吴");
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);

逻辑运算符

/**条件查询:逻辑运算符
     * 查询name含有yoona字样的，或者 年龄在18到30之间的员工
     * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? OR (age BETWEEN ? AND ?))
     */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","yoona")
    .or(
    qw->qw.between("age",18,30)
);
List<Employee> employees = employeeMapper.selectList(wrapper);

/**条件查询:逻辑运算符
 * 查询name含有hkj字样的并且 年龄在小于18或者大于30的员工
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? AND (age < ? OR age > ?))
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","hkj")
    .and(
    qw->qw.lt("age",18)
    .or()
    .gt("age",30)
);
List<Employee> employees = employeeMapper.selectList(wrapper);

/**条件查询:逻辑运算符
 * 查询name含有hkj字样的并且 年龄在小于18或者大于30的员工
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? AND (age < ? OR age > ?))
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","hkj")
    .and(
    qw->qw.lt("age",18)
    .or()
    .gt("age",30)
);
List<Employee> employees = employeeMapper.selectList(wrapper);

QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.like("name","hkj")
                .and(
                        qw->qw.notBetween("age",18,30)
                );
        employeeMapper.selectList(wrapper);

通用Service接口

创建自定义service接口，继承IService接口

/**
 * mybatis-plus自定义服务接口
 * 1>自定义接口IEmployeeService 继承IService接口
 * 2>明确指定泛型,当前接口操作实体对象:Employee
 */
public interface IEmployeeService extends IService<Employee> {
}

创建service实现类，继承ServiceImpl<实体对象对应mapper接口，实体类对象>，并且实现自定义接口

/**
 * mybatis-plus服务层接口实现类:
 * 2>在ServiceImpl<M,T>上明确指定2个泛型:
 *  1:当前操作实体对象对应mapper接口EmployeeMapper
 *  2:当前操作实体类对象Employee
 */
@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService{
}

使用mybatis-plus实现分页操作

编写封装分页数据实体类

@Getter
@Setter
public class QueryObject {
    private int currentPage;
    private int pageSize;
}

@Getter
@Setter
public class EmployeeQuery extends QueryObject {
    private String keyword;
}