记录一下遇到的问题 java将json数据解析为sql语句

这样的json数据转换成sql语句

 解决办法

import java.util.Iterator;

import java.util.Set;
import java.util.Map.Entry;
 
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
 
public class Sql
{
    public static String parseSQL(String json)
    {
        JsonParser parser = new JsonParser();
        JsonObject obj = (JsonObject) parser.parse(json);;
        String table = obj.get("table").getAsString();
        String op_type = obj.get("op_type").getAsString();
        String sql = "";
        if("I".equals(op_type))
        {
            sql += "INSERT INTO " + table + " (";
            JsonObject after = (JsonObject) obj.get("after");
            Set<Entry<String, JsonElement>> entry = after.entrySet();
            Iterator<Entry<String, JsonElement>> it = entry.iterator();
            String vs = " values (";
            while(it.hasNext())
            {
                Entry<String, JsonElement> elem = it.next();
                String key = elem.getKey();
                String val = elem.getValue().toString();
                sql += key + ", ";
                vs += val + ", ";
            }
            sql = sql.replaceAll(",\s*$", "");
            vs = vs.replaceAll(",\s*$", "");
            sql += ") " + vs + ")";
        }
        else if("U".equals(op_type))
        {
            sql += "UPDATE " + table + " SET ";
            JsonObject after = (JsonObject) obj.get("after");
            Set<Entry<String, JsonElement>> entry = after.entrySet();
            Iterator<Entry<String, JsonElement>> it = entry.iterator();
            while(it.hasNext())
            {
                Entry<String, JsonElement> elem = it.next();
                String key = elem.getKey();
                String val = elem.getValue().toString();
                sql += key + "=" + val + ", ";
            }
            sql = sql.replaceAll(",\s*$", "");
            sql += " WHERE ";
            after = (JsonObject) obj.get("before");
            entry = after.entrySet();
            it = entry.iterator();
            while(it.hasNext())
            {
                Entry<String, JsonElement> elem = it.next();
                String key = elem.getKey();
                String val = elem.getValue().toString();
                sql += key + "=" + val + " AND ";
            }
            sql = sql.replaceAll("\s*AND\s*$", "");
        }
        else if("D".equals(op_type))
        {
            sql += "DELETE FROM " + table + " WHERE ";
            JsonObject after = (JsonObject) obj.get("before");
            Set<Entry<String, JsonElement>> entry = after.entrySet();
            Iterator<Entry<String, JsonElement>> it = entry.iterator();
            while(it.hasNext())
            {
                Entry<String, JsonElement> elem = it.next();
                String key = elem.getKey();
                String val = elem.getValue().toString();
                sql += key + "=" + val + " AND ";
            }
            sql = sql.replaceAll("\s*AND\s*$", "");
        }
        return sql;
    }

 测试

public static void main(String[] args)
    {
        String insert =
                "{"table":"GG.TCUSTORD","op_type":"I","op_ts":"2013-06-02 22:14:36.000000","current_ts":"2015-09-18T13:39:35.447000","pos":"00000000000000001444","tokens":{"R":"AADPkvAAEAAEqL2AAA"},"after":{"CUST_CODE":"WILL","ORDER_DATE":"1994-09-30:15:33:00","PRODUCT_CODE":"CAR","ORDER_ID":"144","PRODUCT_PRICE":17520.00,"PRODUCT_AMOUNT":3,"TRANSACTION_ID":"100"}}";
        String update =
                "{"table":"GG.TCUSTORD","op_type":"U","op_ts":"2013-06-02 22:14:41.000000","current_ts":"2015-09-18T13:39:35.748000","pos":"00000000000000002891","tokens":{"L":"206080450","6":"9.0.80330","R":"AADPkvAAEAAEqLzAAC"},"before":{"CUST_CODE":"BILL","ORDER_DATE":"1995-12-31:15:00:00","PRODUCT_CODE":"CAR","ORDER_ID":"765","PRODUCT_PRICE":15000.00,"PRODUCT_AMOUNT":3,"TRANSACTION_ID":"100"},"after":{"CUST_CODE":"BILL","ORDER_DATE":"1995-12-31:15:00:00","PRODUCT_CODE":"CAR","ORDER_ID":"765","PRODUCT_PRICE":14000.00,"PRODUCT_AMOUNT":3,"TRANSCATION_ID":"100"}}";
        String delete =
                "{"table":"GG.TCUSTORD","op_type":"D","op_ts":"2013-06-02 22:14:41.000000","current_ts":"2015-09-18T13:39:35.766000","pos":"00000000000000004338","tokens":{"L":"206080450","6":"9.0.80330","R":"AADPkvAAEAAEqLzAAC"},"before":{"CUST_CODE":"DAVE","ORDER_DATE":"1993-11-03:07:51:35","PRODUCT_CODE":"PLANE","ORDER_ID":"600"}}";
        System.out.println(parseSQL(insert));
        System.out.println(parseSQL(update));
        System.out.println(parseSQL(delete));
    }
}

 

 

转自 https://zhidao.baidu.com/question/1610138102391492667.html

侵删