1

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?

Cristian
  • 47
  • 1
  • 5
  • Can you try this @Formula("COALESCE(field1, field2)") reference:http://stackoverflow.com/questions/34163265/setting-up-query-for-specific-field-of-hibernate-entity – Elias Mar 17 '17 at 14:09

0 Answers0