JdbcTemplate:实现分页功能

实现分页

在数据多的时候,不分页JVM就会挂掉.由于是用的JdbcTemplate,所以就研究了下JdbcTemplate的分页.记录如下.

java

源码

部分代码,后面看时间更新为完整.

分页实体类:

import java.io.Serializable;
import java.util.List;


/**
 * 
 * 分页组件
 * 
 * Entity 需要实现RowMapper<Entity>接口
 * JdbcTemplate jt = new JdbcTemplate();
 * jt.queryPage(sql, paramArray, page, size, new Entity());
 * 
 * @author prd
 * @version 2017-08-10
 * @param <T>
 */
public class Pageable<T> implements Serializable {
	
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3561007086768186332L;
	
	// 传入的实体查询结果.
	private List<T> result;
	
	// 查询结果总数
	private int count;
	
	// 当前页数
	private int page;
	
	// 每页数量
	private int size;
	
	
	public List<T> getResult() {
		return result;
	}
	public void setResult(List<T> result) {
		this.result = result;
	}
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}
	public int getPage() {
		return page;
	}
	public void setPage(int page) {
		this.page = page;
	}
	public int getSize() {
		return size;
	}
	public void setSize(int size) {
		this.size = size;
	}
	
	

}

生成分页的SQL语句:

/**
 * 生成分页的SQL
 * @author 
 * @date 
 */
public abstract class SqlPageHelper {
	
	private static Logger LOG = LoggerFactory.getLogger(SqlPageHelper.class);

	public abstract String getLimitSQL(String sql,int limit,int start,String orderColumn);
	
	public abstract String getPieceSQL(String sql,int rows);
	
	protected JdbcTemplate jdbcTemplate = null;
	
	protected void setJdbcTemplate(JdbcTemplate jdbcTemplate){
		this.jdbcTemplate = jdbcTemplate;
	}
	
	protected int pieceRow=500;//随机取的行数
	
	public String getLimitSQL(String sql,int limit,int start){
		return getLimitSQL(sql, limit, start, null);
	}
	
	public static SqlPageHelper getSqlPageHelper(JdbcTemplate jdbcTemplate){
		SqlPageHelper sqlPageHelper = null;
		try{
			sqlPageHelper = (SqlPageHelper)BeanFactory.getBean(jdbcTemplate.getDatabaseType()+"SqlPageHelper");
		}catch(Exception e){
			sqlPageHelper = (SqlPageHelper)BeanFactory.getBean("simpleSqlPageHelper");
			LOG.error("不存在[{}SqlPageHelper],默认使用simpleSqlPageHelper",jdbcTemplate.getDatabaseType());
		}
		sqlPageHelper.setJdbcTemplate(jdbcTemplate);
		return sqlPageHelper;
	}
	
	/**
	 * 得到sql的统计行数的sql
	 * @param sql
	 * @return
	 */
	public String getCountSQL(String sql){
		StringBuilder countSQL = new StringBuilder("select count(*) cnt from (");
		countSQL.append(sql.trim());
		if(sql.trim().endsWith(";")){
			countSQL.delete(countSQL.length()-1,countSQL.length());
		}
		
		if(countSQL.indexOf(" with ur")>0){
			countSQL.delete(countSQL.indexOf("with ur"),countSQL.length());
		}
		
		if(countSQL.indexOf(" order by")>0){
			countSQL.delete(countSQL.indexOf("order by"),countSQL.length());
		}
		
		countSQL.append(") t");
		//countSQL.append(" with ur");
		
		//LOG.debug("SQL:"+countSQL);
		return countSQL.toString();
	}
	
}

InterSystems Cache 数据库的分页工具类(下面有MySQL的):

import org.springframework.stereotype.Component;


/**
 * Cache生成分页的SQL
 * 
 * ----------------------------------
 *  使用方法: 
 *  
 *  本类不会处理sql中占位符,因此带有占位符的SQL可直接使用.
 * 
 * JdbcTemplate jt = new JdbcTemplate(); 
 * StringBuffer sql = new StringBuffer();// SQL语句 
 * SqlPageHelper sqlPageHelper = jt.getSqlPageHelper();
 * jt.query(sqlPageHelper.getLimitSQL(sql.toString(), 5, 10),paramObjectArray);
 * 
 * SELECT * FROM (SELECT * FROM TABLE_NAME ) WHERE %VID BETWEEN 5 AND 10
 * ----------------------------------
 * 
 * @author prd
 * @date 2017-8-10
 */
@Component
public class CacheSqlPageHelper extends SqlPageHelper {

	public String getLimitSQL(String sql, int limit, int start, String orderColumn) {
		sql = sql.trim();
		StringBuilder limitSQL = new StringBuilder("select * from (");
		limitSQL.append(sql);

		if (sql.endsWith(";")) {
			limitSQL.delete(limitSQL.length() - 1, limitSQL.length());
		}

		limitSQL.append(")  ");
		if (orderColumn != null && orderColumn.length() >= 0) {
			limitSQL.append(" order by " + orderColumn + "");
		}
		// 重新处理 参数,
		// Cache 数据库与MySQL存在很大差异.
		// Cache 返回从a到b之间的数据;
		// MySQL 返回从a开始的b条数据.
		int startWith = 0;
		if(start==1) {
			startWith=(start-1) * limit;
		}else {
			startWith = (start-1) * limit +1;
		}
		limitSQL.append(" where %VID between ").append(startWith).append(" and ").append(start * limit);
		return limitSQL.toString();
	}

