Hibernate : Pagination query throws an “Ambiguous Column Exception” because of unusual mapping

I am working on a project and I have a “weird” mapping to manage. Nevermind, this is the structure :

• Element is composed by a single FK composed of cmpcode, elmlevel and code

• Invoice is composed by cmpcode, element2 (which is an element of level 2, with same cmpcode), element3 (which is an element of level 3, with same cmpcode)

So the table INVOICE has 4 columns

• ID

• CMPCODE

• EL2 (the code of the element of level 2 with the same CMPCODE)

• EL3 (the code of the element of level 3 with the same CMPCODE)

My mapping for the Invoice.java class is :

@Id
private Integer id;

@ManyToOne
@JoinColumn(name = "CMPCODE")
private Company company;

// This one does the mapping to Element, by forcing ELMLEVEL at 2, and using CMPCODE and EL2 for the rest of the mapping
@ManyToOne
@JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula = @JoinFormula(value = "2", referencedColumnName = "ELMLEVEL")),
        @JoinColumnOrFormula(column = @JoinColumn(name = "CMPCODE", referencedColumnName = "CMPCODE", insertable = false, updatable = false)),
        @JoinColumnOrFormula(column = @JoinColumn(name = "EL2", referencedColumnName = "CODE")) })
private Element element2;

// This on is used to be able to set el2 (because we can't in the property above)
private String el2;

@ManyToOne
@JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula = @JoinFormula(value = "3", referencedColumnName = "ELMLEVEL")),
        @JoinColumnOrFormula(column = @JoinColumn(name = "CMPCODE", referencedColumnName = "CMPCODE", insertable = false, updatable = false)),
        @JoinColumnOrFormula(column = @JoinColumn(name = "EL3", referencedColumnName = "CODE")) })
private Element element3;

private String el3;

If I do a single query, it works fine.

If I do a query with pagination, the query generated throws an ambiguous column query. Why ?

Because the single query is :

SELECT ID, CMPCODE, ELMLEVEL_1, CMPCODE_1, EL2_1, EL2_1, ELMLEVEL_2, CMPCODE_2, EL3_1, EL3_1 from INVOICE

As you see, there is 2 times EL2_1, because it’s in 2 properties, the one that is a String and the one that is an Element.

For single query, no issue.

But now, I we do a pagination query :

select * from (SELECT ID, CMPCODE, ELMLEVEL_1, CMPCODE_1, EL2_1, EL2_1, ELMLEVEL_2, CMPCODE_2, EL3_1, EL3_1 from INVOICE) where rownum<10

The ambiguous error is thrown.

I am stuck on it since 3 days. What should I do ?

If I remove the el2 String, I just can’t update it anymore (because of insertable / updatable false).

If I remove insertable/updatable false, I have a repeated column exception.

Note : I have simplified the model but I have element2 to element8

Answer

I just found the solution with a small “hack” of Hibernate. I have replaced the @JoinColumn to CMPCODE for a @JoinFormula that does exactly the same. But with this, I can remove the second element as a String, and also the updatable/insertable flag. And Hibernate seems happy with it.

// This one does the mapping to Element, by forcing ELMLEVEL at 2, and using CMPCODE and EL2 for the rest of the mapping
    @ManyToOne
    @JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula = @JoinFormula(value = "2", referencedColumnName = "ELMLEVEL")),
            @JoinColumnOrFormula(formula = @JoinFormula(name = "CMPCODE", referencedColumnName = "CMPCODE", insertable = false, updatable = false)),
            @JoinColumnOrFormula(column = @JoinColumn(name = "EL2", referencedColumnName = "CODE")) })
    private Element element2;