pagination.java

用户模块和指标模块等等,还有其它的模块吧

package com.srit.gcz.util;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.ListOrderedMap;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
 * jdbctempate翻页 数据库类型到applicationContext-sys.xml中配置
 * 
 * @Company 国研科技
 * @author 陈鹏昊
 * @date 2009-3-24
 * @version 1.0
 * @since 1.0
 */
public class Pagination {
	// 一页显示的记录数
	private int pageSize;
	// 记录总数
	private int totalRows;
	// 总页数
	private int totalPages;
	// 当前页码
	private int currentPage;
	// 结果集
	private List resultList;
	// 分页sql
	private String sql;
	// JdbcTemplate
	private JdbcTemplate jdbcTemplate;

	public Pagination(JdbcTemplate jdbcTemplate){
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * 默认10页只传入sql即可
	 * @param sql
	 * @param currentPage
	 * @return
	 */
	public void queryForList(String sql, int currentPage) {
		this.resultList = queryForListRoot(sql, null, null, currentPage, 10);
	}
	
	/**
	 * 返回类型查询
	 * @param sql
	 * @param elementType
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	public void queryForList(String sql, RowMapper rowMapper, int currentPage, int pageSize) {
		this.resultList =  queryForListRoot(sql, null, rowMapper, currentPage, pageSize);
	}
	
	/**
	 * 无返回类型查询
	 * @param sql
	 * @param param
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	public void queryForList(String sql, Object[] param, int currentPage, int pageSize) {
		this.resultList =  queryForListRoot(sql, param, null, currentPage, pageSize);
	}
	
	/**
	 * 进行查询
	 * @param sql
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	public void queryForList(String sql, Object[] param, RowMapper rowMapper, int currentPage, int pageSize) {
		this.resultList =  queryForListRoot(sql, param, rowMapper, currentPage, pageSize);
	}
	
	/**
	 * 进行查询
	 * @param sql
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private List queryForListRoot(String sql, Object[] param, RowMapper rowMapper, int currentPage, int pageSize) {
		this.currentPage = currentPage;
		this.pageSize = pageSize;
		this.sql = sql;
		this.setTotalRows(param);
		this.setTotalPages();
		String paginationSQL = paginationSql(this.getTotalRows(), this
				.getTotalPages());
		if((param == null)&&(rowMapper != null)){
			return jdbcTemplate.query(paginationSQL, rowMapper);
		}else if((param != null)&&(rowMapper == null)){
			return jdbcTemplate.queryForList(paginationSQL, param);
		}else if((param != null)&&(rowMapper != null)){
			return jdbcTemplate.query(paginationSQL, param, rowMapper);
		}else{
			return jdbcTemplate.queryForList(paginationSQL);
		}
	}

	// 封装SQL语句,由页码和页大小,实现分页
	private String paginationSql(int count, int totalPage) {
		StringBuffer sb = null;
		int dbType = SysConstant.SYS_CONSTENT.getDbType();
		//int dbType = 0;
		if (dbType == 0) {
			// Oracle语法封装
			sb = new StringBuffer();
			if (this.currentPage == 1) {
				sb.append("select tt.* from (select rownum r,t.* from (");
				sb.append(this.sql);
				sb.append(") t) tt where tt.r<=").append(
						this.currentPage * this.pageSize);
			} else {
				sb.append("select tt.* from (select rownum r,t.* from (");
				sb.append(this.sql);
				sb.append(") t) tt where tt.r>").append(
						(this.currentPage - 1) * this.pageSize).append(
						" and tt.r<=").append(this.currentPage * this.pageSize);
			}
		} else if (dbType == 1) {
			// Ms sql server 语法封装
			sb = new StringBuffer();
			sb.append("select top ").append(this.pageSize * this.currentPage)
					.append(" ");
			// 去掉sql语句中的"select"
			sb.append(this.sql.substring(6));
			String tempSql = sb.toString();
			if (this.currentPage != 1) {
				// 临时的页大小
				int tempPageSize = this.pageSize;
				// 当最后一页的时候
				if (this.currentPage == totalPage) {
					// 如果记录总数不能整除页大小,则取记录记录总数除以页大小的余数
					if (count % this.pageSize != 0) {
						tempPageSize = count % this.pageSize;
					}
				}
				sb = new StringBuffer();
				sb.append("select tt.* from(select top ").append(tempPageSize)
						.append(" t.* from(");
				sb.append(tempSql);
				sb.append(") t order by id desc) tt order by id asc");
			}
		} else if (dbType == 2) {
			// Mysql语法封装
			sb = new StringBuffer();
			if (this.currentPage == 1) {
				sb.append(sql).append(" limit ").append(this.pageSize);
			} else {
				// 临时的页大小
				int tempPageSize = this.pageSize;
				// 当最后一页的时候
				if (this.currentPage == totalPage) {
					// 如果记录总数不能整除页大小,则取记录记录总数除以页大小的余数
					if (count % this.pageSize != 0) {
						tempPageSize = count % this.pageSize;
					}
				}
				// 使用倒序取值的方法
				// 该方法在取最后一页的时候有问题！从而使用了上述判断
				sb.append(" select tt.* from(select t.* from((");
				sb.append(this.sql);
				sb.append(" limit ").append(this.currentPage * this.pageSize)
						.append(") t) order by id desc limit ").append(
								tempPageSize).append(") tt order by id asc");
			}
		}
		return sb.toString();
	}

	public int getCurrentPage() {
		return currentPage;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setTotalPages(){
		if (totalRows % pageSize == 0) {
			this.totalPages = totalRows / pageSize;
		} else {
			this.totalPages = (totalRows / pageSize) + 1;
		}
	}
	
	public int getTotalPages() {
		return totalPages;
	}
	
	public void setTotalRows(Object[] param) {
		// 计算总记录数
		StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
		totalSQL.append(sql);
		totalSQL.append(" ) totalTable ");
		// 总记录数
		if(param != null){
			this.totalRows = jdbcTemplate.queryForInt(totalSQL.toString(), param);
		}else{
			this.totalRows = jdbcTemplate.queryForInt(totalSQL.toString());
		}
	}
	
	public int getTotalRows() {
		return totalRows;
	}
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List getResultList() {
		return resultList;
	}
	
	public void setResultList(List resultList) {
		this.resultList = resultList;
	}
}