Ytukyg

Given the situation: The database was created when Hibernate wasn't born yet, we talk true legacy here.
I have tables that never have deleted records, just setting an active flag on the row to mark an entry as currently in use. Queries usually look like:

SELECT * from A left join B on A.id = B.a_id and B.active = 1

With Entity A and B defined in Java, and A has a property

@OneToMany

private Set<B> b; 

How do I annotate the property to add the criteria "and B.active=1"?

JPA-solution preferred, but if necessary we can use hibernate annotations too.

In a similar way we have cases where entries have an active date range, so we would have to add a criteria similar to

"and B.active_From >= now() and (B.active_to is null or B.active_to < now())"

You can use Hibernate's @Where annotation to filter elements from an association. You can find an example and explanation in this Hibernate Tip.

Here's the short version of it:

You can set an SQL snippet as the value of the clause attribute of the @Where annotation. So, your mapping should look like this:

@OneToMany

@Where(clause = "active = 1")

private Set<B> b; 

And you can use the same approach to exclude the ones that are not within the time range

@OneToMany

@Where(clause = "active_from >= now() and (active_to is null or active_to < now())")

private Set<B> b;