1、前 言
最近在开发过程中,需要用 MybatisPlus 实现类似以下形式的 SQL 语句,动态拼接多个条件,进行查询。
select *
from user_info
where is_deleted = 0
and is_tag = 1
and(user_type = '自有' or user_type = '非合作方' or user_type = '合作方')
and(user_role = '管理员' or user_role = '普通用户' or user_role = '访客');
由于不想在 mapper.xml 中写 SQL 语句,因此选择直接在 java 代码中实现,在此记录一下。
2、实 现
这里建立项目工程,整合 SpringBoot 和 MybatisPlus 的过程省略。
2.1 建表语句
这里随便建一个表,用于展示这个示例,建表语句如下:
create table `user_info` (
`user_id` int(11) not null auto_increment COMMENT '主键id',
`user_name` varchar(500) not null COMMENT '用户名称',
`is_tag` tinyint(1) not null default '0' COMMENT '是否标记,0-否,1-是',
`is_deleted` tinyint(1) not null default '0' COMMENT '是否删除,0-否,1-是',
`user_type` varchar(200) default null COMMENT '用户类型',
`user_role` varchar(200) default null COMMENT '用户角色',
`user_from` varchar(700) not null COMMENT '用户来源',
`create_time` datetime default CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间',
primary key (`user_id`)
) engine = InnoDB auto_increment = 254 default CHARSET = utf8mb4 COMMENT = '用户记录表';
2.2 实体类
package com.yuhuofei.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.experimental.SuperBuilder;
import java.time.LocalDateTime;
/**
* @author yuhuofei2021
* @version 1.0
* @type UserInfoDO
* @desc 用户记录表
* @date 2022-08-09 上午10:26:02
*/
@Data
@TableName("user_info")
@ApiModel(description = "用户记录表")
public class UserInfoDO {
private static final long serialVersionUID = -1L;
@ApiModelProperty(value = "主键id")
@TableId(value = "user_id", type = IdType.AUTO)
@JsonFormat(shape = JsonFormat.Shape.STRING)
private Integer userId;
@ApiModelProperty(value = "用户名称")
private String userName;
@ApiModelProperty(value = "是否标注,0-否,1-是")
private Boolean isTag;
@ApiModelProperty(value = "是否删除,0-否,1-是")
private Boolean isDeleted;
@ApiModelProperty(value = "用户类型")
private String userType;
@ApiModelProperty(value = "用户角色")
private String userRole;
@ApiModelProperty(value = "用户来源")
private String userFrom;
@ApiModelProperty(value = "创建时间")
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
@ApiModelProperty(value = "更新时间")
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updateTime;
}
2.3 测 试
MybatisPlus 动态拼接条件,实现查询如下:
@Autowired
private UserInfoMapper userInfoMapper;
public static void main(String[] args) {
List<String> userTypeList = new ArrayList<>();
userTypeList.add("自有");
userTypeList.add("合作方");
userTypeList.add("非合作方");
List<String> userRoleList = new ArrayList<>();
userRoleList.add("管理员");
userRoleList.add("普通用户");
userRoleList.add("访客");
//拼接查询条件
QueryWrapper<UserInfoDO> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("is_deleted", false);
queryWrapper.eq("is_tag", true);
if (null != userTypeList && !userTypeList.isEmpty()) {
queryWrapper.and(wrapper -> userTypeList.forEach(rapper -> wrapper.or(o -> o.eq("user_type", rapper))));
}
if (null != userRoleList && !userRoleList.isEmpty()) {
queryWrapper.and(wrapper -> userRoleList.forEach(rapper -> wrapper.or(o -> o.eq("user_role", rapper))));
}
List<UserInfoDO> userInfoList = userInfoMapper.selectList(queryWrapper);
}
主要是下面这一段,获取列表中的参数,动态拼接查询条件
//拼接查询条件
QueryWrapper<UserInfoDO> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("is_deleted", false);
queryWrapper.eq("is_tag", true);
if (null != userTypeList && !userTypeList.isEmpty()) {
queryWrapper.and(wrapper -> userTypeList.forEach(rapper -> wrapper.or(o -> o.eq("user_type", rapper))));
}
if (null != userRoleList && !userRoleList.isEmpty()) {
queryWrapper.and(wrapper -> userRoleList.forEach(rapper -> wrapper.or(o -> o.eq("user_role", rapper))));
}
希望今天随手记录下的东西,能帮到有缘人!