-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
mchyzer
 Post subject: simple many to many query
PostPosted: Wed Dec 08, 2004 3:35 am 
Senior
Senior

Joined: Sat Jul 17, 2004 5:16 pm
Posts: 143
I have a many to many DB relationship (user, group, and user_group). I want to query for all users in a specific group. Any ideas?

Thanks,
Chris

ps. Yes, I have read all the docs, yes, I am confused. :)


Hibernate version:
2
Mapping documents:

Code:
<?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">    <hibernate-mapping
      package="edu.upenn.isc.fast.beans.plsql">

    <class name="FastGroup" table="FAST_GROUP">
      <id name="groupId" column="GROUP_ID">
        <generator class="native"/>
      </id>

    </class>

  </hibernate-mapping>


Code:
<?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">    <hibernate-mapping
      package="edu.upenn.isc.fast.beans.plsql"> <!-- schema="SCHEMA_IF_NOT_DEFAULT" -->

    <class name="FastUser" table="FAST_USER">
      <id name="userId" column="USER_ID">
        <generator class="native"/>
      </id>
      <set name="groupsFastSet" cascade="all-delete-orphan" table="fast_user_group">
        <key column="user_id"/>
        <many-to-many class="FastGroup" column="group_id"/>
      </set>

    </class>

  </hibernate-mapping>


Code:
<?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">    <hibernate-mapping
      package="edu.upenn.isc.fast.beans.plsql"> <!-- schema="SCHEMA_IF_NOT_DEFAULT" -->

    <class name="FastUserGroup" table="FAST_USER_GROUP">
      <many-to-one name="user" column="USER_ID" />
      <many-to-one name="group" column="GROUP_ID" />
    </class>

  </hibernate-mapping>



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

session.find(
"select theUser from FastGroup theGroup "
+ "join FastUserGroup userGroup "
+ "join FastUser theUser where theGroup.groupId = ?", fastGroup.getGroupId(),Hibernate.INTEGER);

Full stack trace of any exception that occurs:

Code:
Caused by: net.sf.hibernate.QueryException: [b]in expected[/b]: userGroup [select theUser from edu.upenn.isc.fast.beans.plsql.FastGroup theGroup join FastUserGroup userGroup join FastUser theUser where theGroup.groupId = ?]
   at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1520)
   at edu.upenn.isc.fast.hibernate.HibernateSession.retrieveObjects(HibernateSession.java:1021)
   ... 17 more


Name and version of the database you are using:
Oracle
Top
 Profile  
 
mchyzer
 Post subject:
PostPosted: Wed Dec 08, 2004 4:59 am 
Senior
Senior

Joined: Sat Jul 17, 2004 5:16 pm
Posts: 143
I got it working with a native SQL query, which is very cool. I would be curious to know how to do this in HQL, but I can work with this... :) Chris

Code:
List list = session.createSQLQuery(
"select {theUser.*} from Fast_user {theUser}, Fast_Group theGroup, Fast_User_Group userGroup"
+ " where theUser.user_id = userGroup.user_id AND theGroup.group_id = userGroup.group_id"
+ " AND theGroup.group_name_system = 'groupTest1'", "theUser", FastUser.class).list();
Top
 Profile  
 
agori
 Post subject:
PostPosted: Wed Dec 08, 2004 6:06 am 
Regular
Regular

Joined: Tue Oct 12, 2004 9:02 am
Posts: 66
Location: Italy
Why do you use many-to-many in FastUser? and not one-to-many?
And why not a one-to-many in FastGroup?

So you can do:

Set users = group.getUsers();
where here you have a set of users of type UserGroup.

for(Iterator it = usrs.iterator(); it.hasNext(); ) {
UserGroup ug = (UserGroup) it.next();
User user = ug.getUser();
}


what about this HQL?

"select u from UserGroup ug, FastUser u where ug.user.id = u.id and ug.group.id=?"
Top
 Profile  
 
mchyzer
 Post subject:
