How to use the Hibernate @JoinFormula annotation with a @OneToMany JPA association?

Hibernate ORM
1

Suppose I have the following entity:

@Entity
@Table(name = "invoice")
public class Invoice implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator", allocationSize = 1000)
    private Long id;

    @Column(name = "hierarchy_path")
    private String hierarchyPath;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinFormula(value = "hierarchy_path like id || '/_%'", referencedColumnName = "hierarchy_path")
    private Set<Invoice> childInvoices;
}

And following data in table invoice:

id                  hierarchy_path
5325000             5325000/
5326000             5325000/5326000/
5327000             5325000/5326000/5327000/

i.e. every invoice can be corrected by another invoice (and correction can be corrected as well).

I would like childInvoices to contain the set of all corrections of given invoice. However such mapping fails during application startup with exception

java.lang.ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column

I tried many other ways to map it but I cannot get this to work. Please explain why it doesn’t work and if there actually is a way to accomplish that.

The resulting select should like this:

select invoice0_.id                   as id1_8_0_,
       invoice0_.hierarchy_path       as hierarchy_path4_8_0_,
       invoice1_.id                   as id1_8_1_,
       invoice1_.hierarchy_path       as hierarchy_path4_8_1_
from invoice invoice0_ 
left outer join invoice invoice1_ on invoice1_.HIERARCHY_PATH like invoice0_.ID || '/_%';
2

As explained in this article, move the @JoinFormula to the @ManyToOne side and make the @OneToMany bidirectional.

A bidirectional association is also much more efficient than a unidirectional one.

3

I tried this:

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula(value = "SUBSTR(hierarchy_path, 0, INSTR(hierarchy_path, '/') - 1)", referencedColumnName = "id")
    private Invoice rootInvoice;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "rootInvoice")
    private Set<Invoice> childInvoices;

but it fails with the same exception. It works if I remove OneToMany completely and leave only ManyToOne.

1 Like
4

Send a replicating test case as a Pull Request as explained in this article:

http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/

I’ll take a look on it afterward.

5

I added issue https://hibernate.atlassian.net/browse/HHH-12997.

6

It is happening the same to me. I’m just trying to retrieve a list from a JoinFormula but it just seems impossible.

1 Like
7

Same here. I’m interested in this feature and I’m willing to contribute to implement it.