不分页查询
@Override
public List<CityManagerDealerVo> queryCityManagerDealerVolist(CityManagerDealerParam param) {
StringBuffer dataSql = new StringBuffer();
// 拼接参数值
Map parameters = Maps.newHashMap();
dataSql.append(" select * from city_manager_dealer t where 1=1 ");
String commonSql = pageWhere(param, parameters);
dataSql.append(commonSql);
dataSql.append(" ORDER BY t.create_time DESC ");
List rows = DataQueryUtils.getNativeQuery(em, dataSql.toString(), parameters);
return JSON.parseArray(JSON.toJSONString(rows), CityManagerDealerVo.class);
}
分页查询:
@Override
public Page<CityManagerDealer> queryCityManagerDealerlistPage(CityManagerDealerParam param) {
StringBuffer dataSql = new StringBuffer();
StringBuffer countSql = new StringBuffer();
int currentPage = (param.getPageNo() - 1) * param.getPageSize();
int size = param.getPageSize();
// 拼接参数值
Map parameters = Maps.newHashMap();
dataSql.append(" select * from city_manager_dealer t where 1=1 ");
countSql.append(" select count(1) from city_manager_dealer t where 1=1 ");
String commonSql = pageWhere(param, parameters);
dataSql.append(commonSql);
dataSql.append(" ORDER BY t.created_time DESC ");
countSql.append(commonSql);
List rows = DataQueryUtils.getNativeQueryPage2(em, dataSql.toString(), parameters, currentPage, size);
BigInteger total = DataQueryUtils.getNativeQueryCount(em, countSql.toString(), parameters);
return new Page<>(JSON.parseArray(JSON.toJSONString(rows), CityManagerDealer.class), total.intValue(), param.getPageNo(), size);
}
public String pageWhere(CityManagerDealerParam entity, Map parameters) {
StringBuffer dataWhere = new StringBuffer();
if (StringUtils.isNotBlank(entity.getCityManagerCode())) {
dataWhere.append(" and city_manager_code like CONCAT('%',:cityManagerCode,'%')");
parameters.put("cityManagerCode", entity.getCityManagerCode());
}
if (entity.getOrgCode() != null) {
List<String> orgIdList = new ArrayList<>();
String[] split = entity.getOrgCode().split("-");
for (String s : split) {
orgIdList.add(s);
}
if(!ObjectUtils.isEmpty(orgIdList)){
dataWhere.append(" and org_code in (");
for(int i=0;i<orgIdList.size(); i++){
if(i==orgIdList.size()-1){
dataWhere.append(" :orgCode" + i + ")");
parameters.put("orgCode" + i, orgIdList.get(i));
}else {
dataWhere.append(" :orgCode" + i + ",");
parameters.put("orgCode" + i, orgIdList.get(i));
}
}
}
}
if (StringUtils.isNotBlank(entity.getDealerCode())) {
dataWhere.append(" and dealer_code =:dealerCode ");
parameters.put("dealerCode", entity.getDealerCode());
}
return dataWhere.toString();
}
工具类
package com.wl.partner.service.utils;
import org.apache.poi.ss.formula.functions.T;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.query.spi.NativeQueryImplementor;
import org.hibernate.transform.Transformers;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;
public class DataQueryUtils {
public static List<T> getNativeQueryForJob(EntityManager em, String sql) {
Query dataQuery = em.createNativeQuery(sql);
NativeQueryImplementor unwrap = dataQuery.unwrap(NativeQueryImpl.class);
unwrap.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQueryForJobWithParameterMap(EntityManager em, String sql,Map<String, String> map) {
Query dataQuery = getParameter(em, sql, map, 2);
NativeQueryImplementor unwrap = dataQuery.unwrap(NativeQueryImpl.class);
unwrap.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
/**
* 根据占位符sql和参数值列表查询
*
* @param em
* @param sql
* @param parameters
* @return
*/
public static List<T> getNativeQueryWithParameters(EntityManager em, String sql, List<Object> parameters) {
Query dataQuery = em.createNativeQuery(sql);
for (int index = 0; index < parameters.size(); index++) {
dataQuery.setParameter(index + 1, parameters.get(index));
}
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQueryPageWithParameters(EntityManager em, String sql, List<Object> parameters, Integer currentPage, Integer pageCount) {
Query dataQuery = em.createNativeQuery(sql);
for (int index = 0; index < parameters.size(); index++) {
dataQuery.setParameter(index + 1, parameters.get(index));
}
dataQuery.setFirstResult(currentPage).setMaxResults(pageCount);
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQueryPageWithParameters1(EntityManager em, String sql, List<Object> parameters, Integer currentPage, Integer pageCount) {
Query dataQuery = em.createNativeQuery(sql);
for (int index = 0; index < parameters.size(); index++) {
dataQuery.setParameter(index + 1, parameters.get(index));
}
dataQuery.setFirstResult(currentPage).setMaxResults(pageCount);
// dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static BigInteger getNativeQueryCountWithParameters(EntityManager em, String sql, List<Object> parameters) {
Query countQuery = em.createNativeQuery(sql);
for (int index = 0; index < parameters.size(); index++) {
countQuery.setParameter(index + 1, parameters.get(index));
}
List resultList = countQuery.getResultList();
BigInteger totalSize = (BigInteger) resultList.get(0);
return totalSize;
}
public static List<T> getNativeQuery(EntityManager em, String sql, Map<String, String> map) {
Query dataQuery = getParameter(em, sql, map, 2);
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQuery2(EntityManager em, String sql, Map<String, Object> map) {
Query dataQuery = getParameter2(em, sql, map, 2);
// dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQueryObject(EntityManager em, String sql, Map<String, Object> map) {
Query dataQuery = getParameter2(em, sql, map, 2);
// dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQueryPage(EntityManager em, String sql, Map<String, String> map, Integer currentPage, Integer pageCount) {
Query dataQuery = getParameter(em, sql, map, 2);
dataQuery.setFirstResult(currentPage).setMaxResults(pageCount);
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static List<T> getNativeQueryPage2(EntityManager em, String sql, Map<String, Object> map, Integer currentPage, Integer pageCount) {
Query dataQuery = getParameter2(em, sql, map, 2);
dataQuery.setFirstResult(currentPage).setMaxResults(pageCount);
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return dataQuery.getResultList();
}
public static BigInteger getNativeQueryCount(EntityManager em, String sql, Map<String, String> map) {
Query countQuery = getParameter(em, sql, map, 2);
List resultList = countQuery.getResultList();
BigInteger totalSize = (BigInteger) resultList.get(0);
return totalSize;
}
public static BigInteger getNativeQueryCount2(EntityManager em, String sql, Map<String, Object> map) {
Query countQuery = getParameter2(em, sql, map, 2);
List resultList = countQuery.getResultList();
BigInteger totalSize = (BigInteger) resultList.get(0);
return totalSize;
}
public static List<T> getCreateQueryPage(EntityManager em, String sql, Integer currentPage, Integer pageCount, Map<String, String> map) {
Query dataQuery = getParameter(em, sql, map, 1);
dataQuery.setFirstResult(currentPage).setMaxResults(pageCount);
return dataQuery.getResultList();
}
public static long getCreateQueryCount(EntityManager em, String sql, Map<String, String> map) {
Query countQuery = getParameter(em, sql, map, 1);
List resultList = countQuery.getResultList();
long totalSize = (long) resultList.get(0);
return totalSize;
}
public static List<T> getCreateQuery(EntityManager em, String sql, Map<String, String> map) {
Query dataQuery = getParameter(em, sql, map, 1);
return dataQuery.getResultList();
}
/**
* 原始分页方法无法支持获取pageNo>=10的合并结果集的页码数据
* 使用sublist获取得到
* @param em
* @param sql
* @param currentPage
* @param pageCount
* @param map
* @return
*/
public static List<T> getCreateQueryPageSubList(EntityManager em, String sql, Integer currentPage, Integer pageCount, Map<String, String> map) {
Query dataQuery = getParameter(em, sql, map, 1);
int endIndex = currentPage + pageCount;
if (currentPage+pageCount >= dataQuery.getResultList().size()) {
endIndex = dataQuery.getResultList().size();
}
return dataQuery.getResultList().subList(currentPage,endIndex);
}
public static Query getParameter(EntityManager em, String sql, Map<String, String> map, int type) {
Query dataQuery = null;
if (type == 1) {
dataQuery = em.createQuery(sql);
} else if (type == 2) {
dataQuery = em.createNativeQuery(sql);
}
for (Map.Entry<String, String> entry : map.entrySet()) {
dataQuery.setParameter(entry.getKey(), entry.getValue());
}
return dataQuery;
}
public static Query getParameter2(EntityManager em, String sql, Map<String, Object> map, int type) {
Query dataQuery = null;
if (type == 1) {
dataQuery = em.createQuery(sql);
} else if (type == 2) {
dataQuery = em.createNativeQuery(sql);
}
for (Map.Entry<String, Object> entry : map.entrySet()) {
dataQuery.setParameter(entry.getKey(), entry.getValue());
}
return dataQuery;
}
public static List<T> getNativeQueryList(EntityManager em, String sql, Map<String, String> map) {
Query dataQuery = getParameter(em, sql, map, 2);
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.TO_LIST);
return dataQuery.getResultList();
}
}