有時候我們查詢數據庫表時，但是很多時候表中的記錄很多，需要顯示出來的話怎么辦？這時可以使用分頁的方法，就是指定從數據庫表的什么位置開始查詢顯示，以及指定顯示的記錄數目。

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 blocke.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 blocke.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();//查詢數據庫的daoList<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> 結果；

總結

以上是生活随笔為你收集整理的Java web访问MySql数据库分页查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。