jqGrid与后台的交互方式如下:
载入时,jqGrid以POST或GET方式(用定义)向服务器发起数据载入请求,服务器根据请求传递给jqGrid;
传递的参数如下:
page :指示需要查询第几页的数据。
rows :指示每页显示的记录条数。
sidx :指示查询排序的条件,这是一个字符串,可能是数据库表字段或者是POJO对象的属性名。这需要程序来处理。
sord :指示查询排序的方式,可能的值是ASC和DESC
_search :用来指示是否是查询,值是true或者false。
同时,除了_search,其他变量名可以通过
prmNames:{}, 进行指定,如本例中, prmNames:{rows:”pageSize”,page:”page”}, 将rows改为pageSize,将page仍定义为page。这样做的目的是我们想使用Struts2的json插件,避免命名上的冲突。
默认后台返回的json数据为
{ total: xxx, page: yyy, records: zzz, rows: [
{name1:”Row01″,name2:”Row 11″,name3:”Row 12″,name4:”Row 13″,name5:”Row 14″},
因为我们设定后台传递的类型为json,并且更改了prmNames,所以。我们要对jqGrid的属性做一下配置。
设定 datatype: 'json',
并设定
jsonReader: {
root: "rows", //root的意思是,表格数据的名
repeatitems : false,//指定返回的数据的标签是否是可重复的,一般为false
id:"0" //每行数据的唯一标识。可以设置为空字符串或者一个数字。一般设置为0
},
后台使用struts2+spring2.5+hibernate3.2.代码如下
数据库
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`department` int(11) DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_Reference_1` (`department`),
CONSTRAINT `FK_Reference_1` FOREIGN KEY (`department`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30127 DEFAULT CHARSET=utf8;
INSERT INTO `department` VALUES ('1', '经济系');
INSERT INTO `department` VALUES ('2', '物流系');
INSERT INTO `department` VALUES ('3', '数学系');
INSERT INTO `department` VALUES ('4', '计算机系');
INSERT INTO `department` VALUES ('5', '外语系');
INSERT INTO `student` VALUES ('30116', '张三', '1', '23', '1', '2009-01-25 15:37:34');
INSERT INTO `student` VALUES ('30117', '倪纹州', '1', '23', '1', '2010-02-02 15:45:09');
INSERT INTO `student` VALUES ('30118', '云联海', '1', '23', '2', '2010-02-25 15:45:12');
INSERT INTO `student` VALUES ('30119', '梅堃涛', '0', '23', '2', '2010-02-18 15:45:16');
INSERT INTO `student` VALUES ('30120', '赖单德', '1', '23', '4', '2010-02-05 15:45:20');
INSERT INTO `student` VALUES ('30121', '刘联石', '1', '23', '1', '2010-02-17 15:45:23');
INSERT INTO `student` VALUES ('30122', '易登刚', '0', '23', '4', '2010-02-03 15:45:26');
INSERT INTO `student` VALUES ('30123', '蔡优招', '1', '23', '1', '2010-02-02 15:45:29');
INSERT INTO `student` VALUES ('30124', '云联海', '1', '23', '4', '2010-02-09 15:45:31');
INSERT INTO `student` VALUES ('30125', '方流巍', '1', '23', '4', '2010-02-10 15:45:34');
INSERT INTO `student` VALUES ('30126', '尤小刚', '1', '23', '2', '2010-02-11 15:45:37');
先写一个工具类,为什么这么写,后面会逐步介绍
package org.huashui.jqgrid;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.huashui.json.JsonUtils;
/**
* @author huashui
* @url:http://
*/
public class JqGridHandler {
private HttpServletRequest request = null;
private String _search = "false";
private String searchField;
private String searchOper;
private String searchString;
private String filters;
private String sidx = "1";
private String sord = "desc";
// 存储总体的search
FilterSearch filterSearch = null;
public JqGridHandler() {
}
public JqGridHandler(HttpServletRequest request) {
this.request = request;
}
public String getWheres(String prefix, boolean isWhere) {
conditions();
if(tranToSQL(prefix).trim().equals("")){
return "";
}
if (!isWhere) {
return new StringBuilder(" where ").append(tranToSQL(prefix))
.toString();
}
return new StringBuilder(" and ").append(tranToSQL(prefix)).toString();
}
public String getOrders(String prefix, boolean isOrder) {
init();
StringBuilder sb = new StringBuilder();
if (isOrder) {
if (null != prefix) {
sb.append(prefix).append(".");
}
} else {
sb.append(" order by ");
if (null != prefix) {
sb.append(prefix).append(".");
}
}
return sb.append(doTables(sidx)).append(" ").append(sord).toString();
}
// 根据conditions转换成sql格式
public String tranToSQL(String prefix) {
StringBuilder sb = new StringBuilder("");
if (null != filterSearch) {
List<searchrule> rules = filterSearch.getRules();
int count = 0;
if (null != rules && (count = rules.size()) > 0) {
for (SearchRule rule : rules) {
if (null != rule.getField() && null != rule.getData()
&& null != rule.getOp()) {
if ("eq".equalsIgnoreCase(rule.getOp())) {
sb.append(rule.getField()).append(" = ")
.append("'").append(rule.getData()).append(
"'");
} else if ("nq".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" != ").append(
"'").append(rule.getData()).append("'");
} else if ("lt".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" < ")
.append("'").append(rule.getData()).append(
"'");
} else if ("le".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" <= ").append(
"'").append(rule.getData()).append("'");
} else if ("gt".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" > ")
.append("'").append(rule.getData()).append(
"'");
} else if ("ge".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" >= ").append(
"'").append(rule.getData()).append("'");
} else if ("bw".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" like ").append(
"'").append(rule.getData()).append("%")
.append("'");
} else if ("ew".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" like ").append(
"'").append("%").append(rule.getData())
.append("'");
} else if ("cn".equalsIgnoreCase(rule.getOp())) {
if (null != prefix) {
sb.append(prefix).append(".");
}
sb.append(rule.getField()).append(" like ").append(
"'").append("%").append(rule.getData())
.append("%").append("'");
} else {
}
count--;
if (count > 0) {
if (null != filterSearch.getGroupOp()) {
if (filterSearch.getGroupOp().equals("and"))
sb.append(" and ");
else
sb.append(" or ");
}
}
}
}
}
}
return sb.toString();
}
// 装载
@SuppressWarnings("unchecked")
private void conditions() {
// 初始化,如果request为空,说明是从set进来的。
init();
// 分拆,全部写入filersearch
if (null != _search && "true".equalsIgnoreCase(_search)) {
// 先写多选择的,一般有多选择就不会有单选择。
if (null != filters && filters.length() > 0) {
Map m = new HashMap();
m.put("rules", SearchRule.class);
filterSearch = (FilterSearch) JsonUtils.getDTOList(filters,
FilterSearch.class, m);
} else {
if (null != searchOper && null != searchString
&& null != searchField) {
SearchRule rule = new SearchRule();
rule.setData(searchString);
rule.setOp(searchOper);
rule.setField(doTables(searchField));
filterSearch = new FilterSearch();
filterSearch.setGroupOp(null);
List</searchrule><searchrule> rules = new ArrayList</searchrule><searchrule>();
rules.add(rule);
filterSearch.setRules(rules);
}
}
}
}
private void init() {
if (request != null) {
_search = request.getParameter("_search");
searchOper = request.getParameter("searchOper");
searchString = request.getParameter("searchString");
searchField = request.getParameter("searchField");
filters = request.getParameter("filters");
sidx = request.getParameter("sidx");
sord = request.getParameter("sord");
}
}
public HttpServletRequest getRequest() {
return request;
}
public void setRequest(HttpServletRequest request) {
this.request = request;
}
public String get_search() {
return _search;
}
public void set_search(String _search) {
this._search = _search;
}
public String getSearchField() {
return searchField;
}
public void setSearchField(String searchField) {
this.searchField = searchField;
}
public String getSearchOper() {
return searchOper;
}
public void setSearchOper(String searchOper) {
this.searchOper = searchOper;
}
public String getSearchString() {
return searchString;
}
public void setSearchString(String searchString) {
this.searchString = searchString;
}
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public String getSidx() {
return sidx;
}
public void setSidx(String sidx) {
this.sidx = sidx;
}
public String getSord() {
return sord;
}
public void setSord(String sord) {
this.sord = sord;
}
public FilterSearch getFilterSearch() {
return filterSearch;
}
public void setFilterSearch(FilterSearch filterSearch) {
this.filterSearch = filterSearch;
}
private String doTables(String str){
String temp = "";
if(str.startsWith("__")){
str = str.substring(2);
return str.replaceAll("_", ".");
} else {
return str;
}
}
}
</searchrule>
Action
/*
* @{#} StudentListAction.java Create on Jan 27, 2010 5:25:17 PM
* Copyright (c) 2009 by Huashui.
*/
package org.huashui.action;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.json.annotations.JSON;
import org.huashui.bean.PageBean;
import org.huashui.bean.StudentBean;
import org.huashui.hibernate.Student;
import org.huashui.jqgrid.JqGridHandler;
import org.huashui.service.IPaginate;
/**
* @author huashui
* @url:http://
*/
@ParentPackage("json-default")
@Namespace("/admin/json")
public class StudentListAction implements ServletRequestAware {
@Resource
private IPaginate paginate;
private HttpServletRequest request;
/*
* 分页
*/
private Integer page;// 当前页
private Integer total;// 总页数
private Integer pageSize = 20;// 每页显示多少
private Integer records;// 总记录数
private List<studentbean> rows;// 记录
public StudentListAction() {
System.out.println(new Date());
}
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;
}
@JSON(serialize = false)
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getRecords() {
return records;
}
public void setRecords(Integer records) {
this.records = records;
}
public List</studentbean><studentbean> getRows() {
return rows;
}
public void setRows(List</studentbean><studentbean> rows) {
this.rows = rows;
}
public void setServletRequest(HttpServletRequest request) {
this.request = request;
}
@Action(value = "jsondata", results = { @Result(type = "json") })
public String execute() throws Exception {
get();
return com.opensymphony.xwork2.Action.SUCCESS;
}
private void get() throws Exception {
PageBean pageBean = new PageBean();
pageBean.setPage(page);
pageBean.setPageSize(pageSize);
JqGridHandler handler = new JqGridHandler(request);
pageBean.setTotalCountSQL("select count(*) from Student "
+ handler.getWheres(null, false)
+ handler.getOrders(null, false));
pageBean.setListSQL("from Student "
+ handler.getWheres(null, false)
+ handler.getOrders(null, false));
pageBean.setCount(paginate.getTotalCount(pageBean).intValue());
List<student> list = paginate.getList(pageBean);
rows = new ArrayList<studentbean>();
for (Student student : list) {//为了方便接受和传递数据,我们新建一个Bean来进行。
StudentBean bean = new StudentBean();
bean.setAge(student.getAge());
bean.set__department_id(student.getDepartment().getId());
bean.setId(student.getId());
bean.setName(student.getName());
bean.setSex(student.getSex());
bean.setDate(student.getDate());
rows.add(bean);
}
total = pageBean.getPageCount();
records = pageBean.getCount();
}
}
</studentbean></student></studentbean>
分页Bean
package org.huashui.bean;
/**
* @author huashui
* @url:http://
*/
public class PageBean {
private int count = 0; // 记录总数
private int pageSize = 20; // 每页显示记录数
private int pageCount = 0; // 总页数
private int page = 1; // 当前页数
private String totalCountSQL;// 得到总记录数sql语句
private String listSQL;// 得到查询记录sql语句
public int getCount() {
return count;
}
public void setCount(int count) {
if (pageSize != 0) {
pageCount = count / pageSize;
if (count % pageSize != 0) {
pageCount++;
}
}
this.count = count;
}
public String getListSQL() {
return listSQL;
}
public void setListSQL(String listSQL) {
this.listSQL = listSQL;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public String getTotalCountSQL() {
return totalCountSQL;
}
public void setTotalCountSQL(String totalCountSQL) {
this.totalCountSQL = totalCountSQL;
}
}
分页实现
package org.huashui.service.impl;
import java.text.DecimalFormat;
import java.util.List;
import javax.annotation.Resource;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.huashui.bean.PageBean;
import org.huashui.service.IPaginate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
/**
*@author huashui
*@url http://
*分页实现类
*/
@Service("paginate")
@Transactional(propagation= Propagation.NOT_SUPPORTED,readOnly=true)
public class PaginateImpl implements IPaginate {
@Resource SessionFactory sessionFactory;
/**
* 查询信息进行分页
*/
@SuppressWarnings("unchecked")
public List getList(PageBean page) {
Query query = sessionFactory.getCurrentSession().createQuery(page.getListSQL());
query.setMaxResults(page.getPageSize());
query.setFirstResult((page.getPage() - 1) * page.getPageSize());
return query.list();
}
/**
* 查询信息进行分页 带有参数的
*/
@SuppressWarnings("unchecked")
public List getList(PageBean page, String[] str, Object[] ob2)
throws Exception {
Query query = sessionFactory.getCurrentSession().createQuery(page.getListSQL());
if(null!=str && null!=ob2) {
for(int i=0;i<str .length;i++) {
query.setParameter(str[i], ob2[i]);
}
}
return query.list();
}
private int ceil(double in){
String dbStr=new DecimalFormat(“#0″).format(in);
int newdb=Integer.parseInt(dbStr);
if(in>newdb){
newdb++;
}
return newdb;
}
/**
* 获取总条数 带有参数的
*/
public Long getTotalCount(PageBean p, String[] str, Object[] ob2)
throws Exception {
Query query = sessionFactory.getCurrentSession().createQuery(p.getTotalCountSQL());
if(null!=str && null!=ob2) {
for(int i=0;i</str><str .length;i++) {
query.setParameter(str[i], ob2[i]);
}
}
return (Long) query.uniqueResult();
}
/**
* 获取总条数
*/
public Long getTotalCount(PageBean page) throws Exception {
return (Long) sessionFactory.getCurrentSession().createQuery(page.getTotalCountSQL()).uniqueResult();
}
}
hibernate的Bean可以使用Eclipse进行生成。这里就不说了。
spring的配置
< ?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www./schema/beans"
xmlns:xsi="http://www./2001/XMLSchema-instance"
xmlns:context="http://www./schema/context"
xmlns:aop="http://www./schema/aop"
xmlns:tx="http://www./schema/tx"
xsi:schemaLocation="http://www./schema/beans
http://www./schema/beans/spring-beans-2.5.xsd
http://www./schema/context http://www./schema/context/spring-context-2.5.xsd
http://www./schema/aop http://www./schema/aop/spring-aop-2.5.xsd
http://www./schema/tx http://www./schema/tx/spring-tx-2.5.xsd">
<!-- 支持注解注入 -->
<context:component -scan base-package="org.huashui"/>
<!-- 数据库连接 -->
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName"
value="org.gjt.mm.mysql.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/jqgrid?useUnicode=true&characterEncoding=GBK" />
<property name="username" value="root" />
<property name="password" value="123456" />
<!-- 连接池启动时的初始值 -->
<property name="initialSize" value="1" />
<!-- 连接池的最大值 -->
<property name="maxActive" value="500" />
<!-- 最大空闲值.当经过一个高峰时间后,连接池可以慢慢将已经用不到的连接慢慢释放一部分,一直减少到maxIdle为止 -->
<property name="maxIdle" value="2" />
<!-- 最小空闲值.当空闲的连接数少于阀值时,连接池就会预申请去一些连接,以免洪峰来时来不及申请 -->
<property name="minIdle" value="1" />
</bean>
<!-- sessionFactory -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mappingResources">
<list>
<value>org/huashui/hibernate/Department.hbm.xml</value>
<value>org/huashui/hibernate/Student.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.hbm2ddl.auto=update
hibernate.show_sql=true
hibernate.format_sql=false
hibernate.cache.use_second_level_cache=false
hibernate.cache.use_query_cache=false
</value>
</property>
</bean>
<bean id="txManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<tx:annotation -driven transaction-manager="txManager" />
</beans>