如何通过Criteria API与Spring Data JPA Specification实现指定SQL查询？

目标SQL #

select b.id from brands b
where b.id not in
(select b.id from brands b
left join brand_categories bc on bc.brand_id = b.id
left join categories c on c.id = bc.category_id
where c.id = :id)

问题代码 #

public static Specification<Brand> notContains(Long id) {
    return (root, query, criteriaBuilder) -> {
        Subquery<List> subQuery = query.subquery(List.class);
        Root<Brand> subRoot = subQuery.from(Brand.class);
        Join<Category, Brand> join = root.join("categories");
        subQuery.select(subRoot.get("id"));
        subQuery.where(criteriaBuilder.equal(join.get("id"), id));
        return root.get("id").in(subQuery).not();
    };
}

修复后的代码 #

public static Specification<Brand> notContains(Long id) {
    return (root, query, criteriaBuilder) -> {
        // 子查询泛型指定为Long，匹配返回的品牌ID类型
        Subquery<Long> subQuery = query.subquery(Long.class);
        Root<Brand> subRoot = subQuery.from(Brand.class);
        
        // 在子查询的Brand根对象上做左连接，关联Category
        Join<Brand, Category> join = subRoot.join("categories", JoinType.LEFT);
        
        // 子查询选择品牌ID
        subQuery.select(subRoot.get("id"));
        // 子查询条件：匹配目标分类ID
        subQuery.where(criteriaBuilder.equal(join.get("id"), id));
        
        // 主查询条件：品牌ID不在子查询结果集合中
        return criteriaBuilder.not(root.get("id").in(subQuery));
    };
}

关键修复点 #

• 子查询泛型错误：原代码用List作为子查询泛型，实际子查询返回的是单个Long类型的ID集合，需改为Subquery<Long>。
• 关联对象错误：原代码在主查询的root上做关联，导致主查询提前关联分类，逻辑偏离目标SQL；应在子查询的subRoot上执行关联操作。
• Join泛型顺序错误：Join<Brand, Category>才是正确的关联顺序（从Brand到Category），原代码顺序颠倒。
• 显式左连接：添加JoinType.LEFT确保关联逻辑和原始SQL的LEFT JOIN一致。
• 条件写法优化：用criteriaBuilder.not(...)替代链式.not()，更符合Criteria API的规范写法。

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