1.接口协议
变量名 | 含义 | 类型 | 备注 | |
cmd | 请求指令 | number | 100619 | |
dt | 请求时间 | number | 最后一条记录或第一条记录时间,如果为0则为最新 | |
作业ID | number | |||
pullType | 获取类型 | number | 1为下拉刷新,2为上拉获取历史数据 | |
reqUserType | 成果展示用户类型 | number | ||
size | 请求条数 | number |
2.接口设计的参数
- private int homeworkId;
- private int reqUserType;
- private long dt;
- private int pullType;
- private int size;
3.mybatis的sql
- <select id="queryHomeworkShowList" resultType="qtone.xxt.gz.data3.bean.homework.HomeworkShowBean">
- SELECT * FROM (
- select t.*,row_number() over (order by t.dt desc) rn from (
- select
- hs.CONTENT as asComment,hs.UPLOAD_TIME as dt,hs.id as id,hs.PATH as original,hs.THUMB_PATH as thumb,<if test="hslReqParam.reqUserType==1">
- xt.username as asUser,</if>
- <if test="hslReqParam.reqUserType==2">
- xf.name as asUser,</if>
- hs.USER_ID as userId,hs.user_type as userType
- FROM
- ${hslReqParam.area}_HOMEWORK_SHOW hs
- <if test="hslReqParam.reqUserType==1">
- left join xj_teacher xt
- on hs.user_id=xt.id
- </if>
- <if test="hslReqParam.reqUserType==2">
- left join ${hslReqParam.area}_xj_family xf
- on hs.user_id=xf.id
- </if>
- where
- hs.homework_id=#{hslReqParam.homeworkId}
- <if test="hslReqParam.reqUserType==2">
- AND hs.USER_TYPE=2
- </if>
- <if test="hslReqParam.reqUserType==1">
- AND hs.USER_TYPE=1
- </if>
- <if test="hslReqParam.pullType==1">
- <if test="dt !=null and dt!=''">
- <![CDATA[ AND TO_CHAR (hs.UPLOAD_TIME,'yyyy-mm-dd hh24:mi:ssxff')>#{dt} ]]>
- </if>
- </if>
- <if test="hslReqParam.pullType==2">
- <if test="dt !=null and dt!=''">
- <![CDATA[ AND TO_CHAR (hs.UPLOAD_TIME,'yyyy-mm-dd hh24:mi:ssxff')<#{dt} ]]>
- </if>
- </if>
- <![CDATA[ )t ) WHERE rn <= #{hslReqParam.size} and rn >=1 ]]>
- </select>
提醒自己:这次是用的毫秒来算记录的先后. 所以先格式化请求时间
- String dt = DateUtil.getDateFromTimeDate(reqData.getDt(),"yyyy-MM-dd HH:mm:ss.SSS");
- 数据库表字段 datetime类型设计为 timeStamp
- UPLOAD_TIMENTIMESTAMP(8)Y上传时间