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();
	}
	
}

Maven:No compiler is provided in this environment

问题

在执行maven install的时候,提示如下错误:

No compiler is provided in this environment. Perhaps you are running on a JRE rather than a JDK?

maven logo

解决

解决办法是:
1.多project clear几次项目,project clear之后不要运行maven clear;
2.在maven的conf/settings.xml中,添加如下代码即可(依照版本来,我这里是jdk 1.8就写1.8):

<profile>
<id>jdk-1.8</id>
<activation>
<activeByDefault>true</activeByDefault>
<jdk>1.8</jdk>
</activation>
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<maven.compiler.compilerVersion>1.8</maven.compiler.compilerVersion>
</properties>
</profile>

Java 8:Stream的使用

记录

开始在工作中接触一些Java 8 的内容,也开始使用Stream处理集合.下面是一些代码,有的是自己写的,有的来源于网上.

java标志

源码

import java.util.*;
import java.util.stream.Collectors;


class User {
    private String code;
    private String name;

    public User() {
    }

    public User(String name, String code) {
        this.code = code;
        this.name = name;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

public class CsdnTestDemo {

    private static final String[] TAG = {"张三", "李四", "王五"};

    public static void main(String[] args) {
        List<User> lists = new ArrayList<>();
        lists.add(new User("张三", "1"));
        lists.add(new User("李四", "2"));
        lists.add(new User("王五", "3"));
        lists.add(new User("小六", "4"));
        lists.add(new User("第七", "5"));
        lists.add(new User("第八", "6"));

        // 从列表中过滤key,并返回对应的value
        List<String> resultList = lists.stream().filter(p -> Arrays.asList(TAG).contains(p.getName())).map(p -> p.getCode()).collect(Collectors.toList());
        resultList.forEach(x -> System.out.println(x));

        // 实体对象转换为Map.
        Map<String,String> maps = lists.stream().collect(Collectors.toMap(User::getName,User::getCode));

        // 总和.
        int sum = lists.stream().mapToInt(m->Integer.valueOf(m.getCode())).sum();
        System.out.println("总和"+sum);

    }
}