Oracle+Jsp分页

2021-11-12 02:41:12

分页原理：

从jsp页面传到servlet请求中,可以获得当前点击的页数,第一次进入为首页,通过在servlet中获得的当前页数,并且设计一次性显示的内容数,就是几条信息,

并且从dao层查询到数据库中总记录数,就可以得到总的多少页,把这些信息封装到PageBean对象中即可。

// 将分页组件保存到session中,到页面上遍历显示

// 将当前页要显示的数据集合保存到session中,到页面上遍历显示

// 将分页类对象保存到session中,到页面使用来做分页显示

// 重定向到显示页面

返回jsp页面遍历显示

操作步骤：

1.点击查询所有用户信息。

2.页面显示第一页的查询信息,以百度搜索的分页,当点击第六页是,显示的页面顺序变化,当为第一页时,第一页超链接隐藏,当点击最后一页时,下一页超链接隐藏。

设计Oracle数据库：表名test2,只有两个字段,name和password,插入数据

select.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style>
p {
    height: 100px;
    width: 400px;
    margin: 140px auto;
}

p a {
    font-size: 30px;
    text-decoration: none;
}
</style>
</head>
<body bgcolor="#abcdef">
    <p>
        <a href="userlist">点击查询所有用户信息</a>
    </p>
</body>
</html>

UserListServlet.java

package com.auuzee.servlet;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.auuzee.common.PageBean;
import com.auuzee.dao.UserDao;
import com.auuzee.entity.User;

@WebServlet("/userlist")
public class UserListServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    UserDao oDao = new UserDao();

    public UserListServlet() {
        super();
    }

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

    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        System.out.println("进入servlet");
        // 创建PageBean分页类的对象
        PageBean pg = new PageBean();
        // 从页面请求中获得当前页
        String str = request.getParameter("currentPage");
        // 判断当前页是否存在，区分是第一次访问还是通过分页超链接访问
        // 第一次访问时设定当前页为第一页
        int currentPage = 0;
        if (str == null || "".equals(str)) {
            currentPage = 1;
        } else {
            currentPage = Integer.parseInt(str);
        }
        // 设定分页类的每页显示记录数
        pg.setPageSize(10);

        // 获取总记录数
        int size = oDao.selectUsersSize();
        System.out.println("总记录数是：" + size + " 当前页是：" + currentPage);
        // 设定分页类的总记录数
        pg.setTotalCount(size);

        // 设定分页类的当前页
        pg.setCurrentPage(currentPage);
        // 通过数据库分页的方式取得当前页要显示的数据

        // 参数为当前页开始记录索引和每页显示记录数
        List<User> userList = oDao.selectAllUsers(currentPage, pg.getPageSize());
        // 设定分页组件（1,2,3,4,5,6等）
        List<Integer> indexList = new ArrayList<Integer>();
        for (int i = 0; i < pg.getTotalPages(); i++) {
            indexList.add(i + 1);
        }
        // 将分页组件保存到session中，到页面上遍历显示
        request.getSession().setAttribute("il", indexList);
        // 将当前页要显示的数据集合保存到session中，到页面上遍历显示
        request.getSession().setAttribute("ol", userList);
        // 将分页类对象保存到session中，到页面使用来做分页显示
        request.getSession().setAttribute("pb", pg);
        // 重定向到显示页面
        response.sendRedirect("jsp/userlist.jsp");
    }

}

User.java

package com.auuzee.entity;

public class User {

    private String name;
    private String password;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

Config.java

package com.auuzee.dbutil;

import java.io.IOException;
import java.util.Properties;

public class Config {

    private static Properties pro = new Properties();

