八、JDBC——分页查询（mysql的limit方法）

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.Driverurl=jdbc:mysql://localhost:3306/cxg2850105498username=rootpassword=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>

源码下载

http://download.csdn.net/download/cxg200888/9982944