oracle&mysql数据库分页

oracle 分页

dao 层

		groupUnion.setPageStart(pageStart);
        groupUnion.setPageEnd(pageEnd);
        List<GroupUnion> list = groupUnionMapper.selectUnionList(groupUnion);

mapper.xml

<select id="selectUnionList" resultMap="BaseResultMap" parameterType="com.unicom.groupmall.data.domain.gmcore.GroupUnionExample" >
        select * from ( select row_.*, rownum rownum_ from (
        select UNION_NO, UNION_NAME, PAYMENT_ACCOUNT, USER_NAME, PHONE, EMAIL, UNION_STATE,UNION_ATTRIBUTE , REMARK,
        CREATER, to_char(CREATE_TIME,'yyyy-MM-dd HH24:mi:ss') as CREATE_TIME, UPDATER, UPDATE_TIME,GRANT_SMS_CODE,EXPIRE_SMS_CODE
        from GROUP_UNION
        <where>
             1=1
            <if test="unionNo != null and unionNo != ''" >
                AND UNION_NO like CONCAT(CONCAT('%', #{unionNo,jdbcType=VARCHAR}), '%')
            </if>
            <if test="unionName != null and unionName != ''" >
                AND UNION_NAME like CONCAT(CONCAT('%', #{unionName,jdbcType=VARCHAR}), '%')
            </if>
            <if test="unionState != null and unionState != ''" >
                AND UNION_STATE = #{unionState,jdbcType=VARCHAR}
            </if>
            <if test="createTime != null and createTime != ''">
                <![CDATA[ and to_char(CREATE_TIME,'yyyy-MM-dd') >= #{createTime} ]]>
            </if>
            <if test="createTimeEnd != null and createTimeEnd != ''">
                <![CDATA[ and to_char(CREATE_TIME,'yyyy-MM-dd') <= #{createTimeEnd} ]]>
            </if>
        </where>
        order by CREATE_TIME desc
        <![CDATA[ ) row_ where rownum <= #{pageEnd} ) where rownum_ > #{pageStart} ]]>
    </select>

mysql 分页

dao层

			videoExample.setLimit(limit);
            videoExample.setOffset(offset);
            videoExample.setOrderByClause("UPDATE_TIME DESC");
            List<TWoVipTitle> videoList = tWoVipMapper.selectByExample(videoExample);

mapper.xml

※ 其中m是指记录开始的index，从0开始表示第一条记录，n是指从第m+1条开始，取n条。m代表起始偏移量，n偏移总数

SELECT * FROM test LIMIT m,n;
SELECT * FROM test LIMIT 5,10;-- 检索记录行6-15 10条记录
SELECT * FROM test LIMIT 5;-- 检索前5个记录行,表示返回最大的记录行数目

<select id="selectByExample" parameterType="com.sinovatech.release.entity.flowarea.TWoVipTitleExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from t_wo_vip_title
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
    <if test="limit != null">
      <if test="offset != null">
        limit ${offset}, ${limit}
      </if>
      <if test="offset == null">
        limit ${limit}
      </if>
    </if>
  </select>