    static {
        try {
            // 通过反射的方式加载资源文件
            // 只要资源文件和Config类在同一个路径下就能找到并加载到Properties对象中，以map的形式存放
            pro.load(Config.class.getResourceAsStream("db.properties"));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static final String DBURL = pro.getProperty("DBURL");
    public static final String DBIP = pro.getProperty("DBIP");
    public static final String DBPORT = pro.getProperty("DBPORT");
    public static final String DATABASE = pro.getProperty("DATABASE");
    public static final String DBUSER = pro.getProperty("DBUSER");
    public static final String DBPASS = pro.getProperty("DBPASS");
    public static final String DBDRIVER = pro.getProperty("DBDRIVER");
}

DBUtil.java

package com.auuzee.dbutil;

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 DBUtil {

    // 数据库连接字符串
    private String url = Config.DBURL + Config.DBIP + Config.DBPORT + Config.DATABASE;
    // 数据库连接用户名
    private String user = Config.DBUSER;
    // 数据库连接密码
    private String password = Config.DBPASS;

    private Connection con = null;
    private PreparedStatement pst = null;
    private Statement st = null;
    private ResultSet rs = null;

    // 获取连接
    private void getConn() {
        try {
            // 加载驱动
            Class.forName(Config.DBDRIVER);
            // 建立连接
            con = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /*
     * 共通查询方法 params：Strng sql：需要执行的SQL语句 Object[] param：需要替换占位符的参数数组
     * return：rs：执行查询得到的结果集
     */
    public ResultSet select(String sql, Object[] param) {
        // 调用共通方法获取连接
        getConn();
        try {
            // 创建Statement对象
            pst = con.prepareStatement(sql);
            // SQL占位符替换
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pst.setObject(i + 1, param[i]);
                }
            }
            // 执行SQL
            rs = pst.executeQuery();
            return rs;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    /*
     * 共通更新方法 params：Strng sql：需要执行的SQL语句 Object[] param：需要替换占位符的参数数组
     * return：count：执行更新得到的影响行数
     */
    public int update(String sql, Object[] param) {
        // 调用共通方法获取连接
        getConn();
        try {
            // 创建Statement对象
            pst = con.prepareStatement(sql);
            // SQL占位符替换
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pst.setObject(i + 1, param[i]);
                }
            }
            // 执行SQL
            int count = pst.executeUpdate();
            return count;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return -1;
        }
    }

    /*
     * 共通批处理方法 params：String[] sql：需要执行的SQL语句数组 return：counts：执行批处理得到的影响行数数组
     */
    public int[] batch(String[] sql) {
        // 调用共通方法获取连接
        getConn();
        try {
            // 创建Statement对象
            st = con.createStatement();
            // 添加批处理SQL
            for (String s : sql) {
                st.addBatch(s);
            }
            // 执行批处理
            int[] counts = st.executeBatch();
            return counts;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    // 关闭资源
    public void close() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (pst != null) {
                pst.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

db.properties

DBURL=jdbc:oracle:thin:@
DBIP=localhost
DBPORT=:1521
DATABASE=:BANK
DBUSER=system
DBPASS=123456
DBDRIVER=oracle.jdbc.driver.OracleDriver

UserDao.java

package com.auuzee.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.auuzee.dbutil.DBUtil;
import com.auuzee.entity.User;

public class UserDao {
    DBUtil dbu = new DBUtil();

    // 查询订单总记录数
    public int selectUsersSize() {
        String sql = "select count(*) from test2";
        ResultSet rs = dbu.select(sql, null);
        int size = 0;
        try {
            while (rs.next()) {
                size = rs.getInt(1);
            }
            return size;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            dbu.close();
        }
    }

    // 查询所有订单方法
    public List<User> selectAllUsers(int currentPage, int pageSize) {
        String sql = "select * from (select a1.*,rownum rn from"
                + "(select * from test2) a1 where rownum <=?) where rn>=?";
        Object[] param1;
        if (currentPage == 1) {
            Object[] param = { pageSize, 1 };
            param1 = param;
        } else {
            Object[] param = { currentPage * pageSize, (currentPage - 1) * pageSize + 1 };
            param1 = param;
        }
        ResultSet rs = dbu.select(sql, param1);
        List<User> userList = new ArrayList<User>();
        try {
            while (rs.next()) {
                User user = new User();
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                userList.add(user);
            }
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            dbu.close();
        }
    }
}

PageBean.java

package com.auuzee.common;

public class PageBean {

    // 总记录数
    private int totalCount;
    // 每页显示记录数
    private int pageSize = 5;
    // 总页数
    private int totalPages;
    // 当前页
    private int currentPage;
    // 当前页开始记录索引
    private int startIndex;
    // 当前页结束记录索引
    private int endIndex;

    public int getTotalCount() {
        return totalCount;
    }

    // 设定总记录数并计算总页数
    public void setTotalCount(int totalCount) {
        if (totalCount != 0) {
            this.totalCount = totalCount;
            if (this.totalCount % pageSize == 0) {
                totalPages = this.totalCount / pageSize;
            } else {
                totalPages = this.totalCount / pageSize + 1;
            }
        } else {
            totalPages = 1;
        }
    }

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

    public int getPageSize() {
        return this.pageSize;
    }

    // 计算总页数
    public int getTotalPages() {
        return totalPages;
    }

    // 设置当前页，计算开始和结束记录索引
    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
        if (this.currentPage < 1) {
            this.currentPage = 1;
        }
        if (this.currentPage > totalPages) {
            this.currentPage = totalPages;
        }
        startIndex = (this.currentPage - 1) * pageSize;
        endIndex = this.currentPage * pageSize;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public int getStartIndex() {
        return startIndex;
    }

    public int getEndIndex() {
        return endIndex;
    }
}

码农公寓

相关文章