sqlServer分页存储过程的调用

来源：互联网发布：mysql的sequence 编辑：程序博客网时间：2024/05/16 19:36

现象：
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 ONGOSET QUOTED_IDENTIFIER ONGOALTER 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         ENDif @page = 1 --第一页提高性能begin   set @sql = 'select top ' + str(@PageSize) +' '+@fldName+ '  from ' + @tblName   + @strCondition + @fldSortend 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 +@fldSortEND--print @sqlEXEC (@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>

0 0