查询设计

    需求是这样的，一个业务模块有三个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#