mybatis-plus

mybatis-plus,第1张

mybatis-plus mybatis-plus的sql拼接规则:
  1. 实体对象参数属性有值,那么该属性名会拼接sql语句中
  2. 实体对象参数属性为基本属性时,会有默认值,mybatis-plus认为是有值,参与sql拼接
mybatis-plus与mybatis的对比
  1. mybatis的sql语句全要开发者自己去手写,而mybatis-plus则不需要

  2. mybatis不支持lambda形式调用,而mybatis-plus可以

  3. mybatis-plus能自动的去映射实体类转为mybatis内部对象注入容器中

mybatis-plus缺点与优点:

优点:

  1. 不需要手动的去写sql语句
  2. 依赖小,减少开发成本,提高开发效率
  3. 内置分页插件与全局插件

缺点:

  1. 当项目比较大的时候,对手动的去写sql语句比较困难,尤其是多表联动的时候,效率就比较低
  2. 当你的实体类的字段与你的数据库的属性不匹配或实体类的字段多于数据库的属性时会出现sql语法异常

mybatis-plus原理

在baseMapper接口中注入大量的接口,每个接口对应着不同的crud *** 作之外还有对内置分页 *** 作

 
mybatis-plus特性: 
:只做增强不做改变,引入它不会对现有工程产生影响,如丝般顺滑
:启动即会自动注入基本 CURD,性能基本无损耗,直接面向对象 *** 作
:内置通用 Mapper、通用 Service,仅仅通过少量配置即可实现单表大部分 CRUD  *** 作,更有强大的条件构造器,满足各类使用需求
:通过 Lambda 表达式,方便的编写各类查询条件,无需再担心字段写错
:支持多达 4 种主键策略(内含分布式唯一 ID 生成器 - Sequence),可自由配置,完美解决主键问题
:支持 ActiveRecord 形式调用,实体类只需继承 Model 类即可进行强大的 CRUD  *** 作
:支持全局通用方法注入( Write once, use anywhere )
:采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码,支持模板引擎,更有超多自定义配置等您来使用
:基于 MyBatis 物理分页,开发者无需关心具体 *** 作,配置好插件之后,写分页等同于普通 List 查询
:支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
:可输出 Sql 语句以及其执行时间,建议开发测试时启用该功能,能快速揪出慢查询
:提供全表 delete 、 update  *** 作智能分析阻断,也可自定义拦截规则,预防误 *** 作
什么时候用到updateById与update

updateById:通过

  1. 如果更新条件是id而且全部列都要更新则使用updateById

    //需求把1L的name改为小苏
    //解决办法一:把int类型改为包装类型的Integer再进行updateById *** 作进行更新
    //UPDATE employee_copy SET name=?, age=?, admin=? WHERe id=?
    //---------------------------//
    //解决办法二:先查找要改实体类的字段改变属性值再更新
    //SELECT id,name,password,email,age,admin,dept_id FROM employee_copy WHERe id=?
    //UPDATe employee_copy SET name=?, password=?, email=?, age=?, admin=?, dept_id=? WHERe id=?
    Employee employee = employeeMapper.selectById(1L);
    employee.setName("小天");
    employeeMapper.updateById(employee);
    
  2. 如果更新条件不仅是限于id,还要进行部分表中的属性列更新,则使用update

//解决办法三:用update方法进行局部sql更新 ,返回值为int
//eq:表示等于
//set:要设置的值
//UPDATE employee_copy SET name=? WHERe (id = ?)
UpdateWrapper wrapper = new UpdateWrapper<>();
wrapper.eq("id",2L);
wrapper.set("name","xiaowu");
employeeMapper.update(null,wrapper);
mybatis-plus提供删除api

deleteById(id): 接收的值是一个序列化,表示无论是long类型还是int类型都能进行接收

根据id进行删除

//DELETE FROM employee_copy WHERe id=?
employeeMapper.deleteById(1L);

deleteBatchIds(List集合):接收的值是一个序列化集合,表示可以进行多个值 *** 作

//DELETE FROM employee_copy WHERe id IN ( ? , ? , ? )
employeeMapper.deleteBatchIds(Arrays.asList(1L,2L,3L));

deleteMap(map); map中的k相当于表中的属性,v相当于表中的值,进行条件过滤
根据map传递的k和v的值进行条件删除

//DELETE FROM employee_copy WHERe dept_id = ? AND age = ?
HashMap map = new HashMap<>();
map.put("age",25);
map.put("dept_id",2);
employeeMapper.deleteByMap(map);

