java批量关联加载helper类

背景

在项目中，我们经常需要对实体列表进行关联查询

为了优化性能，需要尽可能减少访问数据库的次数

而在当前的项目中使用的MyBatis对关联查询支持并不是太好

而如果在查询中使用join做关联查询侵入就过大

所以在此写一个sqlhelper类来完成关联查询的功能

满足一对一、一对多、多对多关系

实现类

public class SqlHelperUtil {

    /**
     * 获取that列表
     *
     * @param thisList           需要进行load的实体列表
     * @param thatFinder         关联实体的key列表查找方式
     * @param thatFinderCallback 关联实体的回调处理
     * @param thisLinkKey        实体的关联key
     * @param thatLinkKey        关联实体的关联key
     * @return
     */
    private static List<Map<String, Object>> getThatList(List<Map<String, Object>> thisList,
                                                         Function<Condition, List<Map<String, Object>>> thatFinder,
                                                         Consumer<List<Map<String, Object>>> thatFinderCallback,
                                                         String thisLinkKey, String thatLinkKey) {
        Set<Long> linkValueSet = new HashSet<>();
        for (Map<String, Object> thisMap : thisList) {
            //收集所有关联实体的key
            long linkValue = MapUtils.getLongValue(thisMap, thisLinkKey);
            linkValueSet.add(linkValue);
        }

        Condition condition = new Condition();
        //查找条件
        condition.in(thatLinkKey, linkValueSet);
        //查找到关联实体
        List<Map<String, Object>> thatList = thatFinder.apply(condition);

        if (thatFinderCallback != null) {
            //对关联实体进行处理
            thatFinderCallback.accept(thatList);
        }


        return thatList;
    }


    /**
     * 一对一关系加载
     *
     * @param thisList           需要进行load的实体列表
     * @param thatFinder         关联实体的key列表查找方式
     * @param thatFinderCallback 关联实体的回调处理
     * @param thisLinkKey        实体的关联key
     * @param thatLinkKey        关联实体的关联key
     * @param columnName         关联实体在实体的列名
     */
    public static List<Map<String, Object>> hasOne(List<Map<String, Object>> thisList,
                                                   Function<Condition, List<Map<String, Object>>> thatFinder,
                                                   Consumer<List<Map<String, Object>>> thatFinderCallback,
                                                   String thisLinkKey, String thatLinkKey, String columnName) {
        List<Map<String, Object>> thatList = getThatList(thisList, thatFinder, thatFinderCallback, thisLinkKey, thatLinkKey);

        //将关联实体，关联到实体上
        Map<Long, Map<String, Object>> linkMap = new HashMap<>();
        for (Map<String, Object> thatMap : thatList) {
            linkMap.put(MapUtils.getLong(thatMap, thatLinkKey), thatMap);
        }

        for (Map<String, Object> thisMap : thisList) {
            long linkValue = MapUtils.getLongValue(thisMap, thisLinkKey);
            thisMap.put(columnName, linkMap.get(linkValue));
        }
        return thisList;
    }

    /**
     * 一对多关系加载
     *
     * @param thisList           需要进行load的实体列表
     * @param thatFinder         关联实体的key列表查找方式
     * @param thatFinderCallback 关联实体的回调处理
     * @param thisLinkKey        实体的关联key
     * @param thatLinkKey        关联实体的关联key
     * @param columnName         关联实体在实体的列名
     */
    public static List<Map<String, Object>> hasMany(List<Map<String, Object>> thisList,
                                                    Function<Condition, List<Map<String, Object>>> thatFinder,
                                                    Consumer<List<Map<String, Object>>> thatFinderCallback,
                                                    String thisLinkKey, String thatLinkKey, String columnName) {
        List<Map<String, Object>> thatList = getThatList(thisList, thatFinder, thatFinderCallback, thisLinkKey, thatLinkKey);

        //将关联实体，关联到实体上
        Map<Long, List<Map<String, Object>>> linkMap = new HashMap<>();
        List<Map<String, Object>> thatListByLink;
        for (Map<String, Object> thatMap : thatList) {
            thatListByLink = linkMap.get(MapUtils.getLong(thatMap, thatLinkKey));
            //如果为空就初始化列表
            if (thatListByLink == null || thatListByLink.size() == 0) {
                thatListByLink = new ArrayList<>();
            }
            thatListByLink.add(thatMap);
            linkMap.put(MapUtils.getLong(thatMap, thatLinkKey), thatListByLink);
        }

        for (Map<String, Object> thisMap : thisList) {
            long linkValue = MapUtils.getLongValue(thisMap, thisLinkKey);
            thatListByLink = linkMap.get(linkValue);
            if (thatListByLink == null) {
                thisMap.put(columnName, new ArrayList<>());
            } else {
                thisMap.put(columnName, linkMap.get(linkValue));
            }
        }
        return thisList;
    }

