ibatis处理sybase分页的问题

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

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进行分页查询，如：

select * from cmtb_customer_identityinfo limit 0,10;

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

  <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

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分页存储过程：

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

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

  	<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>

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中的，那么调用的程序应该是像这样：

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.iteye.com/blog/460127
[/size]

评论

2 楼 aimer311 2009-02-26  

snowyvalley 写道

这个sybase存储过程不支持子查询

这是什么意思 ，能不能具体说下。最近这个问题还不急着弄，所以我就还没解决。

1 楼 snowyvalley 2009-02-26  

这个sybase存储过程不支持子查询