目录

前言问题描述思路分析解决方案方案一方案二方案三方案四

结语

前言

简单描述一下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 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 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 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 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 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 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 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 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 转(||)。

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

好文推荐

评论可见,请评论后查看内容,谢谢!!!
 您阅读本篇文章共花了: