java Web第三章学习内容（分页查询）

学习内容：
1.分页查询


一.分页查询

思路：1.创建连接数据库的实体类
          2.创建工具类和server类








参考代码：

Dao包下Userdao接口:

public interface Userdao {
    /*查询总记录数*/
    int selectCount();

    /*查询集合 */
    List<User> selectAllUser(Integer pageIndex, Integer pageSize);
}

Dao包下BaseDao::

public class BaseDao {

    private Connection conn;


    /*获取连接*/
    public Connection getConnection(){

        try {
            /*加载驱动*/
            Class.forName("com.mysql.jdbc.Driver");

            /*通过SM获取SQL数据库信息*/
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8", "root", "root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return conn;
    }

    /*增删改*/
    public int update(String sql,Object[] objects){
        int num=0;
        try {
            conn=getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            if (objects!=null&&objects.length>0){
                for (int i=0;i<objects.length;i++){
                    ps.setObject((i+1),objects[i]);
                }
            }
            num = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    /*查询*/
    public ResultSet getResultSet(String sql,Object[] objects){
        ResultSet rs=null;
        try {
            conn=getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            if (objects!=null&&objects.length>0){
                for (int i=0;i<objects.length;i++){
                    ps.setObject((i+1),objects[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }


    /*关闭资源*/
    public void  close(Connection conn,PreparedStatement ps,ResultSet rs){
        try {
            if (conn!=null){
                conn.close();;
            }
            if (ps!=null){
                ps.close();;
            }
            if (rs!=null){
                rs.close();;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

Dao包下-impl包下-UserDaoImpl

public class UserDaoImpl extends BaseDao implements Userdao {
    @Override
    public int selectCount() {
        int count=0;
        try {
            String sql ="select userNo from user";
            Object[] objects={};
            ResultSet rs = this.getResultSet(sql,objects);
            rs.last();
            count=rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    @Override
    public List<User> selectAllUser(Integer pageIndex, Integer pageSize) {
        List<User>userList =null;
        try {
            userList = new ArrayList<User>();
            String sql="select * from user limit ?,?";
            Object[] objects ={(pageIndex-1)*pageSize,pageSize};
            ResultSet rs =this.getResultSet(sql,objects);
            while (rs.next()){
                User user = new User();
                user.setUserNo(rs.getInt("userNo"));
                user.setUserName(rs.getString("userName"));
                user.setUserPWD(rs.getString("userPWD"));
                user.setUserPermission(rs.getString("userPermission"));
                userList.add(user);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userList;
    }
}

emtiy包下User:

public class User implements Serializable {
    private int userNo;
    private String userName;
    private String userPWD;
    private String userPermission;

    public int getUserNo() {
        return userNo;
    }
    public void setUserNo(int userNo) {
        this.userNo = userNo;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserPWD() {
        return userPWD;
    }
    public void setUserPWD(String userPWD) {
        this.userPWD = userPWD;
    }
    public String getUserPermission() {
        return userPermission;
    }
    public void setUserPermission(String userPermission) {
        this.userPermission = userPermission;
    }
}

Utils包下pageUtils：

public class PageUtils {
    /*第一个总记录数*/
    public Integer totalCount;
    /*当前页*/
    public Integer pageIndex;
    /*页量*/
    public Integer pageSize =2;
    /*总页数*/
    public Integer totalPageSiZe;
    /*数据量*/
    public List<User>list;

    public Integer getTotalCount() {
        return totalCount;
    }

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

    public Integer getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalPageSiZe() {
        return totalPageSiZe;
    }

    public void setTotalPageSiZe(Integer totalPageSiZe) {
        this.totalPageSiZe = totalPageSiZe;
    }

    public List<User> getList() {
        return list;
    }

    public void setList(List<User> list) {
        this.list = list;
    }
}

Server包下UserServer接口类：

public interface UserServer{
    /*查询总记录数*/
    int selectCount();
    /*查询集合*/
    List<User> selectAllUser(Integer pageIndex, Integer pageSize);
}

Server包下-impl 包下-UserServerImpl

public class UserServerImpl implements UserServer {
    private Userdao userdao = new UserDaoImpl();
    @Override
    public int selectCount() {
        return userdao.selectCount();
    }
    @Override
    public List<User> selectAllUser(Integer pageIndex, Integer pageSize) {
        return userdao.selectAllUser(pageIndex,pageSize);
    }
}

JSP首页（index.jsp）：

<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
 <table border="1">
<%
    PageUtils pu =(PageUtils) request.getAttribute("pageUtls");
    if (pu==null){
        request.getRequestDispatcher("doIndex.jsp").forward(request,response);
        return;
    }
    List<User>userList =pu.getList();
%>
   <tr>
     <td>编号</td>
     <td>用户名</td>
     <td>密码</td>
     <td>批注</td>
   </tr>

    <%
        for (int i =0;i<userList.size();i++){
    %>
     <tr>
         <td><%=userList.get(i).getUserNo()%></td>
         <td><%=userList.get(i).getUserName()%></td>
         <td><%=userList.get(i).getUserPWD()%></td>
         <td><%=userList.get(i).getUserPermission()%></td>
     </tr>
     <%
         }
     %>

 </table>
<%
    if (pu.getPageIndex()>1){
%>
 <a href="doIndex.jsp?pageIndex=1">首页</a>
 <a href="doIndex.jsp?pageIndex=<%=pu.getPageIndex()-1%>">上一页</a>
 <%
     }
 %>

 <%
     if (pu.getPageIndex()<pu.getTotalPageSiZe()){
 %>
 <a href="doIndex.jsp?pageIndex=<%=pu.getPageIndex()+1%>">下一页</a>
 <a href="doIndex.jsp?pageIndex=<%=pu.getTotalPageSiZe()%>">尾页</a>
  <%
      }
  %>
  </body>
</html>

JSP中转页(doindex.jsp)

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%
    request.setCharacterEncoding("utf-8");
    response.setCharacterEncoding("utf-8");

    String page1= request.getParameter("pageIndex");
    Integer pageIndex;
    if (page1==null){
        page1="1";
    }
    pageIndex = Integer.parseInt(page1);

    UserServer userserver = new UserServerImpl();

    PageUtils pageUtils = new PageUtils();
    /*赋值总页数*/
    pageUtils.setTotalCount(userserver.selectCount());
    /*赋值当前页*/
    pageUtils.setPageIndex(pageIndex);

    pageUtils.setPageSize(2);

    pageUtils.setList(userserver.selectAllUser(pageIndex,2));

    pageUtils.setTotalPageSiZe(userserver.selectCount() %2==0?userserver.selectCount()/2:userserver.selectCount()/2+1);

    request.setAttribute("pageUtls",pageUtils);
    request.setAttribute("pageP",pageUtils.getTotalPageSiZe());
    request.getRequestDispatcher("index.jsp").forward(request,response);
%>