根据json生成sql

qq_41808386

已于 2023-09-22 11:25:49 修改

阅读量448

点赞数

文章标签： json sql 数据库

于 2023-09-22 11:21:39 首次发布

本文链接：https://blog.csdn.net/qq_41808386/article/details/133162876

版权

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.ArrayList;
import java.util.List;

public class SQLGenerator {
    public static void main(String[] args) {
        String jsonString = "[{\"expressionDetails\": [{\"expression\": \">\", \"expressionOrder\": 1, \"field\": \"budget\", \"value\": \"99999\"}, {\"expression\": \"==\", \"expressionOrder\": 2, \"relation\": \"or\", \"field\": \"budget\", \"value\": \"99999\"}], \"ruleOrder\": 1}, {\"expressionDetails\": [{\"expression\": \"!=\", \"expressionOrder\": 1, \"relation\": \"\", \"field\": \"status\", \"value\": \"CAMPAIGN_STATUS_ENABLE\"}, {\"expression\": \"!=\", \"expressionOrder\": 2, \"relation\": \"and\", \"field\": \"status\", \"value\": \"AD_STATUS_DISABLE\"}], \"relation\": \"or\", \"ruleOrder\": 2}]";
        String sql = generateSQL(jsonString);
        System.out.println("Generated SQL:\n" + sql);
    }

    public static String generateSQL(String jsonString) {
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            JsonNode rootNode = objectMapper.readTree(jsonString);
            List<String> sqlList = new ArrayList<>();

            for (JsonNode ruleNode : rootNode) {
                JsonNode expressionDetailsNode = ruleNode.get("expressionDetails");
                String relation = ruleNode.has("relation") ? ruleNode.get("relation").asText() : "and"; // Default to 'and'
                String ruleOrder = ruleNode.get("ruleOrder").asText();

                String ruleSql = processExpressionDetails(expressionDetailsNode);
                sqlList.add("(" + ruleSql + ")");
                
                // Add 'or' relation if specified
                if ("or".equals(relation)) {
                    sqlList.add("OR");
                } else {
                    sqlList.add("AND");
                }
            }

            // Remove the last 'AND' or 'OR'
            if (!sqlList.isEmpty()) {
                sqlList.remove(sqlList.size() - 1);
            }

            String sql = String.join(" ", sqlList);
            return sql;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static String processExpressionDetails(JsonNode expressionDetailsNode) {
        List<String> expressionList = new ArrayList<>();

        for (JsonNode conditionNode : expressionDetailsNode) {
            String expression = conditionNode.get("expression").asText();
            String field = conditionNode.get("field").asText();
            String value = conditionNode.get("value").asText();
            String relation = conditionNode.has("relation") ? conditionNode.get("relation").asText() : "and"; // Default to 'and'

            String condition;

            if (expression.equals("==")) {
                condition = field + " = '" + value + "'";
            } else if (expression.equals(">")) {
                condition = field + " > " + value;
            } else if (expression.equals("!=")) {
                condition = field + " != '" + value + "'";
            } else {
                // Handle other expressions as needed
                condition = ""; // You can provide custom logic here
            }

            expressionList.add("(" + condition + ")");

            // Add 'and' or 'or' relation
            if ("or".equals(relation)) {
                expressionList.add("OR");
            } else {
                expressionList.add("AND");
            }
        }

        // Remove the last 'AND' or 'OR'
        if (!expressionList.isEmpty()) {
            expressionList.remove(expressionList.size() - 1);
        }

        String expressionSql = String.join(" ", expressionList);
        return expressionSql;
    }
}

拼接符和括号可按需求调整

调整后：

public static String generateSQL(String jsonString) {
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            JsonNode rootNode = objectMapper.readTree(jsonString);
            List<String> sqlList = new ArrayList<>();

            for (JsonNode ruleNode : rootNode) {
                JsonNode expressionDetailsNode = ruleNode.get("expressionDetails");
                String relation = "";
                if (ruleNode.has("relation")){
                    relation = ruleNode.get("relation").asText();
                }
                int ruleOrder = 0;
                if (ruleNode.has("ruleOrder")){
                    ruleOrder = ruleNode.get("ruleOrder").asInt();
                }

                String ruleSql = processExpressionDetails(expressionDetailsNode);
                if ("or".equalsIgnoreCase(relation)) {
                    sqlList.add("OR");
                } else if ("and".equalsIgnoreCase(relation)){
                    sqlList.add("AND");
                }

                sqlList.add("(" + ruleSql + ")");

                /* sqlList.add("(" + ruleSql + ")");*/
            }

            return String.join(" ", sqlList);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static String processExpressionDetails(JsonNode expressionDetailsNode) {
        List<String> expressionList = new ArrayList<>();
        if (null == expressionDetailsNode) return "";

        for (JsonNode conditionNode : expressionDetailsNode) {
            String expression = conditionNode.get("expression").asText();
            String field = conditionNode.get("field").asText();
            String value = conditionNode.get("value").asText();
            String relation = "";
            if (conditionNode.has("relation")){
                relation = conditionNode.get("relation").asText();
            }
            //String relation = conditionNode.has("relation") ? conditionNode.get("relation").asText() : "and"; // Default to 'and'
            String condition;

            if ("or".equalsIgnoreCase(relation)) {
                expressionList.add("OR");
            } else if ("and".equalsIgnoreCase(relation)){
                expressionList.add("AND");
            }

            if (expression.equals("==")) {
                condition = field + " = '" + value + "'";
            } else if (expression.equals(">")) {
                condition = field + " > " + value;
            } else if (expression.equals("!=")) {
                condition = field + " != '" + value + "'";
            } else {
                condition = "";
            }
            expressionList.add(condition);

        }

        return String.join(" ", expressionList);
    }

    public static void main(String[] args) {
        String jsonString = "[{\"expressionDetails\": [{\"expression\": \">\", \"expressionOrder\": 1, \"field\": \"budget\", \"value\": \"99999\"}, {\"expression\": \"==\", \"expressionOrder\": 2, \"relation\": \"or\", \"field\": \"budget\", \"value\": \"99999\"}], \"ruleOrder\": 1}, {\"expressionDetails\": [{\"expression\": \"!=\", \"expressionOrder\": 1, \"relation\": \"\", \"field\": \"status\", \"value\": \"CAMPAIGN_STATUS_ENABLE\"}, {\"expression\": \"!=\", \"expressionOrder\": 2, \"relation\": \"and\", \"field\": \"status\", \"value\": \"AD_STATUS_DISABLE\"}], \"relation\": \"or\", \"ruleOrder\": 2}]";
        String sql = generateSQL(JSONUtil.toJsonPrettyStr(jsonString));
        System.out.println("Generated SQL:\n" + sql);
    }