JPA Specification生成SQL错误：WHERE子句引用字段修正求助

我想用JPA Specification构建一条SQL，预期生成的语句如下：

select p1_0.* from plans p1_0 inner join plans_vehicle_models p2_0 on p2_0.plan_id = p1_0.plan_id where p2_0.vehicle_model_id in (select v1_0.model_id from vehicle_models v1_0 where v1_0.model_name = 'ModelName')

但实际生成的SQL却是：

select p1_0.* from plans p1_0 join plans_vehicle_models p2_0 on p1_0.plan_id=p2_0.plan_id where p1_0.plan_id in((select v1_0.model_id from vehicle_models v1_0 where v1_0.model_name=?)).

核心需求是把WHERE子句中的p1_0.plan_id替换成p2_0.vehicle_model_id。

相关代码 #

PlanSpecification类 #

@Component
public class PlanSpecification {

    public static Specification<Plan> vehicleModelNameExactlyIgnoringCase(String vehicleModelName) {
        return ((root, query, criteriaBuilder) -> {
            if (vehicleModelName != null) {
                Subquery<Long> subquery = query.subquery(Long.class);
                Root<VehicleModel> subqueryRoot = subquery.from(VehicleModel.class);
                subquery.select(subqueryRoot.get("id"));
                subquery.where(criteriaBuilder.equal(criteriaBuilder.upper(subqueryRoot.get("modelName")), vehicleModelName.toUpperCase()));
                Join<Plan, PlanToVehicleModelAssignment> plansVehicleModelsJoin = root.join(Plan_.PLANS_VEHICLE_MODELS, JoinType.INNER);
                return criteriaBuilder.in(root.get(PlanToVehicleModelAssignment_.ID)).value(subquery);
            } else {
                throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "`vehicle_model_name` parameter passed to filter resultset on Plan cannot be null");
            }
        });
    }
}

Plan实体类 #

@Data
@Entity
@Table(name = "plans")
public class Plan {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "plan_id", nullable = false, unique = true, columnDefinition = "BIGINT")
    protected Long id;

    @Column(name = "plan_code", unique = true, nullable = false)
    protected String code;

    @OneToMany(mappedBy = "plan", fetch = FetchType.LAZY)
    protected List<PlanToVehicleModelAssignment> planToVehicleModelAssignmentList = new ArrayList<>();
}

PlanToVehicleModelAssignment实体类 #

@Table(name = "plans_vehicle_models")
public class PlanToVehicleModelAssignment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "plan_vehicle_model_id", columnDefinition = "BIGINT")
    private Long id;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "plan_id", referencedColumnName = "plan_id")
    private Plan plan;

    @OneToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "vehicle_model_id", referencedColumnName = "model_id", unique = true)
    private VehicleModel vehicleModel;
}

Plan_元模型类 #

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(Plan.class)
public abstract class Plan_ {
    public static volatile SingularAttribute<Plan, Long> id;
    public static volatile SingularAttribute<Plan, String> code;
    public static volatile ListAttribute<Plan, PlanToVehicleModelAssignment> planToVehicleModelAssignmentList;

    public static final String ID = "id";
    public static final String CODE = "code";
    public static final String PLANS_VEHICLE_MODELS = "planToVehicleModelAssignmentList";
}

PlanToVehicleModelAssignment_元模型类 #

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(PlanToVehicleModelAssignment.class)
public abstract class PlanToVehicleModelAssignment_ {
    public static volatile SingularAttribute<PlanToVehicleModelAssignment, Long> id;
    public static volatile SingularAttribute<PlanToVehicleModelAssignment, Plan> plan;
    public static volatile SingularAttribute<PlanToVehicleModelAssignment, VehicleModel> vehicleModel;

    public static final String ID = "id";
    public static final String PLAN = "plan";
    public static final String VEHICLE_MODEL = "vehicleModel";
}

问题出在Specification的最后一行：你用了root.get(PlanToVehicleModelAssignment_.ID)，这相当于错误引用了Plan实体的属性，实际需要引用关联表plans_vehicle_models中的vehicle_model_id。

修改步骤：

1. 从已创建的plansVehicleModelsJoin对象中获取vehicleModel关联属性
2. 从该属性中获取ID，对应数据库的vehicle_model_id字段
3. 将criteriaBuilder.in的目标改为这个字段

修改后的Specification代码：

@Component
public class PlanSpecification {

    public static Specification<Plan> vehicleModelNameExactlyIgnoringCase(String vehicleModelName) {
        return ((root, query, criteriaBuilder) -> {
            if (vehicleModelName != null) {
                Subquery<Long> subquery = query.subquery(Long.class);
                Root<VehicleModel> subqueryRoot = subquery.from(VehicleModel.class);
                subquery.select(subqueryRoot.get("id"));
                subquery.where(criteriaBuilder.equal(criteriaBuilder.upper(subqueryRoot.get("modelName")), vehicleModelName.toUpperCase()));
                Join<Plan, PlanToVehicleModelAssignment> plansVehicleModelsJoin = root.join(Plan_.PLANS_VEHICLE_MODELS, JoinType.INNER);
                // 关键修改：引用关联表中的vehicle_model_id
                return criteriaBuilder.in(plansVehicleModelsJoin.get(PlanToVehicleModelAssignment_.VEHICLE_MODEL).get("id")).value(subquery);
            } else {
                throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "`vehicle_model_name` parameter passed to filter resultset on Plan cannot be null");
            }
        });
    }
}

如果有VehicleModel_元模型类，建议用元模型替代硬编码的"id"，类型更安全：

return criteriaBuilder.in(plansVehicleModelsJoin.get(PlanToVehicleModelAssignment_.VEHICLE_MODEL).get(VehicleModel_.ID)).value(subquery);

修改后生成的SQL会将WHERE条件改为p2_0.vehicle_model_id in (...)，与预期一致。

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