1. 提供各种 getList()+getPage() 一个抽象类,使用的是hibernate框架,
public abstract class BaseRepository {
@PersistenceContext
protected EntityManager entities;
protected List getList(String sqlStr) {
return this.getList(sqlStr, Maps.newHashMap());
}
protected List getList(String sqlStr, Map params) {
Query query = this.entities.createNativeQuery(sqlStr);
if (params != null) {
for (Object key : params.keySet()) {
query.setParameter((String) key, params.get(key));
}
}
return this.toMap(query);
}
private List toMap(Query query) {
if (query instanceof HibernateQuery) {
query.unwrap(HibernateQuery.class)
.getHibernateQuery()
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.getResultList();
}
throw new IllegalStateException();
}
protected PageData getPage(String sqlStr, int pageSize, int pageIndex) {
return this.getPage(sqlStr, Maps.newHashMap(), pageSize, pageIndex);
}
protected PageData getPage(String sqlStr, Map params, int pageSize, int pageIndex) {
List data = null;
int total = 0;
String totalCount = "totalCount";
if(params.containsKey(totalCount)){
total = Integer.parseInt(params.get(totalCount).toString());
params.remove(totalCount);
}
if (pageSize == 0 && pageIndex == 0){
data = this.getList(sqlStr,params);
}else{
data = this.getList(String.format("%s limit %d, %d", sqlStr, pageIndex * pageSize, pageSize), params);
}
//优化分页查询
if(total == 0){ //重新计算中总条数
total = this.getCount(String.format("select cast(count(1) as UNSIGNED) rows from (%s) as q", sqlStr), params);
}
//int total = this.getCount(String.format("select cast(count(1) as UNSIGNED) rows from (%s) as q", sqlStr), params);
return new PageData(data, total);
}
}