delete:根据wrapper提供的column和val进行条件过滤删除

//DELETE FROM employee WHERe (age = ? AND name = ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("age",0);
wrapper.eq("name","xiaofei");
employeeMapper.delete(wrapper);
myabtis-plus提供的查询api:

select:指定返回的列

selectById:根据id进行查询,返回值为object

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe id=?
Employee employee = employeeMapper.selectById(1L);
System.out.println(employee);

selectBatchIds:根据List集合储存的1个或多个id进行条件查询,返回值为List集合对象

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe id IN ( ? , ? , ? , ? )       
List employees = employeeMapper.selectBatchIds(Arrays.asList(1L, 2L, 3L, 4L));
employees.forEach(System.out::println);

selectMap:根据map集合的k键值对应表中列的属性值表示作为条件,v作为要进行过滤的属性值,返回为List集合对象

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe name = ? AND age = ?
HashMap map = new HashMap<>();
map.put("name","admin");
map.put("age","40");
List employees = employeeMapper.selectByMap(map);
employees.forEach(System.out::println);

selectCount:根据wrapper提供的column和val进行条件过滤进行查询,返回值为Integer

//SELECT COUNT( 1 ) FROM employee WHERe (age = ?) 如果没有条件则统计全部结果
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("age",40); //判断条件age等于40进行过滤
Integer integer = employeeMapper.selectCount(wrapper);
System.out.println(integer);

selectList:根据wrapper提供的column和val进行条件过滤进行查询,返回值为List集合对象

//SELECT id,name,password,email,age,admin,dept_id FROM employee (没有条件下)
//SELECt COUNT( 1 ) FROM employee WHERe (age = ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("age",40);
List employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);

selectPage:根据page的current当前页与size每页显示条数两个字段和wrapper提供的column和val进行条件过滤进行查询,返回值为List集合对象

//SELECT id,name,password,email,age,admin,dept_id FROM employee LIMIT ?,?
QueryWrapper wrapper = new QueryWrapper<>();
Page page = new Page<>();
page.setCurrent(2);
page.setSize(3);
Page employeePage = employeeMapper.selectPage(page, wrapper);
System.out.println("当前页:" + employeePage.getCurrent());
System.out.println("每页显示条数:" + employeePage.getSize());
System.out.println("总页数:" + employeePage.getPages());
System.out.println("总数:" + employeePage.getTotal());
System.out.println("当前页数据:" + employeePage.getRecords());

selectMapsPage:通过IPage>进行分页筛选过滤,根据wrapper提供的column和val进行条件过滤进行查询,返回值为List集合对象

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ?) LIMIT ?
EmployeeQuery qo = new EmployeeQuery();
IPage> page = new Page<>(qo.getCurrentPage(),qo.getPageSize());
QueryWrapper wrapper = new QueryWrapper<>();
qo.setKeyWord("明"); //过滤条件

wrapper.like("name",qo.getKeyWord());

IPage> mapIPage = employeeMapper.selectMapsPage(page, wrapper);
System.out.println("当前页:" + mapIPage.getCurrent());
System.out.println("每页显示条数:" + mapIPage.getSize());
System.out.println("总页数:" + mapIPage.getPages());
System.out.println("总数:" + mapIPage.getTotal());
System.out.println("当前页数据:" + mapIPage.getRecords());

