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()).

Answer 1

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;

}

Answer 2

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.