1 mybatis:xml文件
<!-- 分页查询 -->
<sql id="pageHeader">
select * from (select a.*,rownum rn from(
</sql>
<sql id="pageRoot">
) a where rownum <= #{rowEnd}) where rn >#{rowStart}
</sql>
<select id="getTestByPage" resultMap="testMap">
<include refid="pageHeader"/>
SELECT * FROM T_TEST
<where>
<if test="xcd != null and xcd !=''">xcd like CONCAT(CONCAT('%', #{xcd,jdbcType=VARCHAR}),'%')</if>
</where>
order by to_number(id) desc
<include refid="pageRoot"/>
</select>
调用函数值:
rowStart = (pageNo - 1) * pageSize;
rowEnd = pageNo * pageSize;
2 java
/**
* 获取物理分页SQL
*
* @param sql
* @param pageNow
* @param pageSize
* @return
*/
public static String getPageSql(String sql, int pageNow, int pageSize) {
StringBuffer sb = new StringBuffer();
if (pageNow < 2) {
pageNow = 1;
}
if (pageSize < 1) {
pageSize = 10;
}
sb.append("select * from ( select row_limit.*, rownum rownum_ from (");
sb.append(StringUtils.trim(sql));
sb.append(") row_limit where rownum <= ");
sb.append(pageNow * pageSize);
sb.append(") where rownum_ >");
sb.append((pageNow - 1) * pageSize);
return sb.toString();
}