用mybatis-plus实现分页、多条件查询

mybatis-plus
条件构造器QueryWrapper常用方法

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/**
  *附加条件构造器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
2
//1.设置分页信息
Page<User> page = new Page<>(1,10);　

2.写配置类（config/MyBatisPlusConfig）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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.查询

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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.设置分页信息，和查询条件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//多表分页+条件查询
@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）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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.封装类

?
1
2
3
4
5
6
7
8
9
10
11
12
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，映射

?
1
Page<UserAndDeptVo> selectUserAndDeptByPage(@Param("page") Page<UserAndDeptVo> page,@Param("user") User user);

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<?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>

分页+条件查询

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@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.时间字段上配置注解

?
1
2
3
4
5
@TableField(fill = FieldFill.INSERT)    //插入时生效
private Date createTime;
 
@TableField(fill = FieldFill.INSERT_UPDATE)     //插入和修改时生效
private Date updateTime;

2.编写配置类(MyMetaObjectHandler.java)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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(推荐)
 
    }
}

　　

posted @   智昕  阅读(6516)  评论(0)  编辑  收藏  举报
相关博文：
· Spring Boot 内置工具类
· springBoot2.*使用redis集群/单机方法
· mybatis-plus分页查询
· 2、Mybatis-Plus
· mybatis-plus
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示