解决JexlEngine执行where条件时，等号(=)替换成双等号(==)问题

前言

简单描述一下JexlEngine的用法，使用它可以将一段表达式转成Java代码执行。

问题描述

String WHERE = "app_id=005";

假设上面的where变量需要传递给JexlEngine调用，由于JexlEngine执行表达式的时候，一个等号（=）是会报语法错误的，需要转成两个等号（==）才能执行，如果后面还有and、or条件拼接，则 and 要转成 && ，or 要转成 ||（这一步可转可不转，不过一定不能是大写）。

String WHERE = "pay_time > ='2019/10/21' and pay_time < = '2019/10/22' and app_id = '001'";

需求：给出上面的where条件，我们需要将有且仅有一个等号（=）的情况转成两个等号（==），>=和<=无需转换，最后再将and 转成 && ，or 要转成 || 。

即想要的效果如下：

String WHERE = "pay_time > ='2019/10/21' && pay_time < = '2019/10/22' and app_id == '001'";

思路分析

我们先使用toCharArray将字符串转换为字符数组，然后根据字符的索引去判断一个等号（=）出现的位置，再进行替换成两个等号（==）。

难点：由于是根据等号（=）的索引来进行对应判断和查找，所以在进行判断的时候，需要考虑很多种格式问题，比如：

一个等号（=）的时候，左右有没有空格，或者是多个空格的情况，
如果where直接是两个等号（==）的时候，我们就忽略无需对它进行转换。

解决方案

根据上述的思路分析，我们直接上代码。

方案一

📌普通版

我们先根据需求，将大致的实现代码写出来，最后再去考虑多种情况出现的问题。

package com.example.business.util;

import java.util.ArrayList;

/**
 * @ClassName: Test
 * @Description: 临时测试类
 * @Author yang
 * @Date 2022/12/20
 * @Version 1.0
 */
public class Test {


