ibatis处理sybase分页的问题（还没有解决）

 size=medium]  这两天一直在想怎么在现有系统上比较好的解决这个问题。能够跟现在的系统架构比较好的结合。原来的分页方法用的是ibatis自带的游标分页方法。
我们的调用的方法：

Java代码

1. public List selectById(AmtbAnniversary model,int startRow,int pageSize) {   
2.         return (List) getSqlMapClientTemplate().queryForList("AMTB_ANNIVERSARY.selectById",model,startRow,pageSize);   
3.     }  

public List selectById(AmtbAnniversary model,int startRow,int pageSize) {
        return (List) getSqlMapClientTemplate().queryForList("AMTB_ANNIVERSARY.selectById",model,startRow,pageSize);
    }

这有速度跟内存的问题，当前的测试环境也就1w多条数据，还不是特别明显，但已经感觉出来了。所以准备好好解决这个问题。
根据以前的分页方法，首先想到的是针对不同的数据库在sql语句上进行分页,当前的数据库有h2和sybase。h2支持limit进行分页查询，如：

Java代码

1. select * from cmtb_customer_identityinfo limit 0,10;  

select * from cmtb_customer_identityinfo limit 0,10;

，恩，很好解决。sqlmap中的配置如下：

Java代码

1. <select cacheModel="cache" id="selectByPage" parameterClass="AmtbAnniversary" resultClass="AmtbAnniversary">   
2.   select * from AMTB_ANNIVERSARY limit #startRec#,#lastRec#   
3. </select>  

  <select cacheModel="cache" id="selectByPage" parameterClass="AmtbAnniversary" resultClass="AmtbAnniversary">
    select * from AMTB_ANNIVERSARY limit #startRec#,#lastRec#
  </select>

我的的sqlmap参数一般都是用parameterClass,这是考虑到批量生产的问题，基本上用abator自动生成以后就不需要另外修改了，也不需要额外写parameterMap了。基于这样的需求，我只需要在model生成的时候extends一个baseModel就可以，其中baseModel包括startRec,lastRec等。这样的方法对于一般的数据库都应该是可以很好的解决了。
但是sybase不支持limit，也没有rownum.怎么解决呢，再google了下，发现12.5.3版本以上的已经支持top查询了。兴奋了一把，由于我们现在用的sybase用的是旧版本，不知道具体版本，但反正不能支持top，跟老大咨询了下，内网的sybase的版本比较搞，遂一通设置后，切换到内网测试。先是写了个简单的语句，果然支持，愈来愈兴奋了。来个标准的分页sql

Java代码

1. SELECT TOP 10 *   
2. FROM TestTable   
3. WHERE (ID >   
4.           (SELECT MAX(id)   
5.          FROM (SELECT TOP 20 id   
6.                  FROM TestTable   
7.                  ORDER BY id) AS T))   
8. ORDER BY ID  

SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

返回结果0，郁闷，检查了半天，没错啊，怎么会是0。再google，原来sybase对top的支持还不够好，不支持子查询的top。语句也不报错，真够狠的。这条路看来行不通了。
发现对于sybase的分页一般都是用存储过程，我也搞一搞，在网上看到了这条比较通用的sybase分页存储过程：

Java代码

1. drop procedure getPageWiseData go   
2. create procedure getPageWiseData   
3. (   
4. @sqlStr varchar(8000),   
5. @start int,   
6. @limit int  
7. )   
8. as   
9. DECLARE @dt varchar(10)    --生成临时表的随机数   
10. BEGIN   
11.   
12. --# variable to hold the first row number of the page.   
13. SELECT @dt= substring(convert(varchar, rand()), 3, 10)    --一个字符型的随机数   
14.   
15. SELECT @sqlStr = stuff(@sqlStr, 1, 7, 'select rownum=identity(12), ')   
16. SELECT @sqlStr = stuff(@sqlStr, charindex(' FROM ', upper(@sqlStr)), 6 ,' into tempdb..Lining' + @dt + ' from ')   
17. execute (@sqlStr)   
18.   
19. --# select the data with the calculated range for first and last row on page.   
20. select @sqlStr = 'select * from tempdb..Lining' + @dt + ' where rownum >= '+convert(varchar, @start)+' and rownum <= '+convert(varchar, (@start+@limit))    
21. execute (@sqlStr)   
22.   
23. --删除临时表   
24. SELECT @sqlStr = 'DROP TABLE tempdb..Lining'+@dt  
25. EXECUTE (@sqlStr)    
26. END  

drop procedure getPageWiseData go
create procedure getPageWiseData
(
@sqlStr varchar(8000),
@start int,
@limit int
)
as
DECLARE @dt varchar(10)    --生成临时表的随机数
BEGIN

--# variable to hold the first row number of the page.
SELECT @dt= substring(convert(varchar, rand()), 3, 10)    --一个字符型的随机数

SELECT @sqlStr = stuff(@sqlStr, 1, 7, 'select rownum=identity(12), ')
SELECT @sqlStr = stuff(@sqlStr, charindex(' FROM ', upper(@sqlStr)), 6 ,' into tempdb..Lining' + @dt + ' from ')
execute (@sqlStr)

