如何用Spring Data JPA Specification API关联@Embedded属性查询无关联记录

现有以下JPA实体类：

@Getter
@Setter
@Entity
@Table(name = "closing_account")
public class ClosingAccount {

  @Embedded private AccountParams accountParams = new AccountParams();
}

@Getter
@Setter
@Embeddable
public class AccountParams {

  @ToString.Exclude
  @EqualsAndHashCode.Exclude
  @Fetch(FetchMode.SELECT)
  @OneToMany(mappedBy = "closingRequestId", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
  @LazyCollection(LazyCollectionOption.FALSE)
  private List<CheckBook> checkBooks;
}

@Getter
@Setter
@Entity
@Table(name = "checkbook")
public class CheckBook {

  @Column(name = "closing_account_id", nullable = false)
  private UUID closingRequestId;
}

需要使用Spring Data JPA Specification API创建Specification，生成Predicate筛选closing_account表中没有对应checkBooks关联记录的数据，等价原生SQL如下：

SELECT ca.* FROM closing_account ca 
LEFT JOIN checkbook cb ON cb.closing_account_id = ca.id
WHERE cb.id IS NULL

原尝试代码无法运行，原因是checkBooks并非ClosingAccount的直接属性：

return (root, query, criteriaBuilder) -> {
  return criteriaBuilder.isNull(root.join("checkBooks", JoinType.LEFT).get("closingRequestId"));
};

需要先通过root.get("accountParams")获取嵌入式属性AccountParams的路径，再基于此关联checkBooks，最后判断关联的CheckBook记录是否为空。示例代码如下：

import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;

public class ClosingAccountSpecifications {

    public static Specification<ClosingAccount> hasNoCheckBooks() {
        return (root, query, criteriaBuilder) -> {
            // 先获取嵌入式属性accountParams，再左连接checkBooks
            Join<Object, CheckBook> checkBookJoin = root.get("accountParams").join("checkBooks", JoinType.LEFT);
            // 判断关联的CheckBook主键是否为null（若CheckBook无显式主键，也可判断closingRequestId）
            return criteriaBuilder.isNull(checkBookJoin.get("id"));
        };
    }
}

说明 #

• 由于checkBooks是嵌入式类AccountParams的属性，必须先通过root.get("accountParams")定位到该嵌入式属性，才能进行后续的关联操作。
• 使用JoinType.LEFT确保即使没有关联的CheckBook记录，ClosingAccount也会被查询出来，再通过cb.id IS NULL筛选出无关联记录的结果。

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