sqlServer分页存储过程的调用

快来打我* 2022-06-17 14:25 277阅读 0赞

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

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

![这里写图片描述][SouthEast]

2：action的方法将信息都传给service  
![这里写图片描述][SouthEast 1]  
3：在service中处理逻辑  
![这里写图片描述][SouthEast 2]

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

4：sql接口的写法：  
![这里写图片描述][SouthEast 3]

5：mybatis的sql写法：注意id为callpageparams的map必须加上  
![这里写图片描述][SouthEast 4]

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

![这里写图片描述][SouthEast 5]

这样就可以实现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>

[SouthEast]: /images/20220617/041f855875414a5ea35d2c536f55d6f3.png
[SouthEast 1]: /images/20220617/7182c7750c68423a81336117aa0bc357.png
[SouthEast 2]: /images/20220617/d84fc1eba270487b82b2f1624f0df0b3.png
[SouthEast 3]: /images/20220617/97caed57f0824ba0bd27e0ab0bf3a4e8.png
[SouthEast 4]: /images/20220617/a4fbe7a71f2f4335bb90edc4656d33ab.png
[SouthEast 5]: /images/20220617/b6b7c68334684f998260059aab3fea05.png