sql后台代码

87 阅读 0 评论 58 点赞

我是靠谱客的博主清爽红牛，最近开发中收集的这篇文章主要介绍sql后台代码，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

package em;

import java.sql.Connection;

public class DataService {
public static JSONObject doLoadInfo(JSONObject params, ActionContext context) throws Exception {
String pID = params.getString("epid");
String sql = "select n"
+ " (select count(*) from YH_YHXX where YHDJ='一般隐患' AND EPID='" + pID + "') as YBYH,n"
+ " (select count(*) from YH_YHXX where YHDJ='重大隐患' AND EPID='" + pID + "') as ZDYH,n"
+ " (select count(*) from YH_YHXX where YHDJ='一般隐患' AND EPID='" + pID + "' AND (ZGZT='未整改' OR ZGZT='退回整改')) as YBYH_ZGZ,n"
+ " (select count(*) from YH_YHXX where YHDJ='重大隐患' AND (ZGZT='未整改' OR ZGZT='退回整改') AND EPID='" + pID + "') as ZDYH_ZGZ,n"
+ " (select count(*) from YH_YHXX where YHDJ='一般隐患' AND YHZGQX < SYSDATE AND EPID='" + pID + "' AND (ZGZT='未整改' OR ZGZT='退回整改')) as YBYH_YQWZG,n"
+ " (select count(*) from YH_YHXX where YHDJ='重大隐患' AND YHZGQX < SYSDATE AND EPID='" + pID + "' AND (ZGZT='未整改' OR ZGZT='退回整改')) as ZDYH_YQWZG,n"
+ " (select count(*) from YH_YHXX where YHDJ='一般隐患' AND ZGZT='已整改' AND EPID='" + pID + "') as YBYH_YGZ,n"
+ " (select count(*) from YH_YHXX where YHDJ='重大隐患' AND ZGZT='已整改' AND EPID='" + pID + "') as ZDYH_YGZ,n"
+ " (select count(*) from YH_YHXX where ZGZT='未整改(审核中)' AND EPID='" + pID + "') as DHZYH,n"
+ " (select count(*) from YH_YHXX where EPID='" + pID + "' AND (ZGZT='未整改' OR ZGZT='退回整改' )) as XYZG,n"
+ " (select count(*) from YH_YHXX where ZGZT='已整改' AND HXZT='未核销' AND EPID='" + pID + "') as DHX,n"
+ " (select count(*) from YH_YHXX where YHDJ='重大隐患' AND EPID='" + pID + "') as ZDYHZn"
+ " from dual";
System.out.println(sql);
JSONObject result = new JSONObject();
Connection conn = null;
try {
conn = context.getConnection("em");
Table table = DataUtils.queryData(conn, sql, null, null, null, null);
List<Row> ls_row = table.getRows();
if (ls_row.size() > 0) {
Row row = ls_row.get(0);
result.put("result", "success");
JSONObject userInfo = new JSONObject();
userInfo.put("YBYH", row.getDecimal("YBYH").toString());
userInfo.put("ZDYH", row.getDecimal("ZDYH").toString());
userInfo.put("YBYH_ZGZ", row.getDecimal("YBYH_ZGZ").toString());
userInfo.put("ZDYH_ZGZ", row.getDecimal("ZDYH_ZGZ").toString());
userInfo.put("YBYH_YQWZG", row.getDecimal("YBYH_YQWZG").toString());
userInfo.put("ZDYH_YQWZG", row.getDecimal("ZDYH_YQWZG").toString());
userInfo.put("YBYH_YGZ", row.getDecimal("YBYH_YGZ").toString());
userInfo.put("ZDYH_YGZ", row.getDecimal("ZDYH_YGZ").toString());
userInfo.put("DHZYH", row.getDecimal("DHZYH").toString());
userInfo.put("XYZG", row.getDecimal("XYZG").toString());
userInfo.put("DHX", row.getDecimal("DHX").toString());
userInfo.put("ZDYHZ", row.getDecimal("ZDYHZ").toString());
result.put("Info", userInfo);
} else {
result.put("result", "fail");
result.put("reason", "查询出错！");
}
} finally {
if (conn != null) {
conn.close();
}

}
return result;
}

public static JSONObject doGetInfo(JSONObject params, ActionContext context) throws Exception {
String pID = params.getString("epid");
String sql = "SELECT ID,YHDJ,YHBW,YHMS,ZGZT,HXZT,HXSMn" + "FROM YH_YHXX T WHERE ZGZT = '退回整改' AND EPID='" + pID + "' order by ZGRQ desc";//
System.out.println(sql);
JSONObject result = new JSONObject();
JSONArray sData = new JSONArray();
Connection conn = null;
try {
conn = context.getConnection("em");
Table table = DataUtils.queryData(conn, sql, null, null, null, null);
List<Row> ls_row = table.getRows();
result.put("result", "success");
for (int i = 0; i < ls_row.size(); i++) {
Row row = ls_row.get(i);
JSONObject userInfo = new JSONObject();
userInfo.put("id", row.getDecimal("ID").toString());
userInfo.put("title", "[" + row.getString("YHDJ") + "]" + row.getString("YHBW"));
userInfo.put("content", row.getString("YHMS"));
userInfo.put("state1", row.getString("ZGZT"));
userInfo.put("state2", row.getString("HXZT"));
userInfo.put("introduce", row.getString("HXSM"));
sData.add(userInfo);
}
result.put("data", sData);
} finally {
if (conn != null) {
conn.close();
}

}
return result;
}

public static JSONObject loadBaseData(JSONObject params, ActionContext context) throws Exception {
String sql = "select FID,FBZMC from TR_TYPE t where t.FSJBM='yhlb'";//
String sql2 = "select FID,FBZID,FLEVEL1,FLEVEL2,FLEVEL3,FMS,FCKYJ,FXH from TR_ITEMS t where t.FBZID in(select FID from TR_TYPE t where t.FSJBM='yhlb') order by FXH asc";//
System.out.println(sql);
JSONObject result = new JSONObject();
JSONArray sData = new JSONArray();
JSONArray sData2 = new JSONArray();
Connection conn = null;
try {
conn = context.getConnection("emapp");
Table table = DataUtils.queryData(conn, sql, null, null, null, null);
List<Row> ls_row = table.getRows();
result.put("result", "success");
for (int i = 0; i < ls_row.size(); i++) {
Row row = ls_row.get(i);
JSONObject info = new JSONObject();
info.put("FID", row.getString("FID"));
info.put("FBZMC", row.getString("FBZMC"));
sData.add(info);
}
result.put("dataM", sData);

Table table2 = DataUtils.queryData(conn, sql2, null, null, null, null);
List<Row> ls_row2 = table2.getRows();
for (int i = 0; i < ls_row2.size(); i++) {
Row row2 = ls_row2.get(i);
JSONObject info2 = new JSONObject();
info2.put("FID", row2.getString("FID"));
info2.put("FBZID", row2.getString("FBZID"));
info2.put("FLEVEL1", row2.getString("FLEVEL1"));
info2.put("FLEVEL2", row2.getString("FLEVEL2"));
info2.put("FLEVEL3", row2.getString("FLEVEL3"));
info2.put("FMS", row2.getString("FMS"));
info2.put("FCKYJ", row2.getString("FCKYJ"));
info2.put("FXH", row2.getDecimal("FXH").toString());
sData2.add(info2);
}
result.put("dataD", sData2);
} finally {
if (conn != null) {
conn.close();
}

}
return result;
}

public static JSONObject loadpersonData(JSONObject params, ActionContext context) throws Exception {
String pID = params.getString("epid");
String sql = "select PERSONID, "NAME" as SNAME,EPID from QY_SAFEFYPRODUCTIONPERSON where epid='"+pID+"'";//
System.out.println(sql);
JSONObject result = new JSONObject();
JSONArray sData = new JSONArray();
Connection conn = null;
try {
conn = context.getConnection("em");
Table table = DataUtils.queryData(conn, sql, null, null, null, null);
List<Row> ls_row = table.getRows();
result.put("result", "success");
for (int i = 0; i < ls_row.size(); i++) {
Row row = ls_row.get(i);
JSONObject info = new JSONObject();
info.put("FID", row.getDecimal("PERSONID").toString());
info.put("SNAME", row.getString("SNAME"));
info.put("EPID", row.getDecimal("EPID").toString());
sData.add(info);
}
result.put("dataM", sData);
} finally {
if (conn != null) {
conn.close();
}

}
return result;
}
}