Java 整合 jqGrid 進階實戰（二）：後端分頁、搜尋、增刪改完整對接指南

專案技術棧與環境

• JDK 1.8、MyEclipse 2015、Tomcat 8.0.30
• MySQL 5.7 + Navicat 11
• 前端：jqGrid 5.x + jQuery 1.11 + Bootstrap UI 主題
• 後端：原生 Servlet + DAO 模式 + JSON-lib 序列化

資料庫設計

CREATE DATABASE jqgriddemo CHARSET utf8mb4;
USE jqgriddemo;
CREATE TABLE t_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    type INT,
    pay DECIMAL(10,2),
    text VARCHAR(200)
);

後端分層架構

com.xeonmic
├── vo
│   └── Demo.java              # 實體類，欄位與資料表一一對應
├── dao
│   ├── DemoDAO.java           # 介面
│   └── impl
│       └── DemoDAOImpl.java   # JDBC 實現
├── dbc
│   └── DatabaseConnection.java # 單例連線管理
├── factory
│   └── Factory.java           # 代理工廠獲取 DAO
├── action
│   ├── DemoServlet.java       # 查詢/分頁/搜尋
│   └── DemoChangeServlet.java # 增刪改
└── test
    └── DemoTest.java          # 單元測試

核心 Servlet 實現細節

1. 查詢 Servlet - 支援分頁、排序、多條件搜尋

@WebServlet("/demoServlet")
public class DemoServlet extends HttpServlet {
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("application/json;charset=UTF-8");
        
        // 1. 解析 jqGrid 參數
        int page = Integer.parseInt(req.getParameter("page"));       // 當前頁
        int rows = Integer.parseInt(req.getParameter("rows"));       // 每頁筆數
        String sidx = req.getParameter("sidx");                      // 排序欄位
        String sord = req.getParameter("sord");                      // asc/desc
        boolean isSearch = "true".equals(req.getParameter("_search"));
        
        // 2. 構建動態 WHERE 子句（jqGrid 進階搜尋發送 JSON filters）
        String whereSql = "";
        if (isSearch) {
            String filters = req.getParameter("filters");
            whereSql = buildWhereClause(filters); // 解析 JSON 組裝 SQL 片段
        }
        
        // 3. 排序
        if (sidx != null && !sidx.isEmpty()) {
            whereSql += " ORDER BY " + sidx + " " + sord;
        }
        
        // 4. 查詢總筆數 & 分頁資料
        List<Demo> all = Factory.getDemoDAOInstance().doSearch(whereSql);
        int total = (int) Math.ceil((double) all.size() / rows);
        int from = (page - 1) * rows;
        int to = Math.min(from + rows, all.size());
        List<Demo> pageData = all.subList(from, to);
        
        // 5. 組裝 jqGrid 要求的 JSON 格式
        JSONObject json = new JSONObject();
        json.put("page", page);
        json.put("total", total);
        json.put("records", all.size());
        json.put("rows", JSONArray.fromObject(pageData));
        
        resp.getWriter().write(json.toString());
    }
    
    // filters JSON 結構：{"groupOp":"AND","rules":[{"field":"name","op":"cn","data":"張"}]}
    private String buildWhereClause(String filters) {
        JSONObject jo = JSONObject.fromObject(filters);
        String groupOp = jo.getString("groupOp"); // AND/OR
        JSONArray rules = jo.getJSONArray("rules");
        StringBuilder sb = new StringBuilder(" WHERE ");
        
        for (int i = 0; i < rules.size(); i++) {
            JSONObject rule = rules.getJSONObject(i);
            String field = rule.getString("field");
            String op = rule.getString("op");
            String data = rule.getString("data");
            sb.append(field).append(convertOp(op)).append("'").append(data).append("'");
            if (i < rules.size() - 1) sb.append(" ").append(groupOp).append(" ");
        }
        return sb.toString();
    }
    
    private String convertOp(String op) {
        switch (op) {
            case "eq": return " = "; case "ne": return " <> ";
            case "lt": return " < "; case "le": return " <= ";
            case "gt": return " > "; case "ge": return " >= ";
            case "bw": return " LIKE '"; // 以...開頭，data 需加 %
            case "ew": return " LIKE '%"; // 以...結尾
            case "cn": return " LIKE '%"; // 包含，data 兩邊加 %
            case "nc": return " NOT LIKE '%";
            case "in": return " IN ("; case "ni": return " NOT IN (";
            default: return " = ";
        }
    }
}

2. 增刪改 Servlet

@WebServlet("/demoChangeServlet")
public class DemoChangeServlet extends HttpServlet {
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        String oper = req.getParameter("oper"); // add/edit/del
        boolean success = false;
        
        switch (oper) {
            case "add":
                Demo d = new Demo(-1, 
                    Integer.parseInt(req.getParameter("type")),
                    Double.parseDouble(req.getParameter("pay")),
                    req.getParameter("name"),
                    req.getParameter("text"));
                success = Factory.getDemoDAOInstance().doCreate(d);
                break;
            case "edit":
                d = new Demo(
                    Integer.parseInt(req.getParameter("id")),
                    Integer.parseInt(req.getParameter("type")),
                    Double.parseDouble(req.getParameter("pay")),
                    req.getParameter("name"),
                    req.getParameter("text"));
                success = Factory.getDemoDAOInstance().doChange(d);
                break;
            case "del":
                String[] ids = req.getParameter("id").split(",");
                for (String id : ids) {
                    success = Factory.getDemoDAOInstance().doDelete(Integer.parseInt(id));
                }
                break;
        }
        resp.getWriter().write(success ? "success" : "error");
    }
}

前端 jqGrid 配置關鍵點

$("#grid-table").jqGrid({
    url: "${pageContext.request.contextPath}/demoServlet",
    editurl: "${pageContext.request.contextPath}/demoChangeServlet",
    datatype: "json",
    mtype: "GET",
    colModel: [
        { name: 'id', key: true, width: 80, editable: false },
        { name: 'type', width: 120, editable: true, edittype: 'select', 
          editoptions: { value: "1:銷售入庫;2:退貨入庫" } },
        { name: 'pay', width: 80, editable: true, sorttype: 'number' },
        { name: 'name', width: 150, editable: true },
        { name: 'text', width: 200, editable: true, edittype: 'textarea' }
    ],
    pager: "#grid-pager",
    rowNum: 10, rowList: [10,20,30],
    viewrecords: true,
    multiselect: true,
    caption: "銷售退貨單列表",
    autowidth: true
}).navGrid("#grid-pager", {
    search: true, add: true, edit: true, del: true, refresh: true
}, {}, {}, {}, { multipleSearch: true });

常見坑點與解決

下一步擴展方向

• 主從表：訂單主檔 + 明細檔，jqGrid subGrid 或雙 Grid 聯動
• 樹形表格：treeGrid 模式展示組織架構、菜單權限
• 導出 Excel/PDF：後端 Apache POI / iText，前端匯出按鈕
• Spring Boot 重構：JPA/MyBatis 替換 JDBC、RESTful API 規範化、Swagger 文檔

本文提供的完整代碼結構可直接作為企業級後台管理系統列表頁的基礎模板，建議在理解核心參數交互機制後，根據業務靈活調整 SQL 構建邏輯與權限控制。