public class DatatablesLazyLoad extends HttpServlet{ @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.process(request, response); } /** * datatables 延迟加载 数据加载，flag标注details表示注入数据，titles表示注入标题。 * @param request * @param response */ private void process(HttpServletRequest request, HttpServletResponse response) { ServletContext servletContext=request.getSession().getServletContext(); WebApplicationContext wac =WebApplicationContextUtils.getRequiredWebApplicationContext(servletContext); DictionaryServiceImpl ds=(DictionaryServiceImpl)wac.getBean("oracledictionaryService"); String flag = request.getParameter("flag"); String tableCode = request.getParameter("tableCode"); String fieldCode = request.getParameter("fieldCode"); if(flag==null)return; if(flag.equals("titles")){ }else if(flag.equals("details")){ String draw = request.getParameter("draw"); String start = request.getParameter("start"); String length = request.getParameter("length"); StringBuilder sql = new StringBuilder("select "); List titles = this.getTitles(tableCode,fieldCode,ds); for(int i=0;i<titles.size();i++){ Map record = (Map)titles.get(i); sql.append(" ").append(record.get("FIELDNAME")).append(", "); } if(sql.lastIndexOf(",")>0) sql.deleteCharAt(sql.lastIndexOf(",")); sql.append(" from ").append(tableCode); sql.append(" where 1=1 "); String filterSql = getFilterSql(titles,request); Integer totalCount =ds.getSqlRecordCount("select count(*) from (" + sql.toString()+ ") tmp"); Integer filterCount = ds.getSqlRecordCount("select count(*) from (" + sql.toString()+filterSql+ ") tmp"); String[] strings = fieldCode.split(","); String orderSql = getOrderSql(strings,request); sql.append(filterSql); sql.append(orderSql); List<Map> lt = ds.executeSqlToRecordMap(sql.toString(),Integer.valueOf(start),Integer.valueOf(length)); Map result = new LinkedHashMap(); result.put("draw", draw); result.put("recordsTotal", totalCount);//记录总行数 result.put("recordsFiltered", filterCount);//过滤的行数 int count=Integer.valueOf(length)+1; for(Map r : lt){ r.put("DT_RowId", r.get("id"));//设置行主键 Map rowDate = new LinkedHashMap();//row data rowDate.putAll(r); r.put("DT_RowData", rowDate); r.put("countInx", count); count++; } result.put("data", lt); try { convertListToJson(result, response); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } /** * 得到所有的列标题名称 * @return */ private List getTitles(String tableCode,String fieldCode,DictionaryServiceImpl ds){ /*StringBuilder sql = new StringBuilder("select UPPER(ODS_DF_NAME) fieldName,ODS_DF_CN_NAME fieldLabel,ODS_DF_CODE fieldCode,UPPER(ODS_DF_DATA_TYPE) fieldType from ODS.ODS_DF_INFO_REG "); sql.append(" where ODS_TB_CODE= '"+tableCode+"' "); List<Map> list = ds.executeSqlToRecordMap(sql.toString()); return list;*/ StringBuilder sql = new StringBuilder("select UPPER(ODS_DF_NAME) fieldName,ODS_DF_CN_NAME fieldLabel,ODS_DF_CODE fieldCode,UPPER(ODS_DF_DATA_TYPE) fieldType from ODS.ODS_DF_INFO_REG "); sql.append(" where 1=1 "); sql.append(" and ODS_TB_CODE = '"+tableCode+"'"); sql.append(" and ODS_DF_NAME in ("); String[] tmp = fieldCode.split(","); StringBuilder ids = new StringBuilder(); for(String t : tmp){ if(StringUtils.isEmpty(t)) continue; ids.append("'").append(t).append("',"); } if(ids.lastIndexOf(",")>0) ids.deleteCharAt(ids.lastIndexOf(",")); sql.append(ids); sql.append(")"); List lt = ds.executeSqlToRecordMap(sql.toString()); return lt; } /** * 前台搜索 * @param fieldLt * @return */ private String getFilterSql(List<Map> fieldLt,HttpServletRequest request) { StringBuilder filterSql = new StringBuilder(" and ( "); String searchKey = "search[value]"; String searchValue = request.getParameter(searchKey); System.out.println(searchValue); if(StringUtils.isEmpty(searchValue)) return ""; List<String> filterDetail = new ArrayList(); for(int i=0;i<fieldLt.size();i++){ Map field = fieldLt.get(i); if(field.get("FIELDTYPE").equals("VARCHAR")){ String subKey = "columns["+i+"][searchable]"; if("true".equals(request.getParameter(subKey))){ String fieldName = field.get("FIELDNAME").toString(); String subSql = fieldName + " like '%"+searchValue+"%'"; filterDetail.add(subSql); } } } if(filterDetail.size()==0) return ""; boolean f = true; for(String subSql : filterDetail){ if(f){ f= false; filterSql.append(subSql); }else{ filterSql.append(" OR ").append(subSql); } } filterSql.append(")"); return filterSql.toString(); } /** * 排序 * @param fieldLt * @return */ private String getOrderSql(String[] titles,HttpServletRequest request){ StringBuilder orderSql = new StringBuilder(" order by "); String indexKey = "order[0][column]"; String dirKey = "order[0][dir]"; Integer columnIndex = Integer.valueOf(request.getParameter(indexKey)); String dir = request.getParameter(dirKey); if(columnIndex<=titles.length){ orderSql.append(titles[columnIndex]).append(" ").append(dir); return orderSql.toString(); } return ""; } public void convertListToJson(Map map,HttpServletResponse response)throws Exception{ JSONArray json = JSONArray.fromObject(map); response.setHeader("Cache-Control", "no-cache"); response.setContentType("text/html; charset=GBK"); PrintWriter writer; writer = response.getWriter(); writer.write(json.get(0).toString()); writer.close(); } }
总结，做的比较急，也没好好整理下直接贴出来，因为也不难，肯定有地方写的不好，仅供参考。