最近在学Jquery，发现jgrid做表格的查询很有意思，网上搜集了一些资料，参考人家的例子，自己动手也写了一个简单的例子。
一 View
<%@page contentType="text/html; charset=utf-8" %>
<%
String basePath = request.getContextPath();
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>crud demo</title>
<!-- JQuery CSS -->
<link rel="stylesheet" href="<%=basePath%>/jquery-ui-themes-1.8.14/themes/redmond/jquery-ui.css" type="text/css" />
<!-- jqGrid CSS -->
<link rel="stylesheet" href="<%=basePath%>/jqGrid-4.5.4/css/ui.jqgrid.css" type="text/css" />
<!-- The actual JQuery code -->
<script type="text/javascript" src="http://code.jquery.com/jquery-1.10.2.min.js" ></script>
<!-- The JQuery UI code -->
<script type="text/javascript" src="http://code.jquery.com/ui/1.10.3/jquery-ui.min.js"></script>
<!-- The jqGrid language file code-->
<script type="text/javascript" src="<%=basePath%>/jqGrid-4.5.4/js/i18n/grid.locale-cn.js"></script>
<!-- The atual jqGrid code -->
<script type="text/javascript" src="<%=basePath%>/jqGrid-4.5.4/js/jquery.jqGrid.src.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$("#gridTable").jqGrid({
url: "item_view.action",
datatype: "json",
mtype : "post",
height: 250,
colNames:['Inv No','Date', 'Client', 'Amount','Tax','Total','Notes'],
colModel:[
{name:'id',index:'id', width:55, editable:true, editoptions:{readonly:true}, sorttype:'int'},
{name:'invdate',index:'invdate', width:90, editable:true},
{name:'name',index:'name asc, invdate', width:100,editable:true},
{name:'amount',index:'amount', width:80, align:"right",editable:true,editrules:{number:true},sorttype:'number',formatter:'number'},
{name:'tax',index:'tax', width:80, align:"right",editable:true,editrules:{number:true},sorttype:'number',formatter:'number'},
{name:'total',index:'total', width:80,align:"right",editable:true,editrules:{number:true},sorttype:'number',formatter:'number'},
{name:'note',index:'note', width:150, sortable:false,editable:true}
],
sortname:'id',
sortorder:'asc',
/*editurl: 'server.php', */
viewrecords:true,
rowNum:10,
rowList:[10,20,30],
prmNames : {
search : "search"
},
jsonReader : {
root : "dataList",
records : "record",
repeatitems : false
},
pager:"#gridPager",
caption: "jqgrid CRUD demo",
hidegrid : false
});
$('#gridTable').jqGrid('navGrid','#gridPager',{
refresh:true,
edit:true,
add:true,
del:true,
search:false,
addfunc:openDialog4Adding,
editfunc:openDialog4Updating,
delfunc:openDialog4Deleting
});
//配置对话框
$("#consoleDlg").dialog({
autoOpen:false,
modal:true, //设置对话框为模态对话框
resizable:true,
width:480,
buttons:{
"取消":function(){
$("#consoleDlg").dialog("close")
},
"新增":addItem,
"修改":editItem,
"删除":delItem
}
});
});
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", "add record").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", "edit record");
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", "delete record");
loadSelectedRowData();
};
var loadSelectedRowData = function() {
var selectedRowId = $("#gridTable").jqGrid("getGridParam", "selrow");
if (!selectedRowId) {
alert("请先选择需要编辑的行!");
return false;
} else {
var params = {
"item.id" : selectedRowId
};
//从Server读取对应ID的JSON数据
$.ajax({
url : "item_find.action",
data : params,
dataType : "json",
cache : false,
error : function(textStatus, errorThrown) {
alert("系统ajax交互错误: " + textStatus);
},
success : function(data, textStatus) {
// 如果读取结果成功，则将信息载入到对话框中
var rowData = data.item;
var consoleDlg = $("#consoleDlg");
consoleDlg.find("#selectId").val(rowData.id);
consoleDlg.find("#invdate").val(rowData.invdate);
consoleDlg.find("#name").val(rowData.name);
consoleDlg.find("#amount").val(rowData.amount);
consoleDlg.find("#tax").val(rowData.tax);
consoleDlg.find("#total").val(rowData.total);
consoleDlg.find("#note").val(rowData.note);
// 根据新载入的数据将表格中的对应数据行一并更新一下
var dataRow = {
id : rowData.id,
invdate : rowData.invdate,
name : rowData.name,
amount : rowData.amount,
tax : rowData.tax,
total : rowData.total,
note : rowData.note
};
$("#gridTable").jqGrid("setRowData", data.item.id, dataRow);
// 打开对话框
consoleDlg.dialog("open");
}
});
}
};
var addItem = function() {
var consoleDlg = $("#consoleDlg");
var invdate = $.trim(consoleDlg.find("#invdate").val());
var name = $.trim(consoleDlg.find("#name").val());
var amount = $.trim(consoleDlg.find("#amount").val());
var tax = $.trim(consoleDlg.find("#tax").val());
var total = $.trim(consoleDlg.find("#total").val());
var note = $.trim(consoleDlg.find("#note").val());
var params = {
"item.invdate" : invdate,
"item.name" : name,
"item.amount" : amount,
"item.tax" : tax,
"item.total" : total,
"item.note" : note
};
$.ajax( {
url : "item_add.action",
data : params,
dataType : "json",
cache : false,
error : function(textStatus, errorThrown) {
alert("系统ajax交互错误: " + textStatus);
},
success : function(data, textStatus) {
if (data.message == true) {
var dataRow = {
id : data.item.id, //从server端获得系统分配的id
invdate : invdate,
name : name,
amount : amount,
tax : tax,
total : total,
note : note
};
/*
var srcrowid = $("#gridTable").jqGrid("getGridParam",
"selrow");
*/
$("#gridTable").jqGrid("addRowData",
data.item.id, dataRow, "last"); //将新行插入到末尾
consoleDlg.dialog("close");
alert("添加成功!");
}else{
alert("添加失败!");
}
}
});
};
var editItem = function() {
//待补充 20150619
var consoleDlg = $("#consoleDlg");
var pId = $.trim(consoleDlg.find("#selectId").val());
var invdate = $.trim(consoleDlg.find("#invdate").val());
var name = $.trim(consoleDlg.find("#name").val());
var amount = $.trim(consoleDlg.find("#amount").val());
var tax = $.trim(consoleDlg.find("#tax").val());
var total = $.trim(consoleDlg.find("#total").val());
var note = $.trim(consoleDlg.find("#note").val());
var params = {
"item.id" : pId,
"item.invdate" : invdate,
"item.name" : name,
"item.amount" : amount,
"item.tax" : tax,
"item.total" : total,
"item.note" : note
};
$.ajax({
url : "item_edit",
data : params,
dataType : "json",
cache : "false",
error : function(textStatus, errorThrown) {
alert("系统ajax交互错误: " + textStatus);
},
success : function(data, textStatus){
if(data.message == true){
var dataRow = {
id : data.item.id,
invdate : invdate,
name : name,
amount : amount,
tax : tax,
total : total,
note : note
};
$("#gridTable").jqGrid("setRowData", data.item.id, dataRow,
{
color : "#FF0000"
});
alert("更新成功!");
consoleDlg.dialog("close");
}else{
alert("修改失败!");
}
}
});
};
var delItem = function() {
var consoleDlg = $("#consoleDlg");
var pId = $.trim(consoleDlg.find("#selectId").val());
var params = {
"item.id" : pId
};
$.ajax( {
url : "item_del.action",
data : params,
dataType : "json",
cache : false,
error : function(textStatus, errorThrown) {
alert("系统ajax交互错误: " + textStatus);
},
success : function(data, textStatus) {
if (data.message==true) {
$("#gridTable").jqGrid("delRowData", pId);
consoleDlg.dialog("close");
alert("删除成功!");
} else {
alert("删除操作失败!");
}
}
});
};
</script>
</head>
<body>
<table id="gridTable"></table>
<div id="gridPager"></div>
<div id="consoleDlg">
<form id="consoleForm">
<input type="hidden" id="selectId"/>
<table class="formTable">
<tr>
<th>Date</th>
<td><input type="text" class="textField" id="invdate" name="invdate" />
</td>
</tr>
<tr>
<th>Client</th>
<td><input type="text" class="textField" id="name" name="name" />
</td>
</tr>
<tr>
<th>Amount</th>
<td><input type="text" class="textField" id="amount" name="amount" />
</td>
</tr>
<tr>
<th>Tax</th>
<td><input type="text" class="textField" id="tax" name="tax" />
</td>
</tr>
<tr>
<th>Total</th>
<td><input type="text" class="textField" id="total" name="total" />
</td>
</tr>
<tr>
<th>Notes</th>
<td><input type="text" class="textField" id="note" name="note" />
</td>
</tr>
</table>
</form>
</div>
</body>
</html>二 Model
package cn.demo.jqgrid.domain;
public class Item {
private int id;
private String invdate;
private String name;
private double amount;
private double tax;
private double total;
private String note;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getInvdate() {
return invdate;
}
public void setInvdate(String invdate) {
this.invdate = invdate;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getAmount() {
return amount;
}
public void setAmount(double amount) {
this.amount = amount;
}
public double getTax() {
return tax;
}
public void setTax(double tax) {
this.tax = tax;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
}三 Action
1. BaseAction.java
package cn.demo.jqgrid.action;
import java.util.Collections;
import java.util.List;
import com.opensymphony.xwork2.ActionSupport;
public class BaseAction<T> extends ActionSupport {
/* 默认生成 */
private static final long serialVersionUID = 4268258316273873129L;
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. ItemAction.java
package cn.demo.jqgrid.action;
import java.util.List;
import java.util.Map;
import cn.demo.jqgrid.service.ItemService;
public class ItemAction<Item> extends BaseAction {
private ItemService service = new ItemService();
private cn.demo.jqgrid.domain.Item item;
private boolean message;
public String add(){
try {
item.setId(service.add(item));
message = true;
} catch (Exception e) {
// TODO Auto-generated catch block
message = false;
e.printStackTrace();
}
return "add";
}
public String edit(){
try {
message = service.edit(item);
} catch (Exception e) {
// TODO Auto-generated catch block
message = false;
e.printStackTrace();
}
return "edit";
}
public String del(){
try {
message = service.delete(item.getId());
} catch (Exception e) {
// TODO Auto-generated catch block
message = false;
e.printStackTrace();
}
return "del";
}
public String view(){
Map map = service.getResult(rows, page, sidx, sord);
dataList = (List) map.get("dataList");
record = Integer.parseInt(null == map.get("totalCount") ? "0" : map.get("totalCount").toString());
total = (record + rows -1) / rows;
return SUCCESS;
}
public String find(){
try {
item = service.findById(item.getId());
message = true;
} catch (Exception e) {
// TODO Auto-generated catch block
message = false;
e.printStackTrace();
}
return "find";
}
public cn.demo.jqgrid.domain.Item getItem() {
return item;
}
public void setItem(cn.demo.jqgrid.domain.Item item) {
this.item = item;
}
public boolean isMessage() {
return message;
}
public void setMessage(boolean message) {
this.message = message;
}
}
四 Service
package cn.demo.jqgrid.service;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import cn.demo.jqgrid.dao.Dao;
import cn.demo.jqgrid.domain.Item;
public class ItemService {
public int add(Item item){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int seq=0;
Date invdate = null;
String invdateStr = item.getInvdate();
if(null != invdateStr || !"".equals(invdateStr)){
invdate = Date.valueOf(invdateStr);
}
String sql = "Insert into t_item(id, invdate, name, amount, tax, total, note) values(item_sequence.nextval, ?, ?, ?, ?, ?, ?)";
try {
conn = Dao.getConnection();
ps = conn.prepareStatement(sql);
ps.setDate(1, invdate);
ps.setString(2, item.getName());
ps.setDouble(3, item.getAmount());
ps.setDouble(4, item.getTax());
ps.setDouble(5, item.getTotal());
ps.setString(6, item.getNote());
ps.executeUpdate();
conn.commit();
sql = "select item_sequence.currval from dual";
sql = "Select count(1) From t_item Where 1=1";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()){
seq = rs.getInt(1);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(null != rs){
try {
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != ps){
try {
ps.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return seq;
}
public boolean edit(Item item){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int result = 0;
Date invdate = null;
String invdateStr = item.getInvdate();
if(null != invdateStr || !"".equals(invdateStr)){
invdate = Date.valueOf(invdateStr);
}
String sql = "Update t_item Set invdate=?, name=?, amount=?, tax=?, total=?, note=? Where id = ?";
try {
conn = Dao.getConnection();
ps = conn.prepareStatement(sql);
ps.setDate(1, invdate);
ps.setString(2, item.getName());
ps.setDouble(3, item.getAmount());
ps.setDouble(4, item.getTax());
ps.setDouble(5, item.getTotal());
ps.setString(6, item.getNote());
ps.setInt(7, item.getId());
result = ps.executeUpdate();
conn.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(null != rs){
try {
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != ps){
try {
ps.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
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 t_item where id = ?";
try {
conn = Dao.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
result = ps.executeUpdate();
conn.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(null != rs){
try {
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != ps){
try {
ps.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return result > 0 ? true : false;
}
public Item findById(int id){
Item item = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "Select id,to_char(invdate,'yyyy-mm-dd') invdate,name, amount,tax,total,note From t_item Where id = ?";
try {
conn = Dao.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if(rs.next()){
item = new Item();
item.setId(id);
item.setInvdate(rs.getString("invdate"));
item.setName(rs.getString("name"));
item.setAmount(rs.getDouble("amount"));
item.setTax(rs.getDouble("tax"));
item.setTotal(rs.getDouble("total"));
item.setNote(rs.getString("note"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(null != rs){
try {
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != ps){
try {
ps.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return item;
}
public Map getResult(int pageSize, int currentPage, String sidx, String sord){
Map map = null;
String sql = "Select id,to_char(invdate,'yyyy-mm-dd') invdate,name, amount,tax,total,note From t_item Where 1=1";
if(null == sidx || "".equals(sidx.trim())){
sql += " order by id asc";
}else{
sql += " order by " + sidx + " " + sord;
}
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Item> list = new ArrayList<Item>();
Item item = null;
try {
conn = Dao.getConnection();
map = new HashMap<String, Object>();
list = new ArrayList<Item>();
ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = ps.executeQuery();
rs.absolute((currentPage -1) * pageSize + 1 );
int i = 0;
while(i <= pageSize && !rs.isAfterLast()){
item = new Item();
item.setId(rs.getInt("id"));
item.setInvdate(rs.getString("invdate"));
item.setName(rs.getString("name"));
item.setAmount(rs.getDouble("amount"));
item.setTax(rs.getDouble("tax"));
item.setTotal(rs.getDouble("total"));
item.setNote(rs.getString("note"));
list.add(item);
rs.next();
i++;
}
map.put("dataList", list);
sql = "Select count(1) From t_item Where 1=1";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()){
map.put("totalCount", rs.getInt(1));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(null != rs){
try {
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != ps){
try {
ps.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(null != conn){
try {
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return map;
}
}
五 Struts.xml
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
"http://struts.apache.org/dtds/struts-2.0.dtd">
<struts>
<package name="demo" extends="json-default" namespace="/">
<action name="item_*" class="cn.demo.jqgrid.action.ItemAction" method="{1}">
<result name="find" type="json">
<param name="includeProperties">
item.*, message
</param>
<param name="noCache">true</param>
<param name="ignoreHierarchy">false</param>
<param name="excludeNullProperties">true</param>
</result>
<result name="add" type="json">
<param name="includeProperties">
item\.id, message
</param>
<param name="noCache">true</param>
<param name="ignoreHierarchy">false</param>
</result>
<result name="edit" type="json">
<param name="includeProperties">
item\.id, message
</param>
<param name="noCache">true</param>
<param name="ignoreHierarchy">false</param>
</result>
<result name="del" type="json">
<param name="includeProperties">
item\.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 查询
2 增加
3 修改
4 删除
七 鸣谢
http://trirand.com/blog/jqgrid/jqgrid.html
http://www.trirand.com/jqgridwiki/doku.php?id=wiki%3aform_editing
http://blog.youkuaiyun.com/zxingchao2009/article/details/6563353
http://blog.youkuaiyun.com/gengv/article/details/5719053
扫一扫
7323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
