`

查询设计

阅读更多

    需求是这样的,一个业务模块有三个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 &lt;=     
        #maxRow# ) WHERE RNUM &gt;= #minRow# 

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics