APP 接口之 oracle分页查询数据 上拉查看之前的，下拉刷新数据接口

1.接口协议

	变量名	含义	类型	备注
	cmd	请求指令	number	100619
	dt	请求时间	number	最后一条记录或第一条记录时间，如果为0则为最新
	homeworkId	作业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_TIME	N	TIMESTAMP(8)	Y			上传时间