实现分页
在数据多的时候,不分页JVM就会挂掉.由于是用的JdbcTemplate,所以就研究了下JdbcTemplate的分页.记录如下.
源码
部分代码,后面看时间更新为完整.
分页实体类:
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();
}
}