Ext Grid后台分页完整示例

功能需求：

首先，可以选择新闻类型，选择了新闻类型后，点击刷新按钮，即可显示相应类型下的新闻。

其次，每页显示1条新闻，分页显示。

实现的具体步骤如下：

首先，你得查数据库吧，我专门写了个SQL Helper方法，用于查数据库和将查询结果拼接成为json串。

代码如下：

package com.business.hr.control;import java.sql.*;import java.util.*;import com.business.hr.dao.HbutHrNews;/* * author:Tammy Pi * function:专门为Ext2.0写的后台分页代码 */public class SqlHelper {private static Connection conn = null;public static Connection getConnection() {String driverManager = "oracle.jdbc.driver.OracleDriver";String url = "jdbc:oracle:thin:@localhost:1521:orcl";String username = "hbut_hr";String password = "hbut_hr";try {Class.forName(driverManager);conn = DriverManager.getConnection(url,username,password);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static int getNum(String xwlx) {String sql1 = "select count(xw_id) from hbut_hr_news where xw_lx='"+xwlx+"'";Connection conn = getConnection();int num = 0;if(conn != null) {ResultSet rs1 = null;Statement stm1 = null;try {stm1 = conn.createStatement();rs1 = stm1.executeQuery(sql1);if(rs1!=null&&rs1.next()) {num = rs1.getInt(1);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{  if(rs1 != null) {try {rs1.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(stm1 != null) {try {stm1.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn != null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}return num;}public static String readXw(String xwlx,int firstRow,int maxRow) {String sql = "select * from (select xw_id,xw_bt,xw_zz,xw_sj,xw_ly,xw_lx,ext1,rownum as num from hbut_hr_news where xw_lx='"+xwlx+"' and rownum<="+firstRow+maxRow+") temp where num>"+firstRow;Connection conn = getConnection();int num = getNum(xwlx);if(conn != null) {Statement stm = null;ResultSet rs = null;StringBuffer sb = new StringBuffer();List<HbutHrNews> list =new ArrayList<HbutHrNews>();try {stm = conn.createStatement();rs = stm.executeQuery(sql);while(rs != null && rs.next()) {HbutHrNews temp = new HbutHrNews();temp.setXwId(rs.getString("xw_id"));temp.setXwBt(rs.getString("xw_bt"));temp.setXwZz(rs.getString("xw_zz"));temp.setXwSj(rs.getDate("xw_sj"));temp.setXwLy(rs.getString("xw_ly"));temp.setXwLx(rs.getString("xw_lx"));temp.setExt1(rs.getString("ext1"));list.add(temp);}//拼接json串if(list != null) {//拼接Json串sb.append("{totalProperty:"+num+",root:[");for(int i=0;i<list.size();i++) {HbutHrNews temp = list.get(i);sb.append("{xwid:'"+temp.getXwId()+"',");sb.append("xwbt:'"+temp.getXwBt()+"',");sb.append("xwzz:'"+temp.getXwZz()+"',");sb.append("fbsj:'"+temp.getXwSj()+"',");sb.append("ly:'"+temp.getXwLy()+"',");sb.append("lx:'"+temp.getXwLx()+"',");sb.append("zt:'"+(temp.getExt1().equals("1")?"发布":"草稿")+"'}");if(i!=list.size()-1) {sb.append(",");}}sb.append("]}");}System.out.println("json串为：" + sb.toString());return sb.toString();} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println(e.getMessage());}finally {if(rs != null) {try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(stm != null) {try {stm.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn != null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}return "";}public static void main(String[] args) {SqlHelper.readXw("0101",0,1);}}

我查询的是Oracle数据库，所以是根据rownum进行分页的；如果你使用的是SQL Server数据库，就应该用top来进行分页。

readXw方法，接受xwlx新闻类型，从第几条开始firstRow，查询几条maxRow。

所以拼接成的字符串应该是这种形式：

{totalProperty:2,root:[{xwid:'201203131006028891000',xwbt:'教师录用公示 ',xwzz:'师资办',fbsj:'2012-03-13',ly:'人事处',lx:'0101',zt:'发布'}]}

totalProperty表示总共有多少条记录，而root中则放本页中应该显示的记录。

 

记录被查询出来了，总该有个东西调用它吧。这就需要一个servlet。此Servlet起到连接包含ext的JSP页面与查询数据库的SqlHelper类的作用。

ext传三个参数给servlet,servlet再调用SqlHelper的readXw方法。

Servlet代码如下：

public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {request.setCharacterEncoding("utf-8");  response.setCharacterEncoding("utf-8");  response.setContentType("application/json");    String xwlx = request.getParameter("xwlx");  int start = Integer.parseInt(request.getParameter("start"));  int limit = Integer.parseInt(request.getParameter("limit"));  System.out.println("start:"+start+",limit:"+limit);    String jsonStr = SqlHelper.readXw(xwlx,start,limit);  response.getWriter().write(jsonStr);}

 

最后，就是我们的JSP页面了：

<%@ page language="java"  pageEncoding="UTF-8"%><% response.addHeader("Cache-Control","no-cache");   String path = request.getContextPath();String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";%><html><head><meta http-equiv="pragma" content="no-cache"><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><!--禁止浏览器从本地计算机的缓存中访问页面内容,这样设定，访问者将无法脱机浏览--><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"><title></title>         <link rel="stylesheet" type="text/css" href="/ext2/resources/css/ext-all.css"></link>        <script type="text/javascript" src="/ext2/adapter/ext/ext-base.js"></script>        <script type="text/javascript" src="/ext2/ext-all.js"></script>        <script type="text/javascript" src='<%=path%>/dwr/engine.js'></script><script type="text/javascript" src='<%=path%>/dwr/interface/db2Json.js'></script><script type="text/javascript" src="<%=path%>/dwr/interface/hbutHrDWR.js"></script>  </head>  <body>  <!--上方的工具栏  -->  <div style="width:100%;height:40px;" id="northToolbar"></div>  <!-- 下方的GridPanel -->  <div style="width:100%;height:300px;" id="southDiv"></div>  </body></html><script type="text/javascript">var xwlx = '0101';var xwlxCombo;var curPage = 1;var data,store;Ext.onReady(function() {//alert("<%=path%>");//上方的工具栏var northToolbar = new Ext.Toolbar();northToolbar.render('northToolbar');var xwlxStore = new Ext.data.SimpleStore({fields:['value','text']});db2Json.selectSimpleData("select t.sys_code,(select t1.sys_name from system_property t1 where t1.sys_type = '新闻' and t1.sys_code=t.ext1)||'_'||t.sys_name from system_property t where t.sys_type = '新闻' and t.ext1 is not null order by t.sys_code",function(dat){xwlxStore.loadData(eval(dat));Ext.getCmp('xwlxCombo').setValue('0101');});xwlxCombo = new Ext.form.ComboBox({    id:'xwlxCombo',store:xwlxStore,displayField:'text',valueField:'value',mode:'local',triggerAction:'all',selectOnFocus:true,typeAhead:true});var refreshBtn = new Ext.Toolbar.Button({icon: "img/arrow_refresh.png",cls: "x-btn-text-icon bmenu",text: "刷新",handler:function clickRefresh() {xwlx = xwlxCombo.getValue();    //重新加载store里的数据    store.proxy.conn.url='../servlet/PageServlet?xwlx='+xwlx;    store.load({params:{start:0,limit:1}});}});northToolbar.add({text:'新闻类型'},xwlxCombo,'-',refreshBtn);//显示新闻的gridvar cm = new Ext.grid.ColumnModel([        {header:'新闻ID号',dataIndex:'xwid'},{header:'新闻标题',dataIndex:'xwbt'},{header:'作者',dataIndex:'xwzz'},{header:'时间',dataIndex:'fbsj'},{header:'来源',dataIndex:'ly'},{header:'类型',dataIndex:'lx'},{header:'状态',dataIndex:'zt'}]);store = new Ext.data.Store({proxy: new Ext.data.HttpProxy({url:'../servlet/PageServlet?xwlx='+xwlx}),reader:new Ext.data.JsonReader({totalProperty:'totalProperty',root:'root'},[{name:'xwid'},{name:'xwbt'},{name:'xwzz'},{name:'fbsj'},{name:'ly'},{name:'lx'},{name:'zt'}])});var grid = new Ext.grid.GridPanel({store:store,cm:cm,renderTo:'southDiv',autoHeight:true,width: Ext.get('southDiv').getWidth(),viewConfig:{forceFit:true},bbar:new Ext.PagingToolbar({pageSize:1,store:store,displayInfo:true,displayMsg:'显示第{0}条到第{1}条记录，共{2}条',emptyMsg:'没有记录'})});store.load({params:{start:0,limit:1}});});</script>

需要说明的有几个方面：

1.如果Json串是正确的，而Grid却显示不了数据，那么肯定是返回的数据中有html标签。故用servlet返回json串，而不要用JSP返回json串。

2.如果分页的地方出现了错误，检查bbar的pageSize和store.load({params:{start:**,limit:**}})的规定是否一致；再检查json串中的totalProperty是否是全部记录的数目，而root中是否是当前页面应该显示的数据。

3.在重新选择新闻类型，动态改变store的url代码关键为：

var refreshBtn = new Ext.Toolbar.Button({icon: "img/arrow_refresh.png",cls: "x-btn-text-icon bmenu",text: "刷新",handler:function clickRefresh() {xwlx = xwlxCombo.getValue();    //重新加载store里的数据    store.proxy.conn.url='../servlet/PageServlet?xwlx='+xwlx;    store.load({params:{start:0,limit:1}});}});

即refreshBtn被点击时，获得选择的新闻类型编号，动态改变store的url，并重新加载store。