    public static void main(String[] args) {
        String WHERE = "app_id='005' AND keyid='0101'";
//        String WHERE = "app_id=005 AND keyid=0101";
//        String WHERE = "app_id= 005 AND keyid = 0101";
//        String WHERE = "app_id=005";

        if (WHERE.contains("\'")) {
			//去除字符串多余空格，只能存在一个空格（即把两个以上的空格替换成一个）
            ArrayList<Integer> indexList = new ArrayList<>();
            String strChars = WHERE.replaceAll(" {2,}", " ");
            char[] chars = strChars.toCharArray();
            for (int i = 0; i < chars.length; i++) {
                if (chars[i] == '=') {
                    indexList.add(i);
                }
            }
            for (Integer integer : indexList) {
                StringBuilder builder = new StringBuilder();
                int index = integer;
                boolean appendFlag = false;
                if (chars[index - 1] != '>' && chars[index - 1] != '<' && chars[index - 1] != '!') {
                    if (chars[index + 1] == '=') {//如：String WHERE = "pay_time == '2019/10/22'";
                        System.out.println("原句返回：" + WHERE);
                        return;
                    }
                    if (chars[index + 1] == ' ' && chars[index + 2] == '=') {//如：String WHERE = "pay_time =   ='2019/10/22'";或String WHERE = "pay_time =   = '2019/10/22'";
                        System.out.println("22222222：");
                        dealTwoEqual(WHERE);
                        return;
                    }
                    boolean flag = chars[index + 1] == ' ' || (chars[index + 1] == '\'' && chars[index + 1] != '=');//如：String WHERE = "pay_time = '2019/10/22'";或String WHERE = "pay_time =   '2019/10/22'";
                    while ((index + 1 < chars.length) && flag) {
                        flag = chars[index + 1] == ' ' || (chars[index + 1] == '\'' && chars[index + 1] != '=');
                        if (flag) {
                            appendFlag = true;
                            index++;
                        }
                    }


                }
                if (appendFlag) {
                    int arrIndex = 0;
                    for (int i = 0; i < indexList.size(); i++) {
                        int value = indexList.get(i);
                        if (value == integer) {
                            arrIndex = i;
                        }
                    }

                    if (arrIndex == 0) {
                        builder.append(strChars, 0, integer);
                        builder.append("==");
                        builder.append(strChars, index, chars.length);
                    } else {
                        builder.append(strChars, 0, integer + 1);
                        builder.append("==");
                        builder.append(strChars, index + 1, chars.length + 1);
                    }
                    strChars = builder.toString();
                }
            }
            System.out.println("builder：" + strChars);
            String str = strChars;
            if (str.contains(" and ")) {
                str = str.replace(" and ", " && ");
            }

            if (str.contains(" AND ")) {
                str = str.replace(" AND ", " && ");
            }

            if (str.contains(" or ")) {
                str = str.replace(" or ", " || ");
            }

            if (str.contains(" OR ")) {
                str = str.replace(" OR ", " || ");
            }
            System.out.println("str：" + str);
        } else {
            //去除字符串多余空格，只能存在一个空格（即把两个以上的空格替换成一个）
            ArrayList<Integer> indexList = new ArrayList<>();
            String strChars = WHERE.replaceAll(" {2,}", " ");
            char[] chars = strChars.toCharArray();
            for (int i = 0; i < chars.length; i++) {
                if (chars[i] == '=') {
                    indexList.add(i);
                }
            }
            for (Integer integer : indexList) {
                StringBuilder builder = new StringBuilder();
                int index = integer;
                boolean appendFlag = false;
                if (chars[index - 1] != '>' && chars[index - 1] != '<' && chars[index - 1] != '!') {
                    if (chars[index + 1] == '=') {
                        System.out.println("原句返回：" + WHERE);
                        return;
                    }
                    if (chars[index + 1] == ' ' && chars[index + 2] == '=') {
                        System.out.println("22222222：");
                        dealTwoEqual(WHERE);
                        return;
                    }
                    boolean flag = (chars[index] == '=' && chars[index + 1] == ' ') || (chars[index] == '=' && chars[index + 1] != ' ') || (chars[index] == '=' && chars[index + 1] != '\'' && chars[index + 1] != '=');
                    while ((index + 1 < chars.length) && flag) {
                        flag = (chars[index] == '=' && chars[index + 1] == ' ') || (chars[index] == '=' && chars[index + 1] != ' ') || (chars[index] == '=' && chars[index + 1] != '\'' && chars[index + 1] != '=');
                        if (flag) {
                            appendFlag = true;
                            index++;
                        }
                    }


                }
                if (appendFlag) {
                    int arrIndex = 0;
                    for (int i = 0; i < indexList.size(); i++) {
                        int value = indexList.get(i);
                        if (value == integer) {
                            arrIndex = i;
                        }
                    }

                    if (arrIndex == 0) {
                        builder.append(strChars, 0, integer);
                        builder.append("==");
                        builder.append(strChars, index, chars.length);
                    } else {
                        builder.append(strChars, 0, integer + 1);
                        builder.append("==");
                        builder.append(strChars, index + 1, chars.length + 1);
                    }
                    strChars = builder.toString();
                }
            }
            String str = strChars;
            if (str.contains(" and ")) {
                str = str.replace(" and ", " && ");
            }

            if (str.contains(" AND ")) {
                str = str.replace(" AND ", " && ");
            }

            if (str.contains(" or ")) {
                str = str.replace(" or ", " || ");
            }

            if (str.contains(" OR ")) {
                str = str.replace(" OR ", " || ");
            }
            System.out.println("str：" + str);
        }


    }


    public static void dealOneEqual(String where) {
        ArrayList<Integer> indexList = new ArrayList<>();
        String strChars = where.replaceAll(" {2,}", " ");
        char[] chars = strChars.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            if (chars[i] == '=') {
                indexList.add(i);
            }
        }

        StringBuilder builder = new StringBuilder();
        for (Integer integer : indexList) {
            int index = integer;
            boolean appendFlag = false;
            if (chars[index - 1] != '>' && chars[index - 1] != '<' && chars[index - 1] != '!') {
                appendFlag = true;
                boolean flag = chars[index + 1] == ' ';
                while ((index + 1 < chars.length) && flag) {
                    flag = chars[index + 1] == ' ';
                    if (flag) {
                        index++;
                    }
                }
            }
            if (appendFlag) {
                builder.append(strChars, 0, integer);
                builder.append("==");
                builder.append(strChars, index + 1, chars.length);
            }
        }
        System.out.println("一个等号：" + builder);
    }


    public static void dealTwoEqual(String where) {
        ArrayList<Integer> indexList = new ArrayList<>();
        String strChars = where.replaceAll(" {2,}", " ");
        char[] chars = strChars.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            if (chars[i] == '=') {
                indexList.add(i);
            }
        }

        StringBuilder builder = new StringBuilder();
        for (Integer integer : indexList) {
            int index = integer;
            boolean appendFlag = false;
            boolean flag = chars[index + 1] == ' ' && chars[index + 2] == '=';
            while ((index + 2 < chars.length) && flag) {
                flag = chars[index + 1] == ' ' && chars[index + 2] == '=';
                if (flag) {
                    appendFlag = true;
                    index++;
                }
            }
            if (appendFlag) {
                builder.append(strChars, 0, integer);
                builder.append("==");
                builder.append(strChars, index + 2, chars.length);
            }
        }
        System.out.println("两个等号：" + builder);
    }

}