selectOne:需要自己手动的去给wrapper一个条件否则会报mybatis方法异常,预期是一个对象结果是多个对象的异常,根据wrapper提供的column和val进行条件过滤进行查询,返回值为实体类对象,底层用的还是selectList

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (id = ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("id",1L);
Employee employee = employeeMapper.selectOne(wrapper);
System.out.println(employee);

selectObject:根据wrapper提供的column和val进行条件过滤进行查询,返回值为实体类List集合的Object对象,打印的是id值

//SELECT id,name,password,email,age,admin,dept_id FROM employee
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.like("name","明");
List objects = employeeMapper.selectObjs(wrapper);
objects.forEach(System.err::println);
 
select用法 

需求:查询所有员工, 返回员工name, age列

根据wrapper提供的column和val进行条件过滤进行selectList方法查询,返回值为实体类List集合对象

QueryWrapper wrapper = new QueryWrapper<>();
wrapper.select("name,age");
List employees = employeeMapper.selectList(wrapper);
employees.forEach(System.err::println);
orderByASC与orderByDesc还有orderBy用法

orderByASC为升序

orderByDesc为降序

orderBy:第一个参数是否拼接sql,第二个参数是否为升序,第三个参数则是排序表中的列属性

需求:查询所有员工信息按age正序排, 如果age一样, 按id正序排

//SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age,id ASC
QueryWrapper wrapper = new QueryWrapper<>();
// wrapper.orderBy(true,true,"age,id");
wrapper.orderByAsc("age").orderByAsc("id");
List employees = employeeMapper.selectList(wrapper);
employees.forEach(System.err::println);

需求:查询所有员工信息按age正序排, 如果age一样, 按id倒序排

//SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id DESC
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.orderByAsc("age").orderByDesc("id");
List employees = employeeMapper.selectList(wrapper);
employees.forEach(System.err::println);
groupBy用法

指定要分组的表中的列属性,配合select一起使用

select:第一个参数为表中的列属性,第二个参数为要查询的列

需求:以部门id进行分组查询,查每个部门员工个数, 将大于3人的部门过滤出来

//SELECT dept_id,count(id) count FROM employee GROUP BY dept_id HAVINg count > 3
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id").having("count > 3").select("dept_id","count(id) count");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
eq、ne、allEq用法

eq:对应的列只能是这个属性值才能查询到

需求:查询name=dafei, age=18的员工信息

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name = ? AND age = ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("name","defei").eq("age",18);
maps.forEach(System.err::println);

allEq:根据map提供的k和v进行表中列的属性值条件过滤,一次性包含处理多个条件

需求:查询name=dafei, age=18的员工信息

//SELECt id,name,password,email,age,admin,dept_id FROM employee WHERe (name = ? AND age = ?)
QueryWrapper wrapper = new QueryWrapper<>();
HashMap map = new HashMap<>();
map.put("name","dafei");
map.put("age",18);
wrapper.allEq(map);
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

ne:对应的列只能要不是这个属性值即可查询到

需求:查询name !=dafei员工信息

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name <> ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.ne("name","dafei");
List> map2 = employeeMapper.selectMaps(wrapper);
map2.forEach(System.err::println);
gt、lt、ge、le用法

gt:大于这个表中对应列的属性值

lt:小于这个表中对应列的属性值

ge:大于等于这个表中对应列的属性值

le:小于等于这个表中对应列的属性值

需求:查询age 大于18岁员工信息

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age > ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.gt("age",18);
List> map2 = employeeMapper.selectMaps(wrapper);
map2.forEach(System.err::println);
//--------------------------------------------
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age < ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.lt("age",18);
List> map2 = employeeMapper.selectMaps(wrapper);
map2.forEach(System.err::println);

wrapper中默认是and拼接,如果需要用到or则在or里面进行条件过滤

需求:查询年龄介于18~30岁的员工信息

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age >= ? AND age <= ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.ge("age",18).le("age",30);
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求:查询年龄小于18或者大于30岁的员工信息

or:进行或条件的过滤,一个or执行一个过滤条件

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age <= ? OR (age >= ?))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.le("age",18).or(wr->wr.ge("age",30));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
between与notBetween用法

需求:查询年龄小于18或者大于30岁的员工信息

notBetween:表示不存在这个范围之内的都能进行查询

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age NOT BETWEEN ? AND ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.notBetween(true,"age","18","30");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求:查询年龄大于18~30岁之间的员工信息

between:表示只能查询到存在这个范围之内的值

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age BETWEEN ? AND ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.between(true,"age","18","30");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

isNull与isNotNull用法

需求: 查询dept_id 为null 员工信息

isNull:查询表中该列的属性值为null条件的数据

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (dept_id IS NULL)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.isNull("dept_id");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求: 查询dept_id 为null 员工信息

isNotNull:查询表中该列的属性值不为null条件的数据

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (dept_id IS NOT NULL)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.isNotNull("dept_id");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
inSql、notSql、in、notIn用法

inSql与notSql都是直接去占位,而in与notIn则是先用占位符占了位置再去赋值

需求: 查询id为1, 2 的员工信息

//用inSql查询
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (id IN (1,2))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.inSql("id","1,2");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
//----------------
//用in查询
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (dept_id IN (?,?))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.in("id",Arrays.asList(1L,2L));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求: 查询id不为1, 2 的员工信息

//用notSql查询
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (id NOT IN (1,2))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.notInSql(true,"id","1,2");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
//----------------
//用notIn查询
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (id NOT IN (?,?))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.notIn("id",Arrays.asList(1L,2L));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
like、notLike、likeLeft、likeRight用法

需求: 查询name中含有fei字样的员工

