extHtmlname="report1"reportFileName="<%=raq%>"
funcBarLocation=""
params="<%=param.toString()%>"
needPageMark="yes"
pageCount="20"
totalCountExp="<%=countSql%>"
paperHeight="600"
/>
附录:
3.8. 分页计算标签
本功能采用报表组的原理来实现,因此需要支持报表组的授权
3.8.1. 概念定义
使用分页计算标签可以在报表比较大的情况下实现以页为单位对数据进行读取和展现及导出等操作。
3.8.2. 功能背景
报表大到一定程度,必然会内存溢出,此时比较好的解决办法是边算边输出。
分页计算标签是利用报表组来实现的逐页计算逐页输出的tag标签。
可以大大降低内存占有量,提高运行效率,避免内存溢出等问题。
3.8.3. 使用方法
在这个标签中,主要增加了以下属性:
totalCountExp——总记录数(必填属性)
分页就是基于这个总记录数来的。
它的值是一个润乾的非数据集函数,并且返回的值应该是一个整型数据。
如用query执行一个count的sql。
如:
totalCountExp="query('SELECTcount(*)FROMtable1')"
pageCount——每页记录数(非必填)分页后每一页包含的记录数,其值需为整数。
默认值为20。
cachePageNum——缓存页数(非必填)
根据pageCount和cachePageNum,每次取pageCount*cachePageNum条记录,其值需为整数,默认值为100
设置该属性,可保证缓存页数内的翻页效率。
(reportconfig.xml文件里的alwaysReloadDefine设置为no,exthtml标签里useCache设置为yes,该属性才生效)startRowParamName/endRowParamName——起始行参数名/结束行参数名(非必填)
对应报表数据集记录行中设置的起始行和结束行的参数名。
默认值为startRow和endRow。
其他属性说明,与html标签基本一致:
图 3.2.
∙应用举例一:
下面以订单明细列表为例,按照常规做出一张订单明细的清单式列表。
然后为其添加两个参数:
起始行参数名startRow和结束行参数名endRow。
注意参数类型要求是整型。
图 3.4.
并且在数据集设置的参数标签页设置好起始行和结束行的对应参数@startRow和@endRow。
图 3.5.
下面是最简jsp发布文件,只定义了三个必须属性,其余均采用默认值:
图 3.6.
运行结果如下:
图 3.7.
下面是定义了各种属性之后的jsp:
图 3.8.
∙
运行效果如下:
图 3.9.
这种做法的缺点:
当数据量足够大的时候,某些jdbc包的resultset本身会内存溢出;而且从理论上看,当调用api接口将resultset的指针定位到某一行的时候,其底层其实是一行一行跳转的,虽然速度非常快,但是数据量超过几百万甚至几千万的时候,还是会消耗一些时间。
因此当记录数大到一定程度,翻到最后一页的速度会比开头几页慢。
这种做法的优点:
和数据库类型无关,任何一种数据库都可以采取这种方式,用户不用研究不同数据库的差别。
emerito_info.raq的分页count语句
----------------------------------------------------------------------------
select
count(*)
fromRETIRER_SALARYrs,
RETIRER_SALARY_DETAILrsd
WHERErsd.retierer_salary_id=rs.retierer_salary_id
and(rs.unit_id=?
or?
isnull)and(rs.retierer_salary_idin(${rsid})or?
=2)
andrs.giveyearmonth=?
ORDERBYrs.fullname
---------------------------------------------------------------------------
emerito_salary.raq的分页count语句
SELECT
count(*)
fromRETIRERSr,
RETIRER_SALARYrs,
RETIRER_SALARY_DETAILrsd
WHEREr.RETIRER_ID=rs.RETIRER_IDandrsd.retierer_salary_id=rs.retierer_salary_id
and(r.unit_id=?
or?
isnull)and(rs.retierer_salary_idin(${rsid})or?
=2)
andrs.giveyearmonth=?
ORDERBYrs.fullname
----------------------------------------------------------------------------
job_base.raq的分页count语句
SELECT
count(*)
fromworkersalaryWHERE(unit_id=?
or?
isnull)and(worker_idin(${workerid})or?
=2)ORDERBYdepartmentname,fullname
unitid=?
unitid=?
workerid=${workerid}为,,,,
falg=?
----------------------------------------------------------------------------
job_salary.raq的分页count语句
SELECTcount(*)fromworkersalarya,
WORKER_SALARY_DETAILb
WHEREa.SALARY_ID=b.SALARY_ID
and(a.unit_id=?
or?
isnull)and(a.worker_idin(${workerid})or?
=2)
anda.giveyearmonth=?
----------------------------------------------------------------------------
jobn_bfgz.raq的分页count语句
SELECT
count(*)
fromWORKER_SALARY_DETAIL_ADDwsda,
WORKERSALARYw
WHEREwsda.salary_id=w.salary_id
andw.unit_id=?
----------------------------------------------------------------------------
jobn_jsgz.raq的分页count语句
SELECT
count(*)
from(
select
r.worker_idaslast_worker_id,
r.Totalsalaryaslast_Totalsalary,
r.salary_idaslast_salary_id
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth='2011-11'
andr.unit_id=613
andwsd.giveyearmonth='2011-11')l1
leftjoin(select
r.worker_idascurrent_worker_id,
r.Totalsalaryascurrent_Totalsalary,
r.salary_idascurrent_salary_id
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth='2011-12'
andr.unit_id=613
andwsd.giveyearmonth=
'2011-12')l2onl1.last_worker_id=l2.current_worker_id
WHEREl1.last_totalsalary>l2.current_totalsalary
orl2.current_totalsalaryisnull
----------------------------------------------------------------------------
jobn_kjgz.raq的分页count语句
SELECT
count(*)
fromWORKERSALARYw,WORKER_SALARY_DETAIL_CUTwsdc
WHEREw.salary_id=wsdc.salary_id
andw.unit_id=?
andw.giveyearmonth=?
----------------------------------------------------------------------------
jobn_other.raq的分页count语句
SELECT*
from(select*fromWORKERSALARYwwherew.giveyearmonth=?
andw.unit_id=?
)l1join
(select*fromWORKERSALARYwwherew.giveyearmonth=?
andw.unit_id=?
)l2onl1.worker_id=l2.worker_id
WHEREl1.fullname<>l2.fullnameorl1.idCard<>l2.idCardorl1.dept_id<>l2.dept_id
----------------------------------------------------------------------------
jobn_zjgz.raq的分页count语句
select
count(*)
from(
selectr.worker_idaslast_worker_id,
r.Totalsalaryaslast_Totalsalary,
r.salary_idaslast_salary_id,
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth=?
andr.unit_id=?
andwsd.giveyearmonth=?
)l1
rightjoin(
selectr.worker_idascurrent_worker_id,
r.Totalsalaryascurrent_Totalsalary,
r.salary_idascurrent_salary_id,
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth=?
andr.unit_id=?
andwsd.giveyearmonth=?
)l2onl1.last_worker_id=l2.current_worker_id
wherel1.last_totalsalaryorl1.last_totalsalaryisnull
----------------------------------------------------------------------------
jobup_bfgz.raq的分页count语句
SELECT
count(*)
fromWORKER_SALARY_DETAIL_ADDwsda,
WORKERSALARYw
WHEREwsda.salary_id=w.salary_id
andw.upunit_id=?
----------------------------------------------------------------------------
jobup_jsgz.raq的分页count语句
SELECT
count(*)
from(select
r.worker_idaslast_worker_id,
r.Totalsalaryaslast_Totalsalary,
r.salary_idaslast_salary_id
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth='2011-11'
andr.upunit_id=118
andwsd.giveyearmonth='2011-11')l1
leftjoin(selectr.worker_idascurrent_worker_id,
r.Totalsalaryascurrent_Totalsalary,
r.salary_idascurrent_salary_id
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth='2011-12'
andr.upunit_id=118
andwsd.giveyearmonth=
'2011-12')l2onl1.last_worker_id=l2.current_worker_id
WHEREl1.last_totalsalary>l2.current_totalsalary
orl2.current_totalsalaryisnull
----------------------------------------------------------------------------
jobup_kjgz.raq的分页count语句
SELECT
count(*)
fromWORKERSALARYw,WORKER_SALARY_DETAIL_CUTwsdc
WHEREw.salary_id=wsdc.salary_id
andw.upunit_id=?
andw.giveyearmonth=?
----------------------------------------------------------------------------
jobup_other.raq的分页count语句
SELECTcount(*)from(select*fromWORKERSALARYwwherew.giveyearmonth=?
andw.upunit_id=?
)l1join
(select*fromWORKERSALARYwwherew.giveyearmonth=?
andw.upunit_id=?
)l2onl1.worker_id=l2.worker_id
WHEREl1.fullname<>l2.fullnameorl1.idCard<>l2.idCardorl1.dept_id<>l2.dept_id
----------------------------------------------------------------------------
jobup_salary.raq的分页count语句
SELECTcount(*)fromworkersalarya,
WORKER_SALARY_DETAILb
WHEREa.SALARY_ID=b.SALARY_ID
and(a.upunit_id=?
or?
isnull)and(a.worker_idin(${workerid})or?
=2)
anda.giveyearmonth=?
----------------------------------------------------------------------------
jobup_zjgz.raq的分页count语句
select
count(*)
from(
selectr.worker_idaslast_worker_id,
r.Totalsalaryaslast_Totalsalary,
r.salary_idaslast_salary_id
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth='2011-11'
andr.upunit_id=?
andwsd.giveyearmonth='2011-11'
)l1
rightjoin(
selectr.worker_idascurrent_worker_id,
r.Totalsalaryascurrent_Totalsalary,
r.salary_idascurrent_salary_id
fromWORKERSALARYr
joinworker_salary_detailwsdonr.salary_id=wsd.salary_id
andr.giveyearmonth='2011'
andr.upunit_id=?
andwsd.giveyearmonth=?
)l2onl1.last_worker_id=l2.current_worker_id
wherel1.last_totalsalaryorl1.last_totalsalaryisnull
----------------------------------------------------------------------------
retiree_info.raq的分页count语句
select
count(*)
fromLEAVER_SALARYls,
LEAVER_SALARY_DETAILlsd
WHEREls.leaver_salary_id=lsd.leaver_salary_id
and(ls.unit_id=?
or?
isnull)and(ls.leaver_idin(${lsd})or?
=2)
andls.giveyearmonth=?
orderbyls.fullname
----------------------------------------------------------------------------
retiree_salary.raq的分页count语句
SELECT
count(*)
fromLEAVER_SALARYls,
LEAVER_SALARY_DETAILlsd
WHEREls.leaver_salary_id=lsd.leaver_salary_id
and(ls.unit_id=?
or?
isnull)and(ls.leaver_idin(${lsd})or?
=2)
andls.giveyearmonth=?