输出结果
builder：app_id==‘005’ AND keyid==‘0101’
str：app_id==‘005’ && keyid==‘0101’

经过多重测试后，发现此方案行不通，有些条件还是无法满足，比较条件超过两个，如下：

String WHERE = "app_id='005' AND keyid='0101' AND keyid='0101'";

输出结果
builder：app_id==‘005’ AND keyid==‘0101’ AND keyi===’0101
str：app_id==‘005’ && keyid==‘0101’ && keyi===’0101

第三个就会引起索引替换问题出错，变成三个等号了，没办法，只能重新修改逻辑，继续改善。

★不推荐★

方案二

📌进阶版

针对方案一不能满足多个条件替换的问题，我们对替换拼接的索引改成了动态的，先看代码：

package com.example.business.util;

import java.util.ArrayList;

/**
 * @ClassName: Test
 * @Description: 临时测试类
 * @Author yang
 * @Date 2022/12/20
 * @Version 1.0
 */
public class Test2 {


    public static void main(String[] args) {
//        String WHERE = "app_id='005' AND keyid='0101' AND keyid='0101'";//√
//        String WHERE = "app_id >= '005' AND keyid ='0101' AND keyid ='0101' AND keyid ='0101'";//X
//        String WHERE = "keyid ='0101' AND app_id >= '005' AND keyid ='0101' AND keyid ='0101'";//X
//        String WHERE = "keyid ='0101' AND app_id >= '005' AND app_id <= '005' AND keyid ='0101'";//X
//        String WHERE = "app_id >= '005' AND keyid ='0101'";//√
//        String WHERE = "keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "keyid ='0101' AND keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "pay_time >=2019/10/21 and pay_time <= 2019/10/22 and app_id = 001";//X
//        String WHERE = "pay_time >='2019/10/21' and pay_time <= '2019/10/22' and app_id = '001'";//X
//        String WHERE = "app_id=005 AND keyid=0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101";//√
        String WHERE = "app_id= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//√
//        String WHERE = "app_id>= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND app_id <=  005 AND keyid =   0101 AND keyid = 0105";//X
//        String WHERE = "keyid =    0101 AND keyid =    0105";//√
//        String WHERE = "app_id=005";//√
//        String WHERE = "app_id!=005";


        if (WHERE.contains("\'")) {
            //去除字符串多余空格，只能存在一个空格（即把两个以上的空格替换成一个）
            ArrayList<Integer> indexList = new ArrayList<>();
            String strChars = WHERE.replaceAll(" {2,}", " ");
            if (strChars.contains("> =") || strChars.contains("< =") || strChars.contains("! =") || strChars.contains("= =") || strChars.contains("==")) {
                System.out.println("sql语句格式错误，注意比较符（>=、<=、!=）中间不允许出现空格，不能出现两个等号（==）");
                return;
            }
            char[] chars = strChars.toCharArray();
            for (int i = 0; i < chars.length; i++) {
                if (chars[i] == '=') {
                    indexList.add(i);
                }
            }
            boolean sign = false;
            boolean num = false;
            for (Integer integer : indexList) {
                StringBuilder builder = new StringBuilder();
                int index = integer;
                boolean appendFlag = false;
                if (chars[index - 1] != '>' && chars[index - 1] != '<' && chars[index - 1] != '!') {
                    boolean flag = chars[index] == '=';
                    while ((index + 1 < chars.length) && flag) {
                        flag = chars[index] == '=';
                        if (flag) {
                            appendFlag = true;
                            index++;
                        }
                    }
                } else {
                    sign = true;
                }
//                if (sign) {
//                    builder.append(strChars, 0, integer);
                    builder.append("==");
//                    builder.append(strChars, index, chars.length);
//                    strChars = builder.toString();
//                }
                if (appendFlag) {
                    int arrIndex = 0;
                    for (int i = 0; i < indexList.size(); i++) {
                        int value = indexList.get(i);
                        if (value == integer) {
                            arrIndex = i;
                        }
                    }
                    int sum = 0;
                    char[] chars2 = strChars.toCharArray();
                    if (arrIndex == 0) {
                        builder.append(strChars, 0, integer);
                        builder.append("==");
                        builder.append(strChars, index, chars.length);
                    } else if (arrIndex > 0 && sign) {
                        builder.append(strChars, 0, integer);
                        builder.append("==");
                        builder.append(strChars, index, chars.length);
                        sign = false;
                        num = true;
                    } else if (arrIndex > 0 && !sign && num) {
                        builder.append(strChars, 0, integer + 1);
                        builder.append("==");
                        builder.append(strChars, index + 1, chars.length + 1);
                        num = false;
                    } else {
                        builder.append(strChars, 0, integer + arrIndex);
                        builder.append("==");
                        builder.append(strChars, index + arrIndex, chars.length + arrIndex);
                    }
                    strChars = builder.toString();
                }
            }
//            System.out.println("builder：" + strChars);
            String str = strChars.toLowerCase();
            if (str.contains(" and ")) {
                str = str.replace(" and ", " && ");
            }
            if (str.contains(" or ")) {
                str = str.replace(" or ", " || ");
            }
            System.out.println("str：" + str);
        } else {
            //去除字符串多余空格，只能存在一个空格（即把两个以上的空格替换成一个）
            ArrayList<Integer> indexList = new ArrayList<>();
            String strChars = WHERE.replaceAll(" {2,}", " ");
            if (strChars.contains("> =") || strChars.contains("< =") || strChars.contains("! =") || strChars.contains("= =") || strChars.contains("==")) {
                System.out.println("sql语句格式错误，注意比较符（>=、<=、!=）中间不允许出现空格，不能出现两个等号（==）");
                return;
            }
            char[] chars = strChars.toCharArray();
            for (int i = 0; i < chars.length; i++) {
                if (chars[i] == '=') {
                    indexList.add(i);
                }
            }
            for (Integer integer : indexList) {
                StringBuilder builder = new StringBuilder();
                int index = integer;
                boolean appendFlag = false;
                if (chars[index - 1] != '>' && chars[index - 1] != '<' && chars[index - 1] != '!') {
                    boolean flag = chars[index] == '=';
                    while ((index + 1 < chars.length) && flag) {
                        flag = chars[index] == '=';
                        if (flag) {
                            appendFlag = true;
                            index++;
                        }
                    }
                }
                if (appendFlag) {
                    int arrIndex = 0;
                    for (int i = 0; i < indexList.size(); i++) {
                        int value = indexList.get(i);
                        if (value == integer) {
                            arrIndex = i;
                        }
                    }
                    char[] chars2 = strChars.toCharArray();
                    if (arrIndex == 0) {
                        builder.append(strChars, 0, integer);
                        builder.append("==");
                        builder.append(strChars, index, chars.length);
                    } else {
                        builder.append(strChars, 0, integer + arrIndex);
                        builder.append("==");
                        builder.append(strChars, index + arrIndex, chars.length + arrIndex);
                    }
                    strChars = builder.toString();
                }
            }
            String str = strChars.toLowerCase();
            if (str.contains(" and ")) {
                str = str.replace(" and ", " && ");
            }
            if (str.contains(" or ")) {
                str = str.replace(" or ", " || ");
            }
            System.out.println("str：" + str);
        }
    }    

}

