准备工作
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 ) ) ;
return interceptor;
}
}
4. 数据
分页查询
1. 使用条件构造器
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 ) ;
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
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 ( ) {
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
}
]