package util;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class DB {

    private DB() {

 

    }

 

    static {

       try {

           Class.forName("oracle.jdbc.OracleDriver");

       } catch (ClassNotFoundException e) {

           e.printStackTrace();

       }

    }

 

    public static Connection getConnection() {

       Connection con = null;

       try {

           con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","wt", "wt");

       } catch (SQLException e) {

           e.printStackTrace();

       }

       return con;

    }

 

    public static void closeConnection(Connection con) {

       if (con != null) {

           try {

              con.close();

              con = null;

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

    

    public static Statement getStatement(Connection con){

       Statement statement=null;

       try {

           statement=con.createStatement();

       } catch (SQLException e) {

           e.printStackTrace();

       }

       return statement;

    }   

    public static void closeStatement(Statement statement) {

       if (statement != null) {

           try {

              statement.close();

              statement = null;

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

    

    public static PreparedStatement getPreparedStatement(Connection con,String sql){

       PreparedStatement preparedStatement=null;

       try {

           preparedStatement=con.prepareStatement(sql);

       } catch (SQLException e) {

           e.printStackTrace();

       }

       return preparedStatement;

    }   

    /*

     * isGeneratedKey:是否获得记录的主键

     * Statement.RETURN_GENERATED_KEYS:是否获得记录的主键

     * */

    public static PreparedStatement getPreparedStatement(Connection con,String sql,boolean isGeneratedKey){ 

       PreparedStatement preparedStatement=null;

       try {

           if(isGeneratedKey==true){

              String generatedColumns[] = {"id"};  //该表的主键

              preparedStatement=con.prepareStatement(sql,generatedColumns);

           }

       } catch (SQLException e) {

           e.printStackTrace();

       }

       return preparedStatement;

    }

    public static void closePreparedStatement(PreparedStatement preparedStatement) {

       if (preparedStatement != null) {

           try {

              preparedStatement.close();

              preparedStatement = null;

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

    public static ResultSet executeQuery(Statement statement,String sql){

       ResultSet resultSet=null;

       try {

           resultSet=statement.executeQuery(sql);

       } catch (SQLException e) {

           e.printStackTrace();

       }

       return resultSet;

    }

    public static void executeUpdate(String sql){

       Connection con=DB.getConnection();

       PreparedStatement ps=DB.getPreparedStatement(con, sql);

       try {

           ps.executeUpdate(sql);

       } catch (SQLException e) {

           e.printStackTrace();

       }finally{

           DB.closePreparedStatement(ps);

           DB.closeConnection(con);

       }

    }

    public static void closeResultSet(ResultSet resultSet){

       if(resultSet!=null){

           try {

              resultSet.close();

               resultSet=null;

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

}

package dao;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import model.User;

import util.DB;

 

public class UserOracleDAO {

    

    public List<User> getPage(int pageNumber, int pageSize) {

       Connection con = DB.getConnection();

       String sql = "";

       if (pageNumber == 0) {  //如果第一页的话

           pageNumber = 1;

           sql += "select * from(select a.*, rownum r from( select  * from t_user) a where rownum <="

                  + pageSize + ") B where r >=" + pageNumber + "";

       } else {

           pageNumber += 1;

           sql += "select * from(select a.*, rownum r from( select  * from t_user) a where rownum <="

                  + pageNumber

                  * pageSize

                  + ") B where r >"

                  + ((pageNumber * pageSize) - pageSize) + "";

       }

       Statement st = null;

       ResultSet rs = null;

       List<User> users = new ArrayList<User>();

       try {

           st = con.createStatement();

           rs = st.executeQuery(sql);

           while (rs.next()) {

              User user = new User();

              user.setUsername(rs.getString("username"));

              user.setPassword(rs.getString("password"));

              users.add(user);

           }

       } catch (SQLException e) {

           e.printStackTrace();

       } finally {

           DB.closeResultSet(rs);

           DB.closeStatement(st);

           DB.closeConnection(con);

       }

       return users;

    }

 

    public int getCounts() {

       Connection con = DB.getConnection();

       Statement st = DB.getStatement(con);

       String sql = "select count(*) from t_user";

       ResultSet rs = null;

       int count = 0;

       try {

           rs = st.executeQuery(sql);

           if (rs.next()) {

              count = rs.getInt("count(*)");

           }

       } catch (SQLException e) {

           e.printStackTrace();

       }

       return count;

    }

 

    //获得首页

    public List<User> getFirstPage(int pageSize) {

       Connection con = DB.getConnection();

       int start=1;

       String sql = "select * from(select a.*, rownum r from( select  * from t_user) a where rownum <="

           + pageSize + ") B where r >=" +start+ "";

       Statement st = null;

       ResultSet rs = null;

       List<User> users = new ArrayList<User>();

       try {

           st = con.createStatement();

           rs = st.executeQuery(sql);

           while (rs.next()) {

              User user = new User();

              user.setUsername(rs.getString("username"));

              user.setPassword(rs.getString("password"));

              users.add(user);

           }

       } catch (SQLException e) {

           e.printStackTrace();

       } finally {

           DB.closeResultSet(rs);

           DB.closeStatement(st);

           DB.closeConnection(con);

       }

       return users;

    }

    //获得首页

    public List<User> getLastPage(int pageCount,int pageSize) {

       Connection con = DB.getConnection();

       int start=((pageCount*pageSize)-pageSize)+1;

       int end=(pageCount*pageSize)+1;

       String sql = "select * from(select a.*, rownum r from( select  * from t_user) a where rownum <="

           + end + ") B where r >=" +start+ "";

       Statement st = null;

       ResultSet rs = null;

       List<User> users = new ArrayList<User>();

       try {

           st = con.createStatement();

           rs = st.executeQuery(sql);

           while (rs.next()) {

              User user = new User();

              user.setUsername(rs.getString("username"));

              user.setPassword(rs.getString("password"));

              users.add(user);

           }

       } catch (SQLException e) {

           e.printStackTrace();

       } finally {

           DB.closeResultSet(rs);

           DB.closeStatement(st);

           DB.closeConnection(con);

       }

       return users;

    }

}

package model;

 

import java.io.Serializable;

 

public class User implements Serializable{

    private String username;

    private String password;

    public String getUsername() {

       return username;

    }

    public void setUsername(String username) {

       this.username = username;

    }

    public String getPassword() {

       return password;

    }

    public void setPassword(String password) {

       this.password = password;

    }

}

package servlet;

 

import java.io.IOException;

import java.util.List;

 

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

 

import model.User;

import dao.UserOracleDAO;

 

public class UserServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

 

    public void doGet(HttpServletRequest request, HttpServletResponse response)

           throws ServletException, IOException {

       doPost(request, response);

    }

 

    public void doPost(HttpServletRequest request, HttpServletResponse response)

           throws ServletException, IOException {

       String method=request.getParameter("method");

       UserOracleDAO uod=new UserOracleDAO(); 

       HttpSession session=request.getSession();

       int pageSize=5;

       int userSize=uod.getCounts(); //获得所有记录

        int pageCount=userSize/pageSize==0?userSize/pageSize:userSize/pageSize+1; //判断是否整除,页数是否是整数

       if(method.equals("first")){

           int currentPage=Integer.parseInt(request.getParameter("currentPage"));

           List<User> users=uod.getFirstPage(pageSize);

           session.setAttribute("list",users);

           session.setAttribute("currentPage",String.valueOf(currentPage));

           session.setAttribute("pageCount",String.valueOf(pageCount));

           getServletConfig().getServletContext().getRequestDispatcher("/list.jsp").forward(request,response);

           return;

       }

       if(method.equals("last")){

           int currentPage=pageCount;

           List<User> users=uod.getLastPage(pageCount, pageSize);

           session.setAttribute("list",users);

           session.setAttribute("currentPage",String.valueOf(currentPage));

           session.setAttribute("pageCount",String.valueOf(pageCount));

           getServletConfig().getServletContext().getRequestDispatcher("/list.jsp").forward(request,response);

           return;

       }

       if(method.equals("back")){

           int currentPage=Integer.parseInt(request.getParameter("currentPage"));

           if(currentPage!=0){

              currentPage--;

              List<User> users=uod.getPage(currentPage,pageSize);

              session.setAttribute("list",users);

              session.setAttribute("currentPage",String.valueOf(currentPage));

              session.setAttribute("pageCount",String.valueOf(pageCount));

               getServletConfig().getServletContext().getRequestDispatcher("/list.jsp").forward(request,response);

           }

           return;

       }

       if(method.equals("next")){

           int currentPage=Integer.parseInt(request.getParameter("currentPage"));

           currentPage++;

           List<User> users=uod.getPage(currentPage,pageSize);

           session.setAttribute("list",users);

           session.setAttribute("currentPage",String.valueOf(currentPage));

           session.setAttribute("pageCount",String.valueOf(pageCount));

           getServletConfig().getServletContext().getRequestDispatcher("/list.jsp").forward(request,response);

           return;

       }

    }

}

<?xml version="1.0" encoding="UTF-8"?>

<web-app version="2.5" 

    xmlns="http://java.sun.com/xml/ns/javaee" 

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 

    http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

  <servlet>

    <description>This is the description of my J2EE component</description>

    <display-name>This is the display name of my J2EE component</display-name>

    <servlet-name>UserServlet</servlet-name>

    <servlet-class>servlet.UserServlet</servlet-class>

  </servlet>

 

  <servlet-mapping>

    <servlet-name>UserServlet</servlet-name>

    <url-pattern>/UserServlet</url-pattern>

  </servlet-mapping>

  <welcome-file-list>

    <welcome-file>index.jsp</welcome-file>

  </welcome-file-list>

</web-app>

<%@ page language="java"

    import="java.util.*,model.User,dao.UserOracleDAO"

    pageEncoding="GB18030"%>

<%

    String path = request.getContextPath();

    String basePath = request.getScheme() + "://"

           + request.getServerName() + ":" + request.getServerPort()

           + path + "/";

%>

<%

    List<User> users = (List<User>) session.getAttribute("list");

    int currentPage=0;  //当前页

    int PageCount=0; //总页数

    int pageSize=5;  //每页显示数据数 

    if (users != null) {

       currentPage=Integer.parseInt((String)session.getAttribute("currentPage"));

       PageCount=Integer.parseInt((String)session.getAttribute("pageCount"));

    } else {

       currentPage=0;

       UserOracleDAO uod = new UserOracleDAO();

       users = uod.getFirstPage(pageSize);

       int userSize=uod.getCounts();

       PageCount=userSize/pageSize==0?userSize/pageSize:userSize/pageSize+1; //判断是否整除,页数是否是整数

    }

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

    <head>

       <base href="<%=basePath%>">

 

       <title>My JSP ‘index.jsp‘ starting page</title>

       <meta http-equiv="pragma" content="no-cache">

       <meta http-equiv="cache-control" content="no-cache">

       <meta http-equiv="expires" content="0">

       <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

       <meta http-equiv="description" content="This is my page">

       <!--

    <link rel="stylesheet" type="text/css" href="styles.css">

    -->

    </head>

 

    <body>

       <table border=‘1‘>

           <tr>

              <td>

                  用户名

              </td>

              <td>

                  密码

              </td>

           </tr>

           <%

              for (Iterator<User> it = users.iterator(); it.hasNext();) {

                  User user = it.next();

           %>

           <tr>

              <td><%=user.getUsername()%></td>

              <td><%=user.getPassword()%></td>

           </tr>

           <%

              }

           %>

           <tr>

              <td>

                  <a href="UserServlet?method=first&currentPage=0">首页</a>

                  <a href="UserServlet?method=back&currentPage=<%=currentPage%>">上一页</a>

                  <%if(currentPage==0){ %>

                  <a>1/<%=PageCount%></a>

                  <%}else if(currentPage==PageCount){%>

                      <a><%=PageCount%>/<%=PageCount%></a>

                  <%}else{ %>

                      <a><%=currentPage+1%>/<%=PageCount%></a>

                  <%}%>

                  <a href="UserServlet?method=next&currentPage=<%=currentPage%>">下一页</a>

                  <a href="UserServlet?method=last">尾页</a>

              </td>

           </tr>

 

       </table>

    </body>

</html>