MySql分页查询Web案例

- Mysql分页可使用Limit关键字进行分页查询

• 数据库原理

了解案例分页原理,即可开始下一步操作

案例分析

1. 我们可在实体类定义两个变量来接收数据

public class PageBean<T> {
    //    总记录数
    private int totalCount;
    //    当前页数据
    private List<T> rows;

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public List<T> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }
}

2. 定义实体类后,我们需要在BrandMapper类里面编写对应的mybatis方法实现数据接收

	  /**
     * 分页查询
     *
     * @param begin
     * @param size
     * @return
     */
    @Select("select * from  tb_brand limit #{begin},#{size}")
    List<Brand> selectByPage(@Param("begin") int begin, @Param("size") int size);

    /**
     * 查询总记录数
     *
     * @return
     */
    @Select("select count(*) from tb_brand")
    int selectTotalCount();
    

3. 在BrandService接口中定义分页查询方法

 /**
     * 分页查询
     *
     * @param currentPage 当前页码
     * @param pageSize    每页展示条数
     * @return
     */
    PageBean<Brand> selectByPage(int currentPage, int pageSize);
    

4. 然后在BrandServiceimpl类里实现BrandServiceimpl接口的BrandServiceimpl方法

     @Override
    public PageBean<Brand> selectByPage(int currentPage, int pageSize) {
//        2.获取sqlsession对象
        SqlSession sqlSession = factory.openSession();
//        3.获取BrandMapper
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//        4.计算开始的索引
        int begin = (currentPage - 1) * pageSize;
//        计算查询条目数
        int size = pageSize;
//        5.查询当前页数据
        List<Brand> rows = mapper.selectByPage(begin, size);
//        6.查询总记录数
        int tocalCount = mapper.selectTotalCount();
//        7.封装PageBean对象
        PageBean<Brand> pageBean = new PageBean<>();
        pageBean.setRows(rows);
        pageBean.setTotalCount(tocalCount);
//        8.释放资源
        sqlSession.close();
        return pageBean;
    }

5. 写个BaseServlet类实现servlet优化

/**
 * 替换HttpServlet,根据请求的最后一段路径进行方法分发
 */
public class BaseServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        1.获取请求路径
        String url = req.getRequestURI();
//        2.获取最后一段路径.方法名
        int index = url.lastIndexOf('/');
        String methodName = url.substring(index + 1);
//        2.1获取BeandServlet / UserServlet 字节码对象 Class
        Class<? extends BaseServlet> aClass = this.getClass();
//        2.2获取方法 Method对象

        try {
            Method method = aClass.getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
//            2.3执行方法
            method.invoke(this, req, resp);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }
}

5. 再去Select层里写个BrandServlet类继承BaseServlet类

  public class BrandServiceimpl implements BrandService {
    SqlSessionFactory factory = SqlSessionFactoryUtils.getSqlSessionFactory();
    @Override
    public PageBean<Brand> selectByPage(int currentPage, int pageSize) {
//        2.获取sqlsession对象
        SqlSession sqlSession = factory.openSession();
//        3.获取BrandMapper
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//        4.计算开始的索引
        int begin = (currentPage - 1) * pageSize;
//        计算查询条目数
        int size = pageSize;
//        5.查询当前页数据
        List<Brand> rows = mapper.selectByPage(begin, size);
//        6.查询总记录数
        int tocalCount = mapper.selectTotalCount();
//        7.封装PageBean对象
        PageBean<Brand> pageBean = new PageBean<>();
        pageBean.setRows(rows);
        pageBean.setTotalCount(tocalCount);
//        8.释放资源
        sqlSession.close();
        return pageBean;
    }
}

• 未完待续…