PostPosted: Wed Dec 08, 2004 5:51 pm 
Senior
Senior

Joined: Sat Jul 17, 2004 5:16 pm
Posts: 143
agori wrote:
Why do you use many-to-many in FastUser?


OK, I assumed that since it is a many-to-many relationship in the DB (with 3 tables, 1 for user, 1 for group, 1 for user_group) that I need to do that with Hibernate

Quote:
"select u from UserGroup ug, FastUser u where ug.user.id = u.id and ug.group.id=?"


Hmm, there is a third table involved... I assume you are not considering it. e.g. there is no user_id field in group, only group_id. and the user_group table has two columns user_id and group_id (the many-to-many part).

Thanks,
Chris
Top
 Profile  
 
agori
 Post subject:
PostPosted: Wed Dec 08, 2004 8:05 pm 
Regular
Regular

Joined: Tue Oct 12, 2004 9:02 am
Posts: 66
Location: Italy
mchyzer wrote:
OK, I assumed that since it is a many-to-many relationship in the DB (with 3 tables, 1 for user, 1 for group, 1 for user_group) that I need to do that with Hibernate


you should use 2 one-to-many otherwise there is not reason for map the usergroup table with hibernate.

Quote:
Hmm, there is a third table involved... I assume you are not considering it. e.g. there is no user_id field in group, only group_id. and the user_group table has two columns user_id and group_id (the many-to-many part).

i considered all 3 tables!user table, user group table and group table.
try it and give a look to the sql generated
Top
 Profile  
 
mchyzer
 Post subject:
PostPosted: Thu Dec 09, 2004 12:35 am 
Senior
Senior

Joined: Sat Jul 17, 2004 5:16 pm
Posts: 143
agori wrote:
you should use 2 one-to-many otherwise there is not reason for map the usergroup table with hibernate.

i considered all 3 tables!user table, user group table and group table.
try it and give a look to the sql generated


OK, I see now. Alright, if I have two mappings, is there a way to do the query? I dont want the mapping object (user_group) to be dealt with in Java... Thanks! Chris
Top
 Profile  
 
agori
 Post subject:
PostPosted: Thu Dec 09, 2004 5:51 am 
Regular
Regular

Joined: Tue Oct 12, 2004 9:02 am
Posts: 66
Location: Italy
From you xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd"> <hibernate-mapping
package="edu.upenn.isc.fast.beans.plsql"> <!-- schema="SCHEMA_IF_NOT_DEFAULT" -->

<class name="FastUserGroup" table="FAST_USER_GROUP">
<many-to-one name="user" column="USER_ID" />
<many-to-one name="group" column="GROUP_ID" />
</class>

</hibernate-mapping>

you have already done it! (maybe you didn't write the java class but you should do it if you have this mapping).
I used this in my query!
hovewer there is not need for a query.
You can do
Set users = group.getUsers();

(but you should add a many-to-many in group if i remember well).
Top
 Profile  
 
mchyzer
 Post subject:
PostPosted: Thu Dec 09, 2004 11:55 am 
Senior
Senior

Joined: Sat Jul 17, 2004 5:16 pm
Posts: 143
agori wrote:
you have already done it!


I know, thanks for your patience. I did create it. Now that I know I dont need it, I will get rid of it.

Quote:
You can do
Set users = group.getUsers();

(but you should add a many-to-many in group if i remember well).


Yes, I could do that if I had this field. But I do not want to since there could be 10's of thousands users in a group, so I want to query and set the max size. So assuming I do not have the intermediate mapping, and want to do the query, is there a way?

Something like:

Code:
select theUser from FastUser theUser, FastGroup theGroup where theGroup in theUser.groupsFastSet and theGroup.groupNameSystem = 'groupTest1'


btw this gives exception: net.sf.hibernate.QueryException: unindexed collection before []: fastuser0_.groupsFastSet

Actually, I have this working with createSQLQuery, so I think I will go that route. Thanks anyway! Chris
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.