SpringBoot JPA多表关联查询指定productType与Sizes数据问题

• 实体类@OneToOne映射配置错误，外键关联关系写反
• 查询方法返回值为单个ProductType对象，仅能捕获结果集第一条数据，导致无论传入什么尺寸都只返回首行的XS结果
• 第三个查询未声明WHERE条件，Spring Data JPA会按照方法名findAllByProdTypeAndSizes自动解析属性，试图从ProductType中查找不存在的sizes字段，抛出异常

1. 修正实体类关联映射 #

外键所在的子表实体配置@JoinColumn，父表实体用mappedBy声明关联，避免重复维护外键：

ProductType 调整 #

@Table(name = "MC_Product_Type")
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@ApiModel
@JsonInclude(JsonInclude.Include.NON_NULL)
public class ProductType {
    @Id
    private int prodTypeId;
    private String prodType;
    private String description;

    // 关联关系由SetRules中的productType属性维护
    @OneToOne(mappedBy = "productType", fetch = FetchType.EAGER) // 按需配置加载策略
    private SetRules setRules;
}

SetRules 调整 #

@Table(name = "MC_Set_Rules")
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
public class SetRules {
    @Id
    private int setId;
    // 删掉原来的普通字段prodTypeId，改为关联配置
    @OneToOne
    @JoinColumn(name = "prod_type_id", referencedColumnName = "prod_type_id", insertable = false, updatable = false)
    private ProductType productType;
    private String setName;
    private String setType;
    private String condition;

    // 关联关系由SizeRulesEntity中的setRules属性维护
    @OneToOne(mappedBy = "setRules", fetch = FetchType.EAGER)
    private SizeRulesEntity sizeRules;
}

SizeRulesEntity 调整 #

@Table(name = "MC_Size_Rules")
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
public class SizeRulesEntity {
    @Id
    private int prodRuleId;
    private String prodType;
    private String sizes;
    // 删掉原来的普通字段setId，改为关联配置
    @OneToOne
    @JoinColumn(name = "set_id", referencedColumnName = "set_id", insertable = false, updatable = false)
    private SetRules setRules;
    private int minQty;
    private String dimension;

    // 关联关系由ProductRules中的sizeRules属性维护
    @OneToOne(mappedBy = "sizeRules", fetch = FetchType.EAGER)
    private ProductRules productRules;
}

ProductRules 调整 #

@Table(name = "MC_Product_Rules")
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
public class ProductRules {
    @Id
    private int prodRuleId;
    private String prodType;
    private String allowedType;
    private String availability;
    private String prodLabel;
    private String locations;

    @OneToOne
    @JoinColumn(name = "prod_rule_id", referencedColumnName = "prod_rule_id", insertable = false, updatable = false)
    private SizeRulesEntity sizeRules;
}

2. 修正Repository代码 #

• 修正JpaRepository泛型，ProductType主键类型为int，对应泛型为Integer
• 返回值改为List<ProductType>接收所有符合条件的结果
• WHERE条件直接使用JOIN别名过滤，避免嵌套属性解析异常

// 接口名建议和泛型对应，操作的是ProductType实体可改为ProductTypeRepository，保留原有名称也可正常运行
public interface ProductTypeRepository extends JpaRepository<ProductType, Integer> {
    @Query("SELECT pt FROM ProductType pt " +
            "JOIN pt.setRules s " +
            "JOIN s.sizeRules sr " +
            "JOIN sr.productRules pr " +
            "WHERE pt.prodType = :prodType AND sr.sizes = :sizes")
    List<ProductType> findAllByProdTypeAndSizes(String prodType, String sizes);
}

额外说明 #

如果需要同时查询多个尺寸，可将方法参数改为List<String> sizes，WHERE条件改为sr.sizes IN :sizes即可批量匹配。

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