使用jdbc连接技术,将数据库中的数据进行分页
首先dao 层
//求出总共有多少页
public int getPageCount(int pagesize) {
int pagecount=0;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection conn=null;
PreparedStatement psmt=null;
ResultSet rs=null;
try {
conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/schools?useUnicode=true&"
+ "characterEncoding=GBK", "root", "123456");
String sql="select count(*) from score ";
psmt=conn.prepareStatement(sql);
rs=psmt.executeQuery();
while(rs.next()) {
int rowcount=rs.getInt(1);
pagecount=(int)Math.ceil(1.0*rowcount/pagesize);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
psmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return pagecount;
}
//求出总共有多少数据
public ArrayList<Student> getPageStu(int pageno,int pagesize) {
ArrayList<Student> stus=new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection conn=null;
PreparedStatement psmt=null;
ResultSet rs=null;
try {
conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/schools?useUnicode=true&"
+ "characterEncoding=GBK", "root", "123456");
String sql="select ban,bname from score limit ?,? ";
psmt=conn.prepareStatement(sql);
psmt.setInt(1,(pageno-1)*pagesize);
psmt.setInt(2, pagesize);
rs=psmt.executeQuery();
while(rs.next()) {
Student student=new Student(rs.getInt(1), rs.getString(2));
stus.add(student);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
psmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return stus;
}
service层
StudenDao sd=new StudenDao();
public int getPageCount(int pagesize) {
return sd.getPageCount(pagesize);
}
public ArrayList<Student> getPageStu(int pageno,int pagesize) {
return sd.getPageStu(pageno, pagesize);
}
servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int pageno=1;
if(request.getParameter("pageno") != null) {
pageno=Integer.parseInt(request.getParameter("pageno"));
}
StudentService ss=new StudentService();
int pagecount=ss.getPageCount(100);//pagesize设为每页显示100条
ArrayList<Student> stus=ss.getPageStu(pageno, 100);
int currentpage=pageno;
request.setAttribute("currentpage", currentpage);
request.setAttribute("stus", stus);
request.setAttribute("pagecount", pagecount);//总共有多少页
int pageprev=pageno>1?pageno-1:1;//上一页
int pagenext=pageno<pagecount?pageno+1:pagecount;//下一页
request.setAttribute("pagenow", pageno);//当前页
request.setAttribute("pageprev", pageprev);
request.setAttribute("pagenext", pagenext);
request.getRequestDispatcher("main.jsp").forward(request, response);
}
jsp页面
<table border="1" cellpadding="0" cellspacing="0" style="width: 500px;text-align:center;border-color: #ccc">
<tr id="cs">
<td>班级</td>
<td>姓名</td>
</tr>
<c:forEach items="${stus }" var="stu">
<tr>
<td>${stu.ban }</td>
<td>${stu.name }</td>
</tr>
</c:forEach>
</table>
<div id="dic">
<ul>
<li><a href="PageServlet?pageno=1">首页</a></li>
<li><a href="PageServlet?pageno=${pageprev }">上一页</a></li>
<li><a href="PageServlet?pageno=${pagenext }">下一页</a></li>
<li><a href="PageServlet?pageno=${pagecount }">尾页</a></li>
</ul>
</div>
<form action="PageServlet" method="get" id="f1">
<p>跳转至:
<c:forEach begin="1" end="${pagecount }" var="i">
<c:if test="${pagenow == i }">
<input type="text" name="pageno" value="${i }">页
</c:if>
</c:forEach>
<input type="submit" value="跳转">
</p>
</form>