功能需求:
首先,可以选择新闻类型,选择了新闻类型后,点击刷新按钮,即可显示相应类型下的新闻。
其次,每页显示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。