Hibernate/JPA unidirectional OneToMany with join condition on constant value in source table

Question

I want to use Hibernate annotations to represent a unidirectional one-to-many relationship using a join. I want an added condition on the join so it only happens when a column in the source table (the "one") is equal to a constant value. For example.

SELECT *
FROM buildings b
LEFT JOIN building_floors bf on bf.building_id = b.id AND b.type = 'OFFICE'

I want to represent the b.type = 'OFFICE' part of that query.

My question is quite similar to this one, except I have a condition on the source table. JPA/Hibernate Join On Constant Value

The Java entities look like this:

@Entity
@Table(name = "buildings")
public class Building {

    @Id
    @Column(name = "id")
    private int id;

    @Column(name = "type")
    private String type;

    @OneToMany(mappedBy = "buildingId",
            fetch = FetchType.EAGER,
            cascade = {CascadeType.ALL},
            orphanRemoval = true)
    @Fetch(FetchMode.JOIN)
    // buildings.type = 'OFFICE'   ????
    private Set<BuildingFloors> buildingFloors;

    // getters/setters
}

@Entity
@Table(name = "building_floors")
public class BuildingFloor {

    @Id
    @Column(name = "building_id")
    private int buildingId;

    @Id
    @Column(name = "floor_id")
    private int floorId;

    @Column(name = "description")
    private String description;

    // getters/setters
}

I've tried a few things where I have that placeholder comment:

@Where annotation

This doesn't work since that applies to the target entity.

@JoinColumns annotation

@JoinColumns({
        @JoinColumn(name = "building_id", referencedColumnName = "id"),
        @JoinColumn(name = "'OFFICE'", referencedColumnName = "type")
})

This doesn't work because I get the following error (simplified for clarity): Syntax error in SQL statement "SELECT * FROM buildings b JOIN building_floors bf on bf.building_id = b.id AND bf.'OFFICE' = b.type"

A different @JoinColumns annotation

@JoinColumns({
        @JoinColumn(name = "building_id", referencedColumnName = "id"),
        @JoinColumn(name = "buildings.type", referencedColumnName = "'OFFICE'")
})

This doesn't work because when using a unidirectional OneToMany relationship, the referencedColumnName is from the source table. So I get the error: org.hibernate.MappingException: Unable to find column with logical name: 'OFFICE' in buildings

Thanks in advance!

Answer 1

Why not use inheritance ? (I use it with JPA, I never use hibernate directly)

@Entity
@Inheritance
@Table(name = "buildings")
@DiscriminatorColumn(name="type")
public class Building {

    @Id
    @Column(name = "id")
    private int id;

    @Column(name = "type")
    private String type;
}

And :

@Entity
@DiscriminatorValue("OFFICE")
public class Office extends Building {
    @OneToMany(mappedBy = "buildingId",
        fetch = FetchType.EAGER,
        cascade = {CascadeType.ALL},
        orphanRemoval = true)
    private Set<BuildingFloors> buildingFloors;
}

Answer 2

Create database View with the following select:

SELECT bf.* FROM building_floors bf JOIN buildings b on bf.building_id = b.id AND b.type = 'OFFICE'

Map it to a class OfficeBuildingFloors as an ordinary entity and then use @OneToMany for it in Building class.

Of course, you won't be able to modify such collection and to avoid any exception you can use @Immutable on OfficeBuildingFloors.