Java中jqGrid 学习笔记整理——进阶篇(二)

相关阅读：

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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public List<demo> doSearch(String keys) {
// TODO Auto-generated method stub
if (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;
}
@Override
public 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;
}
@Override
public 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 = 1
demo [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 class="main" 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 URL
url:"<%=basePath%>"+"demoServlet", 
//用于添加、修改、删除的Servlet URL
editurl: "<%=basePath%>"+"demochangeServlet",
//data: grid_data, //当 datatype 为"local" 时需填写 
datatype:"json", //数据来源，本地数据（local，json,jsonp,xml等）
height: 150, //高度，表格高度。可为数值、百分比或'auto'
mtype:"GET",//提交方式
colNames: ['出库单号', '出库类型', '总金额', '申请人（单位）', '备注'],
colModel: [{
name: 'id',
index: 'id', //索引。其和后台交互的参数为sidx
key: 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.s
editoptions: {
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 stub
this.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>demochangeServlet</servlet-name>
<servlet-class>com.xeonmic.action.demochangeServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>demoServlet</servlet-name>
<url-pattern>/demoServlet</url-pattern>
</servlet-mapping>
<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单表功能已全部实现，例子中有哪些设计有问题请告知，下一篇将开始解决 主从表 的设计实现，敬请关注脚本直接网站！