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>