先看结果：

输出结果
str：app_id== 005 && keyid == 0101 && keyid == 0101 && keyid == 0101 && keyid == 0101 && keyid == 0101 && keyid == 0101 && keyid == 0101

发现可以解决方案一的问题。可是，随着慢慢的测试，bug还是出现了。比如条件中不是单纯的等号，而是包含大于等于、小于等于（>=、<=）等等情况，就会抛索引越界异常。如下：

String WHERE = "app_id >= '005' AND keyid ='0101' AND keyid ='0101' AND keyid ='0101'";

输出结果
Exception in thread “main” java.lang.IndexOutOfBoundsException: start 66, end 72, s.length() 71
at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:539)
at java.lang.StringBuilder.append(StringBuilder.java:175)
at com.example.business.util.Test2.main(Test2.java:102)

经过多重的调试，发现根据这个where语句的判断是存在很多问题，很多种时候，空格也是导致判断条件出现漏判，是行不通的，很多种多重条件，无法同时兼容，最后参考sql的语法，决定对目标where进行一下限定判断，即（比较符（>=、<=、!=、<>）中间不允许出现空格，不能出现两个等号（==或= =））

如果是这样，那逻辑就会容易很多了，我们可以忽略比较符（>=、<=、!=、<>）中间出现空格的问题，以及两个等号（==或= =））的问题，这样，我们可以直接根据等号（=）进行分割，对分割后的数组进行判断拼接。详细代码请看方案三。

