jqGrid基础学习：3jqGrid与后台的交互（基于struts2）

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://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为空，说明是从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> rules = new ArrayList<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;
		}
 
	}
}

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;
 
	/*
	 * 分页
	 */
	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> getRows() {
		return rows;
	}
 
	public void setRows(List<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();
	}
 
}

分页Bean

package org.huashui.bean;
 
 
 
/**
 * @author huashui
 * @url:http://huashui.org
 */
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://huashui.org
 *分页实现类
 */
@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.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.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&amp;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>
Posted on 2010-02-06 at 9:12 上午 | Filed: jqGrid | Tags: ,
Entry RSS 2.0 feed | Leave a Trackback | Post a comment |

Related Posts


One Response to “jqGrid基础学习：3jqGrid与后台的交互（基于struts2）”

  1. 亮亮 说：
    2010-05-17于4:18 下午

    IPaginate 这个类呢

    回复

Post a Comment