Java web访问MySql数据库分页查询

有时候我们查询数据库表时，但是很多时候表中的记录很多，需要显示出来的话怎么办？这时可以使用分页的方法，就是指定从数据库表的什么位置开始查询显示，以及指定显示的记录数目。

Mysql数据库提供了limit a,b的关键字，a是数据库表的查询起始位置，是个偏移量，b是指定本次查询的记录数目

下面是我的数据表：

数据库的链接类：

package com.tools;
import java.sql.*;
public class DBConnection {	
private Connection con;			//定义数据库连接类对象
private PreparedStatement pstm;	
private String user="root";		//连接数据库用户名
private String password="123456";		//连接数据库密码
private String driverName="com.mysql.jdbc.Driver";	//数据库驱动
private String url="jdbc:mysql://localhost:3306/shoppingcart";		
//连接数据库的URL,后面的是为了防止插入数据 库出现乱码，?useUnicode=true&characterEncoding=UTF-8
//构造函数
public DBConnection(){
	
}
/**创建数据库连接*/
public Connection getCon(){
	try{
		Class.forName("com.mysql.jdbc.Driver");
	}catch(ClassNotFoundException e){
		System.out.println("加载数据库驱动失败！");
		e.printStackTrace();
	}
	try {
		con=DriverManager.getConnection(url,user,password);		//获取数据库连接
	} catch (SQLException e) {
		System.out.println("创建数据库连接失败！");
		con=null;
		e.printStackTrace();
	}
	return con;					//返回数据库连接对象
}	
/**
 *@功能：对数据库进行增、删、改、查操作
 *@参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据 
 */
	public void doPstm(String sql,Object[] params){
		if(sql!=null&&!sql.equals("")){
			if(params==null)
				params=new Object[0];			
			getCon();
			if(con!=null){
				try{		
					System.out.println(sql);
					pstm=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
					for(int i=0;i<params.length;i++){
						pstm.setObject(i+1,params[i]);
					}
					pstm.execute();
				}catch(SQLException e){
					System.out.println("doPstm()方法出错！");
					e.printStackTrace();
				}				
			}			
		}
	}	
	public ResultSet getRs() throws SQLException{
		return pstm.getResultSet();		
	}
	public int getCount() throws SQLException{
		return pstm.getUpdateCount();		
	}
	public void closed(){
		try{
			if(pstm!=null)
				pstm.close();			
		}catch(SQLException e){
			System.out.println("关闭pstm对象失败！");
			e.printStackTrace();
		}
		try{
			if(con!=null){
				con.close();
			}
		}catch(SQLException e){
			System.out.println("关闭con对象失败！");
			e.printStackTrace();
		}
	}
}

javabean的代码：

package com.beans;

public class Book {
	public static final int PAGE_SIZE=6;//每一页中显示的数目
    private int bookId;
    private String name;
    private String author;
    private String publisher;
    private String price;
    public Book(){
    	
    }
   public Book(int bookId, String name,String author,String publisher,String price){
    	this.bookId=bookId;
    	this.name=name;
    	this.author=author;
    	this.publisher=publisher;
    	this.price=price; 	
    }
   	public int getBookId() {
   		return bookId;
   	}
   	public void setBookId(int bookId) {
   		this.bookId = bookId;
   	}
   	public String getName() {
   		return name;
   	}	
   	public void setName(String name) {
   		this.name = name;
   	}
   	public String getAuthor() {
   		return author;
   	}
   	public void setAuthor(String author) {
   		this.author = author;
   	}
   	public String getPublisher() {
   		return publisher;
   	}
   	public void setPublisher(String publisher) {
   		this.publisher = publisher;
   	}
   	public String getPrice() {
   		return price;
   	}
   	public void setPrice(String price) {
   		this.price = price;
   	}  
}

DAO的代码：

package com.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.beans.Book;
import com.tools.DBConnection;

