Selecting one object from a one-to-many relationship in Hibernate

Question

I have two tables:

Job
job_id, <other data>

Job_Link
job_link_id, job_id, start_timestamp, end_timestamp, <other data>

There may be multiple records in Job_Link specifying the same job_id with the start_timestamp and end_timestamps to indicate when those records are considered "current", it is guaranteed that start_timestamp and end_timestamp will not overlap.

We have entities for both the Job and Job_Link tables and defining a one-to-many relationship to load all the job_links wouldn't be a problem. However we'd like to avoid that and have a single job_link item in the Job entity that will contain only the "current" Job_Link object.

Is there any way to achive that?

Answer 1

It is possible with the @WhereJoinTable annotation. In the Job entity add a @OneToOne link exactly as you would define the @OneToMany on the JobLink object.

@OneToOne(...)
@WhereJoinTable(clause = "now() between start_timestamp and end_timestamp") // mysql
@WhereJoinTable(clause = "sysdate between start_timestamp and end_timestamp") // oracle ...
private JobLink jobLink

Drawbacks : it breaks the database and JPA provider portability