--# select the data with the calculated range for first and last row on page.
select @sqlStr = 'select * from tempdb..Lining' + @dt + ' where rownum >= '+convert(varchar, @start)+' and rownum <= '+convert(varchar, (@start+@limit)) 
execute (@sqlStr)

--删除临时表
SELECT @sqlStr = 'DROP TABLE tempdb..Lining'+@dt
EXECUTE (@sqlStr) 
END

我稍微改了下，比原来的语句快了点。
这虽然不错，但是怎么跟我现在的系统结合呢，看看我调用的语句

Java代码

1.     <parameterMap class="map" id="params">   
2.         <parameter property="sqlStr"  javaType="String" jdbcType="VARCHAR" mode="IN"/>   
3. <parameter property="start"  javaType="java.lang.Integer" jdbcType="int" mode="IN"/>   
4. <parameter property="limit"  javaType="java.lang.Integer" jdbcType="int" mode="IN"/>   
5.     </parameterMap>     
6. lt;procedure id="FIRST" parameterMap="params" resultClass="CmtbCustomerIdentityinfo">   
7.    {call getPageWiseData(?,?,?)}   
8. lt;/procedure>  

  	<parameterMap class="map" id="params">
  		<parameter property="sqlStr"  javaType="String" jdbcType="VARCHAR" mode="IN"/>
		<parameter property="start"  javaType="java.lang.Integer" jdbcType="int" mode="IN"/>
		<parameter property="limit"  javaType="java.lang.Integer" jdbcType="int" mode="IN"/>
  	</parameterMap>  
	<procedure id="FIRST" parameterMap="params" resultClass="CmtbCustomerIdentityinfo">
	    {call getPageWiseData(?,?,?)}
	</procedure>

 

Java代码

1. public List getByObj(CmtbCustomerIdentityinfo example,int startRow,int pageSize) {   
2.         Map params = new HashMap();   
3.         String s = "select convert(varchar,a.CUSTOMER_IDENTIFYID) CUSTOMER_IDENTIFYID,a.KHH,a.NAME,a.IDENTIFY_NO,a.CUST_STATE,b.USER_ID from CMTB_CUSTOMER_IDENTITYINFO a left join CMTB_CUSTOMER_IDENBELONG b on a.CUSTOMER_IDENTIFYID=b.CUSTOMER_IDENTIFYID where a.STATUS='1' and a.CUSTOMER_TYPE='0'";   
4.         params.put("sqlStr", s);   
5.         params.put("start", startRow);   
6.         params.put("limit", pageSize);   
7.         List list = (List) getSqlMapClientTemplate().queryForList("CMTB_CUSTOMER_IDENTITYINFO.FIRST", params);   
8.         return list;   
9.     }  

public List getByObj(CmtbCustomerIdentityinfo example,int startRow,int pageSize) {
    	Map params = new HashMap();
    	String s = "select convert(varchar,a.CUSTOMER_IDENTIFYID) CUSTOMER_IDENTIFYID,a.KHH,a.NAME,a.IDENTIFY_NO,a.CUST_STATE,b.USER_ID from CMTB_CUSTOMER_IDENTITYINFO a left join CMTB_CUSTOMER_IDENBELONG b on a.CUSTOMER_IDENTIFYID=b.CUSTOMER_IDENTIFYID where a.STATUS='1' and a.CUSTOMER_TYPE='0'";
    	params.put("sqlStr", s);
    	params.put("start", startRow);
    	params.put("limit", pageSize);
        List list = (List) getSqlMapClientTemplate().queryForList("CMTB_CUSTOMER_IDENTITYINFO.FIRST", params);
        return list;
    }

看看上面的调用的代码，传入存储过程必须是要一个确定的，已经可以执行的sql，那么如果这个sql是包含参数的呢？我是不是要在queryForList之前，先构造这条sql语句呢？但是这部分是ibatis内部执行的，除非你放弃ibatis的执行过程。我目前对ibatis的了解还不深，不知道谁有比较好的方法。我理想的状况是，因为这个sql的参数也是包含在model中的，那么调用的程序应该是像这样：

Java代码

1. public List getCustomerIdentityinfoByObj(CmtbCustomerIdentityinfo example,int startRow,int pageSize) {   
2.         example.setStart(startRow);   
3.         example.setLimit(pageSize);   
4.         List list = (List) getSqlMapClientTemplate().queryForList("CMTB_CUSTOMER_IDENTITYINFO.FIRST", example);   
5.         return list;   
6.     }  

public List getCustomerIdentityinfoByObj(CmtbCustomerIdentityinfo example,int startRow,int pageSize) {
        example.setStart(startRow);
        example.setLimit(pageSize);
        List list = (List) getSqlMapClientTemplate().queryForList("CMTB_CUSTOMER_IDENTITYINFO.FIRST", example);
        return list;
    }

这就看sqlmap是否能否很好的配置了，目前我是还不知道。
补充一点：看下那条sql语句中的convert(varchar,a.CUSTOMER_IDENTIFYID)，之所以这样写是CUSTOMER_IDENTIFYID在原来的表已经是个主键了，所以在放到临时表的时候需要将起转换为一般的字段。
参见http://aimer311.javaeye.com/blog/460127
[/size]