sqlServer分页存储过程的调用

现象：
sqlServer的分页一直相对比较复杂。这里使用存储过程实现分页逻辑

解决办法
1：action获取查询的条件，初始化每页显示的大小，page代表当前查看第几页，默认设置为第一页。rows表示每页显示的大小。sort代表查询按什么字段排序 如果要按多个字段就写：sort=“tcode,name” 中间用,分割
order代表按什么方式排序，和sort一样多个使用,分割！word代表查询的条件可以设置为多个字段条件查询！

2：action的方法将信息都传给service

3：在service中处理逻辑

这里pageBean是特殊需要 平时安装自己的需要处理查询出来的list就可以
字符串else_if 是拼接查询条件
字符串order_by是拼接排序条件
table是表名
fields是查询的字段 “”表示查询所有

4：sql接口的写法：

5：mybatis的sql写法：注意id为callpageparams的map必须加上

6：处理排序的工具方法：

这样就可以实现sqlServer的分页查询了

注：分页存储过程的建立： 分页存储

USE [yh_test]
GO
/****** Object:  StoredProcedure [dbo].[P_Public_select]    Script Date: 03/29/2017 16:38:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_Public_select]
      @tblName SYSNAME
     ,           --要分页显示的表名
      @fldName NVARCHAR(1000) = ''
     ,  --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段     
      @pageSize INT = 10
     ,            --每页的大小(记录数)     
      @page INT = 1
     ,  --查询条件
      @pageCount INT OUTPUT
     ,        --总页数
      @Counts INT OUTPUT
     ,             --要显示的页码     
      @fldSort NVARCHAR(1000) = ''
     , --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
     -- 排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
                                          --用于指定排序顺序
      @strCondition NVARCHAR(1000) = ''
     ,@ID SYSNAME                --用于定位记录的主键(惟一键)字段,只能是单个字段

AS 
      DECLARE @sql NVARCHAR(max)
      SET NOCOUNT ON
--检查对象是否有效
      IF OBJECT_ID(@tblName) IS NULL 
         BEGIN
               RAISERROR(N'对象"%s"不存在',1,16,@tblName)
               RETURN
         END
      IF OBJECTPROPERTY(OBJECT_ID(@tblName) , N'IsTable') = 0
         AND OBJECTPROPERTY(OBJECT_ID(@tblName) , N'IsView') = 0
         AND OBJECTPROPERTY(OBJECT_ID(@tblName) , N'IsTableFunction') = 0 
         BEGIN
               RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tblName)
               RETURN
         END

--分页字段检查
      IF ISNULL(@ID , N'') = '' 
         BEGIN
               RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
               RETURN
         END

--其他参数检查及规范
      IF ISNULL(@page , 0) < 1 
         SET @page = 1


      IF ISNULL(@PageSize , 0) < 1 
         SET @PageSize = 15

      IF ISNULL(@fldName , N'') = N'' 
         SET @fldName = N'*'

      IF ISNULL(@fldSort , N'') = N'' 
         SET @fldSort = N' ORDER BY '+ @ID       
      ELSE 
         SET @fldSort = N' ORDER BY ' + LTRIM(@fldSort)

      IF ISNULL(@strCondition , N'') = N'' 
         SET @strCondition = N''
      ELSE 
         SET @strCondition = N' WHERE (1=1 ' + @strCondition + N')'

--如果@pageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@pageCount赋值)
      IF @pageCount IS NULL 
         BEGIN
               SET @sql = N'SELECT @Counts=COUNT(*)' + N' FROM ' + @tblName + N' ' + @strCondition
               EXEC sp_executesql 
                @sql
               ,N'@Counts int OUTPUT'
               ,@Counts OUTPUT
               SET @pageCount = ( @Counts + @PageSize - 1 ) / @PageSize
         END



if @page = 1 --第一页提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@fldName+ '  from ' + @tblName   + @strCondition + @fldSort
end 
else
  begin
              /**//*Execute dynamic query*/    
               DECLARE @START_ID varchar(50)
            DECLARE @END_ID varchar(50)
            SET @START_ID = convert(varchar(50),(@page - 1) * @PageSize + 1)
            SET @END_ID = convert(varchar(50),@page * @PageSize)
                set @sql =  ' SELECT '+@fldName+ '
               FROM (SELECT ROW_NUMBER() OVER('+@fldSort+') AS rownum, 
                 '+@fldName+ '
                  FROM '+@tblName+' ' +@strCondition+') AS D
               WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +@fldSort
END
--print @sql

EXEC (@sql)


