Hibernate
Hibernate’s @FilterJoinTable
Couldn’t find a completely articulate example or full documentation of how to add a filter to a Hibernate Many-to-Many collection association mapping, so thought I’d would post a complete example here.
It’s possible to apply the filter to the target entity table which is reasonably well documented, or at least you can have an educated guess, but also to the association table of the Many-to-Many relationship where the docs were a little less clear.
I have the following relational association table:
CREATE TABLE departure_point_resort_association (
departure_point_id VARCHAR(36) NOT NULL,
resort_id VARCHAR(36) NOT NULL,
site_id ENUM('30', '31'...) NOT NULL,
PRIMARY KEY (departure_point_id, resort_id),
CONSTRAINT assoc_departure_point_fk FOREIGN KEY (departure_point_id)
REFERENCES departure_point (id),
CONSTRAINT assoc_resort_fk FOREIGN KEY (resort_id) REFERENCES resort (id)
);
which is mapping a ski resort to potential departure points or airports. There was no real requirement to persist an actual SITE table (which would be entirely static data) and to map to it’s foreign key value, so instead am using a simple MySQL ENUM column above, and wanted to apply a filter at the application level to the collection of Resorts based on this site_id.
@Entity @Table
@FilterDef(name = "siteFilter", parameters = {
@ParamDef(name = "siteId", type = "string")
})
public final class Resort extends BasePersistentObject {
@ManyToMany(fetch = FetchType.EAGER)
@FilterJoinTable(name = "siteFilter", condition = "site_id = :siteId")
@JoinTable(name = "DEPARTURE_POINT_RESORT_ASSOCIATION",
joinColumns = {
@JoinColumn(name = "RESORT_ID")
},
inverseJoinColumns = {
@JoinColumn(name = "DEPARTURE_POINT_ID")
}
)
private Set departurePoints;
Then it’s simply a case of enabling the filter in the client (DAO or whatever your framework dictates):
session.enableFilter("siteFilter").setParameter("siteId", String.valueOf(siteId));