相關閱讀：
Java中jqGrid 學習筆記整理――進階篇(一)
本篇開始正式與後台（java語言）進行數據交互，使用的平台為
JDK：java 1.8.0_71
myEclisp 2015 Stable 2.0
Apache Tomcat-8.0.30
Mysql 5.7
Navicat for mysql 11.2.5(mysql數據庫管理工具)
一、數據庫部分
1、創建數據庫
使用Navicat for mysql創建數據庫（使用其他工具或直接使用命令行暫不介紹）
2、
2.創建表
雙擊打開上步創建數據庫――右擊Tables――選擇New Table
建立如下字段保存時會提示輸入表名
二、程序部分
1、新建項目
使用myEclipse新建――Web Project
輸入項目名稱選擇java和運行該項目的Tomcat 一直點下一步直到下圖頁麵點選下面自動生成web.xml文件的複選框後完成
創建如下包結構並新建一個vo類（屬性與數據庫字段一一對應）
demo.java
package com.xeonmic.vo;public class demo {private int id;private String name;private int type;private double pay;private String text;public demo() {// TODO Auto-generated constructor stub}public demo(int id, int type, Double pay,String name, String text) {this.id = id;this.name = name;this.type = type;this.pay = pay;this.text = text;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getType() {return type;}public void setType(int type) {this.type = type;}public double getPay() {return pay;}public void setPay(Double pay) {this.pay = pay;}public String getText() {return text;}public void setText(String text) {this.text = text;}@Overridepublic String toString() {return "demo [id=" + id + ", name=" + name + ", type=" + type+ ", pay=" + pay + ", text=" + text + "]";}}2、導入數據庫連接和JSON文件的jar包
數據庫連接jar包可以在mysql安裝目錄的如下目錄查找到
另外下載JSON所需jar包，已上傳百度雲（http://pan.baidu.com/s/1dETGjRV）一起復制粘貼到WebRoot/WEB-INF/lib目錄下
然後全選右鍵添加到構建路徑
3、DAO設計模式的基本分層實現
參考《Java Web開發實戰經典基礎篇》這裡不在敘述直接貼源碼後續單獨開一篇專門講這部分基礎知識
―3.1、DatabaseConnection.java
package com.xeonmic.dbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DatabaseConnection {private static final String DBDRIVER="org.gjt.mm.mysql.Driver";private static final String DBURL="jdbc:mysql://localhost:3306/jqgriddemo";private static final String DBUSER="root";private static final String DBPASSWORD="1234";private Connection conn =null;public DatabaseConnection(){try {Class.forName(DBDRIVER);this.conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);} catch (ClassNotFoundException e) {// TODO 自動生成的catch 塊e.printStackTrace();} catch (SQLException e) {// TODO 自動生成的catch 塊e.printStackTrace();}//System.out.println("連接數據庫成功");}public Connection getConnection(){return this.conn;}public void close(){if(this.conn != null){try {this.conn.close();} catch (SQLException e) {// TODO 自動生成的catch 塊e.printStackTrace();}}}}―3.2、DemoDAO.java
package com.xeonmic.dao;import java.util.List;import com.xeonmic.vo.demo;public interface DemoDAO {//添加方法public boolean doCreate(demo demo);//查詢方法public List<demo> doSearch(String keys);//刪除方法public boolean doDelete(int id);//修改方法public boolean doChange(demo demo);}―3.3、DemoDAOImpl.java
package com.xeonmic.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.xeonmic.dao.DemoDAO;import com.xeonmic.vo.demo;public class DemoDAOImpl implements DemoDAO {private Connection conn = null;private PreparedStatement pstmt = null;public DemoDAOImpl(Connection conn){this.conn=conn;}@Overridepublic boolean doCreate(demo demo) {boolean flag = false;String sql = "INSERT INTO t_demo(type,pay,name,text ) VALUES(?,?,?,?)";try {this.pstmt = this.conn.prepareStatement(sql);this.pstmt.setInt(1, demo.getType());this.pstmt.setDouble(2, demo.getPay());this.pstmt.setString(3, demo.getName());this.pstmt.setString(4, demo.getText());if(this.pstmt.executeUpdate()>0){flag = true;}this.pstmt.close();} catch (SQLException e) {// TODO 自動生成的catch 塊e.printStackTrace();}return flag;}@Overridepublic List<demo> doSearch(String keys) {// TODO Auto-generated method stubif (keys==null) {keys="";}String sql = "SELECT id,name,type,pay,text FROM t_demo "+keys;List<demo> all = new ArrayList<demo>();System.out.println(sql);try { this.pstmt = this.conn.prepareStatement(sql); ResultSet rs = this.pstmt.executeQuery();demo demo = null;while(rs.next()){demo = new demo(rs.getInt("id"),rs.getInt("type"),rs.getDouble("pay"),rs.getString("name"),rs.getString("text")); all.add(demo);}this.pstmt.close(); } catch (SQLException e) {// TODO 自動生成的catch 塊e.printStackTrace();}return all;}@Overridepublic boolean doDelete(int id) {boolean flag = false;String sql = "DELETE FROM t_demo WHERE id = ?";try {this.pstmt = this.conn.prepareStatement(sql);this.pstmt.setInt(1, id);if(this.pstmt.executeUpdate()>0){flag = true;}this.pstmt.close();} catch (SQLException e) {// TODO 自動生成的catch 塊e.printStackTrace();}return flag;}@Overridepublic boolean doChange(demo demo) {boolean flag = false;String sql = "UPDATE t_demo SET type=?,pay=?,name=?,text=? WHERE id=?";try {this.pstmt = this.conn.prepareStatement(sql);this.pstmt.setInt(5, demo.getId());this.pstmt.setInt(1, demo.getType());this.pstmt.setDouble(2, demo.getPay());this.pstmt.setString(3, demo.getName());this.pstmt.setString(4, demo.getText());if(this.pstmt.executeUpdate()>0){flag = true;}this.pstmt.close();} catch (SQLException e) {// TODO 自動生成的catch 塊e.printStackTrace();}return flag;}}―3.5、Factory.java
package com.xeonmic.factory;import com.xeonmic.dao.DemoDAO;import com.xeonmic.dao.proxy.DemoDAOProxy;public class Factory {public static DemoDAO getDemoDAOInstance(){return new DemoDAOProxy();}}―3.6、Demotest.java(對前面的方法進行一次簡單測試)
package com.xeonmic.test;import java.util.LinkedList;import java.util.List;import com.xeonmic.factory.Factory;import com.xeonmic.vo.demo;public class Demotest {public static void main(String[] args) {demo demo1 = new demo();demo1.setName("Name");demo1.setPay(0.98);demo1.setType(1);demo1.setText("Text");doCreate(demo1);doSearch(null);if (doSearch(null)!=null&&!doSearch(null).isEmpty()) {demo1 = doSearch("").get(0);demo1.setText("Change Text");doChange(demo1);doSearch("WHERE id = "+demo1.getId());doDelete(demo1.getId());doSearch(null);}}public static List<demo> doSearch(String keys) {List<demo> allDemos = new LinkedList<demo>();allDemos = Factory.getDemoDAOInstance().doSearch(keys);for (demo demo : allDemos) {System.out.println(demo.toString());}return allDemos;}public static void doCreate(demo demo) {if (Factory.getDemoDAOInstance().doCreate(demo)) {System.out.println("添加成功");}else {System.out.println("添加失敗");}}public static void doChange(demo demo) {if (Factory.getDemoDAOInstance().doChange(demo)) {System.out.println("修改成功");}else {System.out.println("修改失敗");}}public static void doDelete(int id) {if (Factory.getDemoDAOInstance().doDelete(id)) {System.out.println("刪除成功");}else {System.out.println("刪除失敗");}}}/** 輸出結果添加成功SELECT id,name,type,pay,text FROM t_demo demo [id=1, name=Name, type=1, pay=0.98, text=Text]SELECT id,name,type,pay,text FROM t_demo demo [id=1, name=Name, type=1, pay=0.98, text=Text]SELECT id,name,type,pay,text FROM t_demo demo [id=1, name=Name, type=1, pay=0.98, text=Text]SELECT id,name,type,pay,text FROM t_demo demo [id=1, name=Name, type=1, pay=0.98, text=Text]修改成功SELECT id,name,type,pay,text FROM t_demo WHERE id = 1demo [id=1, name=Name, type=1, pay=0.98, text=Change Text]刪除成功SELECT id,name,type,pay,text FROM t_demo * */4、JSP頁面和Servlet部分（重要）
―4.1、index.jsp（將index.html中html標籤到html標籤中的內容替換index.jsp中html的內容並對JS進行如下修改）
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>DEMO</title><link rel="stylesheet" type="text/css" href="css/jquery-ui.min.css" /><link rel="stylesheet" type="text/css" href="css/jquery-ui.theme.min.css" /><link rel="stylesheet" type="text/css" href="css/ui.jqgrid-bootstrap-ui.css" /><link rel="stylesheet" type="text/css" href="css/ui.jqgrid.css" /></head><body><div id="main"><!--jqGrid所在--><table id="grid-table"></table><!--jqGrid 瀏覽導航欄所在--><div id="grid-pager"></div></div><script src="js/jquery-1.11.0.min.js" type="text/javascript" charset="utf-8"></script><script src="js/i18n/grid.locale-cn.js" type="text/javascript" charset="utf-8"></script><script src="js/jquery.jqGrid.min.js" type="text/javascript" charset="utf-8"></script><script type="text/javascript">//當datatype 為"local" 時需填寫var grid_selector = "#grid-table";var pager_selector = "#grid-pager";$(document).ready(function() {$("#grid-table").jqGrid({//用於檢索的Servlet URLurl:"<%=basePath%>"+"demoServlet", //用於添加、修改、刪除的Servlet URLediturl: "<%=basePath%>"+"demochangeServlet",//data: grid_data, //當datatype 為"local" 時需填寫datatype:"json", //數據來源，本地數據（local，json,jsonp,xml等）height: 150, //高度，表格高度。可為數值、百分比或'auto'mtype:"GET",//提交方式colNames: ['出庫單號', '出庫類型', '總金額', '申請人（單位）', '備註'],colModel: [{name: 'id',index: 'id', //索引。其和後台交互的參數為sidxkey: true, //當從服務器端返回的數據中沒有id時，將此作為唯一rowid使用只有一個列可以做這項設置。如果設置多於一個，那麼只選取第一個，其他被忽略width: 100,editable: false,editoptions: {size: "20",maxlength: "30"}}, {name: 'type',index: 'type',width: 200, //寬度editable: true, //是否可編輯edittype: "select", //可以編輯的類型。可選值：text, textarea, select, checkbox, password, button, image and file.seditoptions: {value: "1:採購入庫;2:退用入庫"}}, {name: 'pay',index: 'pay',width: 60,sorttype: "double",editable: true}, {name: 'name',index: 'name',width: 150,editable: true,editoptions: {size: "20",maxlength: "30"}}, {name: 'text',index: 'text',width: 250,sortable: false,editable: true,edittype: "textarea",editoptions: {rows: "2",cols: "10"}}, ],viewrecords: true, //是否在瀏覽導航欄顯示記錄總數rowNum: 10, //每頁顯示記錄數rowList: [10, 20, 30], //用於改變顯示行數的下拉列錶框的元素數組。 pager: pager_selector, //分頁、按鈕所在的瀏覽導航欄altRows: true, //設置為交替行表格,默認為false//toppager: true,//是否在上面顯示瀏覽導航欄multiselect: true, //是否多選//multikey: "ctrlKey",//是否只能用Ctrl按鍵多選multiboxonly: true, //是否只能點擊複選框多選// subGrid : true, //sortname:'id',//默認的排序列名//sortorder:'asc',//默認的排序方式（asc升序，desc降序）caption: "採購退貨單列表", //表名autowidth: true //自動寬});//瀏覽導航欄添加功能部分代碼$(grid_selector).navGrid(pager_selector, {search: true, // 檢索add: true, //添加（只有editable為true時才能顯示屬性）edit: true, //修改（只有editable為true時才能顯示屬性）del: true, //刪除refresh: true //刷新}, {}, // edit options{}, // add options{}, // delete options{multipleSearch: true} // search options - define multiple search);});</script></body></html>―4.2、demoServlet.java
package com.xeonmic.action;import java.io.IOException;import java.util.LinkedList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import net.sf.json.JSONArray;import net.sf.json.JSONObject;import com.xeonmic.factory.Factory;import com.xeonmic.vo.demo;/*** Servlet implementation class demoServlet*/public class demoServlet extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("utf-8"); //這裡不設置編碼會有亂碼response.setContentType("text/html;charset=utf-8");response.setHeader("Cache-Control", "no-cache"); int rows = Integer.valueOf(request.getParameter("rows")); //每頁中顯示的記錄行數int page = Integer.valueOf(request.getParameter("page")); //當前的頁碼String sord = request.getParameter("sord");//排序方式String sidx = request.getParameter("sidx");//排序列名Boolean search =(request.getParameter("_search").equals("true"))?true:false;//是否用於查詢請求List<demo> allList = new LinkedList<demo>();//返回結果集String keys="";//查詢條件字符串if(search){keys=" WHERE ";String filters = request.getParameter("filters");//具體的條件System.out.println(filters);//傳入數據的格式是類似這樣的："{"groupOp":"AND","rules":[{"field":"id","op":"eq","data":"1"},{"field":"type","op":"ew","data":"2"}]}"JSONObject jsonObject = JSONObject.fromObject(filters);String groupOp = "AND";//每個規則之間的關係（and/or）if (jsonObject.getString("groupOp")!=null&&!"".equals(jsonObject.getString("groupOp"))) {if (jsonObject.getString("groupOp").equals("OR")) {groupOp = "OR";}}JSONArray rulesjson = jsonObject.getJSONArray("rules");//遍歷每個條件for (int z=0; z < rulesjson.size(); z++) {Object t = rulesjson.get(z);JSONObject rulejson = JSONObject.fromObject(t);String field = rulejson.getString("field");String op = rulejson.getString("op");String data = rulejson.getString("data");String string = "";//用於存儲單個條件sql語句片段//開始轉化為sql語句switch (op) {case "eq"://相等string=" = '"+data+"' ";break;case "ne"://不相等string=" <> '"+data+"' ";break;case "li"://小於string=" < '"+data+"' ";break;case"le"://小於等於string=" <= '"+data+"' ";break;case"gt"://大於string=" > '"+data+"' ";break;case "ge"://大於等於string=" >= '"+data+"' ";break;case "bw"://在...之間{if (data.split(",").length==2) {string=" BETWEEN '"+data.split(",")[0]+"' AND '"+data.split(",")[1]+"' ";}else {string=" = '"+data+"' ";//數據錯誤時處理}} break;case"bn"://不在...之間{if (data.split(",").length==2) {string=" NOT BETWEEN '"+data.split(",")[0]+"' AND '"+data.split(",")[1]+"' ";}else {string=" <> '"+data+"' ";//數據錯誤時處理}}break;case"ew"://以...結束string=" LIKE '%"+data+"' ";break;case "en"://不以...結束string=" NOT LIKE '%"+data+"' ";break;case "cn"://包含string=" LIKE '%"+data+"%' ";break;case "nc"://不包含string=" NOT LIKE '%"+data+"%' ";break;case "in"://在{string=" IN ( ";String[] datas = data.split(",");for (int i = 0; i < datas.length; i++) {string+= " '"+datas[i]+"' ";if (i!=datas.length-1) {string += ",";}else {string += " ) ";}}}break;case "ni"://不在{string=" NOT IN ( ";String[] datas = data.split(",");for (int i = 0; i < datas.length; i++) {string+= " '"+datas[i]+"' ";if (i!=datas.length-1) {string += ",";}else {string += " ) ";}}}break;default:op=null;System.out.println("OP符號錯誤");//OP符號錯誤}if (op!=null) {if (z==rulesjson.size()-1) {keys+=" "+field+" "+string +" ";}else {keys+=" "+field+" "+string +" "+groupOp+" ";}}}}//升降序SQL語句轉換if (sidx!=null&&!"".equals(sidx)) {System.out.println(sidx);keys += " ORDER BY " + sidx;System.out.println("sord="+sord);if (!sord.equals("asc")) {keys += " DESC ";}}allList = Factory.getDemoDAOInstance().doSearch(keys);//分頁部分int total=0; total=(allList.size()%rows==0)?(allList.size()/rows):((allList.size()/rows)+1);int j = 0;int m = (page-1)*rows;int n = (page-1)*rows+rows;JSONArray jArray = new JSONArray();for (j=m; j<allList.size()&&j<n; j++) { jArray.add(JSONObject.fromObject(allList.get(j))); }JSONObject jjson = new JSONObject(); //檢索結果及分頁信息封裝返回jjson.accumulate("page", page);jjson.accumulate("total", total);jjson.accumulate("records", allList.size());jjson.accumulate("rows", jArray);System.out.println(jjson.toString());response.getWriter().write(jjson.toString());}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)*/protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubthis.doGet(request, response);}}―4.3、demochangeServlet.java
package com.xeonmic.action;import java.io.IOException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.xeonmic.factory.Factory;import com.xeonmic.vo.demo;public class demochangeServlet extends HttpServlet {/*** */private static final long serialVersionUID = 1L;/*** The doGet method of the servlet. <br>** This method is called when a form has its tag value method equals to get.* * @param request the request send by the client to the server* @param response the response send by the server to the client* @throws ServletException if an error occurred* @throws IOException if an error occurred*/public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {boolean flag = false;String oper =request.getParameter("oper");switch (oper) {case "del":{String[] ids = request.getParameter("id").split(",");for (int i = 0; i < ids.length; i++) {int id =Integer.valueOf(ids[i]);flag=Factory.getDemoDAOInstance().doDelete(id);}} break;case "add":{int type = Integer.valueOf(request.getParameter("type"));Double pay = Double.valueOf(request.getParameter("pay"));String name = request.getParameter("name");String text = request.getParameter("text");demo demo = new demo(-1,type,pay,name,text);flag = Factory.getDemoDAOInstance().doCreate(demo); }break;case "edit":{int id = Integer.valueOf(request.getParameter("id"));int type = Integer.valueOf(request.getParameter("type"));Double pay = Double.valueOf(request.getParameter("pay"));String name = request.getParameter("name");String text = request.getParameter("text");demo demo = new demo(id,type,pay,name,text);flag = Factory.getDemoDAOInstance().doChange(demo); }break;default:break;}System.out.println(flag);}/*** The doPost method of the servlet. <br>** This method is called when a form has its tag value method equals to post.* * @param request the request send by the client to the server* @param response the response send by the server to the client* @throws ServletException if an error occurred* @throws IOException if an error occurred*/public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {this.doGet(request, response);}}―4.4、web.xml
<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1"><display-name>jqGrid</display-name><servlet><servlet-name>demoServlet</servlet-name><servlet-class>com.xeonmic.action.demoServlet</servlet-class></servlet><servlet><servlet-name>demochangeServ let</servlet-name><servlet-class>com.xeonmic.action.demochangeServlet</servlet-class></servlet><servlet-mapping><servlet-name>demoServlet</servlet-name><url-pattern>/demoServlet</url-pattern></servlet-mappin g><servlet-mapping><servlet-name>demochangeServlet</servlet-name><url-pattern>/demochangeServlet</url-pattern></servlet-mapping><welcome-file-list><welcome-file>index.html</welcome-file><welcome-file>index. htm</welcome-file><welcome-file>index.jsp</welcome-file><welcome-file>default.html</welcome-file><welcome-file>default.htm</welcome-file><welcome-file>default.jsp</welcome-file></welcome-file-list></web-app>
至此，jqGrid單表功能已全部實現，例子中有哪些設計有問題請告知，下一篇將開始解決主從表的設計實現，敬請關註腳本直接網站！