SQL Question
Hibernate and @JoinFormula: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column
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 successfully in native SQL and want to achieve the same in Hibernate with Pojos.
COALESCE(NULLIF())
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
@JoinColumnsOrFormulasException 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 Source
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.