JDBC——分页查询(mysql的limit方法)
显目目录如下
MySql语句的limit
String sql="select * from bookprice order by id asc limit ?,?";//分页查询的sql语句
limit ?,?:
- 第一个问号:用于指定查询记录的起始位置
- 第二个问号:用于指定查询数据所返回的记录数
一、构建数据库链接DBUtil.jsp
package com.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
public class DBUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
//此对象是用于加载properties文件数据
ResourceBundle rb=ResourceBundle.getBundle("Connection");
driver=rb.getString("driver");
url=rb.getString("url");
username=rb.getString("username");
password=rb.getString("password");
}
public static Connection open() {
try {
Class.forName(driver);
return DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(Statement stmt,PreparedStatement pSta,ResultSet rSta,Connection conn){
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pSta!=null){
try {
pSta.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rSta!=null){
try {
rSta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
二、构建数据库链接配置文件Connection.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/cxg2850105498
username=root
password=cxg200888
三、创建ProductControl类
实现功能
- 分页查询所有产品FindPage()
- page是页数
- FindCount()查询总记录数
package com.control;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.plaf.basic.BasicInternalFrameTitlePane.RestoreAction;
import com.dao.DBUtil;
import com.dao.Product;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
public class ProductControl {
/**
* 分页查询所有产品
* page是页数
*/
public List<Product> FindPage(int page){
List<Product> list=new ArrayList<Product>();
Connection conn=null;
PreparedStatement pSta=null;
ResultSet rSte=null;
Statement stmt=null;
conn=DBUtil.open();
String sql="select * from bookprice order by id asc limit ?,?";//分页查询的sql语句
try {
pSta=(PreparedStatement) conn.prepareStatement(sql);
//用于指定查询记录的起始位置
pSta.setInt(1, (page-1)*Product.PAGE_SIZE);
//用于指定查询数据所返回的记录数
pSta.setInt(2, Product.PAGE_SIZE);
rSte=pSta.executeQuery();
while (rSte.next()) {
Product p=new Product();
p.setId(rSte.getInt("id"));
p.setName(rSte.getString("name"));
p.setNum(rSte.getInt("num"));
p.setPrice(rSte.getDouble("price"));
p.setUnit(rSte.getString("unit"));
list.add(p);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close(stmt, pSta, rSte, conn);
}
return list;
}
/**
* 查询总记录数count
*/
public int FindCount(){
int count=0;
Connection conn=null;
Statement stmt=null;
ResultSet rSta=null;
conn=DBUtil.open();
String sql="select count(*) from bookprice";
try {
stmt=(Statement)conn.createStatement();
rSta=stmt.executeQuery(sql);
if(rSta.next()){
count=rSta.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close(stmt, null, rSta, conn);
}
return count;
}
}
四、创建Product类
package com.lyq.bean;
/**
* 商品
* @author Li YongQiang
*
*/
public class Product {
public static final int PAGE_SIZE = 2;
// 编号
private int id;
// 名称
private String name;
// 价格
private double price;
// 数量
private int num;
// 单位
private String unit;
//getXXX()和setXXX()方法
五、处理分页条FindServlet
package com.control;
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.Product;
public class FindServlet extends HttpServlet {
public FindServlet() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
int currPage=1;//当前页为1
if(request.getParameter("page")!=null){
currPage=Integer.parseInt(request.getParameter("page"));
}
ProductControl pc=new ProductControl();//实例化ProductControl类,将调用里面的方法
List<Product> list=pc.FindPage(currPage);//调用Findpage()方法获得在ProductControl中查询的数据
request.setAttribute("list", list);
int count=pc.FindCount();//获得了总记录数
int pages;//总页数
//总页数=总记录/没有记录数
if(count%Product.PAGE_SIZE==0){
pages=count/Product.PAGE_SIZE;
}else {
pages=count/Product.PAGE_SIZE+1;//除不尽总页数就加1
}
//该方法的作用是追加内容到当前StringBuffer对象的末尾,类似于字符串的连接,调用该方法以后,StringBuffer对象的内容也发生改 变
StringBuffer sb=new StringBuffer();
for(int i=1;i<=pages;i++)
{
if(i==currPage)//判断是否当前页
{
sb.append("【"+i+"】");// 构建分页导航条
}
else
{
sb.append("<a href='FindServlet?page=" + i + "'>" + i + "</a>");//构建分页导航条,page传递的值
}
sb.append(" ");// 构建分页导航条
}
request.setAttribute("bar", sb.toString());// 将分页导航条的字符串放置到request中
request.getRequestDispatcher("product_list.jsp").forward(request, response);
}
public void init() throws ServletException {
// Put your code here
}
}
六、用于显示信息product_list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.util.List"%>
<%@page import="com.dao.*"%><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>所有商品信息</title>
<style type="text/css">
td{font-size: 12px;}
h2{margin: 0px}
</style>
</head>
<body>
<table align="center" width="450" border="1" height="180" bordercolor="white" bgcolor="black" cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="5">
<h2>所有商品信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1" >
<td><b>ID</b></td>
<td><b>商品名称</b></td>
<td><b>价格</b></td>
<td><b>数量</b></td>
<td><b>单位</b></td>
</tr>
<%
List<Product> list = (List<Product>)request.getAttribute("list");
for(Product p : list){
%>
<tr align="center" bgcolor="white">
<td><%=p.getId()%></td>
<td><%=p.getName()%></td>
<td><%=p.getPrice()%></td>
<td><%=p.getNum()%></td>
<td><%=p.getUnit()%></td>
</tr>
<%
}
%>
<tr>
<td align="center" colspan="5" bgcolor="white">
<%=request.getAttribute("bar")%>
</td>
</tr>
</table>
</body>
</html>