逻辑表达式 | ! 转sql逻辑查询 and or not 逻辑表达式转sql逻辑查询使用mybatis @SelectProvider

Diane ·

更新时间:2024-11-13

· 585 次阅读

查询需求

在这里插入图片描述

输入格式

处理过程

//keywords 为输入 逻辑查询关键词
    public List countList(String keywords) {
        //逻辑语句转换
        String title = ExpToSqlUtils.expToSql(keywords, "title", " like ", 1);
        //查询结果
        List list = testMapper.getList(title);
        return list ;
    }

转换过程


import org.apache.commons.lang3.StringUtils;
import java.util.HashSet;
import java.util.Set;
import java.util.regex.Pattern;
/**
 * & | ! and or not 空格
 * 转换为sql逻辑查询语句或EsSql的match语出
 */
public class Demo {
    private static final HashSet EXP_TYPE_SET = new HashSet();
    //定义静态符号常量
    static {
        EXP_TYPE_SET.add("!");
        EXP_TYPE_SET.add("|");
        EXP_TYPE_SET.add("&");
        EXP_TYPE_SET.add("(");
        EXP_TYPE_SET.add(")");
    }
    /**
     * 替换and or not 空格 为  | ！ & 表达式
     */
    private static String changeExp(String str){
        if (StringUtils.isNotBlank(str)){
            str = str.replaceAll("and","&").replaceAll("or","|").replaceAll("not","!").replaceAll(" ","&");
        }
        return str;
    }
    /**
     * 逻辑运算符 转sql
     * @param exp  运算符表达式
     * @param columeName  查询字段
     * @param join  查询字段与查询值 之间的 连接符
     * @param flag
     *              0 普通Sql
     *              1 mysql like 专用
     */
    public static String expToSql(String exp,String columeName,String join,Integer flag){
        //替换and or not 空格为！ & |
        exp = changeExp(exp);
        String sColumeName = "@@@@";
        //替换中英文（(
        if (exp.contains("（") || exp.contains("）")|| exp.contains("！")){
            exp = exp.replace("（","(").replace("）",")").replace("！","!");
        }
        String tempExp = exp;
        //匹配正则 是否包含逻辑表达式
        String regex="[^|!&()]{1,}";
        Pattern p=Pattern.compile(regex);
        String exp_temp = exp;
        //添加`特殊字符进行完全匹配（被包含，相同词等问题处理）
        if(p.matcher(exp_temp.substring(0,1)).matches() ){
            exp_temp = "`"+exp_temp;
        }
        if(p.matcher(exp_temp.substring(exp_temp.length()-1,exp_temp.length())).matches() ){
            exp_temp = exp_temp+"`";
        }
        exp_temp = dealFor(exp_temp,p);
        for (String s : EXP_TYPE_SET) {
            tempExp = tempExp.replace(s,"%%");
        }
        String[] split = tempExp.split("%%");
        Set param = new HashSet();
        for (String s : split) {
            if(StringUtils.isBlank(s)){
                continue;
            }
            param.add(s);
        }
        //循环处理 逻辑查询
        for (String s : param) {
            if(flag.equals(1) ){
                if("like".equals(StringUtils.trim(join).toLowerCase())){
                    //like
                    exp_temp = exp_temp.replace("`"+s+"`", "(" + sColumeName + " " + join + " '%" + s + "%' )");
                }else {
                    //match(es 中使用sql查询 可用到)
                    exp_temp = exp_temp.replace("`"+s+"`", " match(" + sColumeName + ", '" + s + "','operator=and' )");
                }
            }else {
                //普通字段
                exp = exp.replace(s, "(" + sColumeName + " " + join + " '" + s + "' )");
            }
        }
        //替换特殊字符为逻辑符号
        exp_temp = exp_temp.replace("!"," not ");
        exp_temp = exp_temp.replace("&"," and ");
        exp_temp = exp_temp.replace("|"," or ");
        exp_temp = exp_temp.replace(")("," ) and ( ");
        String replace = exp_temp.replace(sColumeName, columeName);
        //替换掉多余的`
        return replace.replaceAll("`","");
    }
    //循环处理 有包含的情况--社会安全事件关键词1|通古斯卡大爆炸&!(通古斯|生活)|ddgfw
    private static String dealFor(String exp_temp, Pattern p){
        boolean flag = false;
        for(int i=exp_temp.length(); i>=2 ;i--){
            String s1 = exp_temp.substring(i-2,i-1);
            String s2 = exp_temp.substring(i-1,i);
            if(p.matcher(s1).matches() && !p.matcher(s2).matches() && !s1.equals("`")  ){
                exp_temp = exp_temp.replace(s1+s2, s1+"`"+s2);
                flag = true;
            }
            if(!p.matcher(s1).matches() && p.matcher(s2).matches()  && !s2.equals("`")){
                exp_temp = exp_temp.replace(s1+s2, s1+"`"+s2);
                flag = true;
            }
            if (s1.equals("`") && s2.equals("`")){
                exp_temp = exp_temp.replace(s1+s2, "`");
                flag = true;
            }
        }
        if (flag){
            //递归
            exp_temp = dealFor(exp_temp,p);
        }
        return  exp_temp;
    }
    public static void main(String[] args) {
        String result1 = expToSql("大陆or明天 香港and通古斯卡and大爆炸|通古|爆炸|危险化学品&!(通古斯|(！生活&危险))", "title"," like ",1);
        String result2 = expToSql("快乐科技|快乐就在您身边|快快乐乐生活", "text"," like ",1);
        String result3 = expToSql("(新定价&（健健康康生活|健康电子)）&!晨星", "name"," match ",1);
        System.out.println("result1=="+result1);
        System.out.println("result2=="+result2);
        System.out.println("result3=="+result3);
    }
}

结果展示

result1==(title  like  '%大陆%' ) or (title  like  '%明天%' ) and (title  like  '%香港%' ) and (title  like  '%通古斯卡%' ) and (title  like  '%大爆炸%' ) or (title  like  '%通古%' ) or (title  like  '%爆炸%' ) or (title  like  '%危险化学品%' ) and  not ((title  like  '%通古斯%' ) or ( not (title  like  '%生活%' ) and (title  like  '%危险%' )))
result2==(text  like  '%快乐科技%' ) or (text  like  '%快乐就在您身边%' ) or (text  like  '%快快乐乐生活%' )
result3==( match(name, '新定价','operator=and' ) and ( match(name, '健健康康生活','operator=and' ) or  match(name, '健康电子','operator=and' ))) and  not  match(name, '晨星','operator=and' )

Mybatis 拼接自定义sql 使用@SelectProvider

@Repository
public interface TestMapper {
    @SelectProvider(type = TestProvider.class, method = "countList")
    List getList(String title);
}

Provider拼接sql类可以进行参数自定义的处理

public class TestProvider{
    public String countList(String param){
        String sql = "select * from test_table where "+param;
        return sql;
    }
}

简单可以满足需求
转换过程写的有点杂乱
有优化的共同交流