Java-Jpa扩展-原生SQL查询自定义DTO
Java-JPA原生SQL查询返回自定义DTO:
import com.tjgeo.njsosms.framework.repository.IBaseRepository; import com.tjgeo.njsosms.risk.support.entity.Source; import org.springframework.data.jpa.repository.Query; import java.util.List; /** * ${Description} * * @author wenmingming on 2020/4/13 */ public interface SourceRepository extends IBaseRepository<Source, String> { Source findBySourceId(String sourceId); Source findBySourceIdAndShowState(String sourceId, Integer showState); /** * XX首页-风险柱形图统计 * @return */ @Query(nativeQuery = true, value = "SELECT S1.RISKLEVEL,(SELECT S2.DICNAME FROM SYS_DICTIONARY S2 WHERE S2.DICTYPE='业务板块' AND S2.DICVALUE=S1.PLATE)BIZNAME FROM RISK_SOURCE_DATA S1 WHERE S1.RISKLEVEL IS NOT null") List<Object[]> findByGroupStatics00(); /** * XX首页-风险柱形图统计+线路条件 * @return */ @Query(nativeQuery = true, value = "SELECT S1.RISKLEVEL,(SELECT S2.DICNAME FROM SYS_DICTIONARY S2 WHERE S2.DICTYPE='业务板块' AND S2.DICVALUE=S1.PLATE)BIZNAME FROM RISK_SOURCE_DATA S1 WHERE S1.RISKLEVEL IS NOT null AND (S1.LINEID=:lineId OR S1.LINEID='allLines')") List<Object[]> findByGroupStatics00AndLineId(String lineId); }
Java对JPA进行扩展
/** * JPA-扩展工具 * @param */ public class JpaObjUtil { /** * 将数组数据转换为实体类 * 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致 * * @param list 数组对象集合 * @param clazz 实体类 * @param <T> 实体类 * @param model 实例化的实体类 * @param logger 日志祖级 * @return 实体类集合 */ public static <T> List<T> castEntity(Logger logger,List<Object[]> list, Class<T> clazz, T model,List<String> sqlFields) { List<T> returnList = new ArrayList<T>(); if(sqlFields==null){ sqlFields= Lists.newArrayList(); } sqlFields= sqlFields.stream().distinct().collect(Collectors.toList()); if (list.isEmpty()) { return returnList; } //获取每个数组集合的元素个数 Object[] co = list.get(0); //获取当前实体类的属性名、属性值、属性类别 List<Map> attributeInfoList = Lists.newArrayList(); //创建属性类别数组 Class[] c2 = null; if(sqlFields.size()==0) { //如果数组集合元素个数与实体类属性个数不一致则发生错误 //获取当前实体类的属性名、属性值、属性类别 attributeInfoList = getFiledsInfo(model); //创建属性类别数组 c2 = new Class[attributeInfoList.size()]; if (attributeInfoList.size() != co.length) { return returnList; } //确定构造方法 for (int i = 0; i < attributeInfoList.size(); i++) { c2[i] = (Class) attributeInfoList.get(i).get("type"); } } else{ //如果数组集合元素个数与实体类属性个数不一致则发生错误 //获取当前实体类的属性名、属性值、属性类别 attributeInfoList = getFiledsInfo(model,sqlFields); //创建属性类别数组 c2 = new Class[attributeInfoList.size()]; //确定构造方法 for (int i = 0; i < attributeInfoList.size(); i++) { c2[i] = (Class) attributeInfoList.get(i).get("type"); } } try { for (Object[] o : list) { Constructor<T> constructor = clazz.getConstructor(c2); T intance=constructor.newInstance(o); returnList.add(intance); } } catch (Exception ex) { logger.error("实体数据转化为实体类发生异常：异常信息：{}", ex.getMessage()); return returnList; } return returnList; } /** * 根据属性名获取属性值 * * @param fieldName 属性名 * @param modle 实体类 * @return 属性值 */ private static Object getFieldValueByName(String fieldName, Object modle) { try { String firstLetter = fieldName.substring(0, 1).toUpperCase(); String getter = "get" + firstLetter + fieldName.substring(1); Method method = modle.getClass().getMethod(getter, new Class[]{}); Object value = method.invoke(modle, new Object[]{}); return value; } catch (Exception e) { return null; } } /** * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list * * @param model 实体类 * @return list集合 */ private static List<Map> getFiledsInfo(Object model) { Field[] fields = model.getClass().getDeclaredFields(); List<Map> list = new ArrayList(fields.length); Map infoMap = null; for (int i = 0; i < fields.length; i++) { infoMap = new HashMap(3); infoMap.put("type", fields[i].getType()); infoMap.put("name", fields[i].getName()); infoMap.put("value", getFieldValueByName(fields[i].getName(), model)); list.add(infoMap); } return list; } /** * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list * * @param model 实体类 * @return list集合 */ private static List<Map> getFiledsInfo( Object model, List<String> listFIelds) { Field[] fields = model.getClass().getDeclaredFields(); List<Map> list = new ArrayList(fields.length); Map infoMap = null; for (int i = 0; i < fields.length; i++) { String propName=fields[i].getName(); if(!listFIelds.stream().anyMatch(s1->propName.equals(propName))) continue; infoMap = new HashMap(3); infoMap.put("type", fields[i].getType()); infoMap.put("name", fields[i].getName()); infoMap.put("value", getFieldValueByName(fields[i].getName(), model)); list.add(infoMap); } return list; } /** * * 执行自定义SQL-无参版本 * @param entityManager 实体管理 * @param sql sql脚本 * @return */ public static List<Tuple> queryBySqlV1(EntityManager entityManager,String sql) { return entityManager.createNativeQuery(sql).getResultList(); } /** * * 执行自定义SQL-无参版本 * @param entityManager 实体管理 * @param sql sql脚本 * @return */ public static List<Object[]> queryBySqlV2(EntityManager entityManager,String sql) { return entityManager.createNativeQuery(sql).getResultList(); } /** * 查询总条数 * @param entityManager * @param sql * @return */ public static BigDecimal queryCountBySql(EntityManager entityManager, String sql) { return (BigDecimal) entityManager.createNativeQuery(sql).getSingleResult(); } /** * * 执行自定义SQL-无参版本 * @param entityManager 实体管理 * @param sql sql脚本 * @return */ public static int executeBySql(EntityManager entityManager,String sql) { return entityManager.createNativeQuery(sql).executeUpdate(); } /** * 把字符串数组转化为sql in ( 目标语句 ) * @param list01 * @return */ public static String covertoSqlInValueForString(List<String> list01){ StringBuilder sb=new StringBuilder(); if(list01.size()>0){ for (String s1 : list01) { if(sb.length()>0){ sb.append(","); } sb.append("'"+s1+"'"); } } return sb.toString(); } /** * 把字符串数组转化为sql in ( 目标语句 ) * @param list01 * @return */ public static String covertoSqlInValueForInt(List<Integer> list01){ StringBuilder sb=new StringBuilder(); if(list01.size()>0){ for (Integer s1 : list01) { if(sb.length()>0){ sb.append(","); } sb.append(s1); } } return sb.toString(); } /** * 把字符串数组转化为sql in ( 目标语句 ) * @param list01 * @return */ public static String covertoSqlInValueForDouble(List<Double> list01){ StringBuilder sb=new StringBuilder(); if(list01.size()>0){ for (Double s1 : list01) { if(sb.length()>0){ sb.append(","); } sb.append(s1); } } return sb.toString(); } /** * 执行自定义SQL-参数版本 * @param entityManager 实体管理 * @param sql sql脚本 * @param paramName * @param paramValue * @return */ public static long executeBySqlWithParams(EntityManager entityManager, String sql, String paramName, Object paramValue) { return entityManager.createNativeQuery(sql, Tuple.class) .setParameter(paramName, paramValue) .executeUpdate(); } /** * 执行自定义SQL-参数版本 * @param entityManager 实体管理 * @param sql sql脚本 * @param paramName * @param paramValue * @return */ public static List<Tuple> queryBySqlWithParamsV1(EntityManager entityManager, String sql, String paramName, Object paramValue) { return entityManager.createNativeQuery(sql, Tuple.class) .setParameter(paramName, paramValue) .getResultList(); } /** * 执行自定义SQL-参数版本 * @param entityManager 实体管理 * @param sql sql脚本 * @param paramName * @param paramValue * @return */ public static List<Object[]> queryBySqlWithParamsV2(EntityManager entityManager, String sql, String paramName, Object paramValue) { return entityManager.createNativeQuery(sql, Tuple.class) .setParameter(paramName, paramValue) .getResultList(); } /** * 执行自定义SQL-参数版本 * @param entityManager 实体管理 * @param sql sql脚本 * @param mapParam 参数集 * @return */ public static List<Tuple>queryBySqlWithParamsV1(EntityManager entityManager, String sql, Map<String,Object> mapParam) { if(mapParam==null)mapParam=new HashMap(); Query query= entityManager.createNativeQuery(sql, Tuple.class); mapParam.forEach((paramName, paramValue)->{ query .setParameter(paramName,paramValue); }); return query .getResultList(); } /** * 执行自定义SQL-参数版本 * @param entityManager 实体管理 * @param sql sql脚本 * @param mapParam 参数集 * @return */ public static List<Object[]>queryBySqlWithParamsV2(EntityManager entityManager, String sql, Map<String,Object> mapParam) { if(mapParam==null)mapParam=new HashMap(); Query query= entityManager.createNativeQuery(sql, Tuple.class); mapParam.forEach((paramName, paramValue)->{ query .setParameter(paramName,paramValue); }); return query .getResultList(); } /** * 查询总条数 * @param entityManager * @param sql * @param mapParam * @return */ public static BigDecimal queryCountBySqlWithParams(EntityManager entityManager, String sql, Map<String,Object> mapParam) { if(mapParam==null)mapParam=new HashMap(); Query query= entityManager.createNativeQuery(sql, Tuple.class); mapParam.forEach((paramName, paramValue)->{ query .setParameter(paramName,paramValue); }); return (BigDecimal)query.getSingleResult(); } /** * 设置分页查询总条数等基本信息 * @param entityManager * @param strSql 查询SQL (外层查询不带分页码信息) * @param pageSize 分页大小 * @param currPage 当前页码 * @param mapParam 请求参数 */ public static <T> void setPageBaseInfo( EntityManager entityManager, PageResponseDto<T> objRet,String strSql, Integer pageSize, Integer currPage, Map<String,Object> mapParam) { if(ObjectUtil.isEmpty(mapParam))mapParam=new HashMap<>(); //总条数查询 String strSql0= "Select count(1) ICOUNT from (" +strSql+") "; BigDecimal queryRet=mapParam.size()>0? JpaObjUtil.queryCountBySqlWithParams(entityManager,strSql0,mapParam): JpaObjUtil.queryCountBySql(entityManager,strSql0) ; Integer iTotal=0; if(ObjectUtil.isNotEmpty(queryRet)) { iTotal=queryRet.intValue(); } objRet.setTotalCount(iTotal); objRet.setPageSize(pageSize); Integer iPageCount=(iTotal/pageSize)+(iTotal%pageSize>0?1:0); objRet.setCurrentPage(iTotal==0?0:currPage<(iPageCount-1)?currPage:(iPageCount-1)); } /** * 获取SQL分页部分SQL * @param objRet * @return * @param <T> */ public static <T> String getSqlPageInfo(PageResponseDto<T> objRet,StringBuilder sbWhere){ long iCurrPage=objRet.getCurrentPage()-1; String strRet=((sbWhere.length()>0?" AND ":" WHERE ")) +"\n\t rownum BETWEEN "+iCurrPage* objRet.getPageSize()+" AND "+(iCurrPage+1)* objRet.getPageSize() +" \n"; return strRet; } }
分页类定义
import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import javax.validation.constraints.Min; import java.io.Serializable; import java.util.List; /** * 分页查询响应 * * @param <T> */ @ApiModel("分页查询响应") public class PageResponseDto<T> implements Serializable { /** * 总记录数 */ @ApiModelProperty(value = "总记录数") @Min(value = 0, message = "不能小于0") private long totalCount; /** * 页索引 */ @ApiModelProperty(value = "页索引") @Min(value = 1, message = "不能小于0") private long currentPage; /** * 页大小 */ @ApiModelProperty(value = "页大小") @Min(value = 1, message = "不能小于0") private Integer pageSize; /** * 分页数据 */ @ApiModelProperty(value = "分页数据") private List<T> rows; public long getCurrentPage() { return currentPage + 1; } public void setCurrentPage(long currentPage) { this.currentPage = currentPage; } public long getTotalCount() { return totalCount; } public void setTotalCount(long totalCount) { this.totalCount = totalCount; } public Integer getPageSize() { return pageSize; } public void setPageSize(Integer pageSize) { this.pageSize = pageSize; } public List<T> getRows() { return rows; } public void setRows(List<T> rows) { this.rows = rows; } }
扩展用例
不带分页用法：
List<Object[]> queryObj=Lists.newArrayList(); if (Strings.isNotEmpty(lineId)){ queryObj=sourceRepository.findByGroupStatics00AndLineId(lineId); } else{ queryObj=sourceRepository.findByGroupStatics00(); } List<String> sqlFields1=Lists.newArrayList(); sqlFields1.add("RISKLEVEL"); sqlFields1.add("BIZNAME"); GroupRiskStatic01Dto resultModel=new GroupRiskStatic01Dto(); List<GroupRiskStatic01Dto> riskList= JpaObjUtil.castEntity(logger ,queryObj,GroupRiskStatic01Dto.class,resultModel);
//注意本转换需要对应字段的构造函数
