jqgrid+struts2+jdbc实现的增删改查

zxingchao2005

浏览: 80385 次
性别:
来自: 北京

最近访客更多访客>>

linchers

xuewei97

xinger666

kanpiaoxue

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (83)

社区版块

存档分类

最近发现很多人都在用jqgrid，感觉蛮好玩的，所以自己动手写了个例子。本文参考了http://blog.csdn.net/gengv/category/637232.aspx，在此要感谢这位师傅，直接贴代码：

一、表现层主要代码

1.index.jsp

<%@ page language="java" import="java.util.*" contentType="text/html; charset=gbk" pageEncoding="gbk"%> <% 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>jqGrid练习</title> <meta http-equiv="content-type" content="text/html; charset=gbk"> <link rel="stylesheet" type="text/css" media="screen" href="<%=path%>/css/style.css" /> <link rel="stylesheet" type="text/css" media="screen" href="<%=path%>/css/jquery-ui-all.css" /> <link rel="stylesheet" type="text/css" media="screen" href="<%=path%>/css/jquery-ui-1.8.1.custom.css" /> <link rel="stylesheet" type="text/css" media="screen" href="<%=path%>/css/ui.jqgrid.css"/> <mce:script type="text/javascript" src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <mce:script src="<%=path%></mce:script> <link href="<%=path%>/css/jquery.hiAlerts.css" rel="stylesheet" type="text/css" media="screen" /> <link rel="stylesheet" type="text/css" media="screen" href="<%=path%>/css/demo.css"/> </head> <body> <h2 align="center"> 学生信息列表 </h2> <div> <span style="float:left;" mce_style="float:left;"> 学生姓名:<input type="text" id="stuName" class="queryinput" name="stuName"/> <button class="right-button02" onclick="query()">查询</button> <button class="right-button02" onclick="clearCondition()">重置</button> </span> <span style="float:right;" mce_style="float:right;"> <button class="right-button02" onclick="openDialog4Adding()">添加</button> <button class="right-button02" onclick="openDialog4Updating()">修改</button> <button class="right-button02" onclick="openDialog4Deleting()">删除</button> </span> </div> <br /><br /> <table id="gridTable"></table> <div id="gridPager"></div> <div id="consoleDlg"> <div id="formContainer"> <form id="consoleForm"> <input type="hidden" id="selectId"/> <table class="formTable"> <tr> <th>姓名：</th> <td> <input type="text" class="textField" id="name" name="name" /> </td> </tr> <tr> <th>年龄：</th> <td> <input type="text" class="textField" id="age" name="age" /> </td> </tr> <tr> <th>性别：</th> <td> <input type="text" class="textField" id="sex" name="sex" /> </td> </tr> <tr> <th>地址：</th> <td> <input type="text" class="textField" id="address" name="address" /> </td> </tr> </table> </form> </div> </div> </body> </html>

2.student.js

