一、批量查询 selectBatchIds
List<Employee> emps=employeeMapper.selectBatchIds(Arrays.asList(1,2,13,14) );
二、分页查询
分页查询的步骤,一、在config包下创建拦截器 二、使用时new Page
1. 拦截器MybatisPlusInterceptor
MybatisPlus的拦截器统一返回类型为MybatisPlusInterceptor,包括分页查询,乐观锁,逻辑删除等的拦截器。
@Configuration
public class MpConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
//新增乐观锁拦截器
mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
//新增分页拦截器,并设置数据库类型为mysql
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return mybatisPlusInterceptor;
}
}
2. Page类分页
@Test
void testEmpsByPage(){
Page<Employee> page = new Page<>(1,3);
//selectPage返回值中保存获取到的所有信息
Page<Employee> employeePage =employeeMapper.selectPage(page,null);
long pages = employeePage.getPages(); //总页数
long current = employeePage.getCurrent();//当前页
List<Employee> records=employeePage.getRecords(); //查询数据集合
long total = employeePage.getTotal();//总记录数
boolean hasNext = employeePage.hasNext(); //是否有下一页
boolean hasPrevious = employeePage.hasPrevious(); //是否有上一页
}
3. 自定义分页功能
MybatisPlus实现的查询方法不能够满足需求,需要自己编写sql(即在Mapper接口中自定义方法,在映射文件中编写sql),实现分页功能。注意:依然需要定义拦截器,要不然分页不起作用
- dishMapper.java
@Mapper
public interface DishMapper extends BaseMapper<Dish> {
/**
* 自定义分页方法:返回值类型和第一个参数类型必须是Mybatis-plus所提供的的分页对象Page,
* @param page
* @param name
* @return
*/
Page<Dish> selectPageVo(@Param("page") Page<Dish> page,@Param("name") String name);
}
- dishMapper.xml
<!--Page<Dish> selectPageVo(@Param("page") Page<Dish> page,@Param("name") String name); -->
<select id="selectPageVo" resultType="com.athorse.entities.Dish">
select * from dish where 1=1
<if test="name != null and name!= ''">
and name like "%"#{name}"%"
</if>
order by id desc
</select>
-
在service中调用自定义Mapper接口
@Override
public Page<Dish> getDishPage(Integer page, Integer pageSize,String name) {
Page<Dish> dishPage = new Page<>(page,pageSize);
return dishMapper.selectPageVo(dishPage,name);
}
三、条件构造器 LambdaQueryWrapper
1. 条件的优先级
//查询条件:用户名含有name,并且(年龄<20或者手机号含有8888)员工
//注:QueryWrapper支持链式编程,重载的and函数的参数又是QueryWrapper,可使用Lambda表达式
LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
wrapper.like(name!=null,Employee::getName,name)
.and(w->w.lt(Dish::getAge,20).or(w.like(Employee::getPhone,"8888")));
return dishMapper.getEmployeeByName(name);