MyBatis Oracle与MySQL分页

最新推荐文章于 2021-04-15 09:20:12 发布

原创最新推荐文章于 2021-04-15 09:20:12 发布 · 604 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java

MySQL 同时被 3 个专栏收录

3 篇文章

订阅专栏

MyBatis

2 篇文章

订阅专栏

Oracle

2 篇文章

订阅专栏

本文详细介绍了Oracle和MySQL中分页查询的实现方式，并通过Mybatis框架展示了一种优化的分页应用策略。包括SQL执行代码、Mybatis中分页的应用与优化，以及不同技术细节的对比。

Oracle、MySQL分页实例

SQL执行代码

ORACLE:
SELECT * FROM (
	SELECT ROWNUM AS NUMROW, Z.* from (
		select * from CHANNEL T where 1=1 and T.ID = 1 ORDER BY T.ID desc 
	) Z  where rownum<=10
) WHERE NUMROW >= 1 


MySQL:获取(0 ,10]，10表示向后获取10条数据。
select * from fm_sell T 
where 1=1 and T.SERIAL_NUM = '20141124' ORDER BY T.ID desc LIMIT 0, 10

Mybatis中分页应用与优化

Oracle：
<sql id="WhereClase">
	<where>
		1=1
		<if test="id != null">
			AND T.ID = #{id,jdbcType=BIGINT}
		</if>
		...
	</where>
</sql>

<sql id="OrderBy">
	ORDER BY T.ID desc 
</sql>

<sql id="PageSQLHead">
	SELECT * FROM (SELECT ROWNUM AS NUMROW, Z.* from (
</sql>

<sql id="PageSQLFoot">
	<![CDATA[ ) Z  where rownum<=#{pageLastItem}) WHERE NUMROW >= #{pageFristItem} ]]>
</sql>

<select id="queryCount" parameterClass="ChannelQuery" resultClass="java.lang.Integer">
	select count(1) from CHANNEL T
	<include refid="WhereClase"/>
	<include refid="OrderBy"/>
</select>

<select id="queryPage" parameterClass="ChannelQuery" resultMap="channelResult">
	<include refid="PageSQLHead" />
	select
	<include refid="Base_Column_List" />
	from CHANNEL T
	<include refid="WhereClase" />
	<include refid="OrderBy" />
	<include refid="PageSQLFoot" />
</select>

MySQL：
<resultMap id="BaseResultMap" type="fmSell">
	<id column="ID" property="id" jdbcType="BIGINT" />
	<result column="SERIAL_NUM" property="serialNum" jdbcType="VARCHAR" />
	...
</resultMap>

<sql id="Base_Column_List">
	T.ID, T.SERIAL_NUM, T.STOCK_ID, T.GMT_CREATE, T.GMT_MODIFY
</sql>

<sql id="WhereClase">
	<where>
		1=1
		<if test="serialNum != null and serialNum != ''">
			AND T.SERIAL_NUM = #{serialNum,jdbcType=VARCHAR}
		</if>
		...
	</where>
</sql>

<!-- 分页起始 -->
<sql id="GetPagerSql">
	LIMIT #{pageIndex}, #{pageSize}
</sql>

<sql id="OrderBy">
	ORDER BY T.ID desc 
</sql>

<!-- 分页记录 -->
<select id="selectPagerByQuery" parameterType="fmSellQuery" resultMap="BaseResultMap">
	SELECT <include refid="Base_Column_List" />
	FROM fm_sell T
	<include refid="WhereClase"/>
	<include refid="OrderBy"/>
	<include refid="GetPagerSql"/>
</select>

<!-- 总记录数 -->
<select id="selectCountsByQuery" parameterType="fmSellQuery" resultType="java.lang.Integer">
	SELECT COUNT(1) FROM fm_sell T
	<include refid="WhereClase"/>
	<include refid="OrderBy"/>
</select>