HQL WITH analog using Criteria API(extra JOIN ON parameters)

Gorbush · **Joined:** Mon Jul 21, 2008 5:12 am **Posts:** 13

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.2.6 GA

Mapping documents: Annotations

Name and version of the database you are using: MS SQL 2005

I have two tables - Enitites and Profiles

Entities:
bigint id
varchar name

Profiles:
bigint entity_type
bigint entity_id
bigint role_id
bit allowed

Situation:
I use code like

Code:

@Entity
@Table(name = "entities")
public class Entity {
    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "name")
    private String name;
   
    @OneToMany
    @Where(clause = "entity_type=3")
    @JoinColumn(name = "entity_id")
    private List<Profile> profile;
}

@Entity
@Table(name = "profiles")
public class Profile {
    @Column(name = "entity_type")
    private Long entityType;

    @Column(name = "entity_id")
    private Long entityId;

    @Column(name = "allowed")
    private Boolean allowed;

    @Column(name = "role_id")
    private Long roleId;

    @Column(name = "name")
    private String name;
}

When i try to get entities which are accessible by profile i use such code:

Code:

            Criteria criteria2 = getSession().createCriteria(Entity.class);
            Criteria prf = criteria2.createCriteria("profile", "prf");
            prf.add(Restrictions.eq("prf.allowed", true));
            prf.add(Restrictions.in("prf.roleId", listOfRoleIds));
            List list3 = criteria2.list();

This generates SQL like

Code:

   select 
      this_.id as id_01,
      this_.name as name_01
    from
        entities this_ 
    inner join
        profile prf1_ 
            on this_.id=prf1_.entity_id 
            and (
                prf1_.entity_type=3
            )  
   where prf1_.allowed = 1   and prf1_.role_id in (1,2,3,4)

This sql runs on my database near the 40 seconds.
But if i move conditions to on cause it will run in 110 milliseconds

Code:

     select 
      this_.id as id_01,
      this_.name as name_01
    from
        entities this_ 
    inner join
        profile prf1_ 
            on this_.id=prf1_.entity_id 
            and (
                prf1_.entity_type=3
            )  and (prf1_.allowed = 1   and prf1_.role_id in (1,2,3,4))

I need to specify such roles while creating the criteria - so i cannot use nither filters (as i understand) nor @Where cause.
Again - i cannot use HQL - all aplication use Criteria API.

Is it possible to implement functionality like HQL With using the Criteria API?

amersohail794 · **Joined:** Wed Sep 21, 2005 8:18 am **Posts:** 31

Quote:

Is it possible to implement functionality like HQL With using the Criteria API?

Yes, It is possible. Any thing which you can do with HQL, you can also do with Criteria API.

Quote:

Criteria criteria2 = getSession().createCriteria(Entity.class);
Criteria prf = criteria2.createCriteria("profile", "prf");
prf.add(Restrictions.eq("prf.allowed", true));
prf.add(Restrictions.in("prf.roleId", listOfRoleIds));
List list3 = criteria2.list();

Replace it with following:
Criteria criteria2 = getSession().createCriteria(Entity.class);
Criteria prf = criteria2.createAlias("profile", "prf");
prf.add(Restrictions.eq("prf.allowed", true));
prf.add(Restrictions.in("prf.roleId", listOfRoleIds));
List list3 = criteria2.list();

or Try this one:
Criteria criteria2 = getSession().createCriteria(Entity.class);
prf.add(Restrictions.eq("criteria2.profile.allowed", true));
prf.add(Restrictions.in("criteria2.profile.roleId", listOfRoleIds));
List list3 = criteria2.list();

Gorbush · **Joined:** Mon Jul 21, 2008 5:12 am **Posts:** 13

The first query renders the same SQL as i posted by number 1.

The second query renders exception
org.hibernate.QueryException: could not resolve property: profile.allowed of: Profile
- it required to create alias explicitly using createAlias or createCriteria scince Hibernate 3.2.2 as i know.

(I removed "criteria2." from th line 2 and 3 - it's unknown to this context.

Gorbush · **Joined:** Mon Jul 21, 2008 5:12 am **Posts:** 13

I do not beleive that no one get in the same situation...

Gorbush · **Joined:** Mon Jul 21, 2008 5:12 am **Posts:** 13

Any comments??

joolshomer · **Joined:** Mon Aug 18, 2008 10:19 am **Posts:** 5

Gorbush,

Like you I am having a similar problem as I need criteria to build a dynamic list of where conditions. However, looking at the example above could you not solve this with HQL? Something like ....

FROM entities inner join profile
WITH entityType = :entityType
AND ( allowed = :allowed
AND roleId in : roleId)

Apologies if this is a stupid response

Gorbush · **Joined:** Mon Jul 21, 2008 5:12 am **Posts:** 13

I developed a big system with many classes with subclasses which add some restrictions to the query..
So - i cannot create plain HQL query...

the big problem that there is an issue that not solved scince year of 2004...
http://opensource.atlassian.com/projects/hibernate/browse/HHH-16

Gorbush · **Joined:** Mon Jul 21, 2008 5:12 am **Posts:** 13

Ok,
I made some fixes in hibernate-core and there is a patch to support join ON conditions.

Look in JIRA ticket for the archive:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2308[/url]