import org.apache.commons.lang3.StringUtils;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author: 李浩泉
*
* @Description: 本类是采用JPA的EntityManager进行数据查询
* 核心方法,请见 : getBy()
*
*/
@Service("JpaQueryService")
@Transactional
public class JpaQueryService {
@PersistenceContext
private EntityManager em;
/**
*
* @Description: 取服务单例
* @return
*/
public static JpaQueryService getInstance() {
ApplicationContext context = ApplicationContextManagement.getApplicationContext();
return (JpaQueryService)context.getBean("JpaQueryService");
}
/**
* 创建Select部分
* @param entityClazz
* @return
*/
public <T>String createSelect(Class<T> entityClazz) {
return "select o from "+entityClazz.getName()+" o";
}
/**
* 通过JPA获取所有的对象 :P
* @param entityClazz
* @param <T>
* @return
*/
public <T>List<T> queryForAll(final Class<T> entityClazz) {
return em.createQuery(createSelect(entityClazz)).getResultList();
}
/**
* 通过JPA获取第一个对象
* @param entityClazz
* @param <T>
* @return
*/
public <T>Object queryForSigleResult(final Class<T> entityClazz) {
return em.createQuery(createSelect(entityClazz)).getSingleResult();
}
/**
* 通过查询语句来获取结果数据
* @param entityClazz
* @param sql
* @param <T>
* @return
*/
public <T>List<T> queryBySQL(final Class<T> entityClazz,String sql) {
return em.createQuery(createSelect(entityClazz)+" where "+sql).getResultList();
}
/**
* 获取总行数
* @param cls
* @param <T>
* @return
*/
public <T> long getRowSize(Class<T> cls) {
return new Long(getBy(true, true, cls,null, null, 0, 0).get(0).toString());
}
/**
* 获取条件查询的行数(等值)
* @param cls
* @param paramsMap
* @param <T>
* @return
*/
public <T> long getRowSizeByEqual(Class<T> cls, Map<String, Object> paramsMap) {
return new Long(getBy(true, false, cls,null, paramsMap, 0, 0).get(0).toString());
}
/**
* 获取条件查询的行数(模糊)
* @param cls
* @param paramsMap
* @param <T>
* @return
*/
public <T> long getRowSizeByLike(Class<T> cls, Map<String, String> paramsMap) {
Map<String, Object> map = toStringObjectMap(paramsMap);
return new Long(getBy(true, true, cls,null, map, 0, 0).get(0).toString());
}
/**
* 获取子结果集
* @param cls
* @param from
* @param size
* @param <T>
* @return
*/
public <T> List<T> getSub(Class<T> cls, int from, int size) {
return getBy(false, false, cls, null,null, from, size);
}
/**
* 获取等值的子结果集
* @param cls
* @param paramsMap
* @param from
* @param size
* @param <T>
* @return
*/
public <T> List<T> getSubByEqual(Class<T> cls, Map<String, Object> paramsMap, int from, int size) {
return getBy(false, false, cls,null, paramsMap, from, size);
}
/**
* 获取等值结果集
* @param cls
* @param paramsMap
* @param <T>
* @return
*/
public <T> List<T> getByEqual(Class<T> cls, Map<String, Object> paramsMap) {
return getBy(false, false, cls,null, paramsMap, 0, 0);
}
public <T> List<T> getByEqualWithOrder(Class<T> cls, Map<String, Object> paramsMap,String oderby) {
return getBy(false, false, cls,oderby, paramsMap, 0, 0);
}
/**
* 获取模糊查询子结果集
* @param cls
* @param paramsMap
* @param from
* @param size
* @param <T>
* @return
*/
public <T> List<T> getSubByLike(Class<T> cls, Map<String, String> paramsMap, int from, int size) {
Map<String, Object> map = toStringObjectMap(paramsMap);
return getBy(false, true, cls,null, map, from, size);
}
/**
* 模糊查询
* @param cls
* @param paramsMap
* @param <T>
* @return
*/
public <T> List<T> getByLike(Class<T> cls, Map<String, String> paramsMap) {
Map<String, Object> map = toStringObjectMap(paramsMap);
return getBy(false, true, cls,null, map, 0, 0);
}
/**
* 可构造性的原始查询
* @param getRowCount 是否计算记录条数的查询(即,select count(*))
* @param isVagou 是否模糊查询like(相对于精确查询equal)
* @param cls 对应的实体类名
* @param paramsMap 查询字段映射数据
* @param from 起始记录序号
* @param size 本次查询的记录数
* @return
*/
private <T> List<T> getBy(boolean getRowCount, boolean isVagou, Class<T> cls,String orderby,
Map<String, Object> paramsMap, int from, int size) {
String paramTag; // sql语句中的占位参数标记名
String paramValue; // sql语句中的参数值
String entityValue; // sql语句中的实体参数名
String className = cls.getSimpleName(); // 对应的类名
String preSql = getRowCount ? "select count(e) from " : "select e from ";
StringBuffer sql = new StringBuffer(preSql);
sql.append(className).append(" e where 1=1 ");
Query query;
if (null != paramsMap) {
//真帅
for (String key : paramsMap.keySet()) {
paramTag = ":".concat(key.replace(".", "_"));
entityValue = "lower(e.".concat(key).concat(")");
if (!isVagou) {
sql.append(" and ").append(entityValue).append("=").append(paramTag).append(" ");
} else if(paramTag.endsWith("_id")){
// 如果对应的是主键,则将like改为equal
sql.append(" and ").append(entityValue).append("=").append(paramTag).append(" ");
} else {
sql.append(" and ").append(entityValue).append(" like ").append(paramTag).append(" ");
}
if(QueryUtil.isNotEmpty(orderby)) {
if(StringUtils.contains(orderby,"_")) {
String[] orderbys = orderby.split("_");
sql.append(" order by e."+orderbys[0]+" "+orderbys[1]);
}else {
sql.append(" order by e."+orderby);
}
}
System.out.println("JPA 查询语句== "+sql.toString()+"---"+paramsMap.get(key));
}
}
query = em.createQuery(sql.toString());
if (null != paramsMap) {
// 填值
for (String key : paramsMap.keySet()) {
paramTag = key.replace(".", "_");
paramValue = paramsMap.get(key).toString().toLowerCase();
if (!isVagou) {
try {
query.setParameter(paramTag, new Long(paramValue));
} catch (Exception ex) {
query.setParameter(paramTag, paramValue);
}
} else {
// 如果对应的是主键,则将like改为equal
if(paramTag.endsWith("_id")){
query.setParameter(paramTag, new Long(paramValue));
} else{
query.setParameter(paramTag, "%" + paramValue + "%");
}
}
}
}
if (from == 0 && size == 0) {
return query.getResultList();
}
return query.setFirstResult(from).setMaxResults(size).getResultList();
}
/**
* 将<String, String>类型的映射转换为<String, Object>类型
*
* @param paramsMap
* @return
*/
private Map<String, Object> toStringObjectMap(Map<String, String> paramsMap) {
Map<String, Object> map = new HashMap<String, Object>();
for (String key : paramsMap.keySet()) {
map.put(key, paramsMap.get(key));
}
return map;
}
}