	public String getPieceSQL(String sql, int rows) {
		return getLimitSQL(sql, rows, 1, null);
	}

}

分页查询组件:

public class JdbcTemplate extends org.springframework.jdbc.core.JdbcTemplate {
	

	public SqlPageHelper getSqlPageHelper(){
		return SqlPageHelper.getSqlPageHelper(this);
	}
/**
	 * 分页查询组件.
	 * 
	 * Entity 需要实现RowMapper<Entity>接口
	 * JdbcTemplate jt = new JdbcTemplate();
	 * jt.queryPage(sql, paramArray, page, size, new Entity());
	 * 
	 * @param sql SQL语句
	 * @param args 参数数组
	 * @param page 页码
	 * @param size 分页大小
	 * @param orderColumn 排序(可选)
	 * @param rowMapper 实体对象.需要实现RowMapper接口,并重写mapRow方法.
	 * 
	 * @return Pageable<T> 对象.
	 */
	public <T> Pageable<T> queryPage(String sql,Object[] args,int page,int size,String orderColumn,RowMapper<T> rowMapper){
		// 生成统计语句
		Pattern pa = Pattern.compile("(?<=SELECT).*?(?=FROM)");
		String countSQL = pa.matcher(sql).replaceAll(" COUNT(*) ");
		int count = this.queryForObject(countSQL, args,java.lang.Integer.class);
		// 生成统计语句.
		Pageable<T> pageable = new Pageable<T>();
		pageable.setSize(size);
		pageable.setPage(page);
		pageable.setCount(count);
		pageable.setResult(this.query(this.getSqlPageHelper().getLimitSQL(sql, size, page, orderColumn),args, rowMapper));
		return pageable;
	}
	
	public <T> Pageable<T> queryPage(String sql,Object[] args,int page,int size,String orderColumn,Class<T> classA){
		return queryPage(sql,args,page,size,orderColumn,getSingleColumnRowMapper(classA));
	}
	
	public <T> Pageable<T> queryPage(String sql,Object[] args,int page,int size,String orderColumn, ResultSetExtractor<T> rse){
		return queryPage(sql,args,page,size,orderColumn,rse);
	}
	
	public <T> Pageable<T> queryPage(String sql,Object[] args,int page,int size ,RowMapper<T> rowMapper){
		return queryPage(sql,args,page,size,null,rowMapper);
	}

	public <T> Pageable<T> queryPage(String sql,Object[] args,int page,int size ,Class<T> classA){
		return queryPage(sql,args,page,size,null,getSingleColumnRowMapper(classA));
	}
	
	public <T> Pageable<T> queryPage(String sql,Object[] args,int page,int size, ResultSetExtractor<T> rse){
		return queryPage(sql,args,page,size,null,rse);
	}
	// 分页查询组件END.

}

MySQL分页实现:

@Component
public class MysqlSqlPageHelper extends SqlPageHelper{
	
	public String getLimitSQL(String sql,int limit,int start,String orderColumn){
		if(orderColumn==null || orderColumn.length()==0){
			orderColumn = "1";
		}
		sql=sql.trim();
		StringBuilder limitSQL = new StringBuilder(sql);
		if(sql.endsWith(";")){
			limitSQL.delete(limitSQL.length()-1,limitSQL.length());
		}
		limitSQL.append(" limit ").append(start).append(" , ").append(limit);
		return limitSQL.toString();
	}
	
	public String getPieceSQL(String sql,int rows){
		return getLimitSQL(sql, rows, 1, null);
	}
	
}

Oracle分页实现:

@Component
public class OracleSqlPageHelper extends SqlPageHelper{

	public String getLimitSQL(String sql,int limit,int start,String orderColumn){
		sql = sql.trim();
		StringBuilder limitSQL = new StringBuilder("select * from (select t.*,rownum as pseudo_column_rownum from (");
		limitSQL.append(sql);
		
		if(sql.endsWith(";")){
			limitSQL.delete(limitSQL.length()-1,limitSQL.length());
		}
		
		limitSQL.append(") t ");
		if(orderColumn!=null && orderColumn.length()>=0){
			limitSQL.append(" order by "+orderColumn+"");
		}
		limitSQL.append(") t2 where pseudo_column_rownum between ").append(start+1).append(" and ").append(start+limit);
		return limitSQL.toString();
	}
	
	public String getPieceSQL(String sql,int rows){
		if(rows<pieceRow){
			rows=pieceRow;
		}
		StringBuilder pieceSQL = new StringBuilder(sql.trim());
		
		if(pieceSQL.indexOf(" fetch first")>0){
			pieceSQL.delete(pieceSQL.indexOf("fetch first"),pieceSQL.length());
		}
		
		if(pieceSQL.indexOf(" with ur")>0){
			pieceSQL.delete(pieceSQL.indexOf("with ur"),pieceSQL.length());
		}
		
		pieceSQL.append(" rownum <= "+rows);
		//LOG.debug("SQL:"+pieceSQL);
		return pieceSQL.toString();
	}
	
}

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据