like:进行模糊查询,主要表中的列下的属性值存在xxx字样就行

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.like("name","fei");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求: 查询name中不含有fei字样的员工

notLike:进行模糊查询,主要表中的列下的属性值不存在xxx字样就行

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name NOT LIKE ?)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.notLike("name","fei");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求: 查询姓王的员工信息

likeRight:进行模糊查询,主要表中的列下的属性值存在以xxx字样开头就行

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ?)
//王%(String)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.likeRight("name","王");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求: 查询以王结尾的员工信息

likeLeft:进行模糊查询,主要表中的列下的属性值存在以xxx字样结尾就行

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ?)
//%王(String)
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.likeLeft("name","王");
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
or与and用法

or:在wrapper中进行或条件的过滤,一个or执行一个或过滤条件但可以拼接多个进行多个或过滤条件

and:在wrapper中进行或条件的过滤,一个and执行一个且过滤条件但可以拼接多个and进行多个且过滤条件

需求: 查询age = 18 或者 name=dafei 或者 id =1 的用户

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (age = ? OR (name = ? OR (id = ?)))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("age","18").or(wr->wr.eq("name","dafei").or(w->w.eq("id",1)));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求:查询name含有fei字样的,或者 年龄在18到30之间的用户

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ? OR (age >= ? AND age <= ?))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.like("name","feo").or(wr->wr.ge("age",18).le("age",30));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);
//------------------------------------------------
//使用between
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ? OR (age BETWEEN ? AND ?))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.like("name","feo").or(wr->wr.between("age",18,30));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

需求:查询name含有fei字样的并且 年龄在小于18或者大于30的用户

//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERe (name LIKE ? AND (age >= ? OR (age <= ?)))
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.like("name","fei").and(wr->wr.ge("age",18).or(w->w.le("age",30)));
List> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.err::println);

使用注解进行单表查询

@Select("select e.* from employee e")
List listByAnnoSingle();

测试

//select e.* from employee e
List employees = employeeMapper.listByAnnoSingle();
System.out.println(employees);

使用注解进行关联表查询

@Select("select e.*, d.id d_id, d.name d_name, d.sn d_sn from employee e left join department d on e.dept_id = d.id")
@Results({
    @Result(column="d_id", property = "dept.id"),
    @Result(column="d_name", property = "dept.name"),
    @Result(column="d_sn", property = "dept.sn")
})
List listByAnnoJoin();

测试

//select e.*, d.id d_id, d.name d_name, d.sn d_sn from employee e left join department d on e.dept_id = d.id
@Test
public void test22(){
    List employees = employeeMapper.listByAnnoJoin();
    employees.forEach(System.err::println);
}
QueryWrapper 和 LambdaQueryWrapper的使用
//普通的条件构造器
QueryWrapper queryWrapper = new QueryWrapper();
//指定要显示出来的列
queryWrapper.select("id","username");
List> list = employeeMapper.selectMaps(qeryWrapper);

//------------------------------------

//支持lambda的 条件构造器
LambdaQueryWrapper lambdaQueryWrapper = new LambdaQueryWrapper();
//指定根据排序列
lambdaQueryWrapper.orderByDesc(Employee::getId);
//指定要显示出来的列
lambdaQueryWrapper.select(Employee::getId,Employee::getName,Employee::getPassword);
List employees = employeeMapper.selectList(lambdaQueryWrapper);
LambdaUpdateWrapper和UpdateWrapper语法
//普通的条件构造器
UpdateWrapper updateWrapper = new UpdateWrapper();
updateWrapper.eq("指定要进行修改列的条件","条件属性值");
updateWrapper.set("指定要进行修改列","要进行修改的值");
employeeMapper.update(null,updateWrapper);

//------------------------------------

//支持lambda的 条件构造器
LambdaUpdateWrapper lambdaUpdateWrapper = new LambdaUpdateWrapper();
//指定要进行修改列的值
lambdaUpdateWrapper.eq(Employee::getId);
//指定列要进行属性值修改
lambdaQueryWrapper.set(Employee::getName,"xiaoming");
employeeMapper.update(null.lambdaUpdateWrapper);
QueryWrapper与UpdateWrapper的区别

QueryWrapper:只进行sql语句查询

UpdateWrapper:提供set方法可以有效针对数据库表中的列进行值修改,eq表示列等于什么条件下

欢迎分享,转载请注明来源:内存溢出

原文地址: https://outofmemory.cn/zaji/5687386.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-12-17
下一篇 2022-12-17

发表评论

登录后才能评论

评论列表(0条)