分页实现

我就是我 2022-07-12 12:39 254阅读 0赞

今天来说一下分页实现，我用的是mysql、jdbc、jsp去实现分页。  
mysql使用limit而oracle使用的的是top去实现分页。

首先是定义一个商品类，下面是代码：

/** * 商品类 * @author chenjingbin * */
    public class Product { 
        /** * 页面大小 */
        public static  final int PAGE_SIZE = 3;
        /** * 商品id号 */
        private int id ; 
        /** * 商品价格 */
        private double price;
        /** * 商品数量 */
        private int num ; 
        /** * 商品名称 */
        private String  name ;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public double getPrice() {
            return price;
        }
        public void setPrice(double price) {
            this.price = price;
        }
        public int getNum() {
            return num;
        }
        public void setNum(int num) {
            this.num = num;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        @Override
        public String toString() {
            return "Product [id=" + id + ", price=" + price + ", num=" + num + ", name=" + name + "]";
        }
    
    
    
    }

然后在写DAO类：里面的一些配置参数可以放到一个配置文件里面去写的，不只是我这种写法的。

/** * ProductDao 主要用于封装数据库一些操作 * @author chenjingbin * */
    public class ProductDao { 
    
        private static final String  URL ="jdbc:mysql://localhost:3306/java";
        private static  final String  DRIVER="com.mysql.jdbc.Driver";
        private static final String NAME="root";
        private static final String PWD="111111";
        static{
            try {
                Class.forName(DRIVER);
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        /** * 得到Connection对象函数 * @return */
        public static  Connection getConnection(){
            Connection connection  = null;
            try {
                connection = DriverManager.getConnection(URL, NAME, PWD);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    
            return connection;
        }
        /** * 关闭输入流 * @param connection * @param statement * @param resultSet */
        public static  void close(Connection connection,PreparedStatement preparedStatement ,ResultSet resultSet){
            if(resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(preparedStatement!= null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        /** * 分页查询所有商品信息 * @param page 页数 * @return List<Product> */
        public List<Product> find (int page){
            String sql = "select id,price ,num,name from product order by id desc limit ?,?";
            List<Product > list  = new ArrayList<Product>();
            Connection connection = getConnection();
            PreparedStatement prepareStatement = null ;
            ResultSet resultSet = null; 
            try {
                prepareStatement = connection.prepareStatement(sql);
                prepareStatement .setInt(1, (page-1)*Product.PAGE_SIZE);
                prepareStatement.setInt(2, Product.PAGE_SIZE);
                resultSet  = prepareStatement.executeQuery();
                while(resultSet.next()){
                    Product product = new Product();
                    product.setId(resultSet.getInt("id"));
                    product.setName(resultSet.getString("name"));
                    product.setPrice(resultSet.getDouble("price"));
                    product.setNum(resultSet.getInt("num"));
                    list.add(product);
                }
    
                } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                close(connection, prepareStatement, resultSet);
            }
            return list;
        }
        /** * 查询总记录数 * @return count */
        public int findCount(){
            int count = 0; 
            Connection connection = getConnection();
            String sql = "select count(*) from product ";
            Statement statement = null; 
            ResultSet resultSet = null; 
            try {
                statement = connection.createStatement();
                resultSet = statement.executeQuery(sql);
                while(resultSet.next()){
                    count = resultSet.getInt(1);
                }
    
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                if(connection!= null)
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                if(statement!= null){
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                if(resultSet!= null){
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
            return count;
        }
    }

下面是写servlet类：

import java.io.IOException;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    
    
    public class FindServlet extends HttpServlet { 
        private static final long serialVersionUID = 1L;
    
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                int countpage = 1; //当前码数
                if(request.getParameter("page") != null){
                    countpage = Integer .parseInt(request.getParameter("page"));
                }
                ProductDao productDao = new ProductDao();
                List<Product> list = productDao.find(countpage);
                request.setAttribute("list", list);
                int page ; //总页数
                int count  = productDao.findCount();//查询总记录数
                if(count%Product.PAGE_SIZE == 0){
                    page = count /Product.PAGE_SIZE;
                }else{
                    page = count /Product.PAGE_SIZE +1;
                }
                StringBuffer  stringBuffer = new StringBuffer();
                for (int i = 1; i <= page; i++) {
                    if(i == countpage){
                        stringBuffer.append("["+i+"]");
                    }
                    else{
                        stringBuffer.append("<a href='/webDay01/Find?page="+i+"'>"+i+"</a>");//构建分页条
                    }
                    stringBuffer.append("   ");
                }
            request.setAttribute("bar", stringBuffer.toString());
            request.getRequestDispatcher("product_list.jsp").forward(request, response);
        }
    
        /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // TODO Auto-generated method stub
            this .doGet(request, response);
        }
    
    }

接下来是product\_list.jsp页面：

<%@page import="com.web.page.Product"%>
    <%@page import="java.util.List"%>
    <%@ 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">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
    </head>
    <body>
        <table align="center" width=" 800" border="1">
            <tr>
                <td align="center" colspan="4">
                    <h3>所有商品信息</h3>
                </td>
            </tr>
            <tr>
                <td >
                    商品id
                </td>
                <td >
                    商品名
                </td>
                <td >
                    商品价格
                </td>
                <td >
                    商品数量
                </td>
            </tr>
            <% List<Product> list = (List<Product>)request.getAttribute("list"); for(Product p :list){ %>  
    
                <tr >
                    <td><%= p.getId() %></td>
                    <td><%= p.getName() %></td>
                    <td><%= p.getPrice() %></td>
                    <td><%= p.getNum() %></td>
                </tr>
            <%} %>
            <tr>
                <td align="center" colspan="4">
                    <%=request.getAttribute("bar") %>
                </td>
            </tr>
        </table>
    
    </body>
    </html>

剩下index.jsp页面

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" import="java.util.*"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Insert title here</title>
    </head>
     <body>
      <a href="/webDay01/Find">查询所有商品信息</a>
    </body>
    </html>

最终效果图如下：  
![效果图][SouthEast]  
![效果图2][2]

总结一下：第一，在跳转servlet的时候忘记添加上项目名而出现了404错误，第二，粗心，通过request 返回我需要的对象的时候我打成request.getParameter(“”);这里应该是request.getAttribute(“”);才对的。

[SouthEast]: /images/20220710/92aac5a4c67042f1bbb12ae43d03f9b8.png
[2]: /images/20220710/39924804552846f197993dad8210b5ad.png