SQL Server分页查询

原生SQL语句，实现SQL Server分页查询

2. DECLARE @pageCount INT
3. DECLARE @pageIndex INT
4. SET @pageCount = 10 -- #{page.pageSize}
5. SET @pageIndex = 2 -- #{page.pageNum}
6. ;WITH ACTE AS (
7.         SELECT
8.                 ROW_NUMBER () OVER (ORDER BY id) AS SN,
9.                 id, table_id, name, workcode, create_time, update_time
10.         FROM
11.                 lott_result_information
12.         ),
13. BCTE AS (SELECT CEILING(MAX(SN) * 1.0 / @pageCount) AS PageTotal FROM ACTE)
14. SELECT *,
15.         (SELECT COUNT (*) FROM ACTE ) AS total_number,
16.         (SELECT PageTotal FROM BCTE ) AS total_page_size
17. FROM
18.         ACTE
19. WHERE
20.         ACTE.SN > (@pageIndex - 1) * @pageCount
21.         AND ACTE.SN <= (@pageIndex * @pageCount)

复制代码

实战
1. 编写Mapper.java的接口

2. List<LottResultInformation> selectLottResultByPage(@Param("name") String name, @Param("page") BasePage page);

复制代码

2. 编写Mapper.xml的SQL语句

2. <?xml version="1.0" encoding="UTF-8"?>
3. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4. <mapper namespace="com.demo.framework.mapper.LottMapper">
5.     <resultMap id="BaseResultLottResultMap" type="com.demo.framework.entity.LottResultInformation"  extends="BasePageMap">
6.         <id column="id" property="id" jdbcType="INTEGER"/>
7.         <result column="table_id" property="tableId" jdbcType="INTEGER"/>
8.         <result column="name" property="name" jdbcType="VARCHAR"/>
9.         <result column="workcode" property="workcode" jdbcType="VARCHAR"/>
10.         <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
11.         <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
12.     </resultMap>
14.     <resultMap id="BasePageMap" type="com.demo.framework.entity.BasePage">
15.         <result column="total_number" property="totalNumber" jdbcType="INTEGER"/>
16.         <result column="total_page_size" property="pageTotal" jdbcType="INTEGER"/>
17.     </resultMap>
19.     <select id="selectLottResultByPage" resultMap="BaseResultLottResultMap">
20.         DECLARE @pageCount INT
21.         DECLARE @pageIndex INT
22.         SET @pageCount = #{page.pageSize}
23.         SET @pageIndex = #{page.pageNum}
24.         ;WITH ACTE AS (
25.             SELECT ROW_NUMBER() OVER (ORDER BY id) AS SN,
26.                    id, table_id, name, workcode, create_time, update_time
27.             FROM lott_result_information
28.             <if test="name != null and name !=''">
29.                 where name LIKE CONCAT('%',#{name},'%')
30.             </if>
31.         ),
32.         BCTE AS (SELECT CEILING(MAX(SN) * 1.0 / @pageCount) AS PageTotal FROM ACTE)
33.         SELECT *,
34.                (SELECT COUNT(*) FROM ACTE)  AS total_number,
35.                (SELECT PageTotal FROM BCTE) AS total_page_size
36.         FROM ACTE
37.         WHERE ACTE.SN > (@pageIndex - 1) * @pageCount
38.           AND ACTE.SN &lt;= (@pageIndex * @pageCount)
39.     </select>
41. </mapper>

复制代码

如许的话每次查询都会冗余两列total_number和total_page_size；如许也方便每次获取总页数和总条数。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金