MyBatis-Plus
MyBatis-Plus是一个MyBatis的增强工具,在MyBatis的基础上只做增强不做改变,内置代码生成以及分页等强大工具
-
MyBatis-Plus对比MyBatis
- 提供了无sql的增删改查操作
- 内置了代码生成器、分页插件及性能分析插件等
- 提供了功能丰富的条件构造器快速进行无sql开发
-
使用MyBatis-Plus
- 添加依赖
<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.0</version> </dependency>
- 新建Mapper接口,继承BaseMapper<Employee>接口,并添加实体类作为泛型
此时就可以使用BaseMapper接口中的方法了/** * 这里传递了Employee实体类作为泛型 * mybatis-plus就可以拿到该实体类对象,然后扫描解析 * 从对象中拿到属性名,进行拼接sql语句 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id=? * 所以,如果实体类中属性名与表中字段不一致,则数据封装不进去 * 需要借助mybatis-plus 的@TableField注解用来建立属性与字段的映射关系 */ public interface EmployeeMapper extends BaseMapper<Employee> { }
- MyBatis-Plus也提供了日志功能,用来记录crud操作时的sql语句,只需要在application中添加即可
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
- 添加依赖
-
常用注解
- @TableName
- 贴在类上,指定当前类与哪张表映射
- 默认当前类是与类名相同的表映射,例如,Employee类,与employee表映射
@TableName("employee") public class Employee1 { }
- @TableId
- 贴在属性上,标明该属性映射表中哪个主键id
- 其中type属性是指主键id是哪种类型,例如自增
- 默认情况下,如果不贴此注解,且属性名称与主键名称一致的话,MyBatis Plus会自动给主键id插入一个随机数值,如
1377952223178326017
@TableId(value = "id", type = IdType.AUTO) private Long ida;
- @TableField
- 贴在属性上,指定该属性映射表中哪一列,当属性名与表中列名不同时使用
- 其中exist属性表示该属性是否参与封装数据,默认为true,参与映射封装数据
@TableField(value = "name", exist = true) private String name;
- @TableName
-
通用Mapper接口的CRUD操作
- insert
- insert(T entity):将数据封装到实体类中,插入到数据库
//INSERT INTO employee ( name, age ) VALUES ( ?, ? ) @Test public void testInsert() { Employee employee = new Employee(); employee.setName("kingyumu"); employee.setAge(18); employeeMapper.insert(employee); }
- insert(T entity):将数据封装到实体类中,插入到数据库
- update
-
updateById(T entity):传入一个实体类对象,根据主键id修改表中数据
//UPDATE employee SET name=?, age=? WHERE (name = ?) @Test public void testUpdateById() { Employee employee = new Employee(); employee.setName("admin"); employee.setAge(19); employeeMapper.updateById(employee); }
做修改操作时,mybatis-plus会将对象中所有非null属性拼接sql,所以对象中属性类型如果时基本类型,需要用它的包装类型,例如
int
,需换成Integer
-
update(entity, wrapper):根据自定义的where条件,修改表中数据
//UPDATE employee SET name=?, age=? WHERE (name = ?) @Test public void testUpdate() { Employee employee = new Employee(); employee.setName("root"); employee.setAge(15); UpdateWrapper<Employee> wrapper = new UpdateWrapper<>(); wrapper.eq("name", "admin"); employeeMapper.update(employee, wrapper); }
Wrapper对象可以暂且认为是拼接where条件用的
当entity为null时,也可以通过Wrapper对象设置要修改的字段
//UPDATE employee SET name=?, age=? WHERE (name = ?) @Test public void testUpdateNull() { UpdateWrapper<Employee> wrapper = new UpdateWrapper<>(); wrapper.eq("name", "admin") .set("name", "root") .set("age", 15); employeeMapper.update(null, wrapper); }
-
- delete
- deleteById(Serializable id):根据主键id删除表中数据
//DELETE FROM employee WHERE id=? @Test public void testDeleteById() { employeeMapper.deleteById(3); }
- deleteByMap(Map<String, Object> columnMap):根据传入的map对象作为where条件删除数据
map对象中,key是列名,value是修改成什么值//DELETE FROM employee WHERE name = ? AND age = ? @Test public void testDeleteByMap() { Map<String, Object> map = new HashMap<>(); map.put("name", "kingyumu"); map.put("age", 17); employeeMapper.deleteByMap(map); }
- delete(wrapper):根据自定义的where条件删除数据
//DELETE FROM employee WHERE (name = ?) @Test public void testDelete() { UpdateWrapper<Employee> wrapper = new UpdateWrapper<>(); wrapper.eq("name", "郑总"); employeeMapper.delete(wrapper); }
- deleteBatchIds(Collection<? extends Serializable> idList):根据主键id批量删除数据
//DELETE FROM employee WHERE id IN ( ? , ? ) @Test public void testDeleteBatchIds() { employeeMapper.deleteBatchIds(Arrays.asList(12, 13)); }
- deleteById(Serializable id):根据主键id删除表中数据
- select
-
selectById(Serializable id):根据主键id查询表中数据
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id=? @Test public void testSelectById() { Employee employee = employeeMapper.selectById(20); System.out.println(employee); }
-
selectBatchIds(Collection<? extends Serializable> idList):根据主键id批量查询数据
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id IN ( ? , ? , ? ) @Test public void testSelectBatchIds() { List<Employee> employees = employeeMapper.selectBatchIds(Arrays.asList(2, 3, 4)); employees.forEach(System.out::println); }
-
selectByMap(Map<String, Object> columnMap):根据map对象查询数据,key是列名,value是列值,map元素间是and关系
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE name = ? AND admin = ? @Test public void testSelectByMap() { Map<String, Object> map = new HashMap<>(); map.put("name", "孙总"); map.put("admin", 0); List<Employee> employees = employeeMapper.selectByMap(map); employees.forEach(System.out::println); }
-
selectCount(wrapper):根据wrapper对象查询符合条件的总记录数
//SELECT COUNT( 1 ) FROM employee WHERE (name = ?) @Test public void testSelectCount() { QueryWrapper<Employee> wrapper = new QueryWrapper<>(); wrapper.eq("name", "root"); Integer count = employeeMapper.selectCount(wrapper); System.out.println("count = " + count); }
当wrapper为null或wrapper没有指定条件时,查询的是表中全部记录
//SELECT COUNT( 1 ) FROM employee @Test public void testSelectCountNull() { Integer count = employeeMapper.selectCount(null); System.out.println("count = " + count); }
-
selectList(wrapper):根据wrapper对象查询符合条件的所有信息
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ?) @Test public void testSelectList() { QueryWrapper<Employee> wrapper = new QueryWrapper<>(); wrapper.eq("name", "李总"); List<Employee> employees = employeeMapper.selectList(wrapper); employees.forEach(System.out::println); }
当wrapper为null或wrapper没有指定条件时,查询的是表中全部信息
-
selectMaps(wrapper):根据wrapper对象查询符合条件的所有信息
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ?) @Test public void testSelectMaps() { QueryWrapper<Employee> wrapper = new QueryWrapper<>(); wrapper.eq("name", "李总"); List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper); maps.forEach(System.out::println); }
当wrapper为null或wrapper没有指定条件时,查询的是表中全部信息
当查询的列名在实体类中不存在时,用该方法,map中,key是列名,value是列值
-
selectPage(Page<T> page, wrapper):根据分页对象和wrapper对象分页查询数据
- 先在配置类中配置mybatis-plus的分页拦截器
@Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(); //// 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false paginationInnerInterceptor.setOverflow(true); mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor); return mybatisPlusInterceptor; }
- 进行分页操作
其中page对象相当于之前写的PageResult<T>及分页插件的PageInfo<T>对象//SELECT COUNT(1) FROM employee //SELECT id,name,password,email,age,admin,dept_id FROM employee LIMIT ? @Test public void testSelectPage() { QueryWrapper<Employee> wrapper = new QueryWrapper<>(); // 1 -> 当前页, 5 -> 每页显示5条数据 IPage<Employee> page = new Page<>(1, 5); //当wrapper为null是,不再分页查询,而是查询全部信息 IPage<Employee> employeePage = employeeMapper.selectPage(page, null); //IPage<Employee> 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()); }
- 先在配置类中配置mybatis-plus的分页拦截器
-
- insert
-
条件构造器
-
Wrapper继承体系
-
更新操作
- UpdateWrapper更新
- set(String column, Object val):修改指定字段
//UPDATE employee SET name=? WHERE (id = ?) @Test public void testUpdateWrapper() { UpdateWrapper<Employee> wrapper = new UpdateWrapper<>(); wrapper.set("name", "kingyumu"); wrapper.eq("id", 1); employeeMapper.update(null, wrapper); }
- set(boolean condition, String column, Object val):当条件满足时,修改指定字段
@Test public void testUpdateWrapper() { UpdateWrapper<Employee> wrapper = new UpdateWrapper<>(); String keyword = ""; //如果condition为true,则执行修改操作,否则不执行 wrapper.set(StringUtils.hasText(keyword), "name", "kingyumu"); wrapper.eq("id", 1); employeeMapper.update(null, wrapper); }
- setSql(String sql):将sql直接拼接到set子句中
//UPDATE employee SET name='root' WHERE (id = ?) @Test public void testSetSql() { UpdateWrapper<Employee> wrapper = new UpdateWrapper<>(); wrapper.setSql("name='root'"); wrapper.eq("id", 1); employeeMapper.update(null, wrapper); }
- set(String column, Object val):修改指定字段
- LambdaUpdateWrapper更新
- 使用Lambda语法获取到列名,可以防止列名写错时而不知
//UPDATE employee SET name=? WHERE (id = ?) @Test public void testLambdaUpdateWrapper() { LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>(); //从哪个类中获取到哪个属性以获取到哪个列名 wrapper.set(Employee::getName, "kingyumu"); wrapper.eq(Employee::getId, 1); employeeMapper.update(null, wrapper); }
- 使用Lambda语法获取到列名,可以防止列名写错时而不知
- UpdateWrapper更新
-
LambdaQueryWrapper查询
- 使用Lambda语法获取到列名,可以防止列名写错时而不知
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ?) @Test public void testLambdaQueryWrapper() { LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>(); wrapper.eq(Employee::getName, "kingyumu"); List<Employee> employees = employeeMapper.selectList(wrapper); employees.forEach(System.out::println); }
- 使用Lambda语法获取到列名,可以防止列名写错时而不知
-
构建Wrapper对象的工具类
QueryWrapper<Employee> query = Wrappers.query(new Employee()); UpdateWrapper<Employee> update = Wrappers.update(new Employee()); LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery(Employee.class); LambdaUpdateWrapper<Employee> lambdaUpdate = Wrappers.lambdaUpdate(Employee.class);
-
-
高级查询
- slect:查询指定字段的数据
//SELECT name,age FROM employee @Test public void testSelect() { LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>(); wrapper.select(Employee::getName, Employee::getAge); List<Employee> employees = employeeMapper.selectList(wrapper); employees.forEach(System.out::println); }
- 排序
- orderByAsc/orderByDesc:将查询出的数据按照指定字段正/倒序排列
//SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,dept_id DESC @Test public void testOrderByAscDesc() { LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>(); wrapper.orderByAsc(Employee::getAge); wrapper.orderByDesc(Employee::getDeptId); List<Employee> employees = employeeMapper.selectList(wrapper); employees.forEach(System.out::println); }
- orderBy:将查询出的数据按照指定字段正/倒序排列
//SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,dept_id ASC @Test public void test25() { LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>(); //第二个参数为true表示ASC,正序,为false表示Desc,倒叙 wrapper.orderBy(true, true, Employee::getAge, Employee::getDeptId); List<Employee> employees = employeeMapper.selectList(wrapper); employees.forEach(System.out::println); }
- orderByAsc/orderByDesc:将查询出的数据按照指定字段正/倒序排列
- 条件查询
- 比较运算符
- allEq/eq/ne
- allEq(map, null2IsNull):全等,map是where条件,第二个参数,为true时,则在map的value为null时调用isNull方法
//SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ? AND age = ?) @Test public void testAllEq() { QueryWrapper<Employee> wrapper = new QueryWrapper<>(); Map<String, Object> map = new HashMap<>(); map.put("name", "kingyumu"); map.put("age", 15); //默认为true wrapper.allEq(map, true); //SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name IS NULL AND age = ?) wrapper.allEq(map, false); List<Employee> employees = employeeMapper.selectList(wrapper); employees.forEach(System.out::println); }
- eq(column, value):相等,拼接到where子句中,
column=value
wrapper.eq(Employee::getId, 1);
- ne(column, value):不等,拼接到where子句中,
column != value
wrapper.ne(Employee::getName, "kingyumu");
- allEq(map, null2IsNull):全等,map是where条件,第二个参数,为true时,则在map的value为null时调用isNull方法
- gt/ge/lt/le
- gt(column, value):大于,例如,
gt("age", 18)等价于age > 18
- ge(column, value):大于等于,例如,
ge("age", 18)等价于age >= 18
- lt(column, value):小于,例如,
lt("age", 18)等价于age < 18
- le(column, value):小于等于,例如,
le("age", 18)等价于age <= 18
- gt(column, value):大于,例如,
- between/Notbetween
- between(column, value1, value2):
between("age", 18, 30)
等价于,age between value1 and value2
- Notbetween(column, value1, value2):
notBetween("age", 18, 30)
等价于,age not between value1 and value2
- between(column, value1, value2):
- isNull/isNotNull
- isNull(column):
isNull("name")
等价于name is null
- isNotNull(column):
isNotNull("name")
等价于name is not null
- isNull(column):
- in/notIn/inSql/notInSql
- in(column, valueList):
in("age", Arrays.asList(13, 15))
等价于age in (13, 15)
- notIn(column, valueList):
notIn("age", Arrays.asList(13, 15))
等价于age not in (13, 15)
- inSql(column, sql):
inSql("age", "13,15")
等价于age in (13,15)
- notInSql(column, sql):
notInSql("age", "13,15")
等价于age not in (13,15)
- in(column, valueList):
- allEq/eq/ne
- 模糊查询
- like/notLike
- like(column, value):
like("name", "李")
等价于name like '%李%'
- notLike(column, value):
notLike("name", "李")
等价于name not like '%李%'
- like(column, value):
- likeLeft/likeRight
- likeLeft(column, value):
likeLeft("name", "李")
等价于name like '%李'
- likeRight(column, value):
likeRight("name", "李")
等价于name like '%李'
- likeLeft(column, value):
- like/notLike
- 逻辑运算符
- or/and
- 一个单条件连接另一个单条件
//age = 18 or name = kingyumu wrapper.eq("age", 18) .or() .eq("name", "kingyumu");
- 一个单条件连接另一个非单条件
//查询name含有king字样的,或者 年龄在18到30之间的用户 //name like '%king%' or (age between 18 and 30) wrapper.like("name", "kingyumu") .or(wr -> wr.between("age", 18, 30));
- 一个单条件连接另一个单条件
- or/and
- 比较运算符
- 分组查询
- groupBy(columns):根据列名分组查询,可传多个列名
wrapper.groupBy("dept_id");
- having(sql):在分组后,根据传入的sql条件筛选查询结果
//having count(*) > 3 having("count(*) > 3")
- groupBy(columns):根据列名分组查询,可传多个列名
- 注解方式自定义sql
- 单表操作
- 在Mapper接口中定义一个方法,方法上贴上@Select注解
public interface EmployeeMapper extends BaseMapper<Employee> { /** * @Select 查询操作 * @Insert 新增操作 * @Delete 删除操作 * @Update 更新操作 */ @Select("select * from employee") List<Employee> selectAll(); }
- 使用方法查询数据
@Test public void testAnnoSelect() { List<Employee> employees = employeeMapper.selectAll(); employees.forEach(System.out::println); }
- 在Mapper接口中定义一个方法,方法上贴上@Select注解
- 联表操作
- Mapper接口方法上,需要使用@Results处理字段与属性的映射关系
@Select("select e.*, d.id d_id, d.name d_name, d.sn d_sn from employee e join department d on d.id = e.dept_id") //处理department表的映射关系 @Results({ @Result(column = "d_id", property = "dept.id"), @Result(column = "d_name", property = "dept.name"), @Result(column = "d_sn", property = "dept.sn") }) List<Employee> selectEmployeeAndDept();
- 测试
@Test public void testAnnoMoreTable() { List<Employee> employees = employeeMapper.selectEmployeeAndDept(); employees.forEach(System.out::println); }
- Mapper接口方法上,需要使用@Results处理字段与属性的映射关系
- 单表操作
- slect:查询指定字段的数据
-
通用Service接口
- 使用mybatis-plus的Service接口及方法
- 新建Service接口,继承mybatis-plus的IService<T>接口
public interface EmployeeService extends IService<Employee> { }
- 新建ServiceImpl实现类,实现自己写的接口,并继承mybatis-plus的实现类ServiceImpl<M, T>
@Service public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements EmployeeService { }
- 测试
//SELECT id,name,password,email,age,admin,dept_id FROM employee @Test public void testService(){ List<Employee> employees = employeeService.list(); employees.forEach(System.out::println); }
- 新建Service接口,继承mybatis-plus的IService<T>接口
- ServiceImpl中的常用方法
- getBaseMapper():获取Mapper接口继承的Mapper对象
//SELECT id,name,password,email,age,admin,dept_id FROM employee @Test public void testGetBaseMapper(){ BaseMapper<Employee> baseMapper = employeeService.getBaseMapper(); List<Employee> employees = baseMapper.selectList(null); employees.forEach(System.out::println); }
- getOne(wrapper):只查询一个结果,查询到多个结果时报异常
TooManyResultsException
@Test public void testGetOne(){ LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery(Employee.class); //只能查询一个结果,查询到多个结果时报Expected one result (or null) to be returned by selectOne(), but found: 17 Employee employee = employeeService.getOne(wrapper); }
- list(wrapper):根据wrapper对象查询满足条件的数据
@Test public void testList(){ LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery(Employee.class); wrapper.lt(Employee::getAge, 20); List<Employee> employees = employeeService.list(wrapper); employees.forEach(System.out::println); }
- page(page, wrapper):分页及高级查询
- 在配置类中配置拦截器
@Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(); paginationInnerInterceptor.setDbType(DbType.MYSQL); //// 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false paginationInnerInterceptor.setOverflow(true); mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor); return mybatisPlusInterceptor; }
- 在Service接口中定义方法
IPage<Employee> query(QueryObject queryObject);
- 在ServiceImpl中实现该方法,编写分页代码
@Override public IPage<Employee> query(QueryObject queryObject) { IPage<Employee> iPage = new Page<>(queryObject.getCurrentPage(), queryObject.getPageSize()); //高级查询的条件 LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery(Employee.class); wrapper.like(Employee::getName, queryObject.getKeyword()); return super.page(iPage, wrapper); }
- 测试分页方法
@Test public void test6(){ QueryObject object = new QueryObject(); object.setKeyword("总"); IPage<Employee> page = employeeService.query(object); 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()); }
- 在配置类中配置拦截器
- getBaseMapper():获取Mapper接口继承的Mapper对象
- 配置事务:在ServiceImpl类上方贴上
@Transactional
即可
- 使用mybatis-plus的Service接口及方法