如何使用Criteria Builder处理一对多关联中的Null值问题

通过API使用Criteria Builder查询数据时遇到以下问题：

• 单独传入student_group表中存在的文本值条件，可正常获取结果
• 单独传入lastRollResult为Null的条件，也能正常获取结果
• 同时传入文本条件（如grade:5）和lastRollResult为Null的组合条件时，无法获取结果

API示例 #

• 可正常获取数据：http://localhost:8080/student?query=grade:5,lastRollResult%40SUCCESS%23FAILURE
• 无法获取数据：http://localhost:8080/student?query=grade:5,lastRollResult%null

相关代码 #

Controller类 #

@RequestMapping(method = RequestMethod.GET)
public Page<StudentDTO> index(MySpecificationsBuilder builder,
                              @PageableDefault(value = 25, page = 0) Pageable pageable) {
  Specification<Student> spec = builder.build();
  Page<Student> stu = studentService.findAll(spec, pageable);

  /** here some modification on return */

}

MySpecificationsBuilder类 #

public class MySpecificationsBuilder extends BaseSpecification<Student> {
  
  public MySpecificationsBuilder(final SearchCriteria criteria) {
    super(criteria);
  }

  @Override
  protected Expression<String> getPath(SearchCriteria criteria, Root<Student> root) {
    /** some other conditions */
   
    if (criteria.getKey().equals("lastRollResult"))  {    
      if (!"null".contains(criteria.getValue())) {
        Join results = root.join("lastRoll", JoinType.INNER); // In case of text (JoinType.INNER is enum of Inner.
        return results.get("result");
      } else {
        return root.get("lastRoll"); // in case of null
      }
    }
    return root.get(criteria.getKey());
  }

  /** below toPredicate methods  */

}

实体类定义 #

Student类

@Entity
@Data
@DiscriminatorFormula("case when entity_type is null then ‘Student’ else entity_type end")
@DiscriminatorValue("Student")
public class Student extends AbstractStudent {
  @ManyToOne
  @Fetch(value = FetchMode.SELECT)
  @JoinColumn(name = "created_by_id", insertable = false, updatable = false)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  @NotFound(action = NotFoundAction.IGNORE)
  User createdBy;
  @Transient
  private String name;

  @Transient
  private List<String> tags;
}

AbstractStudent类

@RequiresAudit
@Log4j2
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Data
@Entity
@Table(name = "student_group")
@DiscriminatorColumn(name = "entity_type", discriminatorType = DiscriminatorType.STRING)
public class AbstractStudent extends BaseModel {

  @OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  List<StudentMapping> studentMappings;
  
  @OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  List<StudentDataMapping> studentDataMapping;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @AuditColumn
 
  @Column(name = "last_roll_id")
  private Long lastRollId;

  
  @OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
  @EqualsAndHashCode.student
  @ToString.Exclude
  private Set<TagUse> tagUses;
  @ManyToOne
  @Fetch(value = FetchMode.SELECT)
  @JoinColumn(name = "last_roll_id", referencedColumnName = "id", insertable = false, updatable = false)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  @NotFound(action = NotFoundAction.IGNORE)
  private Result lastRoll;

  /** some other column defination */
}

问题分析 #

问题根源在于：查询lastRollResult文本值时用了INNER JOIN关联lastRoll表，但查询Null值时直接操作root.get("lastRoll")（本质是判断last_roll_id为Null）。组合查询时，INNER JOIN会过滤掉lastRoll为Null的记录，导致和Null条件冲突，最终没有结果返回。

解决方案 #

方案1：统一使用LEFT JOIN替代INNER JOIN #

修改MySpecificationsBuilder中lastRollResult的处理逻辑，无论是否查询Null，都用LEFT JOIN关联lastRoll，然后分别处理两种条件：

@Override
protected Expression<?> getPath(SearchCriteria criteria, Root<Student> root) {
  /** some other conditions */
   
  if (criteria.getKey().equals("lastRollResult"))  {    
    // 统一用LEFT JOIN，保留lastRoll为Null的记录
    Join<Student, Result> lastRollJoin = root.join("lastRoll", JoinType.LEFT);
    if (!"null".contains(criteria.getValue())) {
      // 查询文本值时，取result字段
      return lastRollJoin.get("result");
    } else {
      // 查询Null时，判断关联的lastRoll是否为Null
      return lastRollJoin;
    }
  }
  return root.get(criteria.getKey());
}

同时，在toPredicate方法中，当处理lastRollResult为Null的条件时，要生成IS NULL的Predicate，而非等于Null的判断：

// 假设toPredicate方法中有类似逻辑
if ("null".equals(criteria.getValue())) {
  return cb.isNull(path);
} else {
  // 处理文本值的匹配逻辑，比如IN、EQUAL等
  return cb.in(path).value(criteria.getValue().split("#"));
}

方案2：拆分条件逻辑，避免Join冲突 #

不在getPath中做Join判断，而是在toPredicate方法中分别处理两种场景：

@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
  if (criteria.getKey().equals("lastRollResult")) {
    if ("null".contains(criteria.getValue())) {
      // 直接判断lastRoll为Null
      return cb.isNull(root.get("lastRoll"));
    } else {
      // 用INNER JOIN查询文本值
      Join<Student, Result> lastRollJoin = root.join("lastRoll", JoinType.INNER);
      return cb.in(lastRollJoin.get("result")).value(criteria.getValue().split("#"));
    }
  }
  // 其他字段的处理逻辑
  return super.toPredicate(root, query, cb);
}

这种方式可以避免同一查询中同时存在两种Join逻辑的冲突，每个条件独立处理对应的关联方式。

方案3：直接使用lastRollId字段判断Null #

因为AbstractStudent中已经有lastRollId字段，查询Null条件时可以直接判断该字段为Null，无需关联lastRoll表：

@Override
protected Expression<?> getPath(SearchCriteria criteria, Root<Student> root) {
  /** some other conditions */
   
  if (criteria.getKey().equals("lastRollResult"))  {    
    if (!"null".contains(criteria.getValue())) {
      Join<Student, Result> lastRollJoin = root.join("lastRoll", JoinType.INNER);
      return lastRollJoin.get("result");
    } else {
      // 直接返回lastRollId字段判断Null
      return root.get("lastRollId");
    }
  }
  return root.get(criteria.getKey());
}

这种方式更高效，因为不需要关联表就能判断Null条件，同时和文本查询的INNER JOIN不会冲突。

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