Spring Data JPA多表查询返回自定义实体

在实际开发过程中有时候会需要关联查询多个表当中一些字段。
类似酱紫的SQL ：

select a.USER_ID as userId,a.EXT_CHARGE_TYPE as aExtChargeType,b.EXT_CUST_CODE as extCustCode,b.CUST_NAME,b.AREA_CODE as areaCode,b.CREATE_TIME as bCreateTime,d.DEFAULT_SKIN as defaultSkin,d.FLING_FILE_STATUS as flingFileStatus,c.OPT_TYPE as cOptType,c.IPTV_GROUP as iptvGroup,c.IPTV_GROUP_NAME as iptvGroupName from IPTV_ACCOUNT a 
right join IPTV_CUSTOMER b on a.BIZ_ID=b.BIZ_ID   
left join IPTV_USER c on a.EXT_IPTV_ACOUNT=c.ACCOUNT_NO
left join IPTV_GROUP_SETTING d on b.BIZ_ID=d.IPTV_GROUP order by a.CREATE_TIME desc;

首先要自定义一个这样的实体接收(这样要注意字段类型一定要严格和数据库类型匹配)：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelOutVo {
    private String userId;
    private String aExtChargeType;
    private String extCustCode;
    private String custName;
    private String areaCode;
    private Timestamp bCreateTime;
    private String defaultSkin;
    private String defaultModel;
    private Integer flingFileStatus;
    private Integer cOptType;
    private String iptvGroup;
    private String iptvGroupName;
}

Dao方法（这里要先用Object[]接收查询到的list对象，后面在做转化）：

@Query(value = "select a.USER_ID as userId,a.EXT_CHARGE_TYPE as aExtChargeType,b.EXT_CUST_CODE as extCustCode,b.CUST_NAME,b.AREA_CODE as areaCode,b.CREATE_TIME as bCreateTime,d.DEFAULT_SKIN as defaultSkin,d.FLING_FILE_STATUS as flingFileStatus,c.OPT_TYPE as cOptType,c.IPTV_GROUP as iptvGroup,c.IPTV_GROUP_NAME as iptvGroupName from IPTV_ACCOUNT a 
right join IPTV_CUSTOMER b on a.BIZ_ID=b.BIZ_ID   
left join IPTV_USER c on a.EXT_IPTV_ACOUNT=c.ACCOUNT_NO
left join IPTV_GROUP_SETTING d on b.BIZ_ID=d.IPTV_GROUP order by a.CREATE_TIME desc",nativeQuery = true)
List<Object[]> getExcelAll();

转化为自定义的实体：

//转换实体类
    public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz) throws Exception {
        List<T> returnList = new ArrayList<>();
        if(CollectionUtils.isEmpty(list)){
            return returnList;
        }
        List<Class<?>> allFieldsList = getAllFieldsList(ExcelOutVo.class);
        Class[] mod = allFieldsList.toArray(new Class[list.get(0).length]);
        for (Object[] co : list) {
            Constructor<T> constructor = clazz.getConstructor(mod);
            returnList.add(constructor.newInstance(co));
        }
        return returnList;
    }
        //通过反射获取实体类的每个字段类型
    public static List<Class<?>> getAllFieldsList(final Class<?> cls) {
        Validate.isTrue(cls != null, "The class must not be null");
        final List<Class<?>> allFields = new ArrayList<>();
        Class<?> currentClass = cls;
        while (currentClass != null) {
            final Field[] declaredFields = currentClass.getDeclaredFields();
            for (final Field field : declaredFields) {
                allFields.add(field.getType());
            }
            currentClass = currentClass.getSuperclass();
        }
        return allFields;
    }

使用：

List<ExcelOutVo> testViews = castEntity((iptvDao.getExcelAll()), ExcelOutVo.class);

这样写目的是达到了，但是效率上确实有些降低了,因为需要遍历封装每条数据
如果有更好方法欢迎评论留言哦，感谢您的分享~

7D28525A-F2E4-49BD-887A-A0129DBAAE88_meitu_1.jpg

最后编辑于：2019.11.04 14:41:07