MySQL较为简单,由于有limit关键字可用,需要定义的是分页起始偏移offset和分页大小size
这里参考:http://www.cnblogs.com/AloneSword/p/3412236.html
在Example类中添加offset和size两个变量,及get/set方法。
再在Mapper中的selectByExample最后添加
<if test="offset !=0 or size!=0">
limit #{offset},#{size}
</if>
例如:TestMapper.xml
<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.example.TestExample" >
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from TEST
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
<if test="offset !=0 or size!=0">
limit #{offset},#{size}
</if>
</select>
Oracle的就麻烦一点,这里使用Oracle自带的行号rownum,需要定义最大行号maxrow和最小行号minrow
参考Oracle分页语句:http://blog.163.com/yongqi0408@126/blog/static/4251263220087432522770/
在Example类中添加maxrow和minrow两个变量,及get/set方法。
再修改Mapper中的selectByExample如下:
<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.example.TestExample" >
select * from (
select t1.*, rownum r from (
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from TEST
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
) t1
<if test="maxrow != 0">
where rownum <= ${maxrow}
</if>
) t2
where r > ${minrow}
</select>
使用方法:
TestExample example = new TestExample();
example.createCriteria();
example.setOrderByClause("TS DESC"); // TS是表中的时间戳列,这里按照时间降序排列
example.setMaxrow(maxrow);
example.setMinrow(minrow);
testMapper.selectByExample(example);