----------------------------------------------以上为sqlservice  分页存储过程  -------------------------
参数 如下 
@tblName    需要进行分页查询的表名
@fldName    以逗号分隔需要显示的字段列表   如果没有传入（传入 ""） 则显示所有字段（相当于select * from）
@pageSize   每页的大小
@page  
@pageCount  输出 总页数
@Counts     要显示的页码
@fldSort    以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@ID         表的主键


----------------------------------------service调用的方法--------------------------------------------
@Override//查询app用户登录详情
    public PageBean<AppUserLoginInfo> findUserInfo(int page,int rows,String word,String sort,String order) {
        //拼接模糊关键字查询条件语句
        String else_if="";
        if(word!=null && !word.trim().equals("")){
            else_if+=" and (tcode like '%"+word+"%' or device like '%"+word+"%'or lastlogin like '%"+word+"%' or ip like '%"+word+"%')";
        }
        //拼接排序条件语句
        String order_by="";
        order_by = MyUtils.getOrderBy(sort, order);
        Map<String, Object> parameters=new HashMap<String, Object>();
        int pages=0;
        int counts=0;
        parameters.put("table", "token");
        parameters.put("fields", ""); //字段 为''表示所有
        parameters.put("pageSize", rows);
        parameters.put("pageIndex", page);
        parameters.put("pages", pages);
        parameters.put("total", counts);
        parameters.put("order_by", order_by); //排序列
        parameters.put("else_if", else_if); //条件
        parameters.put("primaryKey", "tcode");  //主键
        List<AppUserLoginInfo> list = appUserDao.findUserInfo(parameters);
        for(int i=0;i<list.size();i++){
            System.out.println(list.get(i).toString());
        }
        PageBean<AppUserLoginInfo> pageBean=new PageBean<AppUserLoginInfo>();
        pageBean.setRows(list);
        pageBean.setPages((Integer)parameters.get("pages"));
        pageBean.setTotal((Integer)parameters.get("total"));
        return pageBean;
    }


    -----------------------------------------------------action的方法
    private int page=1;
    private int rows=20;
    public int getPage() {
        return page;
    }
    public void setPage(int page) {
        this.page = page;
    }
    public int getRows() {
        return rows;
    }
    public void setRows(int rows) {
        this.rows = rows;
    }
    public String newsListPage(){
        return "newsList";
    }

    private String sort="tcode";
    private String order="desc";
    private String word;
    public String getSort() {
        return sort;
    }
    public void setSort(String sort) {
        this.sort = sort;
    }
    public String getOrder() {
        return order;
    }
    public void setOrder(String order) {
        this.order = order;
    }
    public String getWord() {
        return word;
    }
    public void setWord(String word) {
        this.word = word;
    }
    //执行app用户登录信息查询
    @Test
    public void AppUserLoginInfoList(){
        ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
        appUserService =  (AppUserService) ac.getBean("AppUserService");
        System.out.println("PAGE:"+page+"ROWS:"+rows+"WORD:"+word+"SORT:"+sort+"ORDER"+order);
        //传入当前页  每页多少行  按sort字段 order排序  关键字是word查询  
        PageBean<AppUserLoginInfo> result=appUserService.findUserInfo(page,rows,word,sort,order);
        logger.debug("查询app用户登录详细信息"+result.getRows().toString());
    }

    ----------------------------------------dao接口方法-------------------------------------------------
    List<AppUserLoginInfo> findUserInfo(Map<String, Object> parameters);


    ----------------------------mappingsql文件--------------------------------------------------------------
    <mapper namespace="com.oig.dao.AppUserDao">

    <parameterMap type="java.util.Map" id="callPageParams">
        <parameter property="table" jdbcType="NVARCHAR" mode="IN"/>
        <parameter property="fields" jdbcType="NVARCHAR" mode="IN"/>
        <parameter property="pageSize" jdbcType="INTEGER" mode="IN"/>
        <parameter property="pageIndex" jdbcType="INTEGER" mode="IN"/>
        <parameter property="pages" jdbcType="INTEGER" mode="OUT"/>
        <parameter property="total" jdbcType="INTEGER" mode="OUT"/>
        <parameter property="order_by" jdbcType="NVARCHAR" mode="IN"/>
        <parameter property="else_if" jdbcType="NVARCHAR" mode="IN"/>
        <parameter property="primaryKey" jdbcType="NVARCHAR" mode="IN"/>
    </parameterMap>
    <select id="findUserInfo" parameterMap="callPageParams" resultType="com.oig.bean.AppUserLoginInfo" statementType="CALLABLE">
          {call dbo.P_Public_select(?,?,?,?,?,?,?,?,?)} 
    </select>
</mapper>