Simple hql named query that uses a inner join

Question

I want to do something like this in my domain/entity object :

@Entity
@NamedQueries({
@NamedQuery(name="favouriteCats", query="from Cat c inner join on UserCat uc where uc.isFavourtie = true and uc.user = :user")
})
public final class Cat extends BaseTable

So that in my service layer I can do this :

Query query = session.getNamedQuery("favouriteCats")
query.setParameter(0, MyUser);
return query.list();

However, my syntax in HQL is incorrect - and aftern ten minutes looking at official docs I have decided to give up and ask here ... ? My usercat table is joined like so :

@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name="cat_fk", insertable=false, updatable=false)
private cat

The sql is this, it works fine at my db command prompt:

select c.* 
from cat as c inner join usercat as uc on c.id = uc.cat_fk 
and uc.isFavourite = 1 //bit field
and uc.user_fk = 74 //just user id

Is it just me or is the hibernate documentation rather painful, and do you find yourself often wondering whether it would be quicker just to write normal jdbc prepared statements to populate your pojos/domain objects/dto's... ?

Solution 1

I think this might work for you, but I am guessing your Usercat class here:

select c from Usercat as uc inner join uc.cat as c where uc.isFavourtie = true and uc.user = :user

Solution 2

Case Issue, Right query would be:

from Cat c inner join on Usercat uc where uc.isfavourtie = true and uc.user = :user

Note : C in Cat is capital, U in Usercat is capital where as c in Usercat is small and f in isfavourite is small.

Comments

[axtavt]

What are you trying to achieve by this query?

[NimChimpsky]

@axtavt Pass in user id, look up all favourites cats on usercat table, which is joined to cat table

[coding_idiot]

Why don't directly use SQL instead of HQL, the method is session.createSQLQuery(query);

[NimChimpsky]

@XCoder Well yeah, I guess I could, but there are benefits to having a named query, or a criteria query. And this shouldn't be that difficult using hibernate.

[NimChimpsky]

thnx, what would the criteria equivalent look like ?

[col]

I am not aware if you can do that with Criteria without a reference from Cat to Usercat.

[NimChimpsky]

it would seem not, no answers here stackoverflow.com/q/7914607/106261 I sometimes wonder whther hibernate is worth it (it being learning new syntax, installing a load of jars, etc etc)