★不推荐★

方案三

📌终极版

总结了方案一和方案二出现的各种问题，对原先的代码进行了重构，先看代码：

package com.example.business.util;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName: Test
 * @Description: 临时测试类
 * @Author yang
 * @Date 2022/12/20
 * @Version 1.0
 */
public class Test3 {


    public static void main(String[] args) {
//        String WHERE = "app_id='005' AND keyid='0101' AND keyid='0101'";//√
//        String WHERE = "app_id >= '005' AND keyid ='0101' AND keyid ='0101' AND keyid ='0101'";//
//        String WHERE = "keyid ='0101' AND app_id <> '005' AND keyid <>'0101' AND keyid ='0101'";//X
        String WHERE = "keyid ='0101' AND app_id >= '005' AND app_id <= '005' AND keyid ='0101'";//X
//        String WHERE = "app_id >= '005' AND keyid ='0101'";//√
//        String WHERE = "keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "keyid ='0101' AND keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "pay_time >=2019/10/21 and pay_time <= 2019/10/22 and app_id = 001";//X
//        String WHERE = "pay_time >='2019/10/21' and pay_time <= '2019/10/22' and app_id = '001'";//X
//        String WHERE = "app_id=005 AND keyid=0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//√
//        String WHERE = "app_id>= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND app_id <=  005 AND keyid = 0101 AND keyid = 0105";//X
//        String WHERE = "keyid =    0101 AND keyid =    0105";//√
//        String WHERE = "app_id=005";//√
//        String WHERE = "app_id!=005";
//        String WHERE = "app_id< >005";
//        String WHERE = "app_id< =005";
//        String WHERE = "app_id> =005";
//        String WHERE = "app_id>005";
//        String WHERE = "app_id>=005 and app_id BETWEEN '2018-06-28' AND '2018-09-28'";
//        String WHERE = "app_id like '2018-09-28'";
//        String WHERE = "app_id in (1,2) and a=8";
//        String WHERE = "app_id in (1,2) and a>=8";
//        String WHERE = "app_id<005 and appid=005 or key=50 and a>=10 or b<=50 and c!=6";
//        String WHERE = "app_id BETWEEN '2018-06-28' AND '2018-09-28' and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
//        String WHERE = "app_id like '2018-09-28' and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
//        String WHERE = "app_id in (1,2) and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
        WHERE = WHERE.toLowerCase();
        String strChars = WHERE.replaceAll(" {2,}", " ");
        if (strChars.contains("> =") || strChars.contains("< =") || strChars.contains("! =") || strChars.contains("< >") || strChars.contains("= =") || strChars.contains("==")) {
            System.out.println("sql语句格式错误，注意比较符（>=、<=、!=、<>）中间不允许出现空格，不能出现两个等号（==或= =）");
            return;
        }
        String[] arr = strChars.split("=");
        System.out.println(Arrays.toString(arr));
        StringBuilder str = new StringBuilder();

        for (int i = 0; i < arr.length; i++) {
            String a = arr[i];
            if (!a.contains("<") && !a.contains(">") && !a.contains("!") && !(i == arr.length - 1)) {
                str.append(a + "==");
            } else if (i == arr.length - 1) {
                str.append(a);
            } else if (a.contains("<>")) {
                str.append(a + "==");
            } else {
                 str.append(a + "=");
            }
        }
        String strWhere = str.toString().replace("<>", "!=");
        System.out.println(strWhere);

        if (strWhere.contains(" and ")) {
            strWhere = strWhere.replace(" and ", " && ");
        }
        if (strWhere.contains(" or ")) {
            strWhere = strWhere.replace(" or ", " || ");
        }

        System.out.println(strWhere);     
    }
}

