Hibernate JOIN处理NULL父实体：如何为无归属Player填充虚拟Campaign？

方案一：JPQL构造虚拟Campaign直接分组 #

直接在JPQL里通过CASE表达式生成虚拟Campaign对象，把无关联的Player归到这个虚拟分组下，无需后续Java代码遍历处理。

前提是你的Campaign实体要有对应的构造函数（比如接收id和name的public构造方法）：

public Campaign(Long id, String name) {
    this.id = id;
    this.name = name;
}

然后执行以下JPQL查询：

SELECT 
    CASE WHEN p.campaign IS NULL 
         THEN NEW com.yourpackage.Campaign(-1L, '未关联活动') 
         ELSE p.campaign END AS groupCampaign,
    p
FROM Player p
WHERE p.name = 'JOHN'
GROUP BY groupCampaign

查询结果会直接返回分组后的groupCampaign（真实Campaign或虚拟Campaign）和对应的Player集合，Hibernate会自动完成分组映射。

方案二：原生SQL结合自定义结果转换器 #

如果更习惯用原生SQL，你可以先通过LEFT JOIN拿到所有目标Player和关联的Campaign（无关联的Campaign字段为null），再用Hibernate的结果转换器把null的Campaign转换成虚拟对象，并完成分组。

先写原生SQL：

SELECT 
    COALESCE(c.id, -1) as campaign_id,
    COALESCE(c.name, '未关联活动') as campaign_name,
    p.id as player_id,
    p.name as player_name
FROM Players p
LEFT JOIN Campaign c ON p.campaign_id = c.id
WHERE p.name = 'JOHN'

然后自定义一个结果转换器（Hibernate 5.2+推荐用TupleTransformer），遍历结果集时把相同campaign_id的Player归到同一个Campaign（虚拟或真实）下：

public class CampaignPlayerGroupTransformer implements TupleTransformer<Map<Campaign, List<Player>>> {
    private final Map<Campaign, List<Player>> groupMap = new HashMap<>();

    @Override
    public Map<Campaign, List<Player>> transformTuple(Object[] tuple, String[] aliases) {
        Long campaignId = (Long) tuple[0];
        String campaignName = (String) tuple[1];
        Player player = new Player((Long) tuple[2], (String) tuple[3]);

        Campaign campaign;
        if (campaignId == -1) {
            campaign = new Campaign(-1L, campaignName);
        } else {
            campaign = new Campaign(campaignId, campaignName);
        }

        groupMap.computeIfAbsent(campaign, k -> new ArrayList<>()).add(player);
        return groupMap;
    }
}

最后执行查询并应用转换器：

Session session = entityManager.unwrap(Session.class);
Map<Campaign, List<Player>> result = session.createNativeQuery(sql)
    .setResultTransformer(new CampaignPlayerGroupTransformer())
    .uniqueResult();

注意事项 #

• 虚拟Campaign的id建议用一个不会和真实数据冲突的值（比如-1），避免分组混乱
• 使用JPQL构造函数时，构造方法必须是public的，且参数顺序要和JPQL里的一致
• 原生SQL方案要注意字段别名和转换器里的索引对应，避免映射错误

内容的提问来源于stack exchange，提问作者user1609