public class BookDao {
	 DBConnection DB=new DBConnection();//数据库的链接类
     Connection conn=null;
     //返回所有图书列表
     public List<Book> getBookList(){
    	 List<Book> list=new ArrayList<Book>();
    	 try {
    		  conn=DB.getCon();
        	  String sql="select * from books";
			  PreparedStatement pstm=conn.prepareStatement(sql);
			  ResultSet rs=pstm.executeQuery();
			  while(rs.next()){
				  Book book=new Book();
				  book.setBookId(rs.getInt(1));
				  book.setName(rs.getString(2));
				  book.setAuthor(rs.getString(3));
				  book.setPublisher(rs.getString(4));
				  book.setPrice(rs.getString(5));
				  list.add(book);
			  }
			  return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	 return null;
     }
     //根据图书ID返回这本书的信息
     public Book getBookById(int bookid){
    	 Book book=new Book();
    	 try {
   		      conn=DB.getCon();
   		   	  String sql="select * from books where BookID=?";
			  PreparedStatement pstm=conn.prepareStatement(sql);
			  pstm.setInt(1, bookid);
			  ResultSet rs=pstm.executeQuery();
			  while(rs.next())
			  {
				  book.setBookId(rs.getInt(1));
				  book.setName(rs.getString(2));
				  book.setAuthor(rs.getString(3));
				  book.setPublisher(rs.getString(4));
				  book.setPrice(rs.getString(5));				
			  }
			  return book;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}			
   	 return null;
     }
     
}

负责分页查询的Servlet代码：

package com.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.Dao.PageQueryDao;
import com.beans.Book;

public class PageQueryServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
          int currpage=1;//开始时是第一页
          if(request.getParameter("page")!=null){
        	  currpage=Integer.parseInt(request.getParameter("page"));
          }
        
         PageQueryDao pagedao=new PageQueryDao();//查询数据库的dao
         List<Book> list=pagedao.getPageList(currpage);//查询当前页记录
         request.setAttribute("list", list); //把记录放在request里边
         int pages;   //计算查询总页数
         int count=pagedao.FindCounts();
         if(count%Book.PAGE_SIZE==0){
        	 pages=count/Book.PAGE_SIZE;
         }
         else{
        	 pages=count/Book.PAGE_SIZE+1;
         }
         
         StringBuffer sb=new StringBuffer();
         //构建分页条
         for(int i=1;i<=pages;i++){
        	 if(i==currpage){
        		 sb.append("『" + i + "』");
        	 }
        	 else{

       		 sb.append("<a href="PageQueryServlet?page="+i+"">"+i+"</a>");//链接到本servlet
        	 }
        	 sb.append(" ");
         }
         request.setAttribute("bar", sb.toString());
         request.getRequestDispatcher("books.jsp").forward(request, response);	//请求转发	
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

books.jsp显示：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="java.util.List"%>
<%@page import="com.beans.Book"%><html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>图书信息列表</title>

	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
	<style type="text/css">
	    a:link{color:red;text-decoration:none}/*未访问，红色，无下划线*/
	    a:active{color:blue;}/*激活，红色*/
	    a:visited{color:purple;text-decoration:none}/*已访问，紫色，无下划线*/
	    a:hover{color:blue;text-decoration:underline}/*鼠标移动上面，蓝色，下划线*/
	</style>
  </head>
  
  <body bgcolor="#98FB98">
       <table align="center"  >
          <tr>
             <td align="center" colspan="8" >
                 <h2 style=" font-family:隶书;color:#9A32CD">喵喵网上书店</h2>
             </td>
          </tr>
            <%! int i=1,j=1; %>
           <% List<Book> list=(List<Book>)request.getAttribute("list");%>
           <% for(j=1;j<=2;j++){ %><!--这里的我的工作项目是这样的，也可以循环显示成列表形式，只需要修改下表的循环代码即可-->
             <tr>
            <% 
               for(Book b:list){ 
                   if(j==2){
                     if(b.getBookId()<=3||(b.getBookId()>=7&&b.getBookId()<=9)) continue;
                   }                         
             %>         
            <td width="100" height="140"><img src="images/<%=String.valueOf(b.getBookId()-1) %>.jpg"/></td>
            <td width="160"> 
                <font color="#0000FF">ISDN :<%=b.getBookId() %></font><br>
                 <font color="#0000FF"> 书 名:<%=b.getName() %></font><br>
              	 <font color="#0000FF"> 作 者:<%=b.getAuthor() %></font><br>
              	 <font color="#0000FF">出 版:<%=b.getPublisher() %></font><br>
              	 <font color="#0000FF">价 格:<%=b.getPrice() %> </font><br>   
              	    <a href="">查看详细信息</a>                           
            </td> 
               <% if(b.getBookId()%3==0) break; %>                         
          <%} %>   
          </tr>
          <%} %>         
          <tr>
			<td align="right" colspan="8"><%= request.getAttribute("bar") %></td>          
          </tr>
       </table>
  </body>
</html>

结果；