MySql分页查询

limit     (pageSize-1)*每页显示的个数，每页显示的个数

SELECT id,NAME,typename,health,love,birthday FROM pet

WHERE owner_id=(SELECT id FROM petowner WHERE NAME=?)

ORDER BY id ASC LIMIT ? ,?";

DaoImpl层

public class PetDaoImpl extends BaseDao implements IPetDao {

    /**
     * 根据宠物主人查询其拥有的宠物信息
     * @param name 宠物主人姓名
     * @return List<Pet>
     */
    @Override
    public List<Pet> getPetByPoname(String poname,int startCount, int pageSize) {
        // TODO Auto-generated method stub
        List<Pet> petList = new ArrayList<Pet>();
        Pet pet = null;
        
        String sql="SELECT id,NAME,typename,health,love,birthday FROM pet WHERE owner_id=(SELECT id FROM petowner WHERE NAME=?) ORDER BY id  ASC LIMIT ? ,?";
        Object[] params= {poname,startCount,pageSize};
        res=super.excuteSelect(sql, params);
        try {
            while(res.next()){
                pet=new Pet();
                pet.setId(res.getInt("id"));
                pet.setName(res.getString("name"));
                pet.setTypename(res.getString("typename"));
                pet.setHealth(res.getInt("health"));
                pet.setLove(res.getInt("love"));
                pet.setBirthday(res.getDate("birthday"));
                //把宠物对象添加到集合中
                petList.add(pet);
                
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return petList;
    }

    /**
     * 根据宠物主人的名字，获取宠物的个数
     * @param poname
     * @return
     */
    @Override
    public int getCountByPoname(String poname) {
        // TODO Auto-generated method stub
        int count=0;
        String sql ="SELECT COUNT(1) FROM pet WHERE owner_id=(SELECT id FROM petowner WHERE name=?)";
        Object[] params= {poname};
        res=super.excuteSelect(sql, params);
        try {
            if(res.next()) {
                count=res.getInt(1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return count;
    }

}

Servlet层

@WebServlet("/searchPet.do")
public class SearchPetServlet extends HttpServlet {
    //
    IPetBiz petBiz=new PetBizImpl();

    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        HttpSession session=request.getSession();
        String poname=String.valueOf(session.getAttribute("poname2"));
        System.out.println(poname);
        
        //实现分页显示数据列表(1.页码变量，2.每页显示的条目数变量，3，总页数变量[通过数据的总行数与条目数计算])
        //注意点：用户第一次请求，不会在该servlet中不会接收到页面参数，所以会有空异常，如果有异常默认页码为1
        //创建页码变量
        int pageNo = 0;
        //创建页大小变量[显示的条目数]
        int pageSize = 3;
        //每页开始的编号
        int startCount=0;
        //创建数据的总行数变量[通过数据库聚合函数获得]
        int count=petBiz.getCountByPoname(poname);
        System.out.println("宠物的总个数："+count);
        
        //计算总页数
        int maxPage = 0;
        if(count%pageSize==0){
            maxPage = count/pageSize;
        }else{
            maxPage = count/pageSize+1;
        }
        //通过request接收页面传递过来的页码
        
        try {
            pageNo=Integer.parseInt(request.getParameter("pageNo"));
            startCount=(pageNo-1)*pageSize;
        } catch (NumberFormatException e) {
            startCount=0;
        }
        //根据宠物主人名，查询主人拥有的的宠物信息
        List<Pet> petList=new ArrayList<Pet>();
        petList=petBiz.searchPetByPetname(poname,startCount,pageSize);
        System.out.println("petList"+petList.size());
        //把集合放入request作用域
        request.setAttribute("petList",petList);
        request.setAttribute("pageNo", pageNo);
        request.setAttribute("maxPage", maxPage);
        //将数据列表放入Request作用域
        //创建转发对象
        RequestDispatcher rd = request.getRequestDispatcher("searchpet.jsp");
        rd.forward(request, response);
        
    }

}

jsp

    <table>
        <tr>
            <td colspan="4" >您的宠物列表如下：</td>
        </tr>
        <tr>
            <td>宠物名字</td>
            <td>宠物品种</td>
            <td>健康值</td>
            <td>亲密度</td>
            <td>出生日期</td>
        </tr>
        <c:forEach items="${petList}" var="pet">
            <tr>
                <td> ${pet.name}</td>
                <td> ${pet.typename}</td>
                <td> ${pet.health}</td>
                <td> ${pet.love}</td>
                <td> ${pet.birthday}</td>
                
            </tr>
        </c:forEach>
        <tr>
            <td>
                总页数：${maxPage }&nbsp;&nbsp;&nbsp;
                <c:if test="${pageNo>1}">
                <a href="searchPet.do?pageNo=${pageNo -1}">上一页</a>
                </c:if>
                <c:if test="${pageNo<requestScope.maxPage}">
                <a href="searchPet.do?pageNo=${pageNo +1}">下一页</a>
                </c:if>
            </td>
        </tr>
    </table>