canofy

package cn.yicha.advertiser.model;

/**
 * yicha Jun 28, 2008 分页使用
 */

public class Pagination {
	private int start; // 当前页是从哪条记录开始的
	private int end; // 当前页是从哪条记录结束的
	private int currentPage;// 当前页
	private int pageSize;// 每页显示多少条数据
	private int totalSize;// 总的记录数
	private int totalPage; // 共有多少页

	public int getStart() {
		return start;
	}

	public void setStart(int start) {
		this.start = start;
	}

	public int getEnd() {
		return end;
	}

	public void setEnd(int end) {
		this.end = end;
	}

	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 int getTotalSize() {
		return totalSize;
	}

	public void setTotalSize(int totalSize) {
		this.totalSize = totalSize;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

}

package cn.yicha.advertiser.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

/**
 * yicha 
 * Jun 28, 2008 
 */

public class ConnDB {
	
	private Logger logger=Logger.getLogger(ConnDB.class);
	private Connection conn=null;
	private Statement stmt=null;
	private ResultSet rs=null;
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public Connection getConn(){
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			conn=DriverManager.getConnection("proxool.link");
		} catch (ClassNotFoundException e) {
			logger.error("getConn()", e);
		} catch (SQLException e) {
			logger.error("getConn()获取数据库连接池时出错!", e);
		}
		return conn;
	}
	
	/**
	 * 执行查询语句sql
	 * @param sql
	 * @return
	 */
	public ResultSet getResultSet(String sql){
		try {
			if(conn==null||conn.isClosed()) conn=this.getConn();
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
		} catch (SQLException e) {
			logger.error("执行查询语句"+sql+"出错:",e);
		}
		return rs;
	}
	
	/**
	 * 更新sql语句
	 * @param sql
	 * @return  整数，更新的条数
	 */
	public int excuteUpdate(String sql){
		int result=0;
		try {
			if(conn==null||conn.isClosed()) conn=this.getConn();
			result=conn.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}
	
	/**
	 * 关闭连接
	 */
	public void closeConn(){
		try{
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(stmt!=null){
				stmt.close();
				stmt=null;
			}
			if(conn!=null){
				conn.close();
				conn=null;
			}
		}catch (SQLException e) {
			logger.error("关闭数据库连接出错", e);
		}
		
	}
}

package cn.yicha.advertiser.util;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import cn.yicha.advertiser.dao.ConnDB;
import cn.yicha.advertiser.model.Pagination;

/**
 * yicha 
 * Jun 28, 2008 
 */

public class PageUtil {
	
	private static Logger logger=Logger.getLogger(PageUtil.class);
	
	/**
	 * 获取分页的所有信息
	 * @param countSql
	 * @param pagination 只含有currentPage和pageSize
	 * @return 返回的pagination含有start、end、pageSize、totalSize、totalPage、currentPage
	 */
	public static Pagination getPage(String countSql,Pagination pagination){		
		//SELECT * --分页 sqlserver2005特有，与oracle类似
		//FROM (select *,ROW_NUMBER() Over(order by id) as rowNum from t_basicAdData ) as myTable
		//where rowNum between 10000 and 10200
		
		int totalSize=getTotalSize(countSql); //总的记录条数
		int start = 0;   //当前页是从哪条记录开始的
		int end = 0;     //当前页是从哪条记录结束的
		int totalPage = 0;//总的页数
		int currentPage=pagination.getCurrentPage(); //当前页
		int pageSize=pagination.getPageSize(); //每页的条数		
		
		totalPage=(int)Math.ceil((double)totalSize/pageSize);
		//如果当前小于1,则显示第一页
		if(currentPage<1)         currentPage=1;	
		//如果当前页大于总页数,则显示最后一页
		if(currentPage>totalPage) currentPage=totalPage;
		if(end>totalSize) end = totalSize;		
		//得到开始和结束记录号		
		start = pageSize*(currentPage-1)+1;  //sqlserver2005中是从第一条数据开始
	 	end = start + pageSize; 
	 	//设置返回的bean
	 	pagination.setEnd(end);
	 	pagination.setStart(start);
	 	pagination.setTotalPage(totalPage);
	 	pagination.setTotalSize(totalSize);
		return pagination;
	}
	
	/**
	 * 获取总的记录条数
	 * @param countSql  sql语句
	 * @return
	 */
	public static int getTotalSize(String countSql){
		int totalSize=0;
		ConnDB connDB=new ConnDB();
		ResultSet rs=connDB.getResultSet(countSql);
		try {
			if(rs!=null&&rs.next()){
				totalSize=rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.error("getTotalSize()",e);
		}
		return totalSize;
	}
}