通过json实现复杂sql结果集分页
刚进入一个大型项目，框架用的是ext+ajax+spring+hibernate。
但是由于业务逻辑复杂，经常出现列表查询需要同时用到多个表的情况。
由于表关系复杂，使用hibernate开发会对组内开发进度造成较大影响。
针对此类情况，我封装了一套方法给组内调用。原理是通过ResultSetMetaData类获取结果列的名称生成JSONArray字符串，返回给页面调用。
dao层代码如下：
// start，pageSize分别为页面传过来的值，此值不需要开发人员自己设置。public static String getPageJson(String sql,int start,int pageSize){JSONObject obj = new JSONObject();JSONArray array = new JSONArray();Connection conn = null;PreparedStatement pstms2 = null;ResultSet rs = null;String sql1 = "select count(*) ";try {sql1+=sql.substring(sql.indexOf("from"));conn = dataSource.getConnection();pstms2 = conn.prepareStatement(sql1);rs = pstms2.executeQuery();int count=0;//count为结果集总数while (rs.next()) {count = rs.getInt(1);obj.put("count", count);}int end = start+pageSize>count?count:start+pageSize;int begin = start;if (begin>count){}else{String str = "select * from (select A.* , ROWNUM RN from ("+sql+")A where rownum<=" + end+") where RN>="+begin;//拼接分页sqlpstms2 = conn.prepareStatement(str);rs = pstms2.executeQuery();ResultSetMetaData md = rs.getMetaData();int cols = md.getColumnCount();while (rs.next()) {JSONObject obj1 = new JSONObject();for (int i = 1; i <= cols; i++) {if (md.getColumnType(i)==Types.NULL){obj1.put(md.getColumnName(i), "");}else if (md.getColumnType(i)==Types.DATE){if (null!=rs.getDate(i)) {obj1.put(md.getColumnName(i), EsUtil.getStrDateYMD(rs.getDate(i)));//将date类型转换成正确格式}}else{if (md.getColumnName(i)!="RN"&&!"RN".equals(md.getColumnName(i))){obj1.put(md.getColumnName(i), rs.getString(i)==null?"":rs.getString(i));//将除date类型以及null的返回值写入json。如为非string类型会被自动转化为String类型}}}array.put(obj1);}obj.put("list", array);}} catch (JSONException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {try {if (conn != null)conn.close();if (pstms2 != null)pstms2.close();if (rs != null)rs.close();} catch (SQLException e) {e.printStackTrace();}}return obj.toString();}