有时候我们查询数据库表时,但是很多时候表中的记录很多,需要显示出来的话怎么办?这时可以使用分页的方法,就是指定从数据库表的什么位置开始查询显示,以及指定显示的记录数目。
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>
结果;