如何使用HQL跨多层映射表查询指定国家对应的Brand实体

场景说明 #

使用HQL从指定Country中查询Brand实体，需要依次关联Address、City、State三层表完成条件过滤。

实体映射配置 #

Brand类 #

@Table(name = "brand")
public class Brand extends BasicModel {

    private String name;
    @OneToMany(targetEntity = Address.class, cascade = CascadeType.ALL,
                fetch = FetchType.LAZY, orphanRemoval = true)
    @JoinTable(name="brand_address",
            joinColumns = { @JoinColumn(name = "brand_id") },
            inverseJoinColumns = { @JoinColumn(name = "address_id") })
    private Set<Address> address;

Address类 #

@Table(name = "address")
public class Address extends BasicModel {

    @NotNull
    @ManyToOne
    private AddressCity city;

AddressCity与AddressState关联、AddressState与AddressCountry关联的方式，和上述Address关联AddressCity的方式完全一致。brand_address关联表由Hibernate自动生成，数据填充正常。

问题尝试过程 #

可行但不符合最佳实践的方案 #

直接查询Brand后手动过滤其他国家的结果，查询语句如下：

Query query = session
        .createQuery("from Brand brand " +
                        "where brand.name = :brandName " +
                            "and brand.deleted is null");
query.setParameter("brandName", brandName);

该方案会查询出多余数据再做内存过滤，性能较差。

错误写法1 #

Query query = session
        .createQuery("from Brand brand " +
                        "where brand.name = :brandName " +
                            "and Address.city.state.country.code = :countryCode " +
                            "and brand.deleted is null");
query.setParameter("brandName", brandName);
query.setParameter("countryCode", countryCode);

错误原因：未和当前查询的Brand实体建立关联路径，直接引用Address类的属性，HQL无法识别实体关联关系。

错误写法2 #

Query query = session
        .createQuery("from Brand brand " +
                        "where brand.name = :brandName " +
                            "and AddressCountry.code = :countryCode " +
                            "and brand.deleted is null");
query.setParameter("brandName", brandName);
query.setParameter("countryCode", countryCode);

错误原因：直接引用末端实体AddressCountry的属性，未走实体关联路径，HQL无法生成正确的多表关联SQL。

加入left join后的报错 #

尝试添加关联语句：

left join brand.address as a
with a.city.state.country.code = :countryCode

出现报错：java.sql.SQLSyntaxErrorException: Unknown column 'addresscit3_.state_id' in 'on clause'
对应运行日志：

14:02:43.646 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [brand brand0_]
14:02:43.646 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join address_state addresssta4_ on addresscit3_.state_id=addresssta4_.id]
14:02:43.646 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join address_country addresscou5_ on addresssta4_.country_id=addresscou5_.id]
14:02:43.647 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join brand_address addresses1_ on brand0_.id=addresses1_.brand_id inner join address address2_ on addresses1_.address_id=address2_.id and (addresscou5_.code=?)]
14:02:43.647 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join address_city addresscit3_ on address2_.city_id=addresscit3_.id]
14:02:43.647 [main] DEBUG org.hibernate.hql.internal.antlr.HqlSqlBaseWalker - select >> end [level=1, statement=select]

错误原因：Hibernate生成SQL时关联顺序异常，将国家表的关联条件放在了城市表关联之前，导致找不到城市表的state_id字段，同时关联属性配置为懒加载，未提前触发关联加载也会导致关联逻辑异常。

最终解决方案 #

使用join fetch处理懒加载特性，明确指定实体关联路径，Hibernate会自动按正确顺序生成多层关联SQL，最终可用HQL如下：

.createQuery("from Brand brand " +
                "join fetch brand.addresses as a " +
                    "where brand.name = :brandName " +
                    "and a.city.state.country.code = :countryCode " +
                    "and brand.deleted is null");

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