    /**
     * 通过中间表多对多关系加载
     *
     * @param thisList           需要进行load的实体列表
     * @param finderInVia        via实体的key列表查找方式
     * @param thatFinder         关联实体的key列表查找方式
     * @param thatFinderCallback 关联实体的回调处理
     * @param thisLinkKey        实体的关联key
     * @param thisLinkKeyInVia   实体在via中的关联key
     * @param thatLinkKeyInVia   关联实体在via中的关联key
     * @param thatLinkKey        关联实体的关联key
     * @param columnName         关联实体在实体的列名
     */
    public static List<Map<String, Object>> hasManyVia(List<Map<String, Object>> thisList,
                                                       Function<Condition, List<Map<String, Object>>> finderInVia,
                                                       Function<Condition, List<Map<String, Object>>> thatFinder,
                                                       Consumer<List<Map<String, Object>>> thatFinderCallback,
                                                       String thisLinkKey, String thisLinkKeyInVia, String thatLinkKeyInVia, String thatLinkKey, String columnName) {

        List<Map<String, Object>> viaList = getThatList(thisList, finderInVia, null, thisLinkKey, thisLinkKeyInVia);
        List<Map<String, Object>> thatList = getThatList(viaList, thatFinder, thatFinderCallback, thatLinkKeyInVia, thatLinkKey);

        Map<Long, List<Map<String, Object>>> linkMap = new HashMap<>();
        List<Map<String, Object>> thatListByLink;
        for (Map<String, Object> thatMap : thatList) {
            for (Map<String, Object> viaMap : viaList) {
                if (thatMap.get(thatLinkKey).equals(viaMap.get(thatLinkKeyInVia))) {
                    thatListByLink = linkMap.get(MapUtils.getLong(viaMap, thisLinkKeyInVia));
                    //如果为空就初始化列表
                    if (thatListByLink == null || thatListByLink.size() == 0) {
                        thatListByLink = new ArrayList<>();
                    }
                    thatListByLink.add(thatMap);
                    linkMap.put(MapUtils.getLong(viaMap, thisLinkKeyInVia), thatListByLink);
                }
            }
        }

        for (Map<String, Object> thisMap : thisList) {
            long linkValue = MapUtils.getLongValue(thisMap, thisLinkKey);
            thatListByLink = linkMap.get(linkValue);
            if (thatListByLink == null) {
                thisMap.put(columnName, new ArrayList<>());
            } else {
                thisMap.put(columnName, linkMap.get(linkValue));
            }
        }

        return thisList;
    }
}

使用示例

数据表

articles

id	title
1001	文章1001
1002	文章1002

tags

id	name
1	美食
2	服装
3	数码

article_tags

id	aid	tid
9990	1001	1
9991	1001	2
9992	1002	2
9993	1002	3

代码

//文章列表关联查询他们的标签
SqlHelperUtil.hasManyVia(articles, _conditionArticleTag -> {
    return articleTagService.selectList(_conditionArticleTag));
    },_conditionTag -> {
        return tagService.selectList(_conditionTag));
    }, tagList -> {
        //TODO:可以对tag进行字段增减
        //TODO:可以对tag列表继续进行关联加载
    }, "id","aid","tid", "id", "tags");

输出

articles:[
    {
        id:1001,
        title:"文章1001"，
        tags:[
            {id:1,name:"美食"},
            {id:2,name:"服装"},
        ]
    },
    {
        id:1002,
        title:"文章1002"，
        tags:[
            {id:2,name:"服装"},
            {id:3,name:"数码"},
        ]
    }
]