[Notes] JAVA & C# 序列化JSON格式
【PART I】
{
"form": {
"BPM_PageName": "BPM_M_CONTRACT_PROPOSAL",
"PLM_PageName": "CO-16 合約提案申請單",
"DBSource": "BPM",
"dateValue": [
"2021-01-01T00:00:00.000Z",
"2021-01-31T00:00:00.000Z"
],
"cboSite": "",
"name": "",
"number": ""
},
"condition": [
{
"v_id": 1612256144011,
"columnValue": "(1)合約合同",
"selectColumn": "ConTyp",
"selectExpression": "=",
"selectLogical": "AND",
"colType": "list"
},
{
"v_id": 1612256224576,
"columnValue": "指定起迄日",
"selectColumn": "ConDurTyp",
"selectExpression": "=",
"selectLogical": "AND",
"colType": "list"
},
{
"v_id": 1612256241997,
"columnValue": "USD",
"selectColumn": "CyTyp",
"selectExpression": "=",
"selectLogical": "AND",
"colType": "list"
}
]
}
【PART II】JAVA
package andyServer;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.databind.JavaType;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
public class GPFormJson implements Serializable {
@JsonIgnoreProperties(ignoreUnknown = true)
public static class Hit<T> {
public T form;
public List<dynamicForm> condition = Lists.newArrayList();
public static class dynamicForm {
public String v_id;
public String columnValue;
public String selectColumn;
public String selectExpression;
public String selectLogical;
public String colType;
public List<Date> columnDateValue;
//省略getter/setter
}
}
@JsonIgnoreProperties(ignoreUnknown = true)
public static class formJsonInfo {
public String DBSource;
public List<Date> dateValue;
public String cboSite;
public String name;
public String number;
public String BPM_PageName;
public String PLM_PageName;
//省略getter/setter
}
// 使用 Jackson 將 json 轉成泛型 class 的方法
public static <T> Hit<T> convert(String json, Class<T> targetClass) throws Exception {
ObjectMapper objectMapper = new ObjectMapper();
//objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
Hit<T> hit = null;
JavaType clazzType = objectMapper.getTypeFactory().constructParametricType(Hit.class, targetClass);
hit = objectMapper.readValue(json, clazzType);
return hit;
}
}
import andyServer.GPFormJson.Hit;
import andyServer.GPFormJson.Hit.dynamicForm;
import andyServer.GPFormJson.formJsonInfo;
public String PLMSearchAdvanced(String objParam) throws JsonProcessingException {
// TODO Auto-generated method stub
GPJsonObject GPJSON = new GPJsonObject();
IAgileSession session = null;
Connection conn = null;
ResultSet mssqlRs = null;
GPJSON.data = "";
GPJSON.status = false;
ObjectMapper mapper = new ObjectMapper();
String GPJSONStr = "";
try {
Ini ini = new Ini("C:\\Agile\\giantplus.ini");
session=(IAgileSession) new AgilUtil().connect();
DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
JSONArray jsonArray = new JSONArray();
String data,changeName = null;
Hit<formJsonInfo> formHit = GPFormJson.convert(objParam, formJsonInfo.class);
changeName = formHit.form.PLM_PageName;
IAgileClass iac = session.getAdminInstance().getAgileClass(changeName);
IQuery query = (IQuery) session.createObject(IQuery.OBJECT_TYPE,iac.getId());
query.setCaseSensitive(false);
ArrayList<String> attributeID_List = new ArrayList<String>();
ArrayList<String> attributeName_List = new ArrayList<String>();
ArrayList<String> attributeType_List = new ArrayList<String>();
ArrayList<String> BPMName_List = new ArrayList<String>();
String sql = "";
//String js = "{\"form\":{\"DBSource\":\"BPM\",\"dateValue\":[\"2021-01-01T00:00:00.000Z\",\"2021-01-06T00:00:00.000Z\"],\"cboSite\":\"GP\",\"name\":\"\",\"number\":\"\"},\"condition\":[{\"v_id\":\"\",\"columnValue\":\"2\",\"selectColumn\":\"ConName\",\"selectExpression\":\"LIKE\",\"selectLogical\":\"AND\"}]}";
//DB取報表欄位
sql = String.format("select * from ZZ_FLOW_PLM_REPORT where "
+ "PLM_COL_NOTE = '1' and PLM_PAGE_NAME = "
+ "'%s' order by SEQ ASC",changeName);
mssqlRs = excuteMSSQL(sql,ini);
System.out.println(sql);
while (mssqlRs.next()) {
// 將符合條件的attributeID都撈出來
attributeID_List.add(mssqlRs.getString("PLM_COL_ID"));
attributeName_List.add(mssqlRs.getString("PLM_COL_NAME"));
attributeType_List.add(mssqlRs.getString("PLM_COL_TYPE"));
BPMName_List.add(mssqlRs.getString("BPM_COL_ID"));
}
String queryCondition="";
if (!formHit.form.cboSite.equals("")) //Page.Three 廠區 不同單ID可能不同
queryCondition += " and [1544] in ('"+formHit.form.cboSite+"' )";
if (!formHit.form.name.equals("")) //User object 特殊處理
{
IUser User = (IUser) session.getObject(IUser.OBJECT_TYPE, formHit.form.name);
if (User != null) {
queryCondition += " and [1050] in ('"+User+"') ";
}
}
if (!formHit.form.number.equals(""))
queryCondition += " and [1047] like '%"+formHit.form.number+"%' ";
if (!df.format(formHit.form.dateValue.get(0)).equals("") && !df.format(formHit.form.dateValue.get(1)).equals(""))
queryCondition += " and [1061] >= '"+df.format(formHit.form.dateValue.get(0))+"' and [1061] <= '"+df.format(formHit.form.dateValue.get(1))+"' ";
for (dynamicForm Condition:formHit.condition){
String Operator = "";
int a = BPMName_List.indexOf(Condition.selectColumn);
if (Condition.selectExpression.equals("LIKE"))
{
Operator = "[" + attributeID_List.get(a) + "] LIKE '%" + Condition.columnValue + "%'";
}
else if (Condition.selectExpression.equals("="))
{
switch (Condition.colType)
{
case "date":
if (!df.format(Condition.columnDateValue.get(0)).equals("") && !df.format(Condition.columnDateValue.get(1)).equals(""))
Operator += " ( [" + attributeID_List.get(a) + "] >= '"+df.format(Condition.columnDateValue.get(0))+"' and ["+attributeID_List.get(a)+"] <= '"+df.format(Condition.columnDateValue.get(1))+"') ";
break;
case "list":
Operator = "[" + attributeID_List.get(a) + "] in ('" + Condition.columnValue + "') ";
break;
default:
Operator = "[" + attributeID_List.get(a) + "] = '" + Condition.columnValue + "' ";
break;
}
}
if (!"".equals(Condition.selectLogical))
{
if (!"".equals(Operator))
{
queryCondition += Condition.selectLogical + Operator;
}
}
}
//String[] a = {"類型(Type)","幣別(Currency Type)","使用印信(Type of Seal)"};
//String[] b = {"Type","Currency Type","Type of Seal"};
//if (!formHit.form.cboSite.equals(""))
//Date Originated[1061] 廠區[1544] Originator[1050] Number[1047]
//query.setCriteria("[1061] > (%0)",new Object[] {df.parse("2019/12/1")});
query.setCriteria(queryCondition);
ITable results = query.execute();
if (results.size() > 0 && results.size() <= 150) {
ITwoWayIterator it = (ITwoWayIterator) results.getReferentIterator();
while(it.hasNext()){
JSONObject obj = new JSONObject();
obj.put("DBSource","PLM"); //先放入DBSource
IChange change = (IChange)it.next();
for (int i = 0; i < attributeID_List.size(); i++) {
data ="";
switch (attributeType_List.get(i).toString())
{
case "date":
if (change.getValue(new Integer(attributeID_List.get(i).toString())) != null)
data = df.format((Date)change.getValue(new Integer(attributeID_List.get(i).toString())));
//data = df.format((Date)change.getValue(attributeID_List.get(i)));
break;
default:
data = change.getValue(new Integer(attributeID_List.get(i).toString())).toString();
break;
}
obj.put(BPMName_List.get(i),data);
}
jsonArray.put(obj);
}
GPJSON.status = true;
GPJSON.data = jsonArray.toString();
GPJSON.msg = results.size() + "筆數";
}
else if (results.size() == 0)
{
GPJSON.status = true;
GPJSON.msg = "PLM查無資料";
}
else if (results.size() > 150)
{
GPJSON.msg = "PLM搜尋超過150筆限制";
}
else
{
GPJSON.msg = "PLM error";
}
//System.out.println(jsonArray);
}
catch(Exception e){
e.printStackTrace();
GPJSON.status = false;
GPJSON.msg = e.toString();
}
finally {
closeQuietly(session, conn, mssqlRs);
}
GPJSONStr = mapper.writeValueAsString(GPJSON);
return GPJSONStr;
}
public static ResultSet excuteMSSQL(String sqls,Ini ini) {
// TODO Auto-generated method stub
//建立資料庫連線
IAgileSession session = null;
Connection conn = null;
Statement stmt;
ResultSet rs = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(ini.getValue("NewTypeDB", "url"), ini.getValue("NewTypeDB", "user"), ini.getValue("NewTypeDB", "pass"));
stmt = conn.createStatement(SQLServerResultSet.TYPE_SCROLL_SENSITIVE, SQLServerResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sqls);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public static void close(IAgileSession session, Connection conn,
ResultSet rs) throws SQLException {
try {
if(rs != null){
rs.close();
rs = null;
}
} finally{
try{
if(session != null){
session.close();
session = null;
}
}finally{
if(conn != null){
conn.close();
// conn = null;
}
}
}
}
public static void closeQuietly(IAgileSession session, Connection conn,
ResultSet rs){
try{
close(session, conn, rs);
}catch(SQLException e){
//quietly
}
}
【PART III】C#
public class formJson // 建立一個仿 JSON 類別
{
public formJsonInfo form {get; set;}
public List<dynamicForm> condition { get; set; }
}
public class formJsonInfo
{
public DateTimeOffset Date { get; set; }
public IList<DateTimeOffset> dateValue { get; set; }
public string DBSource { get; set; }
public string cboSite { get; set; }
public string name { get; set; }
public string number { get; set; }
public string BPM_PageName { get; set; }
public string PLM_PageName { get; set; }
}
public class dynamicForm
{
public string v_id { get; set; }
public string columnValue { get; set; }
public string selectColumn { get; set; }
public string selectExpression { get; set; }
public string selectLogical { get; set; }
public string colType { get; set; }
public IList<DateTimeOffset> columnDateValue { get; set; }
}
<%@ WebHandler Language="C#" Class="GetContractData" %>
using System;
using System.Web;
using System.Web.Script.Serialization;
using System.Collections.Generic;
using System.IO;
using System.Data;
using CONNECTION.SQL_TOOL;
using CONNECTION.CONNECTION_STRING;
using NLog;
using Newtonsoft.Json;
public class GetContractData : IHttpHandler {
protected static Logger _logger = LogManager.GetCurrentClassLogger();
public class returnObject // 建立一個仿 JSON 類別
{
public Boolean status { get; set; }
public string data { get; set; }
public string msg { get; set; }
}
public class formJson // 建立一個仿 JSON 類別
{
public formJsonInfo form {get; set;}
public List<dynamicForm> condition { get; set; }
}
public class formJsonInfo
{
public DateTimeOffset Date { get; set; }
public IList<DateTimeOffset> dateValue { get; set; }
public string DBSource { get; set; }
public string cboSite { get; set; }
public string name { get; set; }
public string number { get; set; }
public string BPM_PageName { get; set; }
public string PLM_PageName { get; set; }
}
public class dynamicForm
{
public string v_id { get; set; }
public string columnValue { get; set; }
public string selectColumn { get; set; }
public string selectExpression { get; set; }
public string selectLogical { get; set; }
public string colType { get; set; }
public IList<DateTimeOffset> columnDateValue { get; set; }
}
/*######################無dateValue#############################*/
public class formJson_1 // 建立一個仿 JSON 類別
{
public formJsonInfo_1 form { get; set; }
public List<dynamicForm> condition { get; set; }
}
public class formJsonInfo_1
{
//public string dateValue { get; set; }
public string DBSource { get; set; }
public string cboSite { get; set; }
public string name { get; set; }
public string number { get; set; }
public string BPM_PageName { get; set; }
public string PLM_PageName { get; set; }
}
/*###################################################*/
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
string json = string.Empty;
string message = string.Empty;
string data = string.Empty;
bool result = false;
string DBSource, startDate, endDate, cboSite, name, number, pageName = null;
DataTable dt = new DataTable();
DataTable dt_selectColumn = new DataTable();
string SqlCmd = "";
string SqlCmd_selectColumn = "";
string selectStr = "";
string SubSqlCmd = "";
//List<string> dynamicCondition = new List<string>();
List<dynamicForm> dynamicCondition = new List<dynamicForm>();
try
{
if (context.Request.RequestType == "POST")
{
using (var reader = new StreamReader(context.Request.InputStream))
{
json = reader.ReadToEnd();
}
if (!string.IsNullOrEmpty(json))
{
var serializer = new JavaScriptSerializer();
if (json.Contains(",\"dateValue\":\"\","))
{
var formList = serializer.Deserialize<formJson_1>(json);
DBSource = formList.form.DBSource; //DB來源
startDate = ""; //開單時間(起)
endDate = ""; //開單時間(訖)
cboSite = formList.form.cboSite; //廠區
name = formList.form.name; //申請人
number = formList.form.number; //單號
pageName = formList.form.BPM_PageName;
dynamicCondition = formList.condition; //動態條件
}
else
{
var formList = serializer.Deserialize<formJson>(json);
DBSource = formList.form.DBSource; //DB來源
startDate = formList.form.dateValue[0].ToString("d"); //開單時間(起)
endDate = formList.form.dateValue[1].ToString("d"); //開單時間(訖)
cboSite = formList.form.cboSite; //廠區
name = formList.form.name; //申請人
number = formList.form.number; //單號
pageName = formList.form.BPM_PageName;
dynamicCondition = formList.condition; //動態條件
}
SqlCmd_selectColumn = "SELECT BPM_COL_ID from ZZ_FLOW_PLM_REPORT where BPM_PAGE_NAME = '" + pageName + "' and RESULT_ENABLE = '1' order by SEQ ASC";
dt_selectColumn = sqlTool.GetDataTableMsSql(connectionString.bpmServer_133, SqlCmd_selectColumn, _logger);
foreach (DataRow dr in dt_selectColumn.Rows)
{
selectStr += "V." + dr["BPM_COL_ID"].ToString() + ",";
}
SqlCmd = "SELECT " + selectStr +
" CASE " +
" WHEN V.STATUS = '0' THEN '進行中' " +
" WHEN V.STATUS = '1' THEN '結案' " +
" WHEN V.STATUS = '2' THEN '駁回結束' " +
" WHEN V.STATUS = '3' THEN '過期結束' " +
" WHEN V.STATUS = '4' THEN '起單人撤回' " +
" WHEN V.STATUS = '5' THEN '強制流產申請單' " +
" WHEN V.STATUS = '16' THEN '手動暫停' " +
" WHEN V.STATUS = '32' THEN '因錯誤而停止' " +
" ELSE 'Others' " +
" END AS Status " +
",'' AS CurrentProcessName,'' AS DBSource,V.RequisitionID FROM FM7V_BPM_M_CONTRACT_PROPOSAL_M V" +
" WHERE V.Lang = 'zh-tw'";
if (startDate != "")
SqlCmd += " AND V.ApplicantDateTime >= '" + startDate + "' ";
if (endDate != "")
SqlCmd += " AND V.ApplicantDateTime <= '" + endDate + "' ";
if (cboSite != "")
SqlCmd += " AND V.cboSite = '" + cboSite + "' ";
if (name != "")
SqlCmd += " AND V.ApplicantID like '%" + name + "%' ";
if (number != "")
SqlCmd += " AND V.SerialID like '%" + number + "%' ";
foreach (dynamicForm Condition in dynamicCondition)
{
var Operator = "";
var Logical = "";
if (Condition.selectExpression.Equals("LIKE"))
{
Operator = " V." + Condition.selectColumn + " LIKE '%" + Condition.columnValue + "%'";
}
else if (Condition.selectExpression.Equals("="))
{
if (Condition.colType.Equals("date"))
{
var start = Condition.columnDateValue[0].ToString("d"); //時間(起)
var end = Condition.columnDateValue[1].ToString("d"); //時間(訖)
Operator += " (V." + Condition.selectColumn + " >= '" + startDate + "' ";
Operator += " AND V." + Condition.selectColumn + " <= '" + endDate + "') ";
}
else {
Operator = " V." + Condition.selectColumn + " = '" + Condition.columnValue + "' ";
}
}
if (!string.IsNullOrEmpty(Condition.selectLogical))
{
if (!string.IsNullOrEmpty(Operator))
{
SubSqlCmd += Condition.selectLogical + Operator;
}
}
}
if (SubSqlCmd != "")
SqlCmd += "AND ( 1 = 1 " + SubSqlCmd + " )";
SqlCmd += " order by SerialID asc";
if (DBSource == "BPM")
{
var NewTypeJSON = CallNewType(dt, SqlCmd);
result = NewTypeJSON.status;
data = NewTypeJSON.data;
}
else if (DBSource == "PLM")
{
var PLMJSON = CallPLM(json);
result = PLMJSON.status;
data = PLMJSON.data;
message = PLMJSON.msg;
}
else if (DBSource == "ALL")
{
var PLMJSON = CallPLM(json);
if (PLMJSON.status) //若PLM狀態true，再搜尋NewType
{
var NewTypeJSON = CallNewType(dt, SqlCmd);
var plm = PLMJSON.data.Substring(PLMJSON.data.IndexOf("[") + 1, PLMJSON.data.LastIndexOf("]") - 1);
var bpm = NewTypeJSON.data.Substring(NewTypeJSON.data.IndexOf("[") + 1, NewTypeJSON.data.LastIndexOf("]") - 1);
result = NewTypeJSON.status;
data = "[" + plm + "," + bpm + "]";
}
else
{
result = PLMJSON.status;
data = PLMJSON.data;
message = PLMJSON.msg;
}
}
//message = JsonConvert.SerializeObject(dt);
//message += json;
//message = DBSource + ";" + startDate + ";" + endDate + ";" + cboSite + ";" + name + ";" + number;
/*
foreach (dynamicForm a in formList.condition)
{
message += "ok";
message += a.selectColumn + a.columnValue + a.selectExpression + a.selectLogical;
}
*/
}
this.SendResponse(context, result, data, message);
}
}
catch
{
}
}
static returnObject CallNewType(DataTable dt,string SqlCmd)
{
DataTable nextApprove_dt = new DataTable();
string nextApprove_sql = "";
returnObject NewTypeJSON = new returnObject();
var serializer = new JavaScriptSerializer();
dt = sqlTool.GetDataTableMsSql(connectionString.bpmServer_133, SqlCmd, _logger);
foreach (DataRow dr in dt.Rows)
{
string currentApproverName = "";
nextApprove_sql = "SELECT CurrentProcessName FROM FM7V_BPM_M_CONTRACT_PROPOSAL_M V ,FM7V_BPM_M_CONTRACT_PROPOSAL_NextApprover N WHERE V.RequisitionID = N.RequisitionID and V.RequisitionID = '" + dr["RequisitionID"].ToString() + "' and N.Lang = 'zh-tw'";
//nextApprove_dt = SqlDbTools.GetDataTable(CnStr, nextApprove_sql, isOracleDB);
nextApprove_dt = sqlTool.GetDataTableMsSql(connectionString.bpmServer_133, nextApprove_sql, _logger);
if (nextApprove_dt.Rows.Count > 0)
{
currentApproverName += nextApprove_dt.Rows[0]["CurrentProcessName"].ToString();
}
dr["CurrentProcessName"] = currentApproverName;
dr["DBSource"] = "新人類";
}
NewTypeJSON.status = true;
NewTypeJSON.data = JsonConvert.SerializeObject(dt);
return NewTypeJSON;
}
static returnObject CallPLM(string json)
{
returnObject PLMJSON = new returnObject();
var serializer = new JavaScriptSerializer();
using (PLM_Service.AndyTestClient wcf = new PLM_Service.AndyTestClient())
{
PLMJSON = serializer.Deserialize<returnObject>(wcf.PLMSearchAdvanced(json));
//message = JsonConvert.SerializeObject(wcf.PLMSearchAdvanced(json));
/*
result = PLMJSON.status;
data = PLMJSON.data;
message = PLMJSON.msg;
*/
}
return PLMJSON;
}
private void SendResponse(HttpContext context, bool result, string data, string message)
{
context.Response.Write(new JavaScriptSerializer().Serialize(
new
{
Result = result,
Data = data,
Message = message
}));
context.Response.End();
}
public bool IsReusable {
get {
return false;
}
}
}