输出结果
[keyid , ‘0101’ and app_id >, ‘005’ and app_id <, ‘005’ and keyid , ‘0101’]
keyid ==‘0101’ and app_id >= ‘005’ and app_id <= ‘005’ and keyid ==‘0101’
keyid ==‘0101’ && app_id >= ‘005’ && app_id <= ‘005’ && keyid ==‘0101’

原本以为方案三已经完成所有需求，结果经过多种运算符的测试时，还是发现有个bug，就是只有一个大于号或者小于号（< 、>）时，拼接会有问题，如下：

String WHERE = "app_id<005 and appid=005 or key=50 and a>=10 or b<=50 and c!=6";

输出结果
[app_id<005 and appid, 005 or key, 50 and a>, 10 or b<, 50 and c!, 6]
app_id<005 and appid=005 or key50 and a>=10 or b<=50 and c!=6
app_id<005 && appid=005 || key50 && a>=10 || b<=50 && c!=6

经过Debug调试发现，只有一个只有一个大于号或者小于号（< 、>）时，直接走的是else的判断，就拼了一个等号（=），明显是不符合要求的，那么怎么去实现这个bug吗？经过细心观察，发现了一个特点，就是以等号（=）分割后，每段字符后面如果不是特殊字符，即不是大于号、小于号和叹号（< 、>、!）时，是需要拼接两个等号（==）的，这样才符合前面的需求。所以，对else部分的代码又进行了一层代码，详情请往下看。

★不推荐★

方案四

📌标准版

针对方案三出现的bug，排查发现，是漏掉了一个判断限定条件，即分割后的每段字符串，我们应该检验一下它的最后一个字符，是不是特殊字符（即是否包含 <、>、 !）。

package com.example.business.util;

import java.util.Arrays;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName: Test
 * @Description: 临时测试类
 * @Author yang
 * @Date 2022/12/20
 * @Version 1.0
 */
public class Test3 {


    public static void main(String[] args) {
//        String WHERE = "app_id='005' AND keyid='0101' AND keyid='0101'";//√
//        String WHERE = "app_id >= '005' AND keyid ='0101' AND keyid ='0101' AND keyid ='0101'";//
//        String WHERE = "keyid ='0101' AND app_id <> '005' AND keyid <>'0101' AND keyid ='0101'";//X
//        String WHERE = "keyid ='0101' AND app_id >= '005' AND app_id <= '005' AND keyid ='0101'";//X
//        String WHERE = "app_id >= '005' AND keyid ='0101'";//√
//        String WHERE = "keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "keyid ='0101' AND keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "pay_time >=2019/10/21 and pay_time <= 2019/10/22 and app_id = 001";//X
//        String WHERE = "pay_time >='2019/10/21' and pay_time <= '2019/10/22' and app_id = '001'";//X
//        String WHERE = "app_id=005 AND keyid=0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//√
//        String WHERE = "app_id>= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND app_id <=  005 AND keyid = 0101 AND keyid = 0105";//X
//        String WHERE = "keyid =    0101 AND keyid =    0105";//√
//        String WHERE = "app_id=005";//√
//        String WHERE = "app_id!=005";
//        String WHERE = "app_id< >005";
//        String WHERE = "app_id< =005";
//        String WHERE = "app_id> =005";
//        String WHERE = "app_id>005";
//        String WHERE = "app_id>=005 and app_id BETWEEN '2018-06-28' AND '2018-09-28'";
//        String WHERE = "app_id like '2018-09-28'";
//        String WHERE = "app_id in (1,2) and a=8";
//        String WHERE = "app_id in (1,2) and a>=8";
        String WHERE = "app_id<005 and appid=005 or key=50 and a>=10 or b<=50 and c!=6";
//        String WHERE = "app_id BETWEEN '2018-06-28' AND '2018-09-28' and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
//        String WHERE = "app_id like '2018-09-28' and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
//        String WHERE = "app_id in (1,2) and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
        WHERE = WHERE.toLowerCase();
        String strChars = WHERE.replaceAll(" {2,}", " ");
        if (strChars.contains("> =") || strChars.contains("< =") || strChars.contains("! =") || strChars.contains("< >") || strChars.contains("= =") || strChars.contains("==")) {
            System.out.println("sql语句格式错误，注意比较符（>=、<=、!=、<>）中间不允许出现空格，不能出现两个等号（==或= =）");
            return;
        }
        String[] arr = strChars.split("=");
        System.out.println(Arrays.toString(arr));
        StringBuilder str = new StringBuilder();

        for (int i = 0; i < arr.length; i++) {
            String a = arr[i];
            if (!a.contains("<") && !a.contains(">") && !a.contains("!") && !(i == arr.length - 1)) {
                str.append(a + "==");
            } else if (i == arr.length - 1) {
                str.append(a);
            } else if (a.contains("<>")) {
                str.append(a + "==");
            } else {
                String last = a.substring(a.length() - 1);
                if (!(isSpecialChar(last))) {
                    str.append(a + "==");
                } else {
                    str.append(a + "=");
                }
            }
        }
        String strWhere = str.toString().replace("<>", "!=");
        System.out.println(strWhere);

        if (strWhere.contains(" and ")) {
            strWhere = strWhere.replace(" and ", " && ");
        }
        if (strWhere.contains(" or ")) {
            strWhere = strWhere.replace(" or ", " || ");
        }

        System.out.println(strWhere);
    }


