mySql分页

Sql 分页查询语句： select * from goods limit ?,?

limit 开始查询位置（从0开始，0对应数据库中的第一条数据），查询记录的条数

GoodsDao.java:

//每页记录数
    private int pagesize = 6;
//根据page查询该页的记录
    public List<Goods> findByPage(int page) throws SQLException {
        if(page < 1){
            page = 1;
        }
        int offset = 0;
        String sql = "select * from tdb_goods limit ?,?";
        //QueryRunner Dbutil 数据库链接池 提供的对象
        //JDBCUtil_DBCP.getDataSource() 通过工具类得到一个数据源
        QueryRunner queryRunner = new QueryRunner(JDBCUtil_DBCP.getDataSource());
        Object[] params = {offset + (page - 1) * pagesize,pagesize};
        List<Goods> list = null;
        try {
            list = (List<Goods>) queryRunner.query(sql, new BeanListHandler(Goods.class),params);
        } catch (SQLException e) {
            throw new SQLException("查找失败");
        }
        return list;
    }
    //查询记录数并返回最大页数
    public int maxpage(){
        String sql = "select count(*) from tdb_goods";
        QueryRunner queryRunner = new QueryRunner(JDBCUtil_DBCP.getDataSource());
        int maxpage = 1;
        try {
            int count = Integer.parseInt(((Long)(((Object[]) queryRunner.query(sql, new ArrayHandler()))[0])).toString());
            maxpage = count % pagesize == 0? count / pagesize : count / pagesize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maxpage;
    }

Service.java:

public class Service {
    GoodsDao goodsDao =new GoodsDao();//根据页数查询数据
    public List<Goods> show_goos(int page){
        try {
            return goodsDao.findByPage(page);
        } catch (SQLException e) {
            System.out.println(e.getMessage());;
        }
        return null;
    }
    //获得最大页数
    public int getmaxPage() {
        return goodsDao.maxpage();
    }
}

Servlet.java:

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;

import com.hyl.bean.Goods;
import com.hyl.service.Service;

public class MyServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        Service service = new Service();
        //判断页面传过来的page数
        int maxpage = service.getmaxPage();
        if(req.getParameter("page")==null){
            //设置初始默认第一页
            req.setAttribute("page", 1); 
        }else if(Integer.parseInt(req.getParameter("page"))>maxpage){
            //设置默认最大为maxpage
            req.setAttribute("page", maxpage);
        }else if(Integer.parseInt(req.getParameter("page"))==0){
            //设置设置默认最大为maxpage
            req.setAttribute("page", maxpage);
        }else{
            //设置当前页数(便于jsp中给当前页设置特殊属性)
            req.setAttribute("page", Integer.parseInt(req.getParameter("page"))); 
        }
        List<Goods> list = service.show_goos(Integer.parseInt(req.getAttribute("page").toString()));
        req.setAttribute("maxpage", maxpage); 
        req.setAttribute("list", list); 
        getServletContext().getRequestDispatcher("/goods.jsp").forward(req, resp);

    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        doPost(req, resp);
    }
}

用于展示数据的jsp页面：goods.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<title>goods page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">

</head>
<body>
    <table border="1px">
        <tr>
            <td>编号</td>
            <td>商品</td>
            <td>类型</td>
            <td>厂商</td>
            <td>价格</td>
            <td>是否出售</td>
            <td>是否售空</td>
        </tr>
        <c:forEach var="g" items="${list}">
            <tr>
                <td>${g.goods_id}</td>
                <td>${g.goods_name}</td>
                <td>${g.goods_cate}</td>
                <td>${g.brand_name}</td>
                <td>${g.goods_price}</td>
                <td>${g.is_show}</td>
                <td>${g.is_saleoff}</td>
            </tr>
        </c:forEach>
    </table>
    <c:if test="${page!=1}">
        <a href="myservlet?page=1">首页</a>
    </c:if>
    <c:if test="${page>1}">
        <a href="myservlet?page=${page-1 }">上一页</a>
    </c:if>
        <c:forEach begin="1" step="1" end="${maxpage }" var="a">
            <c:choose>
                <c:when test="${page==a}">
                    <a href="myservlet?page=${a}" style="color: red">${a}</a>&nbsp;
                </c:when>
                <c:otherwise>
                    <a href="myservlet?page=${a}">${a}</a>&nbsp;
                </c:otherwise>
            </c:choose>
        </c:forEach>
        
    <c:if test="${page<maxpage}">
        <a href="myservlet?page=${page+1 }">下一页</a>
    </c:if>
    <c:if test="${page!=maxpage}">
        <a href="myservlet?page=${maxpage}">尾页</a>
    </c:if>
</body>
</html>

注：servlet与jsp页面传值过程中变量名要一致（细节问题）

补：EL表达式中c:forEach中的items不可为单一的一个对象或是基本数据，会报错

结果如下：

mySql分页

Over

相关推荐