How to use @Where in Hibernate

Question

Searched for a few hours, but I'm stuck in a my learning curve for PlayFramework with JPA. I'm building a sample website where posts can be made. But these posts can have the states:

• PostDraft (post is a draft, do not publish)
• PostPublished (post can be published)

These states are stored in a seperate table. Obviously, the draft state posts should not be visible yet.

So I have these classes:

• Page class (getting the page information from table, 1 page can have multiple posts)
• Posts class (posts can be in draft and published)

In my page class I have:

@Column(name="POSTS_REF")
@Where(clause="PostPublished")
private List<Posts> userPosts;

But this is not working! So, how can I specifify a where clause, to load only the posts that are in published state without using JPQL??

Thanks!

UPDATE: 2011-10-11

Table: Posts with columns: - id - title - state_ref (reference to the ID of States table) - content

Table: States with columns: - id - statename

So I want to say something like:

select * 
from posts inner join states on posts.state_ref = states.id
where states.statename = 'PostPublished'

UPDATE 2011-10-13

This is my current modification, in my page class: but it does not work either.

/** link to the states */
@JoinColumn(name = "STATES_REF")
@OneToOne
@Where(clause = "states.statename = 'PostPublished'")
public MyState state;

UPDATE 2012-02-13 Emt's answer worked for me after all.

Answer 1

Try something like:

@Column(name="POSTS_REF")
@Where(clause="state='PostPublished'")
private List<Posts> userPosts;

or

@Column(name="POSTS_REF")
@Where(clause="PostPublished=true")
private List<Posts> userPosts;

depending on the status field type on your Post entity.

Answer 2

The where clause must be a complete condition - something like this. Assuming that state is a property on the post.

@Column(name="POSTS_REF")
@Where(clause="state = 'PostPublished'")
private List<Posts> userPosts;

EDIT

Based on the data model - the following should work. I wouldn't recommend using it. Don't map the posts collection - just have a reference to Page from the POsts class, add a method to your DAO to retrieve the published posts for a page by using HQL or criteria query.

@Column(name="POSTS_REF")
@Where(clause="exists (select id from states where state_ref = states.id and states.statename = 'PostPublished')")
private List<Posts> userPosts;

Answer 3

The where condition must contain a database column name:

@Where(clause="state = 'PostPublished'")

Here the state column name is in DB and not in the Hibernate mapping.