第一步:LiveRateMapper里面定义查询方法:
通过对象作为参数查询对象集合
List<LiveRatePage> findAllLiveRate(LiveRatePage liveRatePage);
第二步:根据查询条件写出正确的sql语句:
select (@i:=@i+1) as rank,a.* from (select DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d')CREATE_DT,l.DAY_AGE,l.FARMER_NAME,l.LIVE_RATE,l.AREA FROM liverate l WHERE
DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d') >=DATE_FORMAT('2017-06-01','%Y-%m-%d')
and DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d')<=DATE_FORMAT('2017-06-10','%Y-%m-%d')
ORDER BY l.LIVE_RATE desc
LIMIT 0,20)a,(select @i:=0) as it
;
第三步:LiveRateMapper.xml定义查询语句:
改写mybatis动态sql
<select id="findAllLiveRate" resultMap="BaseResultMap2" parameterType="com.znyq.wfCloud.admin.pageModel.LiveRatePage">
select (@i:=@i+1) as rank,a.* from (select DATE_FORMAT(l.CREATE_DT , '%Y-%m-%d')CREATE_DT,l.DAY_AGE,l.FARMER_NAME,l.LIVE_RATE,l.AREA
FROM liverate l
WHERE 1 = 1
<if test="date1 != null and ''!= date1">
<![CDATA[ and DATE_FORMAT(l.CREATE_DT, '%Y-%m-%d') >=DATE_FORMAT(#{date1},'%Y-%m-%d') ]]>
</if>
<if test="date2 != null and ''!= date2">
<![CDATA[ and DATE_FORMAT(l.CREATE_DT, '%Y-%m-%d') <=DATE_FORMAT(#{date2},'%Y-%m-%d') ]]>
</if>
ORDER BY l.LIVE_RATE desc
limit #{curSize},#{pageSize})a,(select @i:=0) as it
</select>
第四步:配置正确返回对象属性
<resultMap id="BaseResultMap2" type="com.znyq.wfCloud.admin.pageModel.LiveRatePage" >
<result column="rank" property="rank" jdbcType="VARCHAR" />
<result column="DAY_AGE" property="dayAge" jdbcType="VARCHAR" />
<result column="LIVE_RATE" property="liveRate" jdbcType="VARCHAR" />
<result column="FARMER_NAME" property="farmerName" jdbcType="VARCHAR" />
<result column="AREA" property="area" jdbcType="VARCHAR" />
<result column="CREATE_DT" property="rankDate" jdbcType="VARCHAR" />
</resultMap>
对象:LiveRatePage
属性:private String dayAge;
public String getDayAge() {
return dayAge;
}
public void setDayAge(String dayAge) {
this.dayAge = dayAge;
}
ps对象的其他属性和set,get省略了,注意的地方我都标红了查询的id要唯一
返回的结果集配置id要唯一对应,查询的列字段和对象属性要注意对应