oracle分页查询sql语句xml版

最新推荐文章于 2023-01-11 13:19:48 发布

原创最新推荐文章于 2023-01-11 13:19:48 发布 · 1.3k 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文详细介绍了如何在Oracle数据库中使用XML语法进行高效分页查询，通过实例解析SQL语句的构建过程，帮助开发者优化大数据量的查询操作。

<select id = "listQueryDpcInfoDO"  resultMap ="getListDpcInfoDO" >

        select * from
        (
        SELECT A.*, ROWNUM RN
        # from后为具体查询表语句
        FROM ( select *
        from DPCINFO
        <where>
            <if test="centerInputParam.province !=null">
                province = #{centerInputParam.province }
            </if>

            <if test="centerInputParam.city !=null">
                and  city = #{centerInputParam.city}
            </if>
            <if test="centerInputParam.area !=null">
                and county = #{centerInputParam.area}
            </if>
            <if test="centerInputParam.begindate !=null and centerInputParam.enddate !=null">
                and to_char(time,'yyyy-mm-dd') between #{centerInputParam.begindate} and #{centerInputParam.enddate}
            </if>
            <if test="centerInputParam.cardNoOrName !=null">
                and   addr = #{centerInputParam.cardNoOrName}
            </if>
        </where>) A
        <where>
            <if test="pageSize !=null and pageIndex !=null" >
                ROWNUM &lt;= #{pageSize}*#{pageIndex}
            </if>
        </where>
        )
        <where>
            <if test= "pageIndex >1">
                RN >=(#{pageIndex}-1)*#{pageSize}
            </if>
        </where>
    </select>

 <resultMap id="getListDpcInfoDO" type="com.keyou.domain.basicQuery.DpcInfoDO">
        <id property="dpcinfoguid" column="DPCINFOGUID"/>
        <result  property="dpcid" column="DPCID"/>
        <result  property="addr" column="ADDR"/>
        <result  property="time" column="TIME"/>
        <result  property="province" column="PROVINCE"/>
        <result  property="city" column="CITY"/>
        <result  property="county" column="COUNTY"/>
        <result  property="ip" column="IP"/>
    </resultMap>