jqGrid 学习笔记整理——进阶篇(二)
来源:互联网 发布:网络舆情队伍建设 编辑:程序博客网 时间:2024/05/17 23:15
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、创建表
双击打开上步创建数据库——右击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 DemoDAOProxy.java
package com.xeonmic.dao.proxy;import java.util.List;import com.xeonmic.dao.DemoDAO;import com.xeonmic.dao.impl.DemoDAOImpl;import com.xeonmic.dbc.DatabaseConnection;import com.xeonmic.vo.demo;public class DemoDAOProxy implements DemoDAO { private DatabaseConnection dbc = null; private DemoDAO ddao = null; public DemoDAOProxy(){ this.dbc = new DatabaseConnection(); this.ddao = new DemoDAOImpl(this.dbc.getConnection()); } @Override public boolean doCreate(demo demo) { boolean flag = false; flag = this.ddao.doCreate(demo); this.dbc.close(); return flag; } @Override public List<demo> doSearch(String keys) { // TODO Auto-generated method stub List<demo> all = null; all = this.ddao.doSearch(keys); this.dbc.close(); return all; } @Override public boolean doDelete(int id) { boolean flag = false; flag = this.ddao.doDelete(id); this.dbc.close(); return flag; } @Override public boolean doChange(demo demo) { boolean flag = false; flag = this.ddao.doChange(demo); this.dbc.close(); return flag; }}
—3.6、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.7、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单表功能已全部实现,例子中有哪些设计有问题请告知,下一篇将开始解决 主从表 的设计实现,敬请期待!
2 0
- jqGrid 学习笔记整理——进阶篇(二)
- jqGrid 学习笔记整理——进阶篇(一 )
- jqGrid 学习笔记整理——终极篇(一)
- jqGrid 学习笔记整理——基础篇
- jqGrid学习笔记(二)
- jqGrid学习笔记(二)
- jqGrid学习笔记(二)
- jqGrid学习笔记 完整整理
- jqgrid学习(二)
- jqgrid学习(二)
- Redis学习笔记(二)—— 进阶
- jquery学习笔记---jqGrid学习笔记 完整整理
- jqGrid学习笔记(一)
- jqGrid学习笔记(一)
- JqGrid---学习笔记(一)
- GSON使用的学习笔记,进阶篇(二)
- solr学习笔记(二):进阶
- Oracle学习笔记(二十八)——pl/sql的进阶之控制结构
- jQuery on()方法
- [算法练习]实现itoa,atoi
- NYOJ Cut the rope
- 写博客,从现在开始
- UI调试神器 for ios:Reveal的使用与破解
- jqGrid 学习笔记整理——进阶篇(二)
- js 声明变量
- WebViewJavascriptBridge详细使用
- Swift高阶函数map,filter,reduce实践
- 首试GPU并行运算
- 常用正则表达式
- MongoDB在Windows32bit(linux)下配置成自动开启的服务器注意事项
- Swift StateMachine源码分析
- 算法练习-实现memmove,及memmove与memmcpy区别