Hibernate Community • View topic - "Set contains" using Criteria

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

"Set contains" using Criteria

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

swillfah

Post subject: "Set contains" using Criteria

Posted: Sun Oct 09, 2005 3:32 am

Newbie

Joined: Wed May 18, 2005 4:50 am
Posts: 18

I have two classes TEAM and PERSON. Each team has a "leader" (association to PERSON) and may have various "members" (set with many-to-many relation to PERSON).

I am trying to find the "correct" way (using Criteria) to formulate a finder like "all the teams where a certain person is either the leader or a member", which translates to "all the teams where ((leader EQ person) OR (set members CONTAINS person))".

Now I am having problems choosing the "best" method for this "set contains"-Restriction.

Below I have used "createAlias" and a restriction of "ms.id eq person.getId()". This results in an INNER JOIN from TEAM to TEAM_PERSON and an INNER JOIN from TEAM_PERSON to PERSON.

There are two "problems" with this.

1. It's using an INNER JOIN instead of a LEFT OUTER JOIN. Which means teams without any member will never be selected, even when the leader matches.

2. The second INNER JOIN from TEAM_PERSON to PERSON is unnecessary. Since I am only interessed in the existence of a matching person in the members set, only the ID of the PERSON is relevant. And this ID is already in the TEAM_PERSON table. The second join is only adding a performance hit (albeit a small one) and should be avoided.

I have read in various postings here, that the first problem can't be avoided with the Criteria API and it seems like I have to live with that.

But I wonder wether there is a solution to the second problem?

Is there an "easier" way to formulate the "set contains" restriction?

Any help would be appreciated.

Stefan

Hibernate version:

3.0.5

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="hibertest.bo">

   <class name="Person" table="PERSON">
      <id name="id">
         <generator class="native"/>
      </id>
      <property name="name" not-null="true"/>
   </class>
   
   <class name="Team" table="TEAM">
        
      <id name="id">
         <generator class="native"/>
      </id>
        
        <property name="name" not-null="true"/>
        
        <many-to-one
            name="leader"
            class="hibertest.bo.Person"
            cascade="none"
            outer-join="auto"
            update="true"
            insert="true"
            column="LEADER"
            not-null="false"
        />

        <set
            name="members"
            table="TEAM_PERSON"
            lazy="true"
            cascade="none"
            sort="unsorted">
            <key column="TEAM_ID"/>
            <many-to-many
                class="hibertest.bo.Person"
                column="PERSON_ID"
                outer-join="auto"/>
        </set>

   </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

          Criteria crit = session.createCriteria(Team.class);
          
          SimpleExpression exprLeader = Restrictions.eq("leader", person);
          
          crit.createAlias("members", "ms");
          SimpleExpression exprMembers = Restrictions.eq("ms.id", person.getId());
          
          crit.add(Restrictions.disjunction().add(exprLeader).add(exprMembers));
          
          crit.list();

Name and version of the database you are using:

Oracle 10.1.0.4

The generated SQL (show_sql=true):

Code:

select this_.id as id1_, this_.name as name1_1_, this_.LEADER as LEADER1_1_, members3_.TEAM_ID as TEAM1_, ms1_.id as PERSON2_, ms1_.id as id0_, ms1_.name as name0_0_ from TEAM this_ inner join TEAM_PERSON members3_ on this_.id=members3_.TEAM_ID inner join PERSON ms1_ on members3_.PERSON_ID=ms1_.id where (this_.LEADER=? or ms1_.id=?)

Top

mnrz

Post subject:

Posted: Sun Oct 09, 2005 4:44 am

Beginner

Joined: Sat Oct 08, 2005 2:13 am
Posts: 47

Hi
I couldn`t understand excactly what u mean but about first problem i think u better use the find() method as follow:

String hqlStr = "from classname as alias where ....."

and build ur left outer join according to HQL syntax (see Hibernate Reference for more details) and when u build the HQL string then call find() method:

List list = session.find(hqlStr);

plz say more details about ur second problem

Hope this help

Top

swillfah

Post subject:

Posted: Mon Oct 10, 2005 4:47 am

Newbie

Joined: Wed May 18, 2005 4:50 am
Posts: 18

I would like to continue using the Criteria API and avoid HQL as long as possible. I think that building queries using the Criteria API is one of the strong points of Hibernate.

The second problem lies with formulating the "members set CONTAINS person" as "join PERSON as ms and ms.id = person.getId()". This results in the following (shortened and rewritten) SQL

Code:

from TEAM inner join TEAM_PERSON  on TEAM.id=TEAM_PERSON.TEAM_ID
          inner join PERSON       on TEAM_PERSON.PERSON_ID=PERSON.id
where (PERSON.id=?) 

The second join above is unnecessary, this would be equivalent

Code:

from TEAM inner join TEAM_PERSON  on TEAM.id=TEAM_PERSON.TEAM_ID
where (TEAM_PERSON.PERSON_ID=?) 

If the condition were on any other field of person than "person.id" the second join would be necessary. But not in this case.

I am wondering if there is a better way to formulate the "CONTAINS" logic than to use the restriction on the "person.id"-Field. I would think, that this is a very common problem for anybody using Hibernate ...

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum