Ext JS GridPanel + Sqllite + 查询分页

先看看sqllite的分页sql语句，堪称史上最简单语句，跟Ext Grid的分页参数，简直是天造地设的一对！

下面的sql表示查询从第n条到m条的记录

select xxx,xxx from xxx where xxx order by xxx limit n,m

是不是很简单？跟别的数据库比起来，简直是傻瓜式操作!

再看Ext Grid的分页参数

store.load({ params: { start: 0, limit: 5} }); 

表示返回从第0条，到第5条的记录!Ext Grid跟sqllite分页真是太般配了

Webservice Code:

        [WebMethod]
        public DataSet GetHotSpotList(int start, int limit ,string hotspotName)
        {
            var condition = "";
            condition = string.IsNullOrEmpty(hotspotName) ? "" : " and HOTSPOTNAME like '%" + hotspotName + "%'";
            var ds = SqliteHelper.ExecuteDataset("select *,(select count(*) from CONF_3DGIS_HOTSPOT where 1=1 " + condition +
                " ) as totalRecords from CONF_3DGIS_HOTSPOT where 1=1 " + condition + " order by OID" + " limit " + start + "," + limit, null);
            return ds;
        }

注意上面有个字段别名“totalRecords”,返回的是总记录数,因为前台的Ext Grid需要拿到这个数字来显示分页

下面的JS代码为页面第一次加载时，填充grid，并显示第0-5条的记录.

JS Code

//调用Webservice,返回热点列表,并填充grid
var fillHotspotGrid = function () {
    Ext.getDom("hotspotgrid").innerHTML = "";
    var cm = new Ext.grid.ColumnModel([
	    new Ext.grid.CheckboxSelectionModel(), {
	        handleMouseDown: Ext.emptyFn,
	        header: "OID",
	        dataIndex: "OID",
	        width: 20,
	        sortable: true,
	        hidden: true //隐藏该列
	    },
        { header: '热点名称(双击定位)', dataIndex: 'HOTSPOTNAME', sortable: true, width: 50 },
		{ header: '经度', dataIndex: 'LONGITUDE', sortable: true, width: 40, hidden: true },
		{ header: '纬度', dataIndex: 'LATITUDE', sortable: true, width: 40, hidden: true },
		{ header: '海拔', dataIndex: 'ALTITUDE', sortable: true, width: 40, hidden: true }
    ]);

    //数据源
    var store = new Ext.data.Store({
        //url: iisServer + 'GetHotSpotList', // Web Service 地址
        url: 'http://localhost/WebServiceSqlite/DataService.asmx/GetHotSpotList',
        //autoLoad: { params: { start: 0, limit: 5} },
        reader: new Ext.data.XmlReader({
            totalRecords: 'totalRecords', //后端返回的xml中，必须有这个字段，且必须是总记录数
            //如果是json reader就用totalProperty: "results",
            record: 'Table', // 数据。对应于 GetHotSpotList 返回的 DataSet 中的 Table 表
            id: 'OID' // 主键。
        },
        [
		  { name: 'OID' },
		  { name: 'HOTSPOTNAME' },
		  { name: 'LONGITUDE' },
		  { name: 'LATITUDE' },
          { name: 'ALTITUDE' }
		]),
        remoteSort: true // 服务端排序
    });
    storeHotspotGrid = store;

    var pagebar = new Ext.PagingToolbar({
        pageSize: 5, //每页显示条数
        store: store,
        displayInfo: false,
        doLoad: function (start) {
            var txtSearchVal = Ext.get('txtSearchHotspot').dom.value;
            store.baseParams = { hotspotName: txtSearchVal }; //除分页外的参数
            var o = {}, pn = this.paramNames;
            o[pn.start] = start;
            o[pn.limit] = this.pageSize;
            store.load({ params: o });
        }
    });

    // GridPanel 组件
    var grid = new Ext.grid.GridPanel({
        frame: true,
        enableHdMenu: false,
        width: 290,
        height: 245,
        trackMouseOver: true,
        loadMask: { msg: '正在加载数据，请稍侯……' },
        renderTo: 'hotspotgrid',
        store: store,
        //sm:new Ext.grid.CheckboxSelectionModel({header: ""}),       
        cm: cm,
        viewConfig: {
            forceFit: true
        },
        bbar: pagebar,
        //双击事件
        listeners: {
            "rowdblclick": function (grid, rowIndex, e) {
                grid.getSelectionModel().each(function (rec) {
                    goToPosition(rec.get("LONGITUDE"), rec.get("LATITUDE"), rec.get("ALTITUDE"), 0, 0, 0)
                    var coord3D = explorer.Factory.CreateCoord3D();
                    coord3D.eu_x = rec.get("LONGITUDE");
                    coord3D.eu_y = rec.get("LATITUDE");
                    coord3D.eu_z = rec.get("ALTITUDE");
                    hotpoint = coord3D;
                    windowFlags = 1;
                    if (HotSpotLayer == null) {
                        createHotSpotLayer("hotspot");
                    }
                    HotSpotLayer.clear();
                    drawVectorPoint(coord3D);
                }
				);
            }
        }
    });

    storeHotspotGrid.removeAll();
    store.removeAll();
    var txtSearchVal = Ext.get('txtSearchHotspot').dom.value;
    store.baseParams = { hotspotName: txtSearchVal }; //除分页外的参数
    store.load({ params: { start: 0, limit: 5} });    //分页的时候这么写
    hotspotGrid = grid;

}

注意数据源store的参数，store.baseParams，上例是文本框中填写的查询条件，这个不可以跟分页的参数写在一起，并且要 写在store.load的前面!

必须注意分页控件的doLoad事件

        doLoad: function (start) {
            var txtSearchVal = Ext.get('txtSearchHotspot').dom.value;
            store.baseParams = { hotspotName: txtSearchVal }; //除分页外的参数
            var o = {}, pn = this.paramNames;
            o[pn.start] = start;
            o[pn.limit] = this.pageSize;
            store.load({ params: o });
        }

上面仍然要写查询参数,及分页参数,并load数据源,不然点翻页按钮不会有任何效果

下面为查询按钮的代码

Ext.onReady(function () {
    Efs.getExt('btnSearchHotspot').on('click', function () {
        var txtSearchVal = Ext.get('txtSearchHotspot').dom.value;
        storeHotspotGrid.removeAll();
        storeHotspotGrid.baseParams = { hotspotName: txtSearchVal }; //除分页外的参数
        storeHotspotGrid.load({ params: { start: 0, limit: 5} });    //分页的时候这么写
        Ext.get('txtSearchHotspot').dom.value = "";
    }, this)
});

首先清空数据源的数据,然后重新传参数并分页,最后清空查询文本框,方便下次输入.