Use Hibernate Formula annotation for reference loading

Viewed 884

I have 3 entities - Storage, Item and Relation. Storage has several Item entities and items are bound by Relation entities. And relations can bind items from different storage. For simplification let say I want to load relation by query and want to do it fast. So now I have 3 query - loading for Storage, loading for all Items under storage and load of relation (List<Relation> relations field).

Now I want to tell hibernate how to load Collection<Relation> extRelation field. I tried @Formula, @CalculatedColumn and different combination of @ManyToMany and @JoinFormula. But generated query is wrong (or ignores my query). Also I cannot use @OneToMany because of https://hibernate.atlassian.net/browse/HHH-9897 bug.

Latest Exception is:

Caused by: org.h2.jdbc.JdbcSQLException: Table "TEST_STORAGES_TEST_RELATIONS" not found; SQL statement:

SELECT extrelatio0_.test_storages_storage_id AS test_sto1_2_0_
 ,extrelatio0_.extRelation_from_item_id AS extRelat2_3_0_
 ,extrelatio0_.extRelation_to_item_id AS extRelat3_3_0_
 ,relation1_.from_item_id AS from_ite1_1_1_
 ,relation1_.to_item_id AS to_item_2_1_1_
 ,relation1_.relation_id AS relation3_1_1_
 ,relation1_.storage_id AS storage_4_1_1_
FROM test_storages_test_relations extrelatio0_
INNER JOIN test_relations relation1_ ON extrelatio0_.extRelation_from_item_id = relation1_.from_item_id
 AND extrelatio0_.extRelation_to_item_id = relation1_.to_item_id
WHERE extrelatio0_.test_storages_storage_id = ?

My entities:

@Entity
@Table(name = "test_storages")
public class Storage {

    @Id
    @Column(name = "storage_id")
    private BigInteger storageId;

    private String name;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true, targetEntity = Item.class)
    @JoinColumn(name = "storage_id", updatable = false)
    @MapKey
    private List<Item> items;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true, targetEntity = Relation.class)
    @JoinColumn(name = "storage_id", updatable = false)
    @Fetch(org.hibernate.annotations.FetchMode.SELECT)
    private List<Relation> relations;

    @ManyToMany()
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(formula =
            @JoinFormula(
                    value = "(select dep.from_item_id, dep.to_item_id from test_relations dep where dep.storage_id = ?)"
            )
        )
    })
    private Collection<Relation> extRelation;
}

@Entity
@Table(name = "test_items")
public class Item {

    @Id
    @Column(name = "item_id")
    private BigInteger itemId;

    private String name;

    @Column(name = "storage_id")
    private BigInteger storageId;
}

@Entity
@Table(name = "test_relations")
public class Relation {
    @Column(name = "relation_id")
    private BigInteger relationId;

    @Column(name = "storage_id")
    private BigInteger storageId;

    @EmbeddedId
    private RelationPK pk;
}

@Embeddable
public class RelationPK implements Serializable {
    @Column(name = "from_item_id")
    private BigInteger fromItemId;

    @Column(name = "to_item_id")
    private BigInteger toItemId;
}

All sources available on https://github.com/ainlolcat/test_hibernate_formula

0 Answers
Related