查询结果用Object类或其数组的List接收
查询结果用Object数组接收并返回前端获取和展示,很多时候是封装的方法直接用实体类接收和返回,当所查询出的值包含不只一个实体类的属性值时,返回某个实体类不满足要求,或者不需要此实体类属性太多,返回整个实体类效率太低,这时用Object[]接收最莱斯:
String codename = receiveData.getParam("codename".toUpperCase()); String sql = "select itemvalue,itemtext from code_items where codeid = (select codeid from code_main where codename = '"; sql += codename; sql += "') order by itemvalue"; List<Object[]> objlist = service.getResultListNative(sql); List<JSONObject> list = new ArrayList<JSONObject>(); if (objlist != null && objlist.size() > 0) { for (Object[] obj : objlist) { JSONObject jsonObject = new JSONObject(); jsonObject.accumulate("ItemValue", obj[0]); jsonObject.accumulate("ItemText", obj[1]); list.add(new JSONObject().accumulate("codeiteminfo", jsonObject)); } returndata.putUserData("ItemList", JsonUtil.listToJson(list)); }
其中只需要两个字段的值,返回整个实体效率太低,并且select * 的查询语句尽量要避免;
e.g.2:
StringBuffer sql = new StringBuffer(); sql.append("SELECT COALESCE(queue.QueueLen, 0) QueueLen, COALESCE(window.WinCOunt, 0) WinCount "); sql.append(" FROM (SELECT TASKTYPEGUID FROM Audit_Queue_Window_TaskType where WINDOWGUID='" + windowGuid + "') win"); sql.append(" LEFT JOIN"); sql.append(" (SELECT TASKGUID, COUNT(1) QueueLen FROM AUDIT_QUEUE WHERE STATUS = '0' and " + this.getCommonDateSQL()); sql.append(" GROUP BY TASKGUID ) queue ON win.TASKTYPEGUID = queue.TASKGUID"); sql.append(" LEFT JOIN"); sql.append(" (SELECT TASKTYPEGUID, COUNT(1) WinCOunt FROM AUDIT_WINDOW_USER u INNER JOIN Audit_Queue_Window_TaskType t ON u.WINDOWGUID = t.WINDOWGUID"); sql.append(" WHERE u.IS_ONUSER = 1 GROUP BY t.TASKTYPEGUID ) window ON window.TASKTYPEGUID = win.TASKTYPEGUID and QueueLen<>0");
// 语句查询字段多的不谈了,必须用Object类接收
List<Object> list = this.getResultListNative(sql.toString());
Integer len = 0;
// 遍历Object对象,不是Object[]数组接收,要先转换成Object[]数组,方便通过下标获取查询的字段值
for (Object object : list) {
Object[] arr = (Object[]) object;
len += (Integer)arr[0];
}
其中COALESCE是一个函数,作用是返回表达式中第一个非空表达式,如SELECT COALESCE(NULL,NULL,3,4,5) FROM dual,其返回结果为:3;
打听下这个变量名谁起的