文章摘要
这篇文章介绍了如何使用**mapper**框架进行数据库查询的分页操作。文章详细描述了如何配置**结果映射（Result Map）**，以及通过不同的**SQL查询方式**实现分页功能。具体包括普通分页、覆盖索引优化的深分页，以及基于**Id**的分页方式。文章还比较了这三种分页方式的优缺点，并指出了每种方式的适用场景，帮助开发者选择合适的分页实现策略。通过这些技术手段，文章展示了如何在MySQL数据库中高效地处理海量数据的查询问题。
<mapper namespace=”com.test.java.front.test.mysql.deep.page.repository.PlayerRepository”>
<resultMap id=”BaseResultMap” type=”com.test.java.front.test.mysql.deep.page.entity.PlayerEntity”>
<id column=”id” jdbcType=”BIGINT” property=”id” />
<result column=”player_id” jdbcType=”VARCHAR” property=”playerId” />
<result column=”player_name” jdbcType=”VARCHAR” property=”playerName” />
<result column=”height” jdbcType=”INTEGER” property=”height” />
<result column=”weight” jdbcType=”INTEGER” property=”weight” />
<result column=”game_performance” jdbcType=”LONGVARCHAR” property=”gamePerformance” />
</resultMap>
<sql id=”Base_Column_List”>
id, player_id, player_name, height, weight, game_performance
</sql>
<sql id=”conditions”>
<where>
<if test=”playerId !=null”>
and player_id=#{playerId,jdbcType=VARCHAR}
</if>
</where>
</sql>
<sql id=”pager”>
<if test=”skip !=null and limit !=null”>
limit #{skip}, #{limit}
</if>
</sql>
<!– 查询条数 –>
<select id=”selectPageCount” parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam” resultType=”java.lang.Long”>
select count(*) from player
<include refid=”conditions” />
</select>
<!– 分页方式1：普通分页存在深分页问题 –>
<!– select * from player limit 990000,5 –>
<select id=”selectPager1″ parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam” resultMap=”BaseResultMap”>
select
<include refid=”Base_Column_List” />
from player
<include refid=”conditions” />
<include refid=”pager” />
</select>
<!– 分页方式2：覆盖索引优化深分页问题 –>
<!– select * from player a, (select id as tmpId from player limit 990000,5) b where a.id=b.tmpId –>
<select id=”selectPager2″ parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam” resultMap=”BaseResultMap”>
select
<include refid=”Base_Column_List” />
from player a,
(
select id as tmpId from player
<include refid=”conditions” />
<include refid=”pager” />
) b
where a.id=b.tmpId
</select>
<!– 分页方式3：Id分页不支持跳页 –>
<!– select * from player where id > 990000 limit 5 –>
<select id=”selectPager3″ parameterType=”com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryIdParam” resultMap=”BaseResultMap”>
select
<include refid=”Base_Column_List” />
<include refid=”conditions” />
from player where id > #{startId} limit #{pageSize}
</select>
</mapper>