Hibernate annotations. @Where vs @WhereJoinTable

Question

Following java doc

@Where

Where clause to add to the element Entity or target entity of a collection. The clause is written in SQL. A common use case here is for soft-deletes.

@WhereJoinTable

Where clause to add to the collection join table. The clause is written in SQL. Just as with {@link Where}, a common use case is for implementing soft-deletes.

It seems annotations can be used in same way in general:

|---------------------|-------------------|-------------------|
|                     |@Where             | @WhereTable       |
|---------------------|-------------------|-------------------|
|target elements      |TYPE, METHOD, FIELD|TYPE, METHOD, FIELD|
|---------------------|-------------------|-------------------|
|Retention            |RUNTIME            |RUNTIME            |
|---------------------|-------------------|-------------------|
|properties           |clause             |clause             |
|---------------------|-------------------|-------------------|

And as result I've been really confused how I should know which annotation I should use for Relation field. I can't find difference between using of @Where and @WhereJoinTable. Both of them can replace each other, am I right?

Sideburns

Answer 1

First annotation is applied on target entity. Here is very simplified example of this case in pseudo code:

@Entity
public class Role {
    private Long id;
    private boolean enabled;
}     

@Entity
public class User {
    @OneToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "USER_ROLE", joinColumns = @JoinColumn(name = "USER_ID"), inverseJoinColumns = @JoinColumn(name = "ROLE_ID"))
    @Where(clause = "enabled = true")
    private Set<Role> roles = new LinkedHashSet<>(0);
}

As result only enabled roles will be populated from the database into User.roles collections.

Second annotation is applied on the association table. Below is another example in pseudo-code, but now we suppose that association table is not that trivial as in first case:

@Entity
public class Role {
    private Long id;
    private boolean enabled;
}   

@Entity
public class User {
    @OneToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "USER_ROLE", joinColumns = @JoinColumn(name = "USER_ID"), inverseJoinColumns = @JoinColumn(name = "ROLE_ID"))
    @Where(clause = "enabled = true")
    @WhereJoinTable(clause = "now() between valid_from and valid_until")
    private Set<Role> roles = new LinkedHashSet<>(0);
}

and association table has validity attributes, something like 

CREATE TABLE USER_ROLE {
    ID NUMBER NOT NULL,
    USER_ID NUMBER NOT NULL,
    ROLE_ID NUMBER NOT NULL,
    VALID_FROM DATETIME,
    VALID_UNTIL DATETIME
} 

As result only enabled and valid roles will be populated from the database into User.roles collections.

Espy

Nice example, I would prefer to check result (it is possible if example shared via git) but any way thanks for your time and help. – Sideburns

Could you explain why the @WhereJoinTable annotation is necessary here, and why not just use @Where(clause = "enabled = true and now() between valid_from and valid_until")? – Gassaway

No, I can't, sorry. I do not use Hibernate anymore and do not remember details. – Espy

@IgorBljahhin it is because the "WhereJoinTable" is applied to the associating table (in the above example, it applies toward "USER_ROLE"). The "Where" is applied to the target entity table (in the above example, it applies toward "ROLE"). If you write it as you have suggested, the resulting SQL will result in a where clause referencing "ROLE.VALID_FROM" and "ROLE.VALID_UNTIL" instead of "USER_ROLE.VALID_FROM" and "USER_ROLE.VALID_UNTIL". And you will get a SQL exception, because USER does not have a column named "VALID_FROM", nor "VALID_UNTIL". USER_ROLE has those columns. – Orvas

Answer 2

For future reference, @Where and @WhereJoinTable were replaced by @SqlJoinTableRestriction (ref).

Brelje

The capitalization differs, correct one is SQLJoinTableRestriction. – Porphyry