01、针对mssql2005的分页

只针对mssql2005

 

1、以下这二种写法都是一样的，看个人习惯，个人倾向于使用第一种方法。

 with query as 
(
   SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo FROM tbl 
) 
    select * from query where RowNo >=0 and RowNo <= 6

 

 

 

 效果等同于

SELECT * 
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo 
FROM tbl
) AS A
WHERE RowNo >= 0 and RowNo <= 6;  

 

2、sql map文件定义

 <select id="getBeatrecord" parameterType="map" resultType="beatrecordModel">
        <![CDATA[
        with query as (select a.beat_id as beatId,a.ip as ip,a.equipment_id as equipmentId,
        a.do_code as doCode,a.beat as beat,a.valid_time as validTime,a.report_time as reportTime,
        a.modified_time as modifiedTime,a.modified_by as modifiedBy,
        c.equipment_name as equipmentName,c.equipment_code as equipmentCode,
           ROW_NUMBER() OVER (order by a.report_time) as RowNo
         from oitBeatrecord a join oitdispatchorder b on a. do_code=b.do_code  join bd_equipment c on
           b.equipment_id=c.equipment_id
         where a.do_code=#{doCode})
                 select * from query
         where  RowNo>=#{start} and RowNo<= #{limit}
          ]]>
         </select>

 说明：

  参数以map的方式传进来，该例子中传进了三个参数，分页需要关注的是start和limit参数；
   start 参数： 页面中记录开始数；
   limit 参数： 每页显示记录集的最大数

 

3、js前端设置参数

  store.baseParams.start = 0;
  store.baseParams.limit = pageSize;

 

4、action 处理js的请求

if (start == 0) {
            recordCondition.setStartRow(start);
            recordCondition.setPageSize(limit);

        } else {
            recordCondition.setStartRow(start + 1);
            recordCondition.setPageSize(limit + start);

        }

 

5、dao 调用sqlmap文件

 params.put("start", condition.getStartRow());
 params.put("limit", condition.getPageSize());
  orders = this.getSqlSession().selectList("getOitdomodifyRecord", params);