jqGrid(struts2+jdbc+jsp)增删改查的例子

2022-10-11 19:30:01

前几日一直在找关于Java操作jqgrid返回json的例子，在网上也看了不少东西，结果都没几个合理的，于是本人结合网上的零散数据进行整理，完成了一个比较完整的jqgrid小例子，考虑到还有很多像我一样对此有需求的人，于是将此例子进行公布，以供有需要的进行参考，也希望大家可以提出意见，或者是行之有效的方法来完善这个例子，别的不多说，直接附上代码：

首先是jsp文件，也就是需要返回到客户端，能实实在在看到数据的页面

<%@ page language="java" import="java.util.*"
contentType="text/html; charset=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>vigor的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" />
  <script type="text/javascript" src="<%=path%>/js/jquery-1.5.2.min.js"></script>
  <script src="<%=path%>/js/jquery-ui-1.8.1.custom.min.js"
   type="text/javascript"></script>
  <script src="<%=path%>/js/jquery.ui.core.js"></script>
  <script src="<%=path%>/js/jquery.ui.widget.js"></script>
  <script src="<%=path%>/js/jquery.ui.mouse.js"></script>
  <script src="<%=path%>/js/jquery.ui.button.js"></script>
  <script src="<%=path%>/js/jquery.ui.draggable.js"></script>
  <script src="<%=path%>/js/jquery.ui.position.js"></script>
  <script src="<%=path%>/js/jquery.ui.resizable.js"></script>
  <script src="<%=path%>/js/jquery.ui.dialog.js"></script>
  <script src="<%=path%>/js/jquery.effects.core.js"></script>
  <script src="<%=path%>/js/i18n/grid.locale-zh_CN.js"
   type="text/javascript"></script>
  <script src="<%=path%>/js/jquery.jqGrid.min.js" type="text/javascript"></script>
  <script src="<%=path%>/js/student.js" type="text/javascript"></script>
  <script src="<%=path%>/js/jquery.ui.draggable.js"
   type="text/javascript"></script>

<script src="<%=path%>/js/jquery.hiAlerts-min.js"
   type="text/javascript"></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;"> 员工姓名:<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;">
    <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>
其中我进行标记的地方是：蓝色代表操作的方法，红色比较重要，因为返回json就靠这个id了

下面当然是通过我标记的方法去找javascript，因为我的方法都是写在那里面的：

$(function() {
$("#gridTable").jqGrid( {
  url : "student_query.action",
  datatype : "json",
  mtype : "post",
  height : 350,
  autowidth : true,
  colNames:['工号','姓名', '年龄', '性别','地址'],
  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 : 10,
  rowList : [ 10, 20, 30 ],
  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");

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}]);
}

所有的操作都在这个js里面，我们可以找到相应的操作action，这些action我已经进行了标记，下面该怎么样呢，当然是去struts.xml文件里面看看，这些action是怎么声明的咯，进入正题，到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}">//看到我这里标记的，action的定义是这样的
  
   <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>

就像我标记说的，相应的方法就是student加上那个结果名字而成，ok，结果一个个的都找到了，我们当然要去action里面看看相应的操作了

直接代码：

package action;

import java.util.ArrayList;
import java.util.Map;

import service.StudentService;

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;
}
}

这里面我写的都已经很清楚了，大家一看就会明白

接下来就是业务操作类，我只用了一个service，为了更加简便，没有用他的实现类

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;
}
}

至此代码全部完成，但是别忘了，如果想进行交互，还要与数据库进行连接，我把连接写在一个业务类里面：

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", "mysql");
  } catch (SQLException e) {
   e.printStackTrace();
   System.out.println("数据库连接出错");
  }
  return conn;
}
}