基于Specification<T>实现多对一关联表（City/Country）过滤

需求是根据请求中的国家代码数组，筛选出对应国家的所有城市，请求参数示例如下：

{
    "country_code":["IT", "FR", "EG"]
}

涉及的两个实体类代码如下：

City实体类 #

package comgenchi.geotools.model;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;

import org.hibernate.validator.constraints.Range;
import org.springframework.validation.annotation.Validated;

import lombok.Data;

@Data
@Entity
@Table(name="countries")
@Validated
public class City {
    
    @Id
    @GeneratedValue
    protected int Id;

    @ManyToOne
    @JoinColumn(name="country_code")
    protected Country country;

    protected String postal_code;
    
    @NotBlank(message="position can not be blank")
    protected String position;

    @NotBlank(message="region can not be blank")
    protected String region;

    @NotBlank(message="region_code can not be blank")
    protected String region_code;

    @NotBlank(message="province can not be blank")
    protected String province;
    
    @Pattern(regexp="^([A-Z]{2,3})|([/d]{0,5})|(^$)$", message="sigle province format error")
    protected String sigle_province;

    @Range(min=-90, max=90, message="Range for latitude must be from -90 to 90")
    protected String latitude;

    @Range(min=-180, max=180, message="Range for longitude must be from -90 to 90")
    protected String longitude;

}

Country实体类 #

package comgenchi.geotools.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;

import org.springframework.validation.annotation.Validated;

import lombok.Data;

@Data
@Entity
@Table(name="countries")
@Validated
public class Country {

    @Id
    @Column(name="country_code", unique=true, nullable=false)
    @Pattern(regexp="^[A-Za-z]{2}$", message="country_code must be have 2 letters")
    @NotBlank(message = "country_code not blank")
    protected String country_code;

    @Column(name="country", unique=true, nullable=false)
    @NotBlank(message = "country name not blank")
    protected String name;

}

现有Specs<T>类已实现Specification接口，但关联表的连接逻辑未完成，需完善以实现根据国家代码数组筛选城市的功能：

待完善的Specs类 #

package comgenchi.geotools.controller;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.lang.Nullable;

import comgenchi.geotools.model.SearchCriteria;

// return in all method un specification<Country>
public final class Specs<T> implements Specification<T> {

  private final SearchCriteria criteria;

  public Specs(SearchCriteria searchCriteria) {
    this.criteria = searchCriteria;
  }

  @Override
  @Nullable
  public Predicate toPredicate(
    Root<T> root,
    CriteriaQuery<?> query,
    CriteriaBuilder builder
  ) {
    
    Path<Object> key = null;
    //here resolve join Join Country/City

    switch (criteria.getOperation()) {
      case ":":
        if (key.getJavaType() == String.class) {
          return builder.like(
            root.<String>get(criteria.getKey()),
            "%" + criteria.getValue() + "%"
          );
        } else {
          return builder.equal(key, criteria.getValue());
        }
      case "=":
        return builder.equal(key, criteria.getValue());
      case "in":
        return builder.in(key).value(criteria.getValue());
      default:
        return null;
    }
  }
}

完善后的Specs类代码 #

package comgenchi.geotools.controller;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.lang.Nullable;

import comgenchi.geotools.model.City;
import comgenchi.geotools.model.Country;
import comgenchi.geotools.model.SearchCriteria;

public final class Specs<T> implements Specification<T> {

  private final SearchCriteria criteria;

  public Specs(SearchCriteria searchCriteria) {
    this.criteria = searchCriteria;
  }

  @Override
  @Nullable
  public Predicate toPredicate(
    Root<T> root,
    CriteriaQuery<?> query,
    CriteriaBuilder builder
  ) {
    
    Path<Object> keyPath;
    // 处理关联属性：当筛选的是country_code时，需要关联Country实体
    if ("country_code".equals(criteria.getKey())) {
      // 对于City实体，关联到Country
      if (root.getModel().getJavaType().equals(City.class)) {
        Join<City, Country> countryJoin = root.join("country");
        keyPath = countryJoin.get("country_code");
      } else {
        // 如果是Country实体直接查询，直接取字段
        keyPath = root.get(criteria.getKey());
      }
    } else {
      // 非关联属性直接取根实体的字段
      keyPath = root.get(criteria.getKey());
    }

    switch (criteria.getOperation()) {
      case ":":
        if (keyPath.getJavaType() == String.class) {
          return builder.like(
            (Path<String>) keyPath,
            "%" + criteria.getValue() + "%"
          );
        } else {
          return builder.equal(keyPath, criteria.getValue());
        }
      case "=":
        return builder.equal(keyPath, criteria.getValue());
      case "in":
        // 确保传入的是集合类型，这里直接传入参数值即可
        return builder.in(keyPath).value(criteria.getValue());
      default:
        return null;
    }
  }
}

关键说明 #

1. 关联处理逻辑：当筛选字段为country_code时，判断当前根实体是否为City，如果是则通过root.join("country")建立与Country的关联，然后获取Country中的country_code字段路径。
2. 修正路径引用：原代码中在like分支错误使用了root.get(criteria.getKey())，统一改为使用构建好的keyPath，保证关联字段和普通字段的路径一致性。
3. 类型转换：在like操作中对keyPath进行类型转换，避免编译错误。

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