Hibernate Community • View topic - Problem doing a simple query

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Problem doing a simple query

Page 1 of 1

[ 8 posts ]

Previous topic | Next topic

Author

Message

everbright

Post subject: Problem doing a simple query

Posted: Sat Jun 18, 2005 9:09 am

Beginner

Joined: Wed Jun 15, 2005 2:00 pm
Posts: 38

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

Hibernate version: 3.0.5

Mapping documents:

Code:

<hibernate-mapping package="project.vo">
   <class name="Group" table="MM_GROUPS">
      <id name="id" column="id" type="int" unsaved-value="null" length="5">
         <generator class="native" />
      </id>
      <property name="name" column="name" type="string" length="50" not-null="true" unique="false" />
      <set name="members" table="MM_USERGROUPLINKS" cascade="none" order-by="name asc" lazy="false" inverse="false">
         <key column="groupID" />
         <many-to-many class="project.vo.User" column="userID" />
      </set>
   </class>
   
   <class name="User" table="MM_USERS">
      <id name="id" column="id" type="int" unsaved-value="null"
         length="5">
         <generator class="native" />
      </id>
      <property name="name" column="name" type="string" length="50" not-null="true" unique="false" />
      <set name="memberships" table="MM_USERGROUPLINKS" cascade="none" order-by="name asc" lazy="false" inverse="false">
         <key column="userID" />
         <many-to-many class="project.vo.Group" column="groupID" />
      </set>
   </class>
</hibernate-mapping>

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

Code:

Transaction tx = session.beginTransaction();
            
            Query query = session.createQuery("select u from User u order by u.name asc");
            Iterator results = query.iterate();
            while (results.hasNext()) {
                User u = (User) results.next();
                System.out.println("User: " + u.getId() + " " + u.getName());
            }
            
            tx.commit();

Full stack trace of any exception that occurs:

Code:

Jun 18, 2005 8:59:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 207, SQLState: 42S22
Jun 18, 2005 8:59:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Invalid column name 'name'.
Jun 18, 2005 8:59:55 PM org.hibernate.event.def.DefaultLoadEventListener onLoad
INFO: Error performing load command
org.hibernate.exception.SQLGrammarException: could not initialize a collection: 
[project.vo.User.memberships#2]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java
:43)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1441)
   at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader
.java:99)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(Ab
stractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializ
eCollection(DefaultInitializeCollectionEventListener.java:60)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(Ab
stractPersistentCollection.java:280)
   at org.hibernate.engine.PersistenceContext.initializeNonLazyCollections(Persist
enceContext.java:796)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:223)
   at org.hibernate.loader.Loader.loadEntity(Loader.java:1345)
   at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116)
   at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:101)
   at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersiste
r.java:2471)
   at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultL
oadEventListener.java:351)
   at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventList
ener.java:332)
   at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListen
er.java:113)
   at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEven
tListener.java:167)
   at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventList
ener.java:79)
   at org.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:655)
   at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:261)
   at org.hibernate.type.EntityType.resolve(EntityType.java:286)
   at org.hibernate.type.EntityType.nullSafeGet(EntityType.java:207)
   at org.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:92)
   at org.hibernate.impl.IteratorImpl.<init>(IteratorImpl.java:57)
   at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:412)
   at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTranslatorImpl.java:2
81)
   at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:935)
   at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
   at HibernateTest.main(HibernateTest.java:32)
Caused by: java.sql.SQLException: Invalid column name 'name'.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367
)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2606)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2048)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:574)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:3
21)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedSta
tement.java:667)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
   at org.hibernate.loader.Loader.doQuery(Loader.java:391)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:218)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
   ... 27 more
org.hibernate.exception.SQLGrammarException: could not initialize a collection: 
[project.vo.User.memberships#2]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java
:43)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1441)
   at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader
.java:99)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(Ab
stractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializ
eCollection(DefaultInitializeCollectionEventListener.java:60)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(Ab
stractPersistentCollection.java:280)
   at org.hibernate.engine.PersistenceContext.initializeNonLazyCollections(Persist
enceContext.java:796)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:223)
   at org.hibernate.loader.Loader.loadEntity(Loader.java:1345)
   at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116)
   at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:101)
   at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersiste
r.java:2471)
   at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultL
oadEventListener.java:351)
   at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventList
ener.java:332)
   at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListen
er.java:113)
   at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEven
tListener.java:167)
   at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventList
ener.java:79)
   at org.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:655)
   at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:261)
   at org.hibernate.type.EntityType.resolve(EntityType.java:286)
   at org.hibernate.type.EntityType.nullSafeGet(EntityType.java:207)
   at org.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:92)
   at org.hibernate.impl.IteratorImpl.<init>(IteratorImpl.java:57)
   at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:412)
   at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTranslatorImpl.java:2
81)
   at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:935)
   at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
   at HibernateTest.main(HibernateTest.java:32)
