DataTables 的伺服器端模式(serverSide)要求後端依前端傳來的 aoData 參數回傳特定格式的 JSON。若每個列表 API 都自己拼一次,格式很容易分歧。以下示範如何把這層封裝抽出來,讓後端只需專心查資料。
DataTables 固定期待四個欄位:iTotalRecords(未篩選的總筆數)、iTotalDisplayRecords(篩選後總筆數)、sEcho(請求序號,原樣回傳)、aaData(當頁的二維陣列)。
public class DataTableReturnObject {
private int iTotalRecords;
private int iTotalDisplayRecords;
private String sEcho;
private String[][] aaData;
public DataTableReturnObject(int totalRecords, int totalDisplayRecords,
String echo, String[][] d) {
this.setiTotalRecords(totalRecords);
this.setiTotalDisplayRecords(totalDisplayRecords);
this.setsEcho(echo);
this.setAaData(d);
}
public void setiTotalRecords(int v) { this.iTotalRecords = v; }
public int getiTotalRecords() { return iTotalRecords; }
public void setiTotalDisplayRecords(int v) { this.iTotalDisplayRecords = v; }
public int getiTotalDisplayRecords() { return iTotalDisplayRecords; }
public void setsEcho(String v) { this.sEcho = v; }
public String getsEcho() { return sEcho; }
public void setAaData(String[][] v) { this.aaData = v; }
public String[][] getAaData() { return aaData; }
}
補充一點版本差異:DataTables 1.10 之後改採 draw、start、length、recordsTotal、recordsFiltered、data 這組新命名,舊的 sEcho/aaData 屬於 1.9 及更早的 legacy 格式。新專案請直接對齊 1.10+ 規格。
public class BaseController {
protected JSONResponse successed(Object obj) {
JSONResponse ret = new JSONResponse();
ret.setSuccessed(true);
ret.setReturnObject(obj);
return ret;
}
}
public JSONResponse searchList(HttpServletRequest request,
HttpServletResponse response,
String sEcho) throws Exception {
Map<Object, Object> objQueryMap = new HashMap<Object, Object>();
String jsondata = request.getParameter("aoData");
JSONArray jsonarray = JSONArray.fromObject(jsondata);
String strDisplayStart = "";
String strDisplayLength = "";
String[] arrayColumen = new String[new JSONUser().toArray().length];
int strSortId = 0;
String strSort = "";
for (int i = 0; i < jsonarray.size(); i++) {
JSONObject obj = (JSONObject) jsonarray.get(i);
String strName = (String) obj.get("name");
String strValue = obj.get("value").toString();
if (strName.equals("sEcho")) { sEcho = strValue; }
if (strName.equals("iDisplayStart")) { strDisplayStart = strValue; }
if (strName.equals("iDisplayLength")) { strDisplayLength = strValue; }
if (strName.equals("sColumns")) { arrayColumen = strValue.split(","); }
if (strName.startsWith("iSortCol_")) { strSortId = Integer.parseInt(strValue); }
if (strName.startsWith("sSortDir_")) { strSort = strValue; } // desc 或 asc
}
Map<Object, Object> params = new HashMap<Object, Object>();
try {
params = managerService.getUserList(參數);
} catch (Exception e) {
e.printStackTrace();
}
String count = (String) params.get("COUNT");
String[][] strData = (String[][]) params.get("AO_DATA");
return successed(new DataTableReturnObject(
Integer.parseInt(count), Integer.parseInt(count), sEcho, strData));
}
public Map<Object, Object> getUserList(Map<Object, Object> queryParams) throws Exception {
String iCount = 總記錄數;
// 將查詢結果轉換為二維陣列
String[][] data = {};
if (lstUser != null && lstUser.size() > 0) {
int record = lstUser.size();
data = new String[record][];
for (int i = 0; i < lstUser.size(); i++) {
User objUser = (User) lstUser.get(i);
JSONUser jsonUser = new JSONUser();
BeanUtils.copyProperties(jsonUser, objUser);
data[i] = jsonUser.toArray();
}
}
queryParams.clear();
queryParams.put("AO_DATA", data);
queryParams.put("COUNT", iCount);
return queryParams;
}
最關鍵的一條規則:二維陣列中每一列的欄位數量與順序,必須與前端表格的欄位完全一致。少一欄會導致整列錯位,順序錯了則資料會跑到別的欄位去,而且這種錯誤在畫面上往往不明顯,很難察覺。
ajax.error 中提示。strSortId 直接進 SQL 的 ORDER BY 是 SQL Injection 的經典入口。請對照 arrayColumen 取出欄位名後,比對一份允許清單再使用。getUserList 未見分頁邏輯,若實際上是查全部再於記憶體切頁,資料量一大就會拖垮服務。應把 iDisplayStart、iDisplayLength 帶進 SQL 的 LIMIT/OFFSET。iTotalRecords 與 iTotalDisplayRecords 在沒有篩選條件時才相等;有搜尋時前者是全域總數、後者是篩選後總數,兩者要各自查詢。Map<String, Object> 並以常數定義鍵名,重構時才有編譯器幫你檢查。