用mybatis-plus实现分页、多条件查询
mybatis-plus
条件构造器QueryWrapper常用方法
/**
*附加条件构造器QueryWrapper常用方法
*/
wrapper.eq("数据库字段名", "条件值"); //相当于where条件 等于
wrapper.ne("数据库字段名", "条件值"); //相当于where条件 不等于
wrapper.ge("数据库字段名", "要比较的值"); //大于等于
wrapper.le("数据库字段名", "要比较的值"); //小于等于
wrapper.gt("数据库字段名", "要比较的值"); //大于
wrapper.lt("数据库字段名", "要比较的值"); //小于
wrapper.between("数据库字段名", "区间一", "区间二"); //相当于范围内使用的between 在值区间一到区间二之间
wrapper.notBetweeen("数据库字段名", "区间一", "区间二"); //相当于不在范围内使用的between
wrapper.like("数据库字段名", "模糊查询的字符"); //模糊查询like
wrapper.likeLeft("数据库字段名", "模糊查询的字符"); //模糊查询like模糊查询匹配最后一位值‘%值’
wrapper.likeRight("数据库字段名", "模糊查询的字符"); //模糊查询like模糊查询匹配第一位值‘值%’
wrapper.groupBy("数据库字段名"); //相当于group by分组
wrapper.in("数据库字段名", "包括的值,分割"); //相当于in
wrapper.notIn("数据库字段名", "不包括的值,分割"); //相当于not in
wrapper.isNull("数据库字段名"); //值为空或null
wrapper.isNotNull("数据库字段名"); //值不为空或不为null
wrapper.orderByAsc("数据库字段名"); //排序升序
wrapper.orderByDesc("数据库字段名"); //排序降序
wrapper.or(); //—或者 例：wrapper.like("name", keywords).or().like("address", keywords)
wrapper.and(); //——和
wrapper.inSql("sql语句"); //符合sql语句的值
wrapper.notSql("sql语句"); //不符合SQL语句的值
wrapper.esists("SQL语句"); //查询符合SQL语句的值
wrapper.notEsists("SQL语句"); //查询不符合SQL语句的值
一、分页查询
1.设置分页信息
//1.设置分页信息 Page<User> page = new Page<>(1,10);
2.写配置类（config/MyBatisPlusConfig）
package com.fzy.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MyBatisPlusConfig {
// 最新版
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
3.查询
package com.fzy.controller;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.fzy.entity.User;
import com.fzy.service.UserService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/user")
@RequiredArgsConstructor
public class UserController {
private final UserService userService;
//分页查询
@RequestMapping("/page")
public IPage<User> page(){
//1.设置分页信息
Page<User> page = new Page<>(2,3);
//2.查询
Page<User> page1 = userService.page(page);
System.out.println("page1 = " + page1);
System.out.println("page1.getSize() = " + page1.getSize());
System.out.println("page1.getCurrent() = " + page1.getCurrent());
System.out.println("page1.getPages() = " + page1.getPages());
System.out.println("page1.getTotal() = " + page1.getTotal());
System.out.println("page1.getRecords() = " + page1.getRecords());
return page1;
}
}
二、多表分页+条件查询
1.设置分页信息，和查询条件
//多表分页+条件查询
@RequestMapping("/list3")
public Page<UserAndDeptVo> list3(){
//设置分页信息
Page<UserAndDeptVo> page = new Page<>(1, 5);
//设置查询条件
User user = new User(){{
setUsername("冬冬");
}};
//查询
Page<UserAndDeptVo> pageInfo = userService.findUserAndDeptByPage(page,user);
System.out.println("pageInfo.getTotal() = " + pageInfo.getTotal());
System.out.println("pageInfo.getSize() = " + pageInfo.getSize());
System.out.println("pageInfo.getPages() = " + pageInfo.getPages());
return pageInfo;
}
2.写配置类（config/MyBatisPlusConfig）
package com.fzy.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MyBatisPlusConfig {
// 最新版
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
3.封装类
package com.fzy.vo;
import com.fzy.entity.Department;
import com.fzy.entity.User;
import lombok.Data;
@Data
public class UserAndDeptVo {
private User user;
private Department department;
}
4.编写动态sql，映射
Page<UserAndDeptVo> selectUserAndDeptByPage(@Param("page") Page<UserAndDeptVo> page,@Param("user") User user);
<?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.fzy.mapper.UserMapper">
<sql id="Base_Column_List">
id,username,password,
dept_id,is_delete,pro_img
</sql>
<resultMap id="UserAndDeptVo" type="com.fzy.vo.UserAndDeptVo">
<association property="user" javaType="com.fzy.entity.User">
<id property="id" column="id" jdbcType="INTEGER"/>
<result property="username" column="username" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="deptId" column="dept_id" jdbcType="VARCHAR"/>
<result property="isDelete" column="is_delete" jdbcType="INTEGER"/>
<result property="proImg" column="pro_img" jdbcType="VARCHAR"/>
</association>
<association property="department" javaType="com.fzy.entity.Department">
<id property="deptId" column="dept_id" jdbcType="VARCHAR"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
</association>
</resultMap>
//分页连表查询
<select id="selectUserAndDeptByPage" resultMap="UserAndDeptVo">
SELECT u.*,d.*
FROM `user` u
LEFT JOIN `departments` d
ON u.dept_id=d.dept_id
<where>
<if test="user!=null and user!=''">
u.username=#{user.username}
</if>
</where>
</select>
</mapper>
分页+条件查询
@Override
public PageApiRest getList(PageDTO<SysPermission> pageDto) {
//1.设置分页信息
IPage<SysPermission> page = new Page<>(pageDto.getPage(),pageDto.getSize());
//2.设置查询条件
QueryWrapper<SysPermission> queryWrapper = new QueryWrapper<>();
//查询条件不为空，并且标题不为空串，就模糊查询标题
//资源名称
if(pageDto.getWhere()!=null && !StringUtils.isEmpty(pageDto.getWhere().getPermName())){
queryWrapper.lambda().like(SysPermission::getPermName,pageDto.getWhere().getPermName());
}
//资源路径
if(pageDto.getWhere()!=null && !StringUtils.isEmpty(pageDto.getWhere().getLinkUrl())){
queryWrapper.lambda().like(SysPermission::getLinkUrl,pageDto.getWhere().getLinkUrl());
}
//资源分类
if(pageDto.getWhere()!=null && !StringUtils.isEmpty(pageDto.getWhere().getPermDesc())){
queryWrapper.lambda().like(SysPermission::getPermDesc,pageDto.getWhere().getPermDesc());
}
//查询指定字段
queryWrapper.select("id", "title");
//排除字段查询
queryWrapper.select(SysPermission.class, info -> !info.getColumn().equals("content")
&& !info.getColumn().equals("update_by"));
//3.查询
IPage<SysPermission> page1 = baseMapper.selectPage(page, queryWrapper);
//4.封装，返回
PageApiRest<SysPermission> pageRest = new PageApiRest<>();
pageRest.setTotal(page1.getTotal());
pageRest.setData(page1.getRecords());
return pageRest;
}
三、mybatis-plus的修改时间自动填充
1.时间字段上配置注解
@TableField(fill = FieldFill.INSERT) //插入时生效 private Date createTime; @TableField(fill = FieldFill.INSERT_UPDATE) //插入和修改时生效 private Date updateTime;
2.编写配置类(MyMetaObjectHandler.java)
package com.fzy.config;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
this.strictInsertFill(metaObject, "createTime", Date.class, new Date()); // 起始版本 3.3.0(推荐使用)
this.strictInsertFill(metaObject, "updateTime", Date.class, new Date()); // 起始版本 3.3.0(推荐使用)
}
@Override
public void updateFill(MetaObject metaObject) {
this.strictUpdateFill(metaObject, "updateTime", Date.class, new Date()); // 起始版本 3.3.0(推荐)
}
}
