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 block
			e.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 block
				e.printStackTrace();
			} finally{
				
				  if(rs1 != null) {
						
						try {
							rs1.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					if(stm1 != null) {
						
						try {
							stm1.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					if(conn != null) {
						
						try {
							conn.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.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 block
				System.out.println(e.getMessage());
			}finally {
				
				if(rs != null) {
					
					try {
						rs.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if(stm != null) {
					
					try {
						stm.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if(conn != null) {
					
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.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);
	
	//显示新闻的grid
	var 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。