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

 

前几日一直在找关于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>

<!-- Core files -->
  <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>&nbsp;
    <button class="right-button02" onclick=
 clearCondition();
>
     重置
    </button> </span>
   <span style="float: right;">
    <button class="right-button02" onclick=
 openDialog4Adding();
>
     添加
    </button>&nbsp;
    <button class="right-button02" onclick=
 openDialog4Updating();
>
     修改
    </button>&nbsp;
    <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");

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

所有的操作都在这个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;
 }
}

好了,全部业务逻辑完成。现在我们看看效果怎么样,首先启动tomcat,进入url,如果大家的端口跟我的一样,那进入的地址就应该是http://localhost:8080/Struts2JQGrid/

显示的效果如下:

jqGrid(struts2+jdbc+jsp)增删改查的例子
 
看起来还不错哦,最重要的来了,我们主要还是看能不能进行操作,不然都是白瞎,好的,先加一条:
jqGrid(struts2+jdbc+jsp)增删改查的例子
看看有没有加进去jqGrid(struts2+jdbc+jsp)增删改查的例子
ok,很好。那下面我们进行一条数据的修改,假如我现在修改第33条,也就是姓名为bb的,我们把 他改为csdn,看看我改后的效果:
jqGrid(struts2+jdbc+jsp)增删改查的例子
看见了吧,工号我添加的是主键,这个可是唯一的,成功的进行了修改,下面还剩删除了,比如说,我删除弟35个记录,现在总记录是15条,看看我有没有删除成功:jqGrid(struts2+jdbc+jsp)增删改查的例子
看见没有,35不见了,右下角的记录数变成了14,说明删除也是ok的,漂亮!~
至此一套程序成功完成。
如果有需要的读者,可以给我留言,并留下邮箱,我会发去全部的源码,谢谢。
上一篇:C Deepin指针


下一篇:tomcat下的https项目创建与部署