mybatis自定义复杂条件拼接

谭飓风

于 2024-08-26 19:24:25 发布

阅读量100

点赞数 1

文章标签： mybatis sql 数据库

本文链接：https://blog.csdn.net/weixin_53083143/article/details/141571497

版权

定义条件对象

/**
 * @description:
 * @author: yilv
 * @create: 2024-08-26 17:35
 **/
@Data
public class ComplexQueryDto {
    /**
     * 查询参数
     */
    private List<QueryDto> params=new ArrayList<>();

    public ComplexQueryDto addParam(QueryDto queryDto){
        this.params.add(queryDto);
        return this;
    }
}

定义查询对象

/**
 * @description: 通用查询对象
 * @author: yilv
 * @create: 2024-08-26 17:42
 **/
@Data
@Accessors(chain =true)
public class QueryDto {
    /**
     * 字段名
     */
    private String filedName;

    /**
     * 比较运算符
     */
    private String opt;

    /**
     * 单值
     */
    private String filedValue;

    /**
     * 多值集合
     */
    private String[] filedValues;
    /**
     * 关系符
     */
    private String rel;
}

查询条件枚举

/**
 * 操作符
 */
@Getter
public enum QueryOpt {
    //非空
    NOT_NULL("IS NOT NULL"),
    //包含")
    IN("IN"),
    //不包含")
    NOT_IN("NOT IN"),
    //存在")
    LIKE("LIKE"),
    //不存在")
    NOT_LIKE("NOT LIKE"),
    //等于")
    EQ("="),
    //不等于")
    NE("!="),
    //大于")
    GT(">"),
    //大于等于")
    GE(">="),
    //小于")
    LT("<"),
    //小于等于")
    LE("<="),
    //范围")
    BETWEEN("BETWEEN");
    private final String seg;

    public String getSeg() {
        return seg;
    }

    QueryOpt(String seg) {
        this.seg = seg;
    }
}

查询关系枚举

/**
 * 关联运算符
 */
@Getter
public enum QueryRel {
    //与")
    AND("AND"),
    //或")
    OR("OR"),
    //非")
    NOT("NOT");
    private final String seg;

    public String getSeg() {
        return seg;
    }

    QueryRel(String seg) {
        this.seg = seg;
    }
}

编写条件拼接SQL

    <sql id="complexQuery">
        <if test="params != null and params.size() != 0">
            <foreach item="param" index="index" collection="params">
                AND ${param.filedName} ${param.opt}
                <if test="param.opt == 'BETWEEN'">#{param.filedValues[0]} AND #{param.filedValues[0]}</if>
                <if test="param.opt == 'IN'||param.opt == 'NOT IN'">
                    <foreach item="filedValue" index="index" collection="param.filedValues" open="(" separator="," close=")">
                        #{filedValue}
                    </foreach>
                </if>
            </foreach>
        </if>
    </sql>


    <select id="??Query" resultMap="??">
        SELECT
        <include refid="div_Column_List"></include>
        FROM
        <include refid="table"></include>
        <include refid="complexQuery"></include>
    </select>