原生SQL语句，实现SQL Server分页查询
DECLARE @pageCount INT
DECLARE @pageIndex INT
SET @pageCount = 10 -- #{page.pageSize}
SET @pageIndex = 2 -- #{page.pageNum}
;WITH ACTE AS (
SELECT
ROW_NUMBER () OVER (ORDER BY id) AS SN,
id, table_id, name, workcode, create_time, update_time
FROM
lott_result_information
),
BCTE AS (SELECT CEILING(MAX(SN) * 1.0 / @pageCount) AS PageTotal FROM ACTE)
SELECT *,
(SELECT COUNT (*) FROM ACTE ) AS total_number,
(SELECT PageTotal FROM BCTE ) AS total_page_size
FROM
ACTE
WHERE
ACTE.SN > (@pageIndex - 1) * @pageCount
AND ACTE.SN <= (@pageIndex * @pageCount)
实战
- 编写Mapper.java的接口
List<LottResultInformation> selectLottResultByPage(@Param("name") String name, @Param("page") BasePage page);
- 编写Mapper.xml的SQL语句
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.framework.mapper.LottMapper">
<resultMap id="BaseResultLottResultMap" type="com.demo.framework.entity.LottResultInformation" extends="BasePageMap">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="table_id" property="tableId" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="workcode" property="workcode" jdbcType="VARCHAR"/>
<result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
</resultMap>
<resultMap id="BasePageMap" type="com.demo.framework.entity.BasePage">
<result column="total_number" property="totalNumber" jdbcType="INTEGER"/>
<result column="total_page_size" property="pageTotal" jdbcType="INTEGER"/>
</resultMap>
<select id="selectLottResultByPage" resultMap="BaseResultLottResultMap">
DECLARE @pageCount INT
DECLARE @pageIndex INT
SET @pageCount = #{page.pageSize}
SET @pageIndex = #{page.pageNum}
;WITH ACTE AS (
SELECT ROW_NUMBER() OVER (ORDER BY id) AS SN,
id, table_id, name, workcode, create_time, update_time
FROM lott_result_information
<if test="name != null and name !=''">
where name LIKE CONCAT('%',#{name},'%')
</if>
),
BCTE AS (SELECT CEILING(MAX(SN) * 1.0 / @pageCount) AS PageTotal FROM ACTE)
SELECT *,
(SELECT COUNT(*) FROM ACTE) AS total_number,
(SELECT PageTotal FROM BCTE) AS total_page_size
FROM ACTE
WHERE ACTE.SN > (@pageIndex - 1) * @pageCount
AND ACTE.SN <= (@pageIndex * @pageCount)
</select>
</mapper>
这样的话每次查询都会冗余两列total_number和total_page_size；这样也方便每次获取总页数和总条数。
原文链接: https://onlyou.blog.csdn.net//article/details/141852660