Caused by: java.sql.SQLException: Invalid column name 'name'.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367
)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2606)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2048)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:574)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:3
21)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedSta
tement.java:667)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
   at org.hibernate.loader.Loader.doQuery(Loader.java:391)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:218)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
   ... 27 more

Name and version of the database you are using: SQL Server 2000

The generated SQL (show_sql=true):

Code:

Hibernate: select user0_.id as col_0_0_ from MM_USERS user0_ order by user0_.nam
e asc
Hibernate: select user0_.id as id0_, user0_.name as name2_0_ from MM_USERS user0
_ where user0_.id=?
Hibernate: select membership0_.userID as userID1_, membership0_.groupID as group
ID1_, group1_.id as id0_, group1_.name as name0_0_ from MM_USERGROUPLINKS member
ship0_ inner join MM_GROUPS group1_ on membership0_.groupID=group1_.id where mem
bership0_.userID=? order by membership0_.name asc

As per the above, I'm just creating 2 classes, User and Group with many-to-many association between them. The backend database tables are MM_USERS, MM_GROUPS, and MM_USERGROUPLINKS.

I'm basically just trying to list all the users when I got the above errors.

Questions:
1) Why does Hibernate use 3 queries for a simple query like this?
2) Why does Hibernate apply the order by clause to the MM_USERGROUPLINKS table instead?
3) How should I correct my mappings so that this will work out?

Thanks in advance for any suggestions!

Top

mahikty

Post subject:

Posted: Sat Jun 18, 2005 9:27 am

Regular

Joined: Thu Apr 21, 2005 9:05 am
Posts: 50
Location: Boston, U.S

EverBright,

Questions:
1) Why does Hibernate use 3 queries for a simple query like this?

Set the lazy attribute to "true".

The Queries are fired due to the defined relationships.

Hibernate tries to load the entire object graph when you
access the driving table.

2) Why does Hibernate apply the order by clause to the MM_USERGROUPLINKS table instead?

And

3) How should I correct my mappings so that this will work out?

Take one step at a time, Just try out the simple mapping first
and then go ahead.

Top

everbright

Post subject:

Posted: Sat Jun 18, 2005 9:37 am

Beginner

Joined: Wed Jun 15, 2005 2:00 pm
Posts: 38

Hi mahikty,

Thanks for your suggestions. I've set lazy=true and now it works. However it still uses 5 queries to do the ordering, even when I removed the many-to-many relationship, so I think this is an inherent problem with using "order by" in HQL, rather than because of the many-to-many relationship.

mahikty wrote:

Top

mahikty

Post subject:

Posted: Sat Jun 18, 2005 9:46 am

Regular

Joined: Thu Apr 21, 2005 9:05 am
Posts: 50
Location: Boston, U.S

As i mentioned earlier, Just try with "MM_USERS" first.
Try to make it simple and narrow down the area of problem.

Just take one step at a time.

Top

everbright

Post subject:

Posted: Sat Jun 18, 2005 10:36 am

Beginner

Joined: Wed Jun 15, 2005 2:00 pm
Posts: 38

That's what I did. I reduced everything to just one class, and still Hibernate uses 5 queries to get the result :-(

mahikty wrote:

As i mentioned earlier, Just try with "MM_USERS" first.
Try to make it simple and narrow down the area of problem.

Just take one step at a time.

Top

christian

Post subject:

Posted: Sat Jun 18, 2005 10:39 am

Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland

The next step is to read the fetching documentation.

Top

everbright

Post subject:

Posted: Sat Jun 18, 2005 10:54 am

Beginner

Joined: Wed Jun 15, 2005 2:00 pm
Posts: 38

Hi max,

I've set the batch-size for my User class to "3" which should be ok, but Hibernate still uses 5 queries.

Is there some setting somewhere that I may need to set as well, e.g. in hibernate.cfg.xml, to turn on this batch select feature?

Top

everbright

Post subject:

Posted: Sat Jun 18, 2005 11:36 am

Beginner

Joined: Wed Jun 15, 2005 2:00 pm
Posts: 38

Hi,

Sorry, I got mixed up between this post and another one (titled "Hibernate uses 5 queries to retrieve ordered results"), so my previous replies were for the other post.

On this one, I have some clarifications and updates:

The situation is this:

User and Group share a many-to-many relationship, bi-directional.

What I want is that when I load a group, and execute group.getMembers() to initialize the collection of users , it should be sorted according to user name.

I've tried changing the association between User and Group to many-to-one instead, I have no problems at all. With the following setting for the Group class, when I initialize the group's members, they get sorted via the order-by attribute.

Code:

<set name="members" cascade="none" order-by="name asc" lazy="false">
         <key column="groupID" />
         <one-to-many class="project.vo.User"/>
      </set>

The problem with the many-to-many association is that the association goes through a bridging table (MM_USERGROUPLINKS), which will not have the "name" attribute from the user table. That's why the query goes wonky.

Any ideas how to get around this?

Top

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