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