需求是这样的,一个业务模块有三个TAB页,分别为简单查询、高级查询、导出。
简单查询:几个常用的、固定的条件的查询;
高级查询:用户可编辑条件的个数,以及条件之间是与、或的关系;
导出:根据用户导出的结果,选择开始、结束记录号进行导出,当然可以导出txt或者excel
结果列有根据列名排序、记录导出的功能,所以要记录查询条件(queryCondition),即把查询条件有记录到request中,排序、导出时就不在构建查询条件,当然这样也有不足,就是依赖于查询条件(queryCondition)这个字符串,而没有在查询结果列表中把查询的条件解析开来,用户看不到他上次输的条件,比较失败.但记录了查询条件,可以做一些重复下载的操作,这个设计还是很好的。
由于查询条件(queryCondition)有时会保存到DB中,为了安全,在往request里set之前加密,
queryCondition = new String(Base64.encodeBase64(
queryCondition.getBytes(), false));
取出时解密:
queryCondition = (String) dynaBean.get("queryCondition");
queryCondition = new String(Base64.decodeBase64(
queryCondition.getBytes()));
加密后的串在URL作为参数传递时有时候会有因为有特殊字符而报错,这时用javascript提供的URI编码就可以了,在服务器端直接就可以得到完整的数据。
encodeURIComponent('${queryCondition}')
下面是根据一个最大值,最小值、查询条件取记录的SQL
<select id="getExportTxtDate" parameterClass="java.util.Map"
resultClass="magazineBean" remapResults="true">
SELECT * FROM ( SELECT list.*, rownum as RNUM FROM (
select * from (
select b.id as id
,a.CorpID as corpID
,a.CORP_PROVINCENO as provinceNo
,a.Corp_chineseName as chineseName
,b.cn as cn
,b.issn as issn
,c.Corp_chineseName as directorCorpName
,a.Corp_sponsorCorp as sponsorCorp
,b.proprieter as proprieter
,b.editor as editor
,f.Corp_linkTel as linkTel
,f.Corp_fax as fax
,b.Post_id as postId
,e.issue_way as issueWay
,b.Size_num as sizeNum
,b.issued_period as issuedPeriod
,f.Corp_address as address
,f.Corp_postCode as postCode
,b.Year_price as yearPrice
,b.Create_date as createMagazineDate
,a.Corp_tenet as tenet
,a.Corp_englishName as englishName
,a.Corp_legalPerson as legalPerson
,a.Corp_chargeMan as chargeMan
,a.Corp_createDate as createDate
,a.Corp_divide as divide
,d.AreaCode_all as areaCodeAll
from Corp_info a
,Corp_Magazine b
,Corp_info c
,Corp_AreaCode d
,Corp_issue_way e
, (select * from Corp_linkPersonInfo where id in (select max(id) from Corp_linkPersonInfo group by corpID)) f
where a.CorpID=b.CorpID
and a.Corp_directorCorpID=c.CorpID(+)
and a.Corp_provinceNO=d.id(+)
and b.issue_way_id=e.id(+)
and a.CorpID=f.CorpID(+)
) where 1=1
<isNotEmpty prepend="AND" property="queryCondition">
( $queryCondition$ )
</isNotEmpty>
ORDER BY $orderColumn$ $orderType$ ) list WHERE ROWNUM <=
#maxRow# ) WHERE RNUM >= #minRow#