MySQL limit关键字

SELECT * FROM table_name limit num1, [num2]

  

num1表示从num1开始获取的数据行标识。

num2表示一共取多少行数据。

 

用limit实现分页会容易一些，而且效率也还不错。

// 每一页多少条数据
int pageRows = 20;
// 一共多少条数据
int totalRows = 0;
// 总页数
int totalPages = 0;

rs = ps.executeQuery();
// 获取多少个用户
while(rs.next()){
	totalRows = rs.getInt(1);
}
// 计算总页数
if(totalRows % pageRows == 0){
	totalPages = totalRows / pageRows;
}else{
	totalPages = totalRows / pageRows + 1;
}

hs.setAttribute("status", "ok");
// 输出页码到HTML
pw.println("<table class=customer>");
// 输出表头
pw.println("<thead><td>UserId</td><td>UserName</td><td>Password</td><td>Email</td><td>Telephone</td><td>Priority</td></thead>");
String sql = "SELECT UserId, UserName, Password, Priority, Email, Tel FROM customer LIMIT " +
	pageNow * pageRows + "," + pageRows;
ps = con.prepareStatement(sql);
System.out.println(sql);
rs = ps.executeQuery();
while(rs.next()){
	pw.println("<tr>");
	pw.println("<td class=customer>" + rs.getInt("UserId") + "</td>");
	pw.println("<td class=customer>" + rs.getString("UserName") + "</td>");
	pw.println("<td class=customer>" + rs.getString("Password") + "</td>");
	pw.println("<td class=customer>" + rs.getString("Email") + "</td>");
	pw.println("<td class=customer>" + rs.getString("Tel") + "</td>");
	pw.println("<td class=customer>" + rs.getInt("Priority") + "</td>");
	pw.println("</tr>");
}

pw.println("</table><br/><br/>");

// 首页
pw.println("<a href=welcome?currentPage=" + 0 + ">首页</a>");
// 上一页
pw.println("<a href=welcome?currentPage=" + ((pageNow - 1) > 0 ? (pageNow - 1) : pageNow) + ">上一页</a>");
// 输出导航页码
for(int i = pageNow + 1; i <= pageNow + 9 && i > 0 && i < totalPages + 1; ++i){
	pw.println("<a href=welcome?currentPage=" + (i - 1) + ">" + i +"</a>");
}

// 下一页
pw.println("<a href=welcome?currentPage=" + ((pageNow + 1) < totalPages - 1 ? (pageNow + 1) : pageNow) + ">下一页</a>");
// 尾页
pw.println("<a href=welcome?currentPage=" + (totalPages - 1) + ">尾页</a><br/><hr/>");