JDBC简单分页

使用数据库语句的关键字limit实现分页查询

例子:分页查询房东房屋列表并将其显示

Dao层

1、//根据房屋id分页查询房东房屋列表

uId为房东id，page为分页数码

public List<House> selectAllHouse(int uId,int page){
conn = getconnection();

//查询房屋的所有资料并根据id进行正排序

//limit使用后根两个参数，表示查询的数据范围
sql = "select * from t_house where uId=? order by id asc limit ?,?";

try {
ps = conn.prepareStatement(sql);
ps.setInt(1, uId);

//PAGE.SIZE为实体House的静态常量，表示你一页最多有几条
ps.setInt(2, (page - 1) * House.PAGE_SIZE);
ps.setInt(3, House.PAGE_SIZE);
rs = ps.executeQuery();
while(rs.next()){
House house = new House();
house.setId(rs.getInt(1));
house.setName(rs.getString(2));
house.setImg_one(rs.getString(27));
house.setCreateTime(rs.getDate(22));
house.setHouseStyle(rs.getString(5));
house.setRentType(rs.getString(4));
house.setArea(rs.getDouble(15));
house.setDayPrice(rs.getDouble(19));
house.setStatus(rs.getInt(23));
list.add(house);
}
if(list.size()!=0)
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeconnection(conn, rs, ps);
}
return null;
}

Service层

public int selectAllOrder(int uId){
return houseDao.selectAllOrder(uId);
}

Action层

private int page = 1;

//用户查看房屋
public String showHouse(){

session = ActionContext.getContext().getSession();

//根据房东ID查看房东创建的房屋
User u = (User)session.get("user");

//分页返回所有房屋信息
listHouse = houseService.selectAllHouse(u.getUser_id(),page);
System.out.println("是否创建房屋:"+listHouse);

HttpServletRequest request = ServletActionContext.getRequest();

//多少条记录
int count = houseService.selectCount(uId,1);

//一共多少页
int pages = 1;

if (count % House.PAGE_SIZE == 0) { // 计算总页数,总记录数和每页显示的数
           pages = count / House.PAGE_SIZE; // 对总页数赋值
           
} else {
           pages = count / House.PAGE_SIZE + 1; // 对总页数赋值
       }

//显示导航条
StringBuffer sb = new StringBuffer(); // 实例化StringBuffer
       for (int i = 1; i <= pages; i++) { // 通过循环构建分页导航条
           if (i == page) { // 判断是否为当前页
               sb.append("『" + i + "』"); // 构建分页导航条
           } else {
               // 构建分页导航条
               sb.append("<a href='host/myHouse?page=" + i + "'>" + i + "</a>");
           }
           sb.append("　"); // 构建分页导航条
       }
 
   request.setAttribute("bar", sb.toString());
   session.remove("showPubHouseList");
session.put("showAllHouseList", listHouse);
return Action.SUCCESS;
}

JSP：

<div id="content">
  <c:if test="${sessionScope.showAllHouseList!=null and sessionScope.showPubHouseList==null}">
  <jsp:include page="myhouse.jsp" />
  </c:if>
  <c:if test="${sessionScope.showPubHouseList!=null and sessionScope.showAllHouseList==null }">
  <jsp:include page="publishHouse.jsp" />
  </c:if>
  <c:if test="${sessionScope.showPubHouseList==null and sessionScope.showAllHouseList==null }">
  <h3>您还没有此类房屋</h3>
  </c:if>
  </div>