jqGrid與后臺的交互方式如下：
載入時(shí)，jqGrid以POST或GET方式（用定義）向服務(wù)器發(fā)起數據載入請求，服務(wù)器根據請求傳遞給jqGrid；
傳遞的參數如下：
page ：指示需要查詢(xún)第幾頁(yè)的數據。
rows ：指示每頁(yè)顯示的記錄條數。
sidx ：指示查詢(xún)排序的條件，這是一個(gè)字符串，可能是數據庫表字段或者是POJO對象的屬性名。這需要程序來(lái)處理。
sord ：指示查詢(xún)排序的方式，可能的值是ASC和DESC
_search ：用來(lái)指示是否是查詢(xún)，值是true或者false。
同時(shí)，除了_search，其他變量名可以通過(guò)
prmNames:{}, 進(jìn)行指定，如本例中， 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″},
因為我們設定后臺傳遞的類(lèi)型為json，并且更改了prmNames，所以。我們要對jqGrid的屬性做一下配置。
設定 datatype: 'json',
并設定
jsonReader: {
root: "rows", //root的意思是，表格數據的名
repeatitems : false,//指定返回的數據的標簽是否是可重復的，一般為false
id:"0" //每行數據的唯一標識?？梢栽O置為空字符串或者一個(gè)數字。一般設置為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', '經(jīng)濟系');
INSERT INTO `department` VALUES ('2', '物流系');
INSERT INTO `department` VALUES ('3', '數學(xué)系');
INSERT INTO `department` VALUES ('4', '計算機系');
INSERT INTO `department` VALUES ('5', '外語(yǔ)系');
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', '云聯(lián)海', '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', '賴(lài)單德', '1', '23', '4', '2010-02-05 15:45:20');
INSERT INTO `student` VALUES ('30121', '劉聯(lián)石', '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', '蔡優(yōu)招', '1', '23', '1', '2010-02-02 15:45:29');
INSERT INTO `student` VALUES ('30124', '云聯(lián)海', '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');
先寫(xiě)一個(gè)工具類(lèi),為什么這么寫(xiě)，后面會(huì )逐步介紹
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://huashui.org
*/
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為空，說(shuō)明是從set進(jìn)來(lái)的。
init();
// 分拆，全部寫(xiě)入filersearch
if (null != _search && "true".equalsIgnoreCase(_search)) {
// 先寫(xiě)多選擇的，一般有多選擇就不會(huì )有單選擇。
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://huashui.org
*/
@ParentPackage("json-default")
@Namespace("/admin/json")
public class StudentListAction implements ServletRequestAware {
@Resource
private IPaginate paginate;
private HttpServletRequest request;
/*
* 分頁(yè)
*/
private Integer page;// 當前頁(yè)
private Integer total;// 總頁(yè)數
private Integer pageSize = 20;// 每頁(yè)顯示多少
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) {//為了方便接受和傳遞數據，我們新建一個(gè)Bean來(lái)進(jìn)行。
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>
分頁(yè)Bean
package org.huashui.bean;
/**
* @author huashui
* @url:http://huashui.org
*/
public class PageBean {
private int count = 0; // 記錄總數
private int pageSize = 20; // 每頁(yè)顯示記錄數
private int pageCount = 0; // 總頁(yè)數
private int page = 1; // 當前頁(yè)數
private String totalCountSQL;// 得到總記錄數sql語(yǔ)句
private String listSQL;// 得到查詢(xún)記錄sql語(yǔ)句
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;
}
}
分頁(yè)實(shí)現
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://huashui.org
*分頁(yè)實(shí)現類(lèi)
*/
@Service("paginate")
@Transactional(propagation= Propagation.NOT_SUPPORTED,readOnly=true)
public class PaginateImpl implements IPaginate {
@Resource SessionFactory sessionFactory;
/**
* 查詢(xún)信息進(jìn)行分頁(yè)
*/
@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();
}
/**
* 查詢(xún)信息進(jìn)行分頁(yè) 帶有參數的
*/
@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進(jìn)行生成。這里就不說(shuō)了。
spring的配置
< ?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/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" />
<!-- 連接池啟動(dòng)時(shí)的初始值 -->
<property name="initialSize" value="1" />
<!-- 連接池的最大值 -->
<property name="maxActive" value="500" />
<!-- 最大空閑值.當經(jīng)過(guò)一個(gè)高峰時(shí)間后，連接池可以慢慢將已經(jīng)用不到的連接慢慢釋放一部分，一直減少到maxIdle為止 -->
<property name="maxIdle" value="2" />
<!-- 最小空閑值.當空閑的連接數少于閥值時(shí)，連接池就會(huì )預申請去一些連接，以免洪峰來(lái)時(shí)來(lái)不及申請 -->
<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>
聯(lián)系客服
