|
发表于 2019-4-28 13:50:21
|
显示全部楼层
public static JSONArray getUnSuitPercent(Map<String, Object> lstParameters)
{
String BeginDate = String.valueOf(lstParameters.get("fBeginDate")); //lstParameters.get("fBeginDate").toString();
String EndDate = String.valueOf(lstParameters.get("fEndDate"));//lstParameters.get("fEndDate").toString();
StringBuilder resultMsg = new StringBuilder();
JSONArray array = new JSONArray();
Connection connM = null;
String strWhere = "";
if (!BeginDate.equals("")) {
strWhere += " b.DARRIVALDATE >=convert(datetime,'" + BeginDate + "',120)";
}
// 结束日期
if (!EndDate.equals("")) {
strWhere += " and b.DARRIVALDATE <=convert(datetime,'" + EndDate + "',120)";
}
try {
getCurrentConfig();
initProperties();
Class.forName(driverMssql);
// 连接ERP数据库
connM = DriverManager.getConnection(urlMssql, nameMssql, passwordMssql);
connM.setAutoCommit(false);
String sqlMssql = "select * from (select a.cVenCode,ISNULL(SUM(CASE WHEN IBATCHCHKRESULT!=0 then 1 END)*1.0/SUM(1), 0) AS Rate"
+"UnSuitPercent from PO_Pomain a left join QMCHECKVOUCHER b on b.CPOCODE = a.cPOID "
+ strWhere
+"GROUP BY a.cVenCode)t where t.rate>0.001 ";
Statement statementM = connM.createStatement();
ResultSet setM = statementM.executeQuery(sqlMssql);
ResultSetMetaData md = setM.getMetaData();
int num = md.getColumnCount();
while (setM.next()){
JSONObject mapOfColValues = new JSONObject();
for (int k = 1; k <= num; k++) {
mapOfColValues.put(md.getColumnName(k), setM.getObject(k));
//在此处更新数据库
}
array.add(mapOfColValues);
}
connM.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
resultMsg.append(e.getMessage());
} catch (SQLException e) {
try {
connM.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
} // 回滚
e.printStackTrace();
} finally {
if (connM != null)
try {
connM.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return array;
}
这段程序获取了jsonarray的内容,如果在前台,可以用loaddata函数直接转换成table 但是我想把他转写为update 语法更新B表 如果能提供学习此类程序写法或者学习资料,也是感激不尽 |
|