Related readings:
Organizing jqGrid study notes in Java - Advanced chapter (I)
This article officially starts to interact with the background (java language), and the platform used is
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 database management tool)
1. Database part
1. Create a database
Create a database using Navicat for mysql (not introduced using other tools or using the command line directly)
2.
2. Create a table
Double-click to open the previous step to create a database - right-click Tables - select New Table
When you create the following fields, you will prompt to enter the table name.
2. Procedure part
1. Create a new project
Create new using myEclipse - Web Project
Enter the project name to select java and Tomcat running the project. Click Next until the following page click the check box below to automatically generate web.xml file.
Create the following package structure and create a new vo class (properties correspond to database fields one by one)
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. Import the jar package of database connection and JSON file
The database connection jar package can be found in the following directory of the mysql installation directory
In addition, the jar package required for JSON has been uploaded to Baidu Cloud (http://pan.baidu.com/s/1dETGjRV) and copied and pasted into the WebRoot/WEB-INF/lib directory.
Then select all right click to add to the build path
3. Basic layered implementation of DAO design model
Refer to "Java Web Development Classic Basics" here, we will not describe the source code and then write a separate article to specifically talk about this part of the basic knowledge.
―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 automatically generated catch block e.printStackTrace();} catch (SQLException e) {// TODO automatically generated catch Block e.printStackTrace();}//System.out.println("Conn connection successfully");}public Connection getConnection(){return this.conn;}public void close(){if(this.conn != null){try {this.conn.close();} catch (SQLException e) {// TODO automatically generated catch block e.printStackTrace();}}}}―3.2. DemoDAO.java
package com.xeonmic.dao;import java.util.List;import com.xeonmic.vo.demo;public interface DemoDAO {//Add method public boolean doCreate(demo demo);//Query method public List<demo> doSearch(String keys);//Delete method public boolean doDelete(int id);//Modify method 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 Auto-generated catch block 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 automatically generated catch block 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 automatically generated catch block 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 automatically generated catch block 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 (conduct a simple test of the previous method)
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("Add successful");}else {System.out.println("Add failed");}}public static void doChange(demo demo) {if (Factory.getDemoDAOInstance().doChange(demo)) {System.out.println("Modification was successful");}else {System.out.println("Modification failed");}}public static void doDelete(int id) {if (Factory.getDemoDAOInstance().doDelete(id)) {System.out.println("Delete successfully");}else {System.out.println("Delete failed");}}}/** The output result is successfully added 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]Seterect modification id,name,type,pay,text FROM t_demo WHERE id = 1demo [id=1, name=Name, type=1, pay=0.98, text=Change Text]Delete successfully SELECT id,name,type,pay,text FROM t_demo * */4. JSP page and Servlet section (important)
―4.1. index.jsp (replace the content of html tags in index.html into html tags and modify the content of html in index.jsp and make the following modifications to 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"><!-- where jqGrid is located--><table id="grid-table"></table><!--jqGrid Browse Navigation Bar--><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">//When the datatype is "local", you need to fill in var grid_selector = "#grid-table";var pager_selector = "#grid-pager";$(document).ready(function() {$("#grid-table").jqGrid({//Servlet URLLurl used for retrieval:"<%=basePath%>"+"demoServlet", //Servlet URLLediturl used for adding, modifying, and deleting: "<%=basePath%>"+"demochangeServlet",//data: grid_data, //When datatype is "local", you need to fill in datatype:"json", //Data source, local data (local, json, jsonp, xml, etc.) height: 150, //Height, table height. Can be a value, percentage or 'auto'mtype:"GET", // Submission method colNames: ['Out-out order number', 'out-out type', 'total amount', 'Applicant (unit)', 'Note'], colModel: [{name: 'id',index: 'id', //Index. The parameter of its interaction with the background is sidxkey: true, //When there is no id in the data returned from the server side, use this as the only rowid and use only one column to do this setting. If more than one is set, only the first one is selected, and the others are ignored width: 100,editable: false,editoptions: {size: "20",maxlength: "30"}}, {name: 'type',index: 'type',width: 200, //Width editable: true, //Is editable edittype: "select", //Types that can be edited. Optional values: text, textarea, select, checkbox, password, button, image and file.seditoptions: {value: "1: Purchase and enter; 2: Return to enter"}}, {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, //Whether to display the total number of records in the browsing navigation bar rowNum: 10, //Display the number of records per page rowList: [10, 20, 30], //Array of elements used to change the number of rows to display the drop-down list box. pager: pager_selector, //The browsing navigation bar where the page and buttons are located is altRows: true, //Set as alternating row table, default is false//toppager: true, //Whether the browsing navigation bar is displayed on it multiselect: true, //Whether the browsing navigation bar is multiselect: true, //Whether the browsing navigation bar is multiselect: true, //Whether the browsing navigation bar is multiselect: true, //Whether the browsing navigation bar is multiselected by Ctrl keys multiboxonly: true, //Whether the check box is multiselected by subGrid: true, //sortname:'id',//Default sorting method (asc ascending order, desc descending order) caption: "Purchase return order list", //Table name autowidth: true //Auto-width});//Browse navigation bar to add function part code $(grid_selector).navGrid(pager_selector, {search: true, //Retrieve add: true, //Add (Offices can be displayed only when editable is true) edit: true, //Modify (Offices can be displayed only when editable is true) del: true, //Delete refresh: true //Refresh}, {}, // 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"); //If encoding is not set here, there will be garbled response.setContentType("text/html;charset=utf-8"); response.setHeader("Cache-Control", "no-cache"); int rows = Integer.valueOf(request.getParameter("rows")); //The number of records displayed in each page int page = Integer.valueOf(request.getParameter("page")); //The current page number String sord = request.getParameter("sord");//Sorting method String sidx = request.getParameter("sidx");//Sorting column name Boolean search =(request.getParameter("_search").equals("true"))?true:false;//Is it used to query request List<demo> allList = new LinkedList<demo>();//Return result set String keys="";//Query condition string if(search){keys=" WHERE ";String filters = request.getParameter("filters");//Specific conditions System.out.println(filters);//The format of the incoming data is similar to this: "{"groupOp":"AND","rules":[{"field":"id","op":"eq","data":"1"},{"field":"type","op":"ew","data":"2"}]}"JSONObject jsonObject = JSONObject.fromObject(filters);String groupOp = "AND";//Relationship between each rule (and/or)if (jsonObject.getString("groupOp")!=null&&!"".equals(jsonObject.getString("groupOp"))) {if (jsonObject.getString("groupOp").equals("OR")) {groupOp = "OR";}}JSONArray rulesjson = jsonObject.getJSONArray("rules");//Tranquility each condition 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 = "";// Used to store a single conditional sql statement fragment// Begin to convert to sql statement switch (op) {case "eq"://equal string=" = '"+data+"' ";break;case "ne"://unequal string=" <> '"+data+"' ";break;case "li"://less than string=" < '"+data+"' ";break;case"le"://less than or equal to string=" <= '"+data+"' ";break;case"gt"://greater than string=" > '"+data+"' ";break;case "ge"://greater than or equal to string=" >= '"+data+"' ";break;case "bw"://between...{if (data.split(",").length==2) {string=" BETWEEN '"+data.split(",")[0]+"' AND '"+data.split(",")[1]+"' ";}else {string=" = '"+data+"' ";//handle when data errors}} break;case"bn"://not between...{if (data.split(",").length==2) {string=" NOT BETWEEN '"+data.split(",")[0]+"' AND '"+data.split(",")[1]+"' ";}else {string=" <> '"+data+"' ";//handle when data errors}}break;case"ew"://end with...string=" LIKE '%"+data+"' ";break;case "en"://not end with...string=" NOT LIKE '%"+data+"' ";break;case "cn"://contains string=" LIKE '%"+data+"%' ";break;case "nc"://not containing string=" NOT LIKE '%"+data+"%' ";break;case "in"://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"://not in {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 symbol error");//OP symbol error}if (op!=null) {if (z==rulesjson.size()-1) {keys+=" "+field+" "+string +" ";}else {keys+=" "+field+" "+string +" "+groupOp+" ";}}}}//Ascending and descending SQL statement conversion 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);//Pagination section int total=0; total=(allList.size()%rows==0)?(allList.size()/rows):((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(); //Retrieval results and paging information encapsulation return 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 opera =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 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-mapping 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>
At this point, the jqGrid single table function has been fully implemented. Please let me know if there are any design problems in the example. The next article will start to solve the design implementation of the master and slave table. Please pay attention to the script direct website!