【Oracle/MyBatis】Oracle分页语句在MyBatis的Mapper.xml中的实际运用

【前篇】

Oracle的三种分页方式

【Mapper类中的对应函数】

package com.hy.mapper;

import com.hy.entity.Project;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface ProjectMapper {
...
    List<Project> pagedQueryPrj(String keyword,long uid, int start, int end);
}

【Mapper.xml】

<?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.hy.mapper.ProjectMapper">

    <select id="pagedQueryPrj" resultType="com.hy.entity.Project">
        select * from (select tta.*,rownum as rn from (

        select a.id,a.name,to_char(a.create_time,'yyyy-mm-dd hh24:mi:ss') as createTime,0 as taskCnt
        from dmo_project  a
        where a.userid=#{uid}
        <if test="keyword != null and keyword !=''">
            AND a.name LIKE '%'||#{keyword,jdbcType=VARCHAR}||'%'
        </if>

        ) tta where rownum &lt;=#{end} ) ttb where ttb.rn &gt;#{start}
    </select>
</mapper>

说明：select节点里的SQL分蓝色外层和肉色内层两部分，内层是实际SQL，是要根据业务改变的；外层就是固定分页语句，一点也不需要动。

 

END