MyBatis-Plus分页查询、分组查询

准备工作

1. 实体类

• 对地址字段address使用字段类型转换器，将List转为字符串数组保存在数据库中

package com.example.server.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import lombok.Data;

import java.util.List;

@Data
@TableName(autoResultMap = true)
public class SysUser {

    @TableId(type = IdType.AUTO)
    private String id;

    @TableLogic
    private Integer delFlag;

    private String name;

    private Integer age;

    private String gender;

    @TableField(typeHandler = JacksonTypeHandler.class)
    private List<String> address;

}

• 对应的表

CREATE TABLE SYS_USER
(
    ID       INT PRIMARY KEY    NOT NULL AUTO_INCREMENT,
    NAME     VARCHAR(100)       NOT NULL,
    AGE      INT                NOT NULL,
    GENDER   VARCHAR(100)       NOT NULL,
    ADDRESS  VARCHAR(100)       NOT NULL,
    DEL_FLAG SMALLINT DEFAULT 0 NOT NULL
);

2. Mapper类

package com.example.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.server.entity.SysUser;

public interface SysUserMapper extends BaseMapper<SysUser> {


}

3. 分页插件

@Configuration
public class MybatisPlusConfig {

    /**
     * 添加分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); // 如果配置多个插件, 切记分页最后添加
        // 如果有多数据源可以不配具体类型, 否则都建议配上具体的 DbType
        return interceptor;
    }
}

4. 数据

分页查询

1. 使用条件构造器

• 使用lambdaQuery条件构造器

package com.example.server;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.server.entity.SysUser;
import com.example.server.mapper.SysUserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class ServerApplicationTests {

    @Autowired
    private SysUserMapper sysUserMapper;

    @Test
    void selectList() {
        IPage<SysUser> page = new Page<>(1, 10);
        // 只查询18和20岁的用户
        IPage<SysUser> userIPage = sysUserMapper.selectPage(page, Wrappers.<SysUser>lambdaQuery().in(SysUser::getAge, List.of(18, 20)).orderByAsc(SysUser::getId));
        System.out.println(JSON.toJSONString(userIPage));
    }

}

• 打印结果

{
    "current": 1,
    "pages": 1,
    "records": [
        {
            "address": [
                "北京市朝阳区"
            ],
            "age": 20,
            "delFlag": 0,
            "gender": "MALE",
            "id": "1",
            "name": "Jack"
        },
        {
            "address": [
                "北京市朝阳区",
                "南京市鼓楼区"
            ],
            "age": 18,
            "delFlag": 0,
            "gender": "MALE",
            "id": "2",
            "name": "Fisher"
        }
    ],
    "size": 10,
    "total": 2
}

2. 使用自定义sql

• 定义查询条件vo类

package com.example.server.vo;


import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class SysUserVO {

    private Integer ageMax;

    private Integer ageMin;

    private List<String> genderList;

    private List<String> addressList;

}

• 动态sql，因为SysUser中使用了字段类型处理器typeHandler，需要手动指定resultMap（格式为”mybatis-plus_实体类”，和BaseMapper后的泛型类一致），否则address字段的转换器会不生效

package com.example.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.server.entity.SysUser;
import com.example.server.vo.SysUserVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;


public interface SysUserMapper extends BaseMapper<SysUser> {

    @ResultMap("mybatis-plus_SysUser")
    @Select("<script>" +
            "SELECT * FROM SYS_USER WHERE DEL_FLAG = 0 " +
            "  <if test='sysUser.ageMax != null'>" +
            "AND AGE <![CDATA[ <= ]]> #{sysUser.ageMax} " +
            "  </if>" +
            "  <if test='sysUser.ageMin != null'>" +
            "AND AGE <![CDATA[ >= ]]> #{sysUser.ageMin} " +
            "  </if>" +
            " <if test='sysUser.genderList != null and sysUser.genderList.size() > 0'>" +
            "AND GENDER IN " +
            "<foreach collection='sysUser.genderList' item='item' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "  </if>" +
            " <if test='sysUser.addressList != null and sysUser.addressList.size() > 0'>" +
            "AND ( " +
            "<foreach collection='sysUser.addressList' item='item' index='index' open='' close='' separator='OR'>" +
            "ADDRESS LIKE concat('%', #{item}, '%') " +
            "</foreach>" +
            " )" +
            "  </if>" +
            "</script>")
    IPage<SysUser> selectUsersByPage(@Param("page") IPage<SysUser> page, @Param("sysUser") SysUserVO sysUser);

}

• 测试方法

package com.example.server;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.server.entity.SysUser;
import com.example.server.mapper.SysUserMapper;
import com.example.server.vo.SysUserVO;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class ServerApplicationTests {

    @Autowired
    private SysUserMapper sysUserMapper;

    @Test
    void selectUsersByPage() {
        // 分页查询，年龄在18到35岁之间，性别男或女，地址在北京或南京的用户
        IPage<SysUser> page = new Page<>(1, 10);
        SysUserVO sysUserVO = SysUserVO.builder().ageMax(35).ageMin(18).genderList(List.of("MALE", "FEMALE")).addressList(List.of("北京市", "南京市")).build();
        IPage<SysUser> userIPage = sysUserMapper.selectUsersByPage(page, sysUserVO);
        System.out.println(JSON.toJSONString(userIPage));
    }

}

• 打印结果

{
    "current": 1,
    "pages": 1,
    "records": [
        {
            "address": [
                "北京市朝阳区"
            ],
            "age": 20,
            "delFlag": 0,
            "gender": "MALE",
            "id": "1",
            "name": "Jack"
        },
        {
            "address": [
                "北京市朝阳区",
                "南京市鼓楼区"
            ],
            "age": 18,
            "delFlag": 0,
            "gender": "MALE",
            "id": "2",
            "name": "Fisher"
        },
        {
            "address": [
                "北京市朝阳区"
            ],
            "age": 35,
            "delFlag": 0,
            "gender": "MALE",
            "id": "5",
            "name": "James"
        }
    ],
    "size": 10,
    "total": 3
}

分组查询

1. 分组结果类

• 对性别进行分组统计

package com.example.server.dto;

import lombok.Data;

@Data
public class SysUserDTO {

    private String gender;

    private Integer num;

}

2. 自定义sql

package com.example.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.server.dto.SysUserDTO;
import com.example.server.entity.SysUser;
import org.apache.ibatis.annotations.Select;

import java.util.List;


public interface SysUserMapper extends BaseMapper<SysUser> {

    @Select("select gender, count(*) num from sys_user where del_flag = 0 GROUP BY gender")
    List<SysUserDTO> selectUsersByGender();

}

• 这里没有使用mybatis-plus的groupBy方法进行统计，使用继承了BaseMapper<SysUser>后使用原生的api得到的返回值是SysUser，但是这里不好引入统计结果字段，如果加了，统计没有问题，但是查详情的时候有问题，因为表中没有这个字段

3. 测试类

package com.example.server;

import com.alibaba.fastjson.JSON;
import com.example.server.dto.SysUserDTO;
import com.example.server.mapper.SysUserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class ServerApplicationTests {

    @Autowired
    private SysUserMapper sysUserMapper;

    @Test
    void selectUsersByGender() {
        // 对性别做分组统计
        List<SysUserDTO> sysUsers = sysUserMapper.selectUsersByGender();
        System.out.println(JSON.toJSONString(sysUsers));
    }

}

• 打印结果

[
    {
        "gender": "MALE",
        "num": 4
    },
    {
        "gender": "FEMALE",
        "num": 1
    }
]