JsqlParser工具类获取 where 参数
周睿范
1、需求：
需求：目前有一方需要使用where一类的sql当参数传过来，我们接受参数，然后转成我们本地的参数，去查询我们数据。
举例：
- 对方：类似这种json ，key 是一个where的sql
{"vulKeys": "vul_id='DADASDASDSAAAAAAAAAA' and key between 1 and 5 "}
- 我方：需要转换成我们自己的映射字段
{"vulKeys": "vul_id_a='DADASDASDSAAAAAAAAAA' and key_a between 1 and 5 "}
这里面会包含所有可能的sql运算符号，= 、 >=、 <=、 or、 and 、（a=b and c=d）等等 现在来搞一下。
2、代码
引入pom：
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>3.1</version>
</dependency>
实现 ：
package com.***;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.parser.SimpleNode;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import org.apache.commons.lang3.StringUtils;
import java.util.*;
/**
* @author fei.chen
* @date 2022/10/26下午 3:56
*/
public class SqlParserUtil {
public static String getSqlWhereHandel(String sql, Map<String, String> cloumMap) throws JSQLParserException {
Select select = (Select) CCJSqlParserUtil.parse(sql);
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
Expression expr = CCJSqlParserUtil.parseCondExpression(plainSelect.getWhere().toString());
List<Map<String, Object>> arrList = new ArrayList<>();
//递归处理
sqlHandel(expr, arrList);
//列映射替换
String sqlResult = cloumHandel(arrList, cloumMap);
return sqlResult;
}
public static String cloumHandel(List<Map<String, Object>> arrList, Map<String, String> cloumMap) {
for (Map<String, Object> map : arrList) {
if (map.size() == 3) {
Optional<Object> first = map.values().stream().findFirst();
String s = cloumMap.get(first.get().toString());
if (StringUtils.isNotEmpty(s)) {
map.put("left", s);
}
}
}
StringBuilder sb = new StringBuilder();
for (Map<String, Object> map : arrList) {
if (map.size() == 3) {
sb.append(map.get("left")).append(" ").append(map.get("operator")).append(" ").append(map.get("right")).append(" ");
} else {
sb.append(map.values().stream().findFirst().get()).append(" ");
;
}
}
return sb.toString();
}
public static Object sqlHandel(Object expr, List<Map<String, Object>> arrList) {
if (expr instanceof OrExpression) {
Expression leftExpression = ((OrExpression) expr).getLeftExpression();
sqlHandel(leftExpression, arrList);
Map<String, Object> param2 = new HashMap<>();
param2.put("operator", "OR");
arrList.add(param2);
Expression rightExpression = ((OrExpression) expr).getRightExpression();
sqlHandel(rightExpression, arrList);
} else if (expr instanceof AndExpression) {
Expression leftExpression = ((AndExpression) expr).getLeftExpression();
sqlHandel(leftExpression, arrList);
Map<String, Object> param = new HashMap<>();
param.put("operator", "AND");
arrList.add(param);
Expression rightExpression = ((AndExpression) expr).getRightExpression();
sqlHandel(rightExpression, arrList);
} else if (expr instanceof EqualsTo) {
Map<String, Object> node = getNode((Expression) expr);
arrList.add(node);
System.out.println("node-----------------" + node.get("left") + node.get("operator") + node.get("right"));
} else if (expr instanceof Parenthesis) {
Expression leftExpression = ((Parenthesis) expr).getExpression();
Map<String, Object> param1 = new HashMap<>();
param1.put("operator", "(");
arrList.add(param1);
sqlHandel(leftExpression, arrList);
Map<String, Object> param2 = new HashMap<>();
param2.put("operator", ")");
arrList.add(param2);
} else if (expr instanceof GreaterThan) {
Map<String, Object> node = getGreaterThanNode((Expression) expr);
arrList.add(node);
System.out.println("node-----------------" + node.get("left") + node.get("operator") + node.get("right"));
} else if (expr instanceof LikeExpression) {
Map<String, Object> node = getLikeNode((LikeExpression) expr);
arrList.add(node);
System.out.println("node-----------------" + node.get("left") + node.get("operator") + node.get("right"));
}else if(expr instanceof Between){
Map<String, Object> node = getBetweenNode((Between) expr);
arrList.add(node);
System.out.println("node-----------------" + node.get("left") + node.get("operator") + node.get("right"));
}
return null;
}
public static Map<String, Object> getNode(Expression expression) {
Map<String, Object> param = new HashMap<>();
SimpleNode astNode = expression.getASTNode();
Expression node = (Expression) astNode.jjtGetValue();
param.put("left", ((EqualsTo) node).getLeftExpression().toString());
param.put("operator", ((EqualsTo) node).getStringExpression());
param.put("right", ((EqualsTo) node).getRightExpression().toString());
return param;
}
public static Map<String, Object> getGreaterThanNode(Expression expression) {
Map<String, Object> param = new HashMap<>();
String leftExpression = ((Between) expression).getLeftExpression().toString();
Expression rightExpression = ((Between) expression).getBetweenExpressionStart();
Expression stringExpression = ((Between) expression).getBetweenExpressionEnd();
param.put("left", leftExpression);
param.put("operator", stringExpression);
param.put("right", rightExpression);
return param;
}
public static Map<String, Object> getLikeNode(Expression expression) {
Map<String, Object> param = new HashMap<>();
SimpleNode astNode = expression.getASTNode();
param.put("left", ((LikeExpression) expression).getLeftExpression().toString());
param.put("operator", astNode.jjtGetFirstToken().toString());
param.put("right", astNode.jjtGetLastToken().toString());
return param;
}
public static Map<String, Object> getBetweenNode(Expression expression) {
Map<String, Object> param = new HashMap<>();
String start = ((Between) expression).getBetweenExpressionStart().toString();
String end = ((Between) expression).getBetweenExpressionEnd().toString();
param.put("left", ((Between) expression).getLeftExpression().toString());
param.put("operator", "between");
param.put("right", Integer.parseInt(start) +" AND " + Integer.parseInt(end));
return param;
}
}
// 使用方法
public static void main(String[] args) throws JSQLParserException {
String aa = "{\"assetInfoRange\": \"ip like '10.65.128.1'\",\"vulKeys\": \"vul_id='DADASDASDSAAAAAAAAAA' and key between 1 and 5 \"}";
"and key between 1 and 5 \"}";
JSONObject jsonObject = JSON.parseObject(aa);
String assetInfoRange = jsonObject.get("assetInfoRange").toString();
// 测试这个哦
String vulKeys = jsonObject.get("vulKeys").toString();
String sql = "select * from aaa where " + vulKeys;
Map<String, String> map1 = new HashMap<>();
map1.put("vul_id", "vul_id_a");
map1.put("university", "university_a");
map1.put("gpa", "gpa_a");
// sql是我们要转换的 ； map1 是把sql的字段转换成查询我们数据库的字段
String s = getSqlWhereHandel(sql, map1);
System.out.println(s);
}
// 最后运行结果
vul_id_a = 'MVM-2011-1554368855919435776' AND key between 1 AND 5
类似资料：