// -------------------- 定义grid--------------------------
var page_size = 10; //定义页面记录最大显示数量
var MyRecord = Ext.data.Record.create([
{name : 'ygId'},
{ name : 'workDate'},
{name : 'workEdDate'},
{name : 'wtBgtime'},
{name : 'wtEdtime' },
{name : 'wtPotime'},
]);
var store = new Ext.data.Store({
proxy : new Ext.data.HttpProxy({
url : 'empAbsentInfoManage.action?method=getlist' //从后台获取数据
}),
reader : new Ext.data.JsonReader({
root : "root",
totalProperty : "total"
}, MyRecord)
});
store.load({
params : {//这两个参数是分页的关键,当你点击 下一页 时,这里的值会传到后台,也就是会重新运行:store.load
start : 0,
limit : page_size
}
});
var sm = new Ext.grid.CheckboxSelectionModel();
var fuuzy = new Ext.form.TextField({
id : "fuzzy",
name : "fuzzy"
});
var grid = new Ext.grid.GridPanel({
region : "center",
// region:"east",
store : store,
columns : [// 设计页面显示的表格信息,字段与Ext.data.Record.create匹配
sm,
{
id : 'id',
header : "人员ID",
width : 75,
sortable : true,
dataIndex : 'ygId'
}, {
header : "开始日期",
width : 75,
sortable : true,
dataIndex : 'workDate'
}, {
header : "开始时间",
width : 75,
sortable : true,
dataIndex : 'wtBgtime'
},{
header : "结束日期",
width : 75,
sortable : true,
dataIndex : 'workEdDate'
}, {
header : "结束时间",
width : 75,
sortable : true,
dataIndex : 'wtEdtime'
}, {
header : "总时间",
width : 75,
sortable : true,
dataIndex : 'wtPotime'
}],
sm:sm,
stripeRows : true,
autoSizeColumns : true,
viewConfig : {
forceFit : true
},
autoExpandColumn : 'id',
title : '员工请假信息管理',
tbar : [fuuzy, {
text : "查询",
handler : queryRecord
}, {
text : "增加",
iconCls : 'add',
handler : addRecord
}, {
text : "修改",
iconCls : 'edit',
handler : updateRecord
}, {
text : "删除",
iconCls : 'delete',
handler : deleteRecord
}],
// 分页
bbar : new Ext.PagingToolbar({
pageSize : page_size,
store : store,
displayInfo : true
})
});
//--------------------------------------------------------------------
//====== 再来看看后台实现 =====================
//获得员工基本信息列表(根据工号模糊查询)
public void GetEmpWorkList() throws Exception {
int start = Integer.parseInt(request.getParameter("start"));
int limit = Integer.parseInt(request.getParameter("limit"));
int end = start + limit;
String fuzzy="";
String sql=
"select * from\n"+
" (\n"+
" select YG_ID, to_char(lt_bgtime,'YYYY-MM-DD HH24:MI:SS'),to_char(lt_EDTIME,'YYYY-MM-DD HH24:MI:SS'), LT_POTIME ,\n"+
" (row_number() over(ORDER BY YG_ID)) rb\n"+
" from ltb \n "+
" ) tt\n" +
" where tt.rb between "+ start+" and "+end; // 关键就在这里,前台的两个参数是用到SQL分页语句中,从而实现了分页查询与前台的分页显示的结合
System.out.print("==============默认SQL ===============>>>>\n"+sql);
Object obj=request.getParameter("fuzzy");
if(obj!=null)
{
fuzzy=obj.toString();
//sql= "select YG_ID, to_char(Lt_bgtime,'YYYY-MM-DD HH24:MI:SS'),to_char(Lt_EDTIME,'YYYY-MM-DD HH24:MI:SS'), LT_POTIME from Ltb where YG_ID like '"+fuzzy+"%'";
sql = "select * from\n"+
" (\n"+
" select YG_ID, to_char(lt_bgtime,'YYYY-MM-DD HH24:MI:SS'),to_char(lt_EDTIME,'YYYY-MM-DD HH24:MI:SS'), LT_POTIME ,\n"+
" (row_number() over(ORDER BY YG_ID)) rb\n"+
" from ltb where YG_ID like '"+fuzzy+"%'\n "+
" ) tt\n" +
" where tt.rb between "+ start+" and "+end; // 关键就在这里,前台的两个参数是用到SQL分页语句中,从而实现了分页查询与前台的分页显示的结合
System.out.print("===============分页SQL ===============>>>>\n"+sql);
}
NativeSQLHelperRemote bll = (NativeSQLHelperRemote) Ejb3Factory
.getInstance().getFacadeRemote("NativeSQLHelper");
List list=bll.queryByNativeSQL(sql); //执行一下SQL语句,这个类是项目的EJB中封装好的,下同
Iterator it=list.iterator();
List blocklist = new ArrayList();
while(it.hasNext())
{
EmpAbsentInfo ewi =new EmpAbsentInfo();
Object[] o =(Object[]) it.next();
if(!o[0].toString().equals(""))
{
ewi.setYgId(o[0].toString());
}
if(o[1]!=null)
{
int index = new String(o[1].toString()).indexOf(" ");
ewi.setWorkDate(o[1].toString().substring(0,index));
ewi.setWtBgtime(o[1].toString().substring(index));
}
if(o[2]!=null)
{
int index = new String(o[2].toString()).indexOf(" ");
ewi.setWorkEdDate(o[2].toString().substring(0,index));
ewi.setWtEdtime(o[2].toString().substring(index));
}
if(o[3]!=null)
{
ewi.setWtPotime(o[3].toString());
}
blocklist.add(ewi);
}
String str = "{total :"+GetInfoCount(fuzzy)+", root:" + JSONUtil.serialize(blocklist) + "}";
//total 给出了记录的总数量
System.out.print(str);
write(str);
}
//获取信息记录总数
public int GetInfoCount(String fuzzy) throws Exception{
String sql=
"select count(*) from ltb " +
"where YG_ID like '%"+fuzzy+"%'";
NativeSQLHelperRemote bll = (NativeSQLHelperRemote) Ejb3Factory
.getInstance().getFacadeRemote("NativeSQLHelper");
Object count= bll.getSingal(sql);
Integer i=Integer.parseInt(count.toString());
return i;
}
//----------------------------------------------------------------------------------------------------------
总结:前台没有什么特别的地方,后台主要是要用到分页查询语句 :row_number() over(),最后就是得到该查询的记录的总数量,返回给前台。EXT中grid分页实现的完整例子---从前台到后台
var page_size = 10; //定义页面记录最大显示数量
var MyRecord = Ext.data.Record.create([
{name : 'ygId'},
{ name : 'workDate'},
{name : 'workEdDate'},
{name : 'wtBgtime'},
{name : 'wtEdtime' },
{name : 'wtPotime'},
]);
var store = new Ext.data.Store({
proxy : new Ext.data.HttpProxy({
url : 'empAbsentInfoManage.action?method=getlist' //从后台获取数据
}),
reader : new Ext.data.JsonReader({
root : "root",
totalProperty : "total"
}, MyRecord)
});
store.load({
params : {//这两个参数是分页的关键,当你点击 下一页 时,这里的值会传到后台,也就是会重新运行:store.load
start : 0,
limit : page_size
}
});
var sm = new Ext.grid.CheckboxSelectionModel();
var fuuzy = new Ext.form.TextField({
id : "fuzzy",
name : "fuzzy"
});
var grid = new Ext.grid.GridPanel({
region : "center",
// region:"east",
store : store,
columns : [// 设计页面显示的表格信息,字段与Ext.data.Record.create匹配
sm,
{
id : 'id',
header : "人员ID",
width : 75,
sortable : true,
dataIndex : 'ygId'
}, {
header : "开始日期",
width : 75,
sortable : true,
dataIndex : 'workDate'
}, {
header : "开始时间",
width : 75,
sortable : true,
dataIndex : 'wtBgtime'
},{
header : "结束日期",
width : 75,
sortable : true,
dataIndex : 'workEdDate'
}, {
header : "结束时间",
width : 75,
sortable : true,
dataIndex : 'wtEdtime'
}, {
header : "总时间",
width : 75,
sortable : true,
dataIndex : 'wtPotime'
}],
sm:sm,
stripeRows : true,
autoSizeColumns : true,
viewConfig : {
forceFit : true
},
autoExpandColumn : 'id',
title : '员工请假信息管理',
tbar : [fuuzy, {
text : "查询",
handler : queryRecord
}, {
text : "增加",
iconCls : 'add',
handler : addRecord
}, {
text : "修改",
iconCls : 'edit',
handler : updateRecord
}, {
text : "删除",
iconCls : 'delete',
handler : deleteRecord
}],
// 分页
bbar : new Ext.PagingToolbar({
pageSize : page_size,
store : store,
displayInfo : true
})
});
//--------------------------------------------------------------------
//====== 再来看看后台实现 =====================
//获得员工基本信息列表(根据工号模糊查询)
public void GetEmpWorkList() throws Exception {
int start = Integer.parseInt(request.getParameter("start"));
int limit = Integer.parseInt(request.getParameter("limit"));
int end = start + limit;
String fuzzy="";
String sql=
"select * from\n"+
" (\n"+
" select YG_ID, to_char(lt_bgtime,'YYYY-MM-DD HH24:MI:SS'),to_char(lt_EDTIME,'YYYY-MM-DD HH24:MI:SS'), LT_POTIME ,\n"+
" (row_number() over(ORDER BY YG_ID)) rb\n"+
" from ltb \n "+
" ) tt\n" +
" where tt.rb between "+ start+" and "+end; // 关键就在这里,前台的两个参数是用到SQL分页语句中,从而实现了分页查询与前台的分页显示的结合
System.out.print("==============默认SQL ===============>>>>\n"+sql);
Object obj=request.getParameter("fuzzy");
if(obj!=null)
{
fuzzy=obj.toString();
//sql= "select YG_ID, to_char(Lt_bgtime,'YYYY-MM-DD HH24:MI:SS'),to_char(Lt_EDTIME,'YYYY-MM-DD HH24:MI:SS'), LT_POTIME from Ltb where YG_ID like '"+fuzzy+"%'";
sql = "select * from\n"+
" (\n"+
" select YG_ID, to_char(lt_bgtime,'YYYY-MM-DD HH24:MI:SS'),to_char(lt_EDTIME,'YYYY-MM-DD HH24:MI:SS'), LT_POTIME ,\n"+
" (row_number() over(ORDER BY YG_ID)) rb\n"+
" from ltb where YG_ID like '"+fuzzy+"%'\n "+
" ) tt\n" +
" where tt.rb between "+ start+" and "+end; // 关键就在这里,前台的两个参数是用到SQL分页语句中,从而实现了分页查询与前台的分页显示的结合
System.out.print("===============分页SQL ===============>>>>\n"+sql);
}
NativeSQLHelperRemote bll = (NativeSQLHelperRemote) Ejb3Factory
.getInstance().getFacadeRemote("NativeSQLHelper");
List list=bll.queryByNativeSQL(sql); //执行一下SQL语句,这个类是项目的EJB中封装好的,下同
Iterator it=list.iterator();
List blocklist = new ArrayList();
while(it.hasNext())
{
EmpAbsentInfo ewi =new EmpAbsentInfo();
Object[] o =(Object[]) it.next();
if(!o[0].toString().equals(""))
{
ewi.setYgId(o[0].toString());
}
if(o[1]!=null)
{
int index = new String(o[1].toString()).indexOf(" ");
ewi.setWorkDate(o[1].toString().substring(0,index));
ewi.setWtBgtime(o[1].toString().substring(index));
}
if(o[2]!=null)
{
int index = new String(o[2].toString()).indexOf(" ");
ewi.setWorkEdDate(o[2].toString().substring(0,index));
ewi.setWtEdtime(o[2].toString().substring(index));
}
if(o[3]!=null)
{
ewi.setWtPotime(o[3].toString());
}
blocklist.add(ewi);
}
String str = "{total :"+GetInfoCount(fuzzy)+", root:" + JSONUtil.serialize(blocklist) + "}";
//total 给出了记录的总数量
System.out.print(str);
write(str);
}
//获取信息记录总数
public int GetInfoCount(String fuzzy) throws Exception{
String sql=
"select count(*) from ltb " +
"where YG_ID like '%"+fuzzy+"%'";
NativeSQLHelperRemote bll = (NativeSQLHelperRemote) Ejb3Factory
.getInstance().getFacadeRemote("NativeSQLHelper");
Object count= bll.getSingal(sql);
Integer i=Integer.parseInt(count.toString());
return i;
}
//----------------------------------------------------------------------------------------------------------
总结:前台没有什么特别的地方,后台主要是要用到分页查询语句 :row_number() over(),最后就是得到该查询的记录的总数量,返回给前台。EXT中grid分页实现的完整例子---从前台到后台