JSP+Servlet+JdbcTemplate实现分页

数据库准备

建立users表

在工程中定义util包，对数据库进行配置配置（该工程使用Druid连接池，需在lib目录下导入对应的jar包）。
配置文件如图

public class DruidUtils {
    private static DataSource dataSource;
    static {
        Properties config = new Properties();
        try {
            config.load(DruidUtils.class.getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(config);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///usermanagerment?serverTimezone=GMT
username=root
password=
initialSize=5
maxActive=10
maxWait=3000

定义实体类

在domain包下定义实体类

//用户实体类

public class User {
    private Integer id;
    private String name;
    private String gender;
    private Integer age;
    private String address;
    private String qq;
    private String email;
    private String username;
    private String password;
    //定义构造方法，get/set方法
    ...
}

//分页实体类
public class PageBean<T> {
    //要显示的数据-数据库获取
    private List<T> data;
    //当前页码-前端获取
    private int page;
    //每页显示条数-前端获取
    private int pageSize;
    //一共的数据条数-数据库获取
    private int totalCount;
    //一共的页码数--计算获得
    private int totalPage;
    //定义构造方法，get/set方法
}

接收前端参数

在web包下定义PageListServlet

//在doGet方法实现
   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 1.接收请求(参数)
        String _page = request.getParameter("page");
        String _pageSize = request.getParameter("pageSize");
        //设置默认显示第一页，每页显示3条数据
        if (StringUtils.isEmpty(_page)) {
            _page = "1";
        }
        if (StringUtils.isEmpty(_pageSize)) {
            _pageSize = "3";
        }
        int page = Integer.parseInt(_page);
        int pageSize = Integer.parseInt(_pageSize);
        // 2.调用service
        PageBean<User> pageBean = userService.findPageBean(page, pageSize);
        // 3.指派jsp显示(转发/重定)
        request.setAttribute("pb", pageBean);
        request.getRequestDispatcher("/list.jsp").forward(request, response);
    }

业务实现

在service下的userService接口定义findPageBean方法，并在其实现类中实现方法

   @Override
    public PageBean<User> findPageBean(int page, int pageSize) {
        PageBean<User> pb = new PageBean<>();
        pb.setPage(page);
        pb.setPageSize(pageSize);
        //调用dao的方法，从数据库获取用户数据
        List<User> list = userDao.findByPage(page, pageSize);
        pb.setData(list);
        int count = userDao.findCount();
        pb.setTotalCount(count);
        int totalPage = (count + pageSize - 1) / pageSize;
        pb.setTotalPage(totalPage);
        return pb;
    }

sql方法

采用JdbcTemplate对数据进行封装。（使用前需导入jar包）

  @Override
    public List<User> findByPage(int page,int pageSize) {
        int startRow = (page-1)*pageSize;
        String sql = "select * from users limit ?,?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),startRow,pageSize);
    }

实现效果

页码切换

在JSP页面修改请求体。

<!--点击页吗切换,如果是当前页，则让链接失效-->
<c:forEach begin="1" end="${pb.totalPage}" var="i">
        <c:if test="${i==pb.page}">
          <li class="active"><span>${i}</span></li>
        </c:if>
        <c:if test="${i!=pb.page}">
          <li><a href="${pageContext.request.contextPath}/user/pageList?page=${i}">${i}</a></li>
        </c:if>
      </c:forEach>

<!--上一页，下一页切换-->
  <c:if test="${pb.page==1}">
            <li class="disabled"><a href="#" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>
        </c:if>
        <c:if test="${pb.page!=1}">
            <li>
                <a href="${pageContext.request.contextPath}/user/pageList?page=${pb.page-1}" aria-label="Previous">
                    <span aria-hidden="true">&laquo;</span>
                </a>
            </li>
        </c:if>

   <c:if test="${pb.page>=pb.totalPage}">
        <li class="disabled"><a href="#" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>
        </c:if>
        <c:if test="${pb.page<pb.totalPage}">
          <a href="${pageContext.request.contextPath}/user/pageList?page=${pb.page+1}" aria-label="Next">
            <span aria-hidden="true">&raquo;</span>
          </a>
        </c:if>

这样便可以切换页吗，显示不同的数据