Hibernate and @JoinFormula: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column

Question

I'm trying to write a hibernate adapter for an old database schema. This schema does not have a dedicated id column, but uses about three other columns to join data.

On some tables, I need to use coalesce. This is what I came up with so far:

About the definition:

• A car can have elements, assigned by the car's user or by the car's group of users.
• If FORIGN_ELEMENT holds a user's name, definition will be 'u'
• If FORIGN_ELEMENT holds a group's name, definition will be 'g'
• This also means, one table (CAR_TO_ELEMENT) is misused to map cars to elements and cargroups to elements. I defined a superclass CarElement and subclasses CarUserElement and CarGroupElement.
• state is either "active" or an uninteresting string
• I set definitition and state elsewhere, we do not need to worry about this.
• Use DEP_NR on the join table. If it's zero, use USR_DEP_NR. I did this with COALESCE(NULLIF()) successfully in native SQL and want to achieve the same in Hibernate with Pojos.

Okay, here we go with the code:

@Entity
@Table(name="CAR")
public class Car extends TableEntry implements Serializable {
    @Id
    @Column(name="DEP_NR")
    private int depnr;

    @Id
    @Column(name="USER_NAME")
    @Type(type="TrimmedString")
    private String username;

    @ManyToOne(fetch = FetchType.EAGER, targetEntity=CarGroup.class)
    @JoinColumns(value={ 
            @JoinColumn(name="GROUP_NAME"),
            @JoinColumn(name="DEP_NR"),
            @JoinColumn(name="state"),
    })
    private CarGroup group;

    @OneToMany(fetch=FetchType.EAGER, targetEntity=CarUserElement.class, mappedBy="car")
    private Set<CarUserElement> elements;
}

@Entity
@Table(name="CAR_GROUP")
public class CarGroup extends TableEntry implements Serializable {
    @Id
    @Column(name="DEP_NR")
    private int depnr;

    @Id
    @Column(name="GROUP_NAME")
    @Type(type="TrimmedString")
    private String group;

    @ManyToOne(fetch = FetchType.EAGER, targetEntity=Car.class)
    @JoinColumns(value={ 
            @JoinColumn(name="GROUP_NAME"),
            @JoinColumn(name="DEP_NR"),
            @JoinColumn(name="state"),
    })
    private Set<Car> cars;

    @OneToMany(fetch=FetchType.EAGER, targetEntity=CarGroupElement.class, mappedBy="car")
    private Set<CarGroupElement> elements;
}

@MappedSuperclass
public class CarElement extends TableEntry {
    @Id
    @ManyToOne(fetch = FetchType.EAGER, targetEntity=Element.class)
    @JoinColumns(value={ 
            @JoinColumn(name="ELEMENT_NAME"),
            @JoinColumn(name="state"),
    })
    private Element element;
}

@Entity
@Table(name="CAR_TO_ELEMENT")
public class CarUserElement extends CarElement {
    @Id
    @Column(name="DEFINITION")
    private char definition;

    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumnsOrFormulas(value = {
        @JoinColumnOrFormula(formula=@JoinFormula(value="COALESCE(NULLIF(DEP_NR, 0), USR_DEP_NR)", referencedColumnName="DEP_NR")),
        @JoinColumnOrFormula(column=@JoinColumn(name="FORIGN_ELEMENT", referencedColumnName="USER_NAME")),
        @JoinColumnOrFormula(column=@JoinColumn(name="STATE", referencedColumnName="STATE"))
    })
    private Car car;

}

@Entity
@Table(name="CAR_TO_ELEMENT")
public class CarGroupElement extends CarElement {
    @Id
    @Column(name="DEFINITION")
    private char definition;

    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumnsOrFormulas(value = {
        @JoinColumnOrFormula(formula=@JoinFormula(value="COALESCE(NULLIF(DEP_NR, 0), USR_DEP_NR)", referencedColumnName="DEP_NR")),
        @JoinColumnOrFormula(column=@JoinColumn(name="FORIGN_ELEMENT", referencedColumnName="GROUP_NAME")),
        @JoinColumnOrFormula(column=@JoinColumn(name="STATE", referencedColumnName="STATE"))
    })
    private Car car;

}

I tried all available versions of hibernate (from 3.5.1 [first version with @JoinColumnsOrFormulas] up to 4.x.x), but I always get this error:

