MyBatis Oracle与MySQL分页

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>