CODEWITHSUNDEEP
javahibernatehqlinner-join
NimChimpsky
NimChimpsky

Reputation: 47290

Simple hql named query that uses a inner join

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... ?

Upvotes: 2

Views: 16897

Answers (2)

col
col

Reputation: 397

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

Upvotes: 4

coding_idiot
coding_idiot

Reputation: 13734

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.

Upvotes: 1

Related Questions