    public static boolean isSpecialChar(String str) {
        String regEx = "[ _`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]|\n|\r|\t";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);
        return m.find();
    }
}

输出结果
[app_id<005 and appid, 005 or key, 50 and a>, 10 or b<, 50 and c!, 6]
app_id<005 and appid005 or key50 and a>=10 or b<=50 and c!=6
app_id<005 && appid005 || key50 && a>=10 || b<=50 && c!=6

经过了上面的多种条件测试，我能想到的sql语法的都测试了，暂时没有发现问题。

★推荐★

附 JexlEngine 方法

/**
 * 这个方法能够将字符串当作java代码来执行
 *
 * @param express
 * @return
 */
public static Object executeString(String express, Map<String, Object> parameter) {
    JexlEngine jexlEngine = new JexlEngine();

    Expression expression;
    try {
        expression = jexlEngine.createExpression(express);//将参数中的字符串传进来
    } catch (Exception e) {
        throw new JexlException(null, "Jexl异常");
    }

    JexlContext jexlContext = new MapContext();
    for (String key : parameter.keySet()) {//遍历传过来的参数
        jexlContext.set(key, parameter.get(key));//将传进来的参数替换到表达式中去
    }
    if (null == expression.evaluate(jexlContext)) {//执行表达式
        return "";//为空就返回空字符串
    }
    return expression.evaluate(jexlContext);//执行表达式，返回结果
}

完整测试源码：

package com.example.business.util;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName: Test
 * @Description: 临时测试类
 * @Author yang
 * @Date 2022/12/20
 * @Version 1.0
 */
public class Test3 {


