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;@TableLogicprivate 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 {/*** 添加分页插件*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); // 如果配置多个插件, 切记分页最后添加// 如果有多数据源可以不配具体类型, 否则都建议配上具体的 DbTypereturn 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 {@Autowiredprivate SysUserMapper sysUserMapper;@Testvoid 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 {@Autowiredprivate SysUserMapper sysUserMapper;@Testvoid 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 {@Autowiredprivate SysUserMapper sysUserMapper;@Testvoid selectUsersByGender() {// 对性别做分组统计List<SysUserDTO> sysUsers = sysUserMapper.selectUsersByGender();System.out.println(JSON.toJSONString(sysUsers));}}

• 打印结果

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