sql中使用collection返回集合数据

        今天在写一个接口时，有两级目录（父子关系），接口需要把两级数据以嵌套的形式返回给前端。我这个新手菜鸟一上来就查询两次sql，然后业务中处理嵌套关系，事实这种方法也能达到目的。但主管PR代码时，问为啥不在sql语句中处理呢？我当然不知道还有这种骚操作，下面是在主管的指导下改造的通过sql语句实现的，记录下来慢慢欣赏。

1、先理一下表中的数据结构

①project_case表，其中有个数组类型字段category_id_list，用来记录此条数据对应的二级目录

②project_case_category表，其中parent_id为0表示一级目录，其他则为二级目录

2、sql中实现代码

<resultMap id="ProjectCaseCategoryVo" type="com.hikvision.idatafusion.dhidata.bean.vo.projectcase.ProjectCaseCategoryVo">
    <result column="first_category_id" jdbcType="BIGINT" property="id"/>
    <result column="first_category_name" jdbcType="VARCHAR" property="categoryName"/>
    <collection property="children" ofType="com.hikvision.idatafusion.dhidata.bean.vo.projectcase.ProjectCaseCategoryVo" javaType="java.util.List">
        <result property="id" column="second_category_id" jdbcType="INTEGER"/>
        <result property="categoryName" column="second_category_name" jdbcType="VARCHAR"/>
    </collection>
</resultMap>

<select id="getListFront" resultMap="ProjectCaseCategoryVo">
    select first_category.id as first_category_id, first_category.category_name as first_category_name,
           second_category.id as second_category_id, second_category.category_name as second_category_name
    from (
         select distinct unnest(category_id_list) as category_id from project_case where delete_flag = 0 and publish_state = '1'
         ) t
    left join project_case_category second_category on second_category.id  = t.category_id
    left join project_case_category first_category on first_category.id  = second_category.parent_id
    where second_category.delete_flag = 0 and first_category.delete_flag = 0
    order by first_category.category_sort desc, second_category.category_sort desc
</select>

上面的sql写的时候要注意两点：①property要和实体类中的属性对应，比如id、categoryName、children都是实体类中的字段；②column要和数据库中的字段保持一致，比如first_category_id、first_category_name等，都是sql语句查询出来后重命名的字段。

3、sql中对应的实体类代码

①ProjectCaseCategoryVo

@Data
@ToString
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class ProjectCaseCategoryVo extends ProjectCaseCategory implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "同一类型的案例")
    private List<ProjectCaseCategory> projectCaseVoList;


    @ApiModelProperty(value = "子分类")
    private List<ProjectCaseCategoryVo> children = new ArrayList<>();
}

②ProjectCaseCategory

@Data
@ToString
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class ProjectCaseCategory extends AbstractEntity {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("分类名称")
    private String categoryName;

    @ApiModelProperty("分类排序")
    private Integer categorySort;

    @ApiModelProperty(value = "父节点ID")
    public Long parentId;
}

4、sql中处理后，业务层的代码就简单了，查询后直接返回给前端便可

List<ProjectCaseCategory> list = projectCaseCategoryDao.getListFront(dto);
List<ProjectCaseCategoryVo> projectCaseCategoryVos = BeanUtil.copyProperties(list, ProjectCaseCategoryVo.class);
return projectCaseCategoryVos;

5、此时返回给前端的数据格式正是想要的

6、业务代码中有个工具类BeanUtil，是在实体类转换工具BeanUtils基础上封装的

public class BeanUtil extends BeanUtils {

    /**
     * copy 对象
     *
     * @param sourceList
     * @param targetClazz
     * @param <T>
     * @return
     */
    public static <T> List<T> copyProperties(List<?> sourceList, Class<T> targetClazz) {
        List<T> resultList = new ArrayList<T>();
        if (CollectionUtils.isEmpty(sourceList)) {
            return resultList;
        }
        for (Object object : sourceList) {
            try {
                T target = targetClazz.newInstance();
                copyProperties(object, target);
                resultList.add(target);
            } catch (Exception e) {
                log.error("copyProperties fail", e);
                throw new BusinessException(ErrorCodes.OBJECT_CLONE_FAIL);
            }
        }
        return resultList;
    }

    /**
     * copy 对象
     *
     * @param source
     * @param targetClazz
     * @param <T>
     * @return
     */
    public static <T> T copyProperties(Object source, Class<T> targetClazz) {
        if (source == null) {
            return null;
        }
        T target = null;
        try {
            target = targetClazz.newInstance();
            copyProperties(source, target);
        } catch (Exception e) {
            log.error("copyProperties fail", e);
            throw new BusinessException(ErrorCodes.OBJECT_CLONE_FAIL);
        }
        return target;
    }

    /**
     * 判断对象的所有属性是否都为空
     * @param object
     * @return
     * @throws IllegalAccessException
     */
    public static boolean checkFieldAllNull(Object object, List<String> ignoreProperties) {
        for (Field f : object.getClass().getDeclaredFields()) {
            if (ignoreProperties != null && ignoreProperties.contains(f.getName())) {
                continue;
            }
            f.setAccessible(true);
            if (Modifier.isFinal(f.getModifiers()) && Modifier.isStatic(f.getModifiers())) {
                continue;
            }
            try {
                if (!isEmpty(f.get(object))) {
                    System.out.println(f.getName());
                    return false;
                }
            } catch (Exception e) {
                return false;
            }
            f.setAccessible(false);
        }
        //父类public属性
        for (Field f : object.getClass().getFields()) {
            if (ignoreProperties != null && ignoreProperties.contains(f.getName())) {
                continue;
            }
            f.setAccessible(true);
            if (Modifier.isFinal(f.getModifiers()) && Modifier.isStatic(f.getModifiers())) {
                continue;
            }
            try {
                if (!isEmpty(f.get(object))) {
                    return false;
                }
            } catch (Exception e) {
                return false;
            }
            f.setAccessible(false);
        }
        return true;
    }

    @SuppressWarnings("rawtypes")
    private static boolean isEmpty(Object object) {
        if (object == null) {
            return true;
        } else if (object instanceof String && (object.toString().equals(""))) {
            return true;
        } else if (object instanceof Collection && ((Collection) object).isEmpty()) {
            return true;
        } else if (object instanceof Map && ((Map) object).isEmpty()) {
            return true;
        } else if (object instanceof Object[] && ((Object[]) object).length == 0) {
            return true;
        }
        return false;
    }
}