$(function() { $("#gridTable").jqGrid( { url : "student_query.action", datatype : "json", mtype : "post", height : 350, autowidth : true, colNames:['ID','姓名', '年龄', '性别','地址'], colModel : [ { name : "id", index : "id", label : "ID", width : 40, sortable : true }, { name : "name", index : "name", label : "name", width : 80, sortable : true }, { name : "age", index : "age", label : "age", width : 80, sortable : true }, { name : "sex", index : "sex", label : "sex", width : 160, sortable : true }, { name : "address", index : "address", label : "address", width : 120, sortable : true } ], viewrecords : true, sortname : "id", rowNum : 5, rowList : [ 5, 10, 15 ], prmNames : { search : "search" }, //(1) jsonReader : { root : "dataList", // (2) records : "record", // (3) repeatitems : false // (4) }, pager : "#gridPager", caption : "学生信息列表", hidegrid : false }); // 配置对话框 $("#consoleDlg").dialog( { autoOpen : false, modal : true, // 设置对话框为模态（modal）对话框 resizable : true, width : 480, buttons : { // 为对话框添加按钮 "取消" : function() { $("#consoleDlg").dialog("close") }, "新增" : addStudent, "修改" : updateStudent, "删除" : deleteStudent } }); $("#stuName").val(" "); }); var openDialog4Adding = function() { var consoleDlg = $("#consoleDlg"); var dialogButtonPanel = consoleDlg.siblings(".ui-dialog-buttonpane"); consoleDlg.find("input").removeAttr("disabled").val(""); dialogButtonPanel.find("button:not(:contains('取消'))").hide(); dialogButtonPanel.find("button:contains('新增')").show(); consoleDlg.dialog("option", "title", "新增学生").dialog("open"); }; var openDialog4Updating = function() { var consoleDlg = $("#consoleDlg"); var dialogButtonPanel = consoleDlg.siblings(".ui-dialog-buttonpane"); consoleDlg.find("input").removeAttr("disabled"); dialogButtonPanel.find("button:not(:contains('取消'))").hide(); dialogButtonPanel.find("button:contains('修改')").show(); consoleDlg.dialog("option", "title", "修改学生"); loadSelectedRowData(); } var openDialog4Deleting = function() { var consoleDlg = $("#consoleDlg"); var dialogButtonPanel = consoleDlg.siblings(".ui-dialog-buttonpane"); consoleDlg.find("input").attr("disabled", true); dialogButtonPanel.find("button:not(:contains('取消'))").hide(); dialogButtonPanel.find("button:contains('删除')").show(); consoleDlg.dialog("option", "title", "删除学生"); loadSelectedRowData(); } var loadSelectedRowData = function() { var selectedRowId = $("#gridTable").jqGrid("getGridParam", "selrow"); if (!selectedRowId) { hiAlert("请先选择需要编辑的行!"); return false; } else { var params = { "stu.id" : selectedRowId }; // 从Server读取对应ID的JSON数据 $.ajax( { url : "student_view.action", data : params, dataType : "json", cache : false, error : function(textStatus, errorThrown) { hiAlert("系统ajax交互错误: " + textStatus); }, success : function(data, textStatus) { // 如果读取结果成功，则将信息载入到对话框中 var rowData = data.stu; var consoleDlg = $("#consoleDlg"); consoleDlg.find("#selectId").val(rowData.id); consoleDlg.find("#name").val(rowData.name); consoleDlg.find("#age").val(rowData.age); consoleDlg.find("#sex").val(rowData.sex); consoleDlg.find("#address").val(rowData.address); // 根据新载入的数据将表格中的对应数据行一并更新一下 var dataRow = { id : rowData.id, name : rowData.name, age : rowData.age, sex : rowData.sex, address : rowData.address }; $("#gridTable").jqGrid("setRowData", data.stu.id, dataRow); // 打开对话框 consoleDlg.dialog("open"); } }); } }; var addStudent = function() { var consoleDlg = $("#consoleDlg"); var name = $.trim(consoleDlg.find("#name").val()); var age = $.trim(consoleDlg.find("#age").val()); var sex = $.trim(consoleDlg.find("#sex").val()); var address = $.trim(consoleDlg.find("#address").val()); var params = { "stu.name" : name, "stu.age" : age, "stu.sex" : sex, "stu.address" : address }; $ .ajax( { url : "student_add.action", data : params, dataType : "json", cache : false, error : function(textStatus, errorThrown) { hiAlert("系统ajax交互错误: " + textStatus); }, success : function(data, textStatus) { if (data.message == true) { var dataRow = { id : data.stu.id, // 从Server端得到系统分配的id name : name, age : age, sex : sex, address : address }; var srcrowid = $("#gridTable").jqGrid("getGridParam", "selrow"); if (srcrowid) { $("#gridTable").jqGrid("addRowData", data.contact.id, dataRow, "before", srcrowid); } else { $("#gridTable").jqGrid("addRowData", data.stu.id, dataRow, "first"); } consoleDlg.dialog("close"); hiAlert("添加成功!"); } else { hiAlert("添加失败!"); } } }); }; var updateStudent = function() { var consoleDlg = $("#consoleDlg"); var pId = $.trim(consoleDlg.find("#selectId").val()); var name = $.trim(consoleDlg.find("#name").val()); var age = $.trim(consoleDlg.find("#age").val()); var sex = $.trim(consoleDlg.find("#sex").val()); var address = $.trim(consoleDlg.find("#address").val()); var params = { "stu.id" : pId, "stu.name" : name, "stu.age" : age, "stu.sex" : sex, "stu.address" : address }; $.ajax( { url : "student_update.action", data : params, dataType : "json", cache : false, error : function(textStatus, errorThrown) { hiAlert("系统ajax交互错误: " + textStatus); }, success : function(data, textStatus) { if (data.message == true) { var dataRow = { id : data.stu.id, name : name, age : age, sex : sex, address : address }; $("#gridTable").jqGrid("setRowData", data.stu.id, dataRow, { color : "#FF0000" }); hiAlert("更新成功!"); consoleDlg.dialog("close"); } else { hiAlert("修改失败!"); } } }); }; var deleteStudent = function() { var consoleDlg = $("#consoleDlg"); var pId = $.trim(consoleDlg.find("#selectId").val()); var params = { "stu.id" : pId }; $.ajax( { url : "student_delete.action", data : params, dataType : "json", cache : false, error : function(textStatus, errorThrown) { hiAlert("系统ajax交互错误: " + textStatus); }, success : function(data, textStatus) { if (data.message==true) { $("#gridTable").jqGrid("delRowData", pId); consoleDlg.dialog("close"); hiAlert("联系人删除成功!"); } else { hiAlert("删除操作失败!"); } } }); }; function query(){ var sdata = { //构建查询需要的参数 stuName: $("#stuName").val() }; //获得当前postData选项的值 var postData = $("#gridTable").jqGrid("getGridParam", "postData"); //将查询参数融入postData选项对象 $.extend(postData, sdata); $("#gridTable").jqGrid("setGridParam", { search: true,mtype : "post" // 将jqGrid的search选项设为true }).trigger("reloadGrid", [{page:1}]); //重新载入Grid表格，以使上述设置生效 } function clearCondition(){ $("#stuName").val(" "); var sdata = { //构建一套空的查询参数 stuName: "" }; var postData = $("#gridTable").jqGrid("getGridParam", "postData"); $.extend(postData, sdata); //将postData中的查询参数覆盖为空值 $("#gridTable").jqGrid("setGridParam", { search: false,mtype : "post" // 将jqGrid的search选项设为false }).trigger("reloadGrid", [{page:1}]); }

二、逻辑处理层

1.BaseAction.java

package action; import java.util.Collections; import java.util.List; import com.opensymphony.xwork2.ActionSupport; public class BaseAction<T> extends ActionSupport{ protected List<T> dataList = Collections.emptyList();//分页结果集 protected Integer rows = 0;//每页显示的数目 protected Integer page = 0;//当前页数 protected Integer total = 0;//总页数 protected Integer record = 0;// 总记录数 protected String sord;//排序方式 protected String sidx;//排序字段 protected String search;//是否开启查询 public List<T> getDataList() { return dataList; } public void setDataList(List<T> dataList) { this.dataList = dataList; } public Integer getRows() { return rows; } public void setRows(Integer rows) { this.rows = rows; } public Integer getPage() { return page; } public void setPage(Integer page) { this.page = page; } public Integer getTotal() { return total; } public void setTotal(Integer total) { this.total = total; } public Integer getRecord() { return record; } public void setRecord(Integer record) { this.record = record; } public String getSord() { return sord; } public void setSord(String sord) { this.sord = sord; } public String getSidx() { return sidx; } public void setSidx(String sidx) { this.sidx = sidx; } public String getSearch() { return search; } public void setSearch(String search) { this.search = search; } }

2.StudentAction

package action; import java.net.URLDecoder; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Map; import org.apache.commons.lang.StringEscapeUtils; import service.StudentService; import bean.Student; public class StudentAction<Student> extends BaseAction { private StudentService service = new StudentService(); private bean.Student stu; private boolean message; private String stuName; //添加 public String add() { try { stu.setId(service.add(stu)); message = true; } catch (Exception e) { message = false; e.printStackTrace(); } return "add"; }//查看 public String view(){ try { stu=service.findById(stu.getId()); message = true; } catch (Exception e) { message = false; e.printStackTrace(); } return "view"; } //修改 public String update(){ try { message=service.update(stu); } catch (Exception e) { message = false; e.printStackTrace(); } return "update"; } //删除 public String delete(){ try { message=service.delete(stu.getId()); } catch (Exception e) { message = false; e.printStackTrace(); } return "delete"; } //查询 public String query() throws Exception{ Map map = service.getResult(rows, page, stuName,sidx, sord); dataList = (ArrayList) map.get("dataList"); record = Integer.parseInt(map.get("tatalCount") == null ? "" : map.get( "tatalCount").toString()); total = (int) Math.ceil((double) record / (double) rows); return "success"; } public void setStu(bean.Student stu) { this.stu = stu; } public bean.Student getStu() { return stu; } public void setMessage(boolean message) { this.message = message; } public boolean getMessage() { return message; } public void setStuName(String stuName) { this.stuName = stuName; } public String getStuName() { return stuName; } }

三、数据访问层

StudentService.java

package service; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import util.DBUtil; import bean.Student; public class StudentService { public int add(Student stu) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int result=0; String sql = "insert into student(name,age,sex,address) values(?,?,?,?)"; try { conn = DBUtil.getConnection(); ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); ps.setString(1, stu.getName()); ps.setInt(2, stu.getAge()); ps.setString(3, stu.getSex()); ps.setString(4, stu.getAddress()); ps.executeUpdate(); rs = ps.getGeneratedKeys(); if(rs.next()) { result = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } return result; } public boolean update(Student stu) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int result = 0; String sql = "update student set name=?,age=?,sex=?,address=? where id=?"; try { conn = DBUtil.getConnection(); ps = conn.prepareStatement(sql); ps.setString(1, stu.getName()); ps.setInt(2, stu.getAge()); ps.setString(3, stu.getSex()); ps.setString(4, stu.getAddress()); ps.setInt(5, stu.getId()); result = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } return result>0?true:false; } public boolean delete(int id) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int result = 0; String sql = "delete from student where id=?"; try { conn = DBUtil.getConnection(); ps = conn.prepareStatement(sql); ps.setInt(1, id); result = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } return result>0?true:false; } public Student findById(int id){ Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; boolean flag = false; String sql = "select * from student where id=?"; Student stu=null; try { conn = DBUtil.getConnection(); ps = conn.prepareStatement(sql); ps.setInt(1, id); rs=ps.executeQuery(); if(rs.next()){ stu = new Student(); stu.setAddress(rs.getString("address")); stu.setAge(rs.getInt("age")); stu.setId(rs.getInt("id")); stu.setName(rs.getString("name")); stu.setSex(rs.getString("sex")); } } catch (Exception e) { e.printStackTrace(); } return stu; } public Map getResult(int pageSize, int currentPage,String stuName, String sidx, String sord) { String sql = "select * from student where 1=1 "; if(stuName!=null&&!stuName.trim().equals("")){ sql+=" and name like '"+"%"+stuName+"%'"; } if (sidx == null || sidx.trim().equals("")) { sql += "order by id asc"; } else { sql += "order by " + sidx + " " + sord; } Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; Map result = null; List<Student> list = null; Student stu = null; try { conn = DBUtil.getConnection(); result = new HashMap(); list = new ArrayList(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); if ((currentPage - 1) * pageSize != 0) rs.absolute((currentPage - 1) * pageSize); int i = 0; while (rs.next() && i++ < pageSize) { stu = new Student(); stu.setAddress(rs.getString("address")); stu.setAge(rs.getInt("age")); stu.setId(rs.getInt("id")); stu.setName(rs.getString("name")); stu.setSex(rs.getString("sex")); list.add(stu); } result.put("dataList", list); sql = "select count(*) from student where 1=1 "; if(stuName!=null&&!stuName.trim().equals("")){ sql+=" and name like '"+"%"+stuName+"%'"; } ps = conn.prepareStatement(sql); rs = ps.executeQuery(sql); if (rs.next()) { result.put("tatalCount", rs.getInt(1)); } rs.close(); ps.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } return result; } }

四、公用的类及其配置文件

1.Student.java

package bean; public class Student { private int id; private String name; private int age; private String sex; private String address; 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 getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } }

2.DBUtil.java

package util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { public static Connection getConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); System.out.println("驱动程序没有找到" + e.getMessage()); } try { conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/practice", "root", "123"); } catch (SQLException e) { e.printStackTrace(); System.out.println("数据库连接出错"); } return conn; } }

3.Struts.xml

<?xml version="1.0" encoding="gbk"?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN" "http://struts.apache.org/dtds/struts-2.0.dtd"> <struts> <package name="anxin" extends="json-default" namespace="/"> <action name="student_*" class="action.StudentAction" method="{1}"> <result name="add" type="json"> <param name="includeProperties"> stu/.id, message </param> <param name="noCache">true</param> <param name="ignoreHierarchy">false</param> </result> <result name="view" type="json"> <param name="includeProperties"> stu.*, message </param> <param name="noCache">true</param> <param name="ignoreHierarchy">false</param> <param name="excludeNullProperties">true</param> </result> <result name="update" type="json"> <param name="includeProperties"> stu/.id, message </param> <param name="noCache">true</param> <param name="ignoreHierarchy">false</param> </result> <result name="delete" type="json"> <param name="includeProperties"> stu/.id, message </param> <param name="noCache">true</param> <param name="ignoreHierarchy">false</param> </result> <result name="success" type="json"> <param name="includeProperties"> ^dataList/[/d+/]/./w+, rows, page, total, record </param> <param name="noCache">true</param> <param name="ignoreHierarchy">false</param> </result> </action> </package> </struts>

五、页面效果图

1.列表