paging.java

新闻发布系统

package JavaBean;

import java.math.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DBOperation.DBConnection;

/**
 * 简单的翻页获取数据，数据存在属性lst(ArrayList)中
 * 需要继承类中的doSql()方法
 * @author cjylove
 *
 */
public abstract class Paging {
	protected int currentPage = 1;    //当前页
	protected int totalRows = 0;		//总数据条数
	protected int totalPage = 0;		//总页数
	protected int rowsPerPage = 5;	//每页的行数
	protected Object firstOfPage;	//当前页第一行数的编号
	protected Object lastOfPage; 	//当前页最后一行数据的编号
	protected String tableName;    //查询数据的表名
	protected String orderColunmName;     //排序字段名
	protected boolean hasCondition = false;		//是否带有约束
	
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	public int getRowsPerPage() {
		return rowsPerPage;
	}
	public void setRowsPerPage(int rowsPerPage) {
		this.rowsPerPage = rowsPerPage;
	}
	public int getTotalPage() {
		double arg0 = (double)this.getTotalRows() / (double)this.getRowsPerPage();
		this.totalPage = (int)Math.ceil(arg0);
		return totalPage;
	}
	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}
	public Object getFirstOfPage() {
		return firstOfPage;
	}
	public void setFirstOfPage(Object firstOfPage) {
		this.firstOfPage = firstOfPage;
	}
	public Object getLastOfPage() {
		return lastOfPage;
	}
	public void setLastOfPage(Object lastOfPage) {
		this.lastOfPage = lastOfPage;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public String getOrderColunmName() {
		return orderColunmName;
	}
	public void setOrderColunmName(String orderColunmName) {
		this.orderColunmName = orderColunmName;
	}
	public int getTotalRows() {
		if(this.totalRows == 0) {
			try {
				ResultSet rs = db.sta.executeQuery("select count(*) from FirstLevelTitle");
				int rows = 0;
				while(rs.next()){
					rows = rs.getInt(1);
				}
				this.totalRows = rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return totalRows;
	}
	public void setTotalRows(int maxRows) {
		this.totalRows = maxRows;
	}
	
	protected DBConnection db;		//数据库操作类
	protected ArrayList lst;     //需要返回的数据
	protected String SqlCondition = "";
	
	
	/**
	 * 不带SQL条件
	 * 需要参数：数据库名、表名、排序字段
	 * @param databaseName
	 * @param tableName
	 * @param orderColunmName
	 */
	public Paging(String databaseName,String tableName,String orderColunmName) {
		lst = new ArrayList();
		db = new DBConnection(databaseName);
		this.tableName = tableName;
		this.orderColunmName = orderColunmName;
	}
	
	/**
	 * 具有where条件语句
	 * @param databaseName
	 * @param tableName
	 * @param orderColunmName
	 * @param SqlCondition
	 */
	public Paging(String databaseName,String tableName,String orderColunmName,String SqlCondition) {
		this(databaseName,tableName,orderColunmName);
		this.SqlCondition = SqlCondition;
		this.hasCondition = true;
	}
	
	/**
	 * 获得当前页面的数据
	 * 只需传页码
	 * 和设置些其它属性
	 * @param page 
	 * @return
	 */
	public ArrayList getData(int page) {
		if(page==1)
			this.openPage();
		else if(page - currentPage > 0)
			this.downPage();
		else if(page - currentPage < 0)
			this.upPage();
		return lst;
	}
	
	/**
	 * 上翻一页
	 *
	 */
	public void upPage() {
		if(this.currentPage != 1)
		{
			String sql = null;
			if(this.hasCondition) {
				sql = "select * from (select  top "+this.rowsPerPage
				+" * from (select * from "+this.tableName+" "+this.SqlCondition+
				") as b where "+this.orderColunmName+" < '"+this.firstOfPage+"' order by "+
				this.orderColunmName+" desc) as c order by "+this.orderColunmName+" asc";
			} else {				
				sql = "select * from (select  top "+this.rowsPerPage
				+" * from "+this.tableName+" where "+this.orderColunmName+" < '"+this.firstOfPage+"' order by "+
				this.orderColunmName+" desc) as c order by "+this.orderColunmName+" asc";
			}
			ResultSet rs = this.db.executeQuery(sql);
			this.doSql(rs);
			this.currentPage -= 1;
		}
	}
	
	/**
	 * 下翻一页
	 *
	 */
	public void downPage() {
		if(this.currentPage != this.totalPage)
		{
			String sql = null;
			if(this.hasCondition) {
				sql = "select top "+this.rowsPerPage+
				" * from (select * from "+this.tableName+" "+this.SqlCondition+
				") as c where "+this.orderColunmName+" > '"+this.lastOfPage+
				"' order by "+this.orderColunmName+" asc";
			} else {
				sql = "select top "+this.rowsPerPage+
				" * from "+this.tableName+" where "+this.orderColunmName+
				" > '"+this.lastOfPage+"' order by "+this.orderColunmName+" asc";
			}
			ResultSet rs = this.db.executeQuery(sql);
			this.doSql(rs);
			this.currentPage += 1;
		}
	}
	
	/**
	 * 打开第一页
	 *
	 */
	public void openPage() {
		String sql = null;
		if(this.hasCondition) {
			sql = "select top "+this.rowsPerPage+
			" * from (select * from "+this.tableName+this.SqlCondition+
			") as c order by "+this.orderColunmName+" asc";
		} else {
			sql = "select top "+this.rowsPerPage+" * from "+this.tableName
			+" order by "+this.orderColunmName+" asc";
		}
		ResultSet rs = this.db.executeQuery(sql);
		this.doSql(rs);
		this.currentPage = 1;
	}
	
	/**
	 * 从数据库获取数据，对应的实体类一一赋值
	 * 先将lst(ArrayList)清空
	 * 然后放入属性lst(ArrayList)中
	 * 最后对属性firstOfPage和lastOfPage分别将第一行数据和最后行数据的排序字段的值赋给
	 * 
	 * @param sql
	 */
	abstract public void doSql(ResultSet rs);
}