JsqlParser工具类获取 where 参数

淡淡的烟草味﹌ 2023-10-08 17:07 2阅读 0赞

## 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