    public static void main(String[] args) {
//        String WHERE = "app_id='005' AND keyid='0101' AND keyid='0101'";//√
//        String WHERE = "app_id >= '005' AND keyid ='0101' AND keyid ='0101' AND keyid ='0101'";//
//        String WHERE = "keyid ='0101' AND app_id <> '005' AND keyid <>'0101' AND keyid ='0101'";//X
//        String WHERE = "keyid ='0101' AND app_id >= '005' AND app_id <= '005' AND keyid ='0101'";//X
//        String WHERE = "app_id >= '005' AND keyid ='0101'";//√
//        String WHERE = "keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "keyid ='0101' AND keyid ='0101' AND app_id >= '005'";//√
//        String WHERE = "pay_time >=2019/10/21 and pay_time <= 2019/10/22 and app_id = 001";//X
//        String WHERE = "pay_time >='2019/10/21' and pay_time <= '2019/10/22' and app_id = '001'";//X
//        String WHERE = "app_id=005 AND keyid=0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101";//√
//        String WHERE = "app_id= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//√
//        String WHERE = "app_id>= 005 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND keyid = 0101";//X
//        String WHERE = "app_id >=  005 AND app_id <=  005 AND keyid = 0101 AND keyid = 0105";//X
//        String WHERE = "keyid =    0101 AND keyid =    0105";//√
//        String WHERE = "app_id=005";//√
//        String WHERE = "app_id!=005";
//        String WHERE = "app_id< >005";
//        String WHERE = "app_id< =005";
//        String WHERE = "app_id> =005";
//        String WHERE = "app_id>005";
//        String WHERE = "app_id>=005 and app_id BETWEEN '2018-06-28' AND '2018-09-28'";
//        String WHERE = "app_id like '2018-09-28'";
//        String WHERE = "app_id in (1,2) and a=8";
//        String WHERE = "app_id in (1,2) and a>=8";
        String WHERE = "app_id<005 and appid=005 or key=50 and a>=10 or b<=50 and c!=6";
//        String WHERE = "app_id BETWEEN '2018-06-28' AND '2018-09-28' and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
//        String WHERE = "app_id like '2018-09-28' and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
//        String WHERE = "app_id in (1,2) and appid=005 or key=50 and a>=10 or b<=50 and c!=6;";
        WHERE = WHERE.toLowerCase();
        String strChars = WHERE.replaceAll(" {2,}", " ");
        if (strChars.contains("> =") || strChars.contains("< =") || strChars.contains("! =") || strChars.contains("< >") || strChars.contains("= =") || strChars.contains("==")) {
            System.out.println("sql语句格式错误，注意比较符（>=、<=、!=、<>）中间不允许出现空格，不能出现两个等号（==或= =）");
            return;
        }
        String[] arr = strChars.split("=");
        System.out.println(Arrays.toString(arr));
        StringBuilder str = new StringBuilder();

        for (int i = 0; i < arr.length; i++) {
            String a = arr[i];
            if (!a.contains("<") && !a.contains(">") && !a.contains("!") && !(i == arr.length - 1)) {
                str.append(a + "==");
            } else if (i == arr.length - 1) {
                str.append(a);
            } else if (a.contains("<>")) {
                str.append(a + "==");
            } else {
                String last = a.substring(a.length() - 1);
                if (!(isSpecialChar(last))) {
                    str.append(a + "==");
                } else {
                    str.append(a + "=");
                }
            }
        }
        String strWhere = str.toString().replace("<>", "!=");
        System.out.println(strWhere);

        if (strWhere.contains(" and ")) {
            strWhere = strWhere.replace(" and ", " && ");
        }
        if (strWhere.contains(" or ")) {
            strWhere = strWhere.replace(" or ", " || ");
        }

        System.out.println(strWhere);
        // 测试功能
        Map<String, Object> map = new HashMap<>();
        map.put("keyid", "0101");
        map.put("app_id", "006");
        Object o = StringVerifyUtil.executeString("keyid =='0101' and  app_id != '005'", map);
        System.out.println(o);
    }


    public static boolean isSpecialChar(String str) {
        String regEx = "[ _`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]|\n|\r|\t";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);
        return m.find();
    }
}

输出结果
[app_id<005 and appid, 005 or key, 50 and a>, 10 or b<, 50 and c!, 6]
app_id<005 and appid005 or key50 and a>=10 or b<=50 and c!=6
app_id<005 && appid005 || key50 && a>=10 || b<=50 && c!=6
true

结语

这种处理where语句的，涉及到的各种写法实在是太多了，光是前面写逻辑都写了不少于10个版本，种种问题都是警告种种测试才发现，很难一步到位写好整个逻辑判断。（在此说明：此种做法是根据某些场景的特殊需求，迫不得已才去做。实际中应该没有几个人会有这样的需求。）原来以为在Java中能找到合适的数据库对象类处理，奈何没有找到（如果有知道的，欢迎评论区不吝指教）。

归根结底就是使用JexlEngine这个类（能将字符转成Java代码运行），它不认sql的一个等号（=），必须是两个等号（==）才行，而且AND连接语句也不能是大写，要小写and，这里建议转（&&），or 转（||）。

创作不易，感谢您的点赞与支持。

原文链接：https://blog.csdn.net/weixin_44563573/article/details/128632746

解决JexlEngine执行where条件时，等号(=)替换成双等号(==)问题

目录

前言

问题描述

思路分析

解决方案

方案一

方案二

方案三

方案四

结语

相关推荐