I have an issue with retrieving an embedded record using @JoinColumnOrFormula in Hibernate.
The embeddable:
@Embeddable
public class Location implements Serializable {
@Id
@Column(name = "loc_id")
private String id;
@Column(name = "loc_city")
private String city;
@Column(name = "loc_building")
private String building;
@Column(name = "loc_floor")
private String floor;
@Column(name = "loc_room")
private String room;
}
is being used by:
@Entity
@Table(name = "Persons")
public class Person implements Serializable {
...
@ManyToOne
@JoinColumnsOrFormulas({
@JoinColumnOrFormula(formula = @JoinFormula(value = "prs_loc_city", referencedColumnName = "loc_city")),
@JoinColumnOrFormula(formula = @JoinFormula(value = "prs_loc_building", referencedColumnName = "loc_building")),
@JoinColumnOrFormula(formula = @JoinFormula(value = "prs_loc_floor", referencedColumnName = "loc_floor")),
@JoinColumnOrFormula(formula = @JoinFormula(value = "prs_loc_room", referencedColumnName = "loc_room"))})
private Location location = new Location();
...
}
which generates the correct Oracle SQL for retrieving the person's location:
SELECT
...
FROM PERSONS this_
LEFT OUTER JOIN LOCATIONS location_
ON this_.prs_loc_city = location_.loc_city
AND this_.prs_loc_building = location_.loc_building
AND this_.prs_loc_floor = location_.loc_floor
AND this_.prs_loc_room = location_.loc_room
However, this works for all cases when all four columns are provided. A person with a column prs_loc_room empty (null) will not retrieve the corresponding record from the Locations table, although it exists (loc_room is null).
The correct SQL for this to work would be something like:
SELECT
...
FROM PERSONS this_
LEFT OUTER JOIN LOCATIONS location_
ON NVL(this_.prs_loc_city, 'null') = NVL(location_.loc_city, 'null')
AND NVL(this_.prs_loc_building, 'null') = NVL(location_.loc_building, 'null')
AND NVL(this_.prs_loc_floor, 'null') = NVL(location_.loc_floor, 'null')
AND NVL(this_.prs_loc_room, 'null') = NVL(location_.loc_room, 'null')
But how to reach this using Hibernate?