后台管理之:分页显示用户信息

UserManagement.jsp主页面：

<%
            request.setCharacterEncoding("utf-8");
            response.setCharacterEncoding("utf-8");
           int pageNow;
           String s_pageNow=request.getParameter("pageNow");
      if(s_pageNow!=null){
              pageNow=Integer.parseInt(s_pageNow);
           }else{
              pageNow=1;
            }
      UserBeanCl ubc=new UserBeanCl();
      int pageCount=ubc.getPageCount();
      ArrayList al= ubc.getUserByPage(pageNow);


      //显示
      %>
                       
         
        
       
         <table width="800px"  border="1" style="margin-top:50px;font-size:18px;text-align:center" >
             <tr height="36px">
                  <td width="4%">用户名</td>
                  <td width="5%">密码</td>
                  <td width="4%">email</td>
                  <td width="1%">性别</td>
                  <td width="1%">年龄</td>
                  <td width="5%">操作</td>
             </tr>
             
              <%
      for(int i=0;i<al.size();i++){
      Customer cust=(Customer)al.get(i);
      %>
      
      <tr>
          <td height="32px"><%=cust.getCustomerName()%></td>
          <td><%=cust.getCustomerPassword()%></td>
          <td><%=cust.getCustomerEmail()%></td>
          <td><%=cust.getCustomerSex()%></td>
          <td><%=cust.getCustomerAge()%></td>
          <td>
              <a href="updateUser.jsp?username=<%=cust.getCustomerName()%>&password=<%=cust.getCustomerPassword()%>
              &email=<%=cust.getCustomerEmail()%>&sex=<%=cust.getCustomerSex()%>&age=<%=cust.getCustomerAge() %>">修改</a>
              <a href="UsersClServlet?flag=delUser&username=<%=cust.getCustomerName()%>">删除</a>
          </td>
      </tr>
      
    <%} %>  
      
         </table>
     
       </form>
      
      
         <span style="margin-right:10px">共有 <%=pageCount%>页</span> 
         <span style="margin-right:100px">当前第<%=pageNow %>页</span>
      <%
      //上一页
       if(pageNow!=1){
       out.println("<a href=ShowCustomerServlet?pageNow="+(pageNow-1)+">上一页</a>");
       }
       //显示超链接
       for(int i=1;i<=pageCount;i++){
        out.println("<a href=ShowCustomerServlet?pageNow="+i+">["+i+"]</a>");
       }
       //下一页
       if(pageNow!=pageCount){
       out.println("<a href=ShowCustomerServlet?pageNow="+(pageNow+1)+">下一页</a>");
       } 
     %>      

     

ShowCustomerServlet页面：

protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {


String s_pageNow=request.getParameter("pageNow");
int pageNow=Integer.parseInt(s_pageNow);

//调用UserBeanCl

try {

UserBeanCl ubc=new UserBeanCl();
ArrayList al=ubc.getUserByPage(pageNow);   //得到当前页的所有对象
int pageCount=ubc.getPageCount();          //得到总页数
request.setAttribute("result", al);        //将al、pagecount放入request中
request.setAttribute("pageCount", pageCount);
request.getRequestDispatcher("UserManagement.jsp").forward(request,response);
} catch (Exception e) {

e.printStackTrace();
}

}


@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

     doGet(request, response);
}

UserBeanCl：

        private Statement stmt=null;
private ResultSet rs=null;
private Connection conn=DatabaseBean.getConn();
int pageCount=0;
int pageSize=1;
int rowCount=0;

       public ArrayList getUserByPage(int pageNow){

ArrayList al=new ArrayList();
try {

stmt=conn.createStatement();
String sql="select top "+pageSize+"* from Customer where CustomerName not in (select top "
    +pageSize*(pageNow-1)+" CustomerName from Customer)";

         rs=DatabaseBean.getResultset(stmt, sql);
   //封装成arraylist
    
    while (rs.next()) {
Customer cust=new Customer();
cust.setCustomerName(rs.getString(1));
cust.setCustomerPassword(rs.getString(2));
cust.setCustomerEmail(rs.getString(3));
cust.setCustomerSex(rs.getString(8));
cust.setCustomerAge(rs.getString(7));
al.add(cust);
}
    
} catch (Exception e) {

e.printStackTrace();
}finally{
DatabaseBean.closeRs(rs);
DatabaseBean.closeStmt(stmt);
DatabaseBean.closeConn(conn);
}
return al;
}


public int getPageCount(){


try {
stmt=conn.createStatement();
    String sqlString="select count(*) from Customer";
    rs=DatabaseBean.getResultset(stmt, sqlString);
    if (rs.next()) {
rowCount=rs.getInt(1);//     获取到 count(*)的具体数值

}
} catch (Exception e) {

e.printStackTrace();
}finally{
DatabaseBean.closeRs(rs);
DatabaseBean.closeStmt(stmt);
DatabaseBean.closeConn(conn);
}

if (rowCount%pageSize==0) {
pageCount=rowCount/pageSize;
}else {
pageCount=rowCount/pageSize+1;
}
return pageCount;
}

DatabaseBean：

public static  Connection getConn(){
Connection conn=null;
try{
String driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=Sweet";
String username="sa";
String password="123456";
Class.forName(driverClass);
conn=DriverManager.getConnection(url, username, password);
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
return conn;
}
public static Statement getStatement(Connection conn){
Statement stmt=null;
try {
if(conn != null) {
stmt = conn.createStatement();
}
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}

public static ResultSet getResultset(Statement stmt,String sql){
ResultSet rs=null;
try{
if(stmt!=null&&sql!=null){
rs=stmt.executeQuery(sql);
}
}
catch(SQLException e){
e.printStackTrace();
}
 
return rs;
}

public static int getUpdate(Statement stmt,String sql){
int rtn=0;
try{
if(stmt!=null)
{
rtn=stmt.executeUpdate(sql);
}
}
catch(SQLException e){
e.printStackTrace();
}
 
return rtn;
}
 
 

 
public static void closeConn(Connection conn) {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void closeStmt(Statement stmt) {
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



public static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}