Oracle分页查询实例

1.定义分页实体类

@Component
public class PageBean implements Serializable {
    private static final long serialVersionUID = 1L;

    private Integer page;//当前页
    private Integer limit;//每页记录数
    private Integer startRow;//查询开始行
    private Integer endRow;//查询结束行

    public PageBean(Integer page,Integer limit){
        this.page = page;
        this.limit = limit;
    }

    public Integer getPage() {
        return page;
    }

    public void setPage(Integer page) {
        this.page = page;
    }

    public Integer getLimit() {
        return limit;
    }

    public void setLimit(Integer limit) {
        this.limit = limit;
    }

    public Integer getStartRow() {
        return (this.getPage() - 1) * this.getLimit();
    }

    public void setStartRow(Integer startRow) {
        this.startRow = startRow;
    }

    public Integer getEndRow() {
        return this.getStartRow() + this.getLimit();
    }

    public void setEndRow(Integer endRow) {
        this.endRow = endRow;
    }

}

2.定义分页查询类

/**
 * 分页查询
 * */
@RequestMapping("getHouseMapList")
@ResponseBody
public Object getHouseMapList(HttpServletRequest request, HttpServletResponse response){
	//前端页面传递的查询参数
	String communityId = request.getParameter("communityId");
	String buildingNo = request.getParameter("buildingNo");
	String unitNo = request.getParameter("unitNo");
	//前端页面传递的分页参数
	String page = request.getParameter("page");
	String limit = request.getParameter("limit");
	PageBean pb = new PageBean(page,limit);
	//参数添加到Map
	Map<String, Object> param = new HashMap<>();
	param.put("pb", pb);
	param.put("communityId", communityId);
	param.put("buildingNo", buildingNo);
	param.put("unitNo", unitNo);
	//执行分页查询SQL
	List<Map> list = this.houseMapManager.selectHouseMapList(param);
	//根据查询结果获取总记录数，返回到前端，用于分页
	int count = 0;
	if (list != null && list.size() > 0) {
		count = Integer.parseInt(list.get(0).get("TOTAL_COUNT").toString());
	}

	JSONObject retObj = new JSONObject();
	retObj.put("code", 0);
	retObj.put("msg", "查询成功");
	retObj.put("count", count);
	retObj.put("data", list);

	return retObj;
}

3.分页SQL

<!--分页查询-->
<select id="selectHouseMapList"  parameterType="map" resultType="java.util.Map">
	WITH RT AS(
	SELECT H.COMMUNITY_ID,H.BUILDING_NO,H.UNIT_NO,H.ROOM_NO
	FROM T_HOUSE_INFO H
	WHERE 1=1
	<if test="communityId!=null and communityId!=''">
		AND H.COMMUNITY_ID = #{communityId}
	</if>
	<if test="buildingNo!=null and buildingNo!=''">
		AND H.BUILDING_NO = #{buildingNo}
	</if>
	<if test="unitNo!=null and unitNo!=''">
		AND H.UNIT_NO = #{unitNo}
	</if>
	GROUP BY H.COMMUNITY_ID,H.BUILDING_NO,H.UNIT_NO,H.ROOM_NO
	)
	SELECT * FROM(
	SELECT T.*,ROWNUM RN FROM(
	SELECT RT.*,M.ROOM_MAP,M.BUILDING_MAP,CI.COMMUNITY_NAME,(SELECT COUNT(*) FROM RT) TOTAL_COUNT
	FROM RT
	LEFT JOIN T_HOUSE_MAP M
	ON RT.COMMUNITY_ID=M.COMMUNITY_ID
	AND RT.BUILDING_NO=M.BUILDING_NO
	AND RT.UNIT_NO=M.UNIT_NO
	AND RT.ROOM_NO=M.ROOM_NO
	LEFT JOIN T_COMMUNITY_INFO CI
	ON RT.COMMUNITY_ID=CI.COMMUNITY_ID
	<![CDATA[
		) T WHERE ROWNUM <= #{pb.endRow}
		) WHERE RN > #{pb.startRow}
	]]>
</select>