Exception in thread "main" java.lang.ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column
    at org.hibernate.cfg.annotations.TableBinder.bindFk(TableBinder.java:351)
    at org.hibernate.cfg.annotations.CollectionBinder.bindCollectionSecondPass(CollectionBinder.java:1338)
    at org.hibernate.cfg.annotations.CollectionBinder.bindOneToManySecondPass(CollectionBinder.java:791)
    at org.hibernate.cfg.annotations.CollectionBinder.bindStarToManySecondPass(CollectionBinder.java:719)
    at org.hibernate.cfg.annotations.CollectionBinder$1.secondPass(CollectionBinder.java:668)
    at org.hibernate.cfg.CollectionSecondPass.doSecondPass(CollectionSecondPass.java:66)
    at org.hibernate.cfg.Configuration.originalSecondPassCompile(Configuration.java:1597)
    at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1355)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1737)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1788)

Other hibernate users seem to have the same problem: They can't get it working with any version, see this thread and other stackoverflow questions: https://forum.hibernate.org/viewtopic.php?f=1&t=1010559

To be more complete, here's my TrimmedString Class: https://forum.hibernate.org/viewtopic.php?p=2191674&sid=049b85950db50a8bd145f9dac49a5f6e#p2191674

Thanks in advance!

PS: It works with joining just these three colulmns with just one DEP-NR-Column (i.e. either DEP_NR OR USR_DEP_NR using just @JoinColumns). But I need this coalesce(nullif()).

Dustin

Answer 1

Join formulas are very fragile in Hibernate for the time being; I always had a difficult time to get them work properly.

The workaround that helped me often was to create database views which exposed the proper columns (including foreign keys that don't exist in the original tables). Then I mapped the entities to the views using classing Hibernate/JPA mappings.

Sometimes there are redundant joins in the generated SQL when using such entities, but the database optimizes such queries in most cases so that the execution plan is optimal anyway.

Another approach could be using @Subselects, which are some kind of Hibernate views, but I expect them to be less performant than the classic database views.

Pamplona

Sadly, I cannot create views - old "fragile" Database. Yes, views would have helped me to avoid join formulas. :-( – Dustin

Ok, I edited my answer; you may also want to take a look at Hibernate @Subselect. – Pamplona

Thanks for your suggestion. Sadly, this will create one more query each time, and I have thousands of those. Subselects might work some way or another, but I always thought joinformulas are made specific for this use case. I like the idea of having a solution, though. – Dustin

I use subselects now, even if they are not the same. It's the only way I got it working, and you never had join formulas working either. Thank you very much. – Dustin

Thanks folks! Subselect saved me too! – Convolution

Answer 2

I ran into a similar problem, and it seems that the issue is that you are using a @Formula inside an @Id. Hibernate wants Ids to be insertable, and Formulas are read-only.

In my case I was able to work around the problem by making the individual columns Id properties on their own, and making the joined object a separate property. I don't know if this would work in your case since you're using two different columns in your formula, but if so your code might look something like:

@Entity
@Table(name="CAR_TO_ELEMENT")
public class CarUserElement extends CarElement {
    @Id
    @Column(name="DEFINITION")
    private char definition;

    @Id
    @Column(name="DEP_NR")
    private Integer depNr;

    @Id
    @Column(name="USR_DEP_NR")
    private Integer usrDepNr;

    @Id
    @Column(name="FORIGN_ELEMENT")
    private String userName;

    @Id
    @Column(name="STATE")
    private String state;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumnsOrFormulas(value = {
        @JoinColumnOrFormula(formula=@JoinFormula(value="COALESCE(NULLIF(DEP_NR, 0), USR_DEP_NR)", referencedColumnName="DEP_NR")),
        @JoinColumnOrFormula(column=@JoinColumn(name="FORIGN_ELEMENT", referencedColumnName="USER_NAME", insertable = false, updatable = false)),
        @JoinColumnOrFormula(column=@JoinColumn(name="STATE", referencedColumnName="STATE", insertable = false, updatable = false))
    })
    private Car car;

}

Dutiful

Sadly, Hibernate is complaining about duplicate definitions. :-( – Dustin

What does Hibernate think is being duplicated? Did you include the insertable = false, updatable = false bit? – Dutiful

Answer 3

I ran into the cast exception as well and I'm on Hibernate 5.x.

Until Hibernate dedicates time to fix the issue, I found that while this guy's approach may not be cleanest (he even eludes to that fact!), it works.

You just need to add the @Column mappings (and get/set methods) to your association table objects that are returning null and manually set the values when you populate the relation data. Simple but effective!

Saleratus

Link is broken. – Taurus

This was the only solution I could find that really worked. Thank you! – Morrow