通过Oracle数据库实现分页类

使用jdbc连接Oracle数据库实现分页类

导入ojdbc8.jar包

通过阿里提供的连接池连接数据库：需要导入druid-1.1.23.jar包，然后导入druid.properties文件

1.创建一个图书类

public class BookBean {
	//每页记录数
	public static final int PAGE_SIZE=2;
	//图书编号
	private int id;
	//图书名称
	private String name;
	//图书定价
	private double price;
	//数量
	private int bookCount;
	//作者
	private String author;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public int getBookCount() {
		return bookCount;
	}
	public void setBookCount(int bookCount) {
		this.bookCount = bookCount;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public static int getPageSize() {
		return PAGE_SIZE;
	}
	
	public BookBean() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public BookBean(int id, String name, double price, int bookCount, String author) {
		super();
		this.id = id;
		this.name = name;
		this.price = price;
		this.bookCount = bookCount;
		this.author = author;
	}
	@Override
	public String toString() {
		return "BookBean [id=" + id + ", name=" + name + ", price=" + price + ", bookCount=" + bookCount + ", author="
				+ author + "]";
	}
	

}

2.创建一个分页类，里面定义分页的属性

import java.sql.Connection;
import java.util.List;

public class MyPage<T>{
	//当前页
	private int currentPage;
	//每页条数
	private int pageSize=4;
	//分页数据
	//万能分页
	 //new MyPage<book>
	private List<T> content;
	//总条数
	private int totalCount;
	//总页码
	private int totalPage;
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public List<T> getContent() {
		return content;
	}
	public void setContent(List<T> content) {
		this.content = content;
	}
	public int getTotalCount() {
		return totalCount;
	}
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}
	public int getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}
	public MyPage() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "MyPage [currentPage=" + currentPage + ", pageSize=" + pageSize + ", content=" + content
				+ ", totalCount=" + totalCount + ", totalPage=" + totalPage + "]";
	}
	
	
	
}

3.连接数据库，分页查询，获取总记录数

import java.io.InputStream;
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 java.util.Properties;
import java.util.Scanner;

import javax.sql.DataSource;

import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestPage {
	public static void main(String[] args) {
		//模拟前端的参数 当前第几页
		//根据第几页  查询相对应的数据
		Scanner sc=new Scanner(System.in);
		System.out.println("请输入当前页：");
		int currentPage=sc.nextInt();
		System.out.println("每页条数：");
		int pageSize=sc.nextInt();
		setMyPage(currentPage,pageSize);
	}
	
	private static Connection getConnection() {
		Connection conn=null;
		try {
			Properties properties=new Properties();
			InputStream in=BookDao.class.getClassLoader().getResourceAsStream("druid.properties");
			properties.load(in);
			//获取连接池对象
			DataSource datasource=DruidDataSourceFactory.createDataSource(properties);
		   //获取连接
			conn=datasource.getConnection();
			//测试System.out.println(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	//封装MyPage对象
	public static void setMyPage(int currentPage,int pageSize) {
		//需要new MyPage对象，封装5个参数，并且返回
		MyPage<BookBean> page=new MyPage<>();
		page.setCurrentPage(currentPage);
		page.setPageSize(pageSize);
		//分页数据内容  
		/*
		 * 分页数据内容 
		 *  取决于 currentPage 和pageSize
		 *  start (currentPage-1)*pageSize+1
		 *  end currentPage*pageSize
		 * */
		int start=(currentPage-1)*pageSize+1;
		int end=currentPage*pageSize;
		page.setContent(getContent(start,end));
		System.out.println(page.getContent());
		//总条数
		int totalCount=getTotalCount();
		page.setTotalCount(totalCount);
		//总页数
		int totalPage=totalCount%pageSize==0?totalCount/pageSize:totalCount/pageSize+1;
		page.setTotalPage(totalPage);
		System.out.println(page.getTotalPage());
	}
	
	private static List<BookBean> getContent(int start, int end) {
				List<BookBean> list=new ArrayList<>();
				//获取连接
				Connection conn=null;
				Statement stmt=null;
				ResultSet rs=null;
				try {
					conn=getConnection();
					stmt=conn.createStatement();
				   //分页查询语句
				   String sql="select id,name,price,count,author"
				   		+ " from (select rownum r,id,name,price,count,author from tb_book)"
				   		+ "where r between "+start+" and "+end+"";
			  	//String sql ="select id,name,price,count,author from tb_Book";
					rs=stmt.executeQuery(sql);
					while(rs.next()) {
						BookBean b=new BookBean();
						b.setId(rs.getInt("id"));
						b.setName(rs.getString("name"));
					    b.setPrice(rs.getInt("price"));
					    b.setBookCount(rs.getInt("count"));
					    b.setAuthor(rs.getString("author"));
					    list.add(b);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if(rs!=null) {
						try {
							rs.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					if(stmt!=null) {
						try {
						   stmt.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					if(conn!=null) {
						try {
							conn.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				}
				return list;
	}
	private static int getTotalCount() {
		int count=0;
		Connection conn=getConnection();
		Statement stmt=null;
		ResultSet rs=null;
		String sql="select count(*) from tb_book";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				count=rs.getInt(1);
			}
			System.out.println(count);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	
}