如何从根实体实现单向一对一关联的查询与过滤

需要通过Criteria API实现：获取所有不属于指定Set<FooType>的FooEntity实例，同时需兼容其他属性过滤逻辑。

实体类定义 #

FooEntity #

public class FooEntity implements Serializable {

    @Id
    @GeneratedValue(generator = "foo_seq")
    @Column(name = "foo_id")
    @Access(value = AccessType.PROPERTY)
    private Long id;
}

FooDetailsEntity #

public class FooDetailsEntity  {

    @Id
    @GeneratedValue(generator = "foo_details_seq")
    @Column(name = "foo_details_id", updatable = false)
    private long id;

  
    @Column(name = "type", nullable = false)
    @Enumerated(EnumType.STRING)
    private FooType type;

    @OneToOne(fetch = FetchType.LAZY)
    @MapsId
    @JoinColumn(name = "fk_foo_id", referencedColumnName = "foo_id")
    @Setter(AccessLevel.PACKAGE)
    private FooEntity owningFoo;

    
    public enum FooType {
        BIG,
        SMALL
    }
}

尝试的错误代码 #

private List<FooEntity> getFoos(Root<FooEntity> root, CriteriaQuery<FooEntity> cq, Set<Predicate> predicatesToApply, CriteriaBuilder cb, Set<FooDetailsEntity.FooType> fooTypesToExclude) {
    final Set<Predicate> applicablePredicates = new HashSet<>(predicatesToApply);

    if (!fooTypesToExclude.isEmpty()) {
        log.debug("Excluding certain foo types.");

        Root<FooDetailsEntity> subRoot = cq.from(FooDetailsEntity.class);
        final Join<FooDetailsEntity, FooEntity> subJoin = subRoot.join(FooDetailsEntity_.owiningFoo);

        fooTypesToExclude
                .forEach(type ->  {
                    applicablePredicates.add(cb.and(cb.notEqual(subJoin.get(FooDetailsEntity.type), type)));
                });
    }

    cq.where(applicablePredicates.toArray(new Predicate[0]));

    TypedQuery<FooEntity> tq = em.createQuery(cq);
    tq.setHint(QueryHints.READ_ONLY, true);
    return tq.getResultList();
}

期望生成的SQL #

select
        f.*
    from
        foo f
    left join foo_details fd on
        fd.fk_foo_id = f.foo_id
    where
        fd."type" not in('BIG', 'SMALL')

问题分析与修正方案 #

原代码存在三个核心问题：

1. 直接通过cq.from(FooDetailsEntity.class)会生成交叉连接，而非需求中的左连接
2. 循环添加notEqual生成冗余的多AND条件，且未正确关联主表Root
3. 拼写错误：FooDetailsEntity_.owiningFoo应为FooDetailsEntity_.owningFoo

正确实现逻辑应从主表FooEntity的Root出发，左关联FooDetailsEntity后添加NOT IN条件：

private List<FooEntity> getFoos(Root<FooEntity> root, CriteriaQuery<FooEntity> cq, Set<Predicate> predicatesToApply, CriteriaBuilder cb, Set<FooDetailsEntity.FooType> fooTypesToExclude) {
    final Set<Predicate> applicablePredicates = new HashSet<>(predicatesToApply);

    if (!fooTypesToExclude.isEmpty()) {
        log.debug("Excluding certain foo types.");
        // 从FooEntity左连接到FooDetailsEntity
        Join<FooEntity, FooDetailsEntity> detailsJoin = root.join("owningFoo", JoinType.LEFT);
        // 添加NOT IN过滤条件
        applicablePredicates.add(cb.not(detailsJoin.get("type").in(fooTypesToExclude)));
        // 若需包含未关联FooDetailsEntity的FooEntity，需追加OR条件：
        // applicablePredicates.add(cb.or(detailsJoin.isNull(), cb.not(detailsJoin.get("type").in(fooTypesToExclude))));
    }

    cq.where(applicablePredicates.toArray(new Predicate[0]));

    TypedQuery<FooEntity> tq = em.createQuery(cq);
    tq.setHint(QueryHints.READ_ONLY, true);
    return tq.getResultList();
}

关键说明 #

• 使用root.join("owningFoo", JoinType.LEFT)生成左连接，完全匹配期望SQL的连接逻辑
• detailsJoin.get("type").in(fooTypesToExclude)生成IN条件，外层套cb.not()直接得到NOT IN，写法简洁高效
• 若业务需要包含无关联FooDetailsEntity的FooEntity，需添加detailsJoin.isNull()的OR分支，否则左连接后null值会被NOT IN过滤

修正后生成的SQL #

会与期望结构完全一致：

select
        f.*
    from
        foo f
    left join foo_details fd on
        fd.fk_foo_id = f.foo_id
    where
        fd."type" not in ('BIG', 'SMALL')

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