记录一下遇到的问题 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)); } }
好的代码像粥一样,都是用时间熬出来的