i have a problem about hql

i have a user object ,
i use hibernate hql, getHibernateTemplate.find(from user where username = 'name', "jack"); to get the object.
The user entity has a lot of @OneToMany or @ManyToMany relationships to other objects.
It takes about 6 seconds to load the user object using hql.

if i set "hibernate.show_sql=true", then my console looks something like this:

Hibernate: select marketitem0_.id as id6_0_, marketitem0_.acceptAGB as acceptAGB6_0_, marketitem0_.acceptDataPrivacy as acceptDa3_6_0_, marketitem0_.city as city6_0_, marketitem0_.country_id as country16_6_0_, marketitem0_.createDate as createDate6_0_, marketitem0_.description as descript6_6_0_, marketitem0_.email as email6_0_, marketitem0_.geoData_id as geoData17_6_0_, marketitem0_.marketCategory_id as marketC18_6_0_, marketitem0_.moneyId as moneyId6_0_, marketitem0_.postCode as postCode6_0_, marketitem0_.price as price6_0_, marketitem0_.seller_id as seller19_6_0_, marketitem0_.selling as selling6_0_, marketitem0_.sendEmail as sendEmail6_0_, marketitem0_.telephone as telephone6_0_, marketitem0_.title as title6_0_, marketitem0_.version as version6_0_ from market_item marketitem0_ where marketitem0_.geoData_id=?
Hibernate: select snuser0_.id as id1_0_, snuser0_.activated as activated1_0_, snuser0_.birthday as birthday1_0_, snuser0_.blog_id as blog30_1_0_, snuser0_.city as city1_0_, snuser0_.contactUpdate as contactU6_1_0_, snuser0_.country_id as country31_1_0_, snuser0_.email as email1_0_, snuser0_.fax as fax1_0_, snuser0_.forename as forename1_0_, snuser0_.geoData_id as geoData32_1_0_, snuser0_.interest as interest1_0_, snuser0_.job as job1_0_, snuser0_.language as language1_0_, snuser0_.memberSince as memberS13_1_0_, snuser0_.mobile as mobile1_0_, snuser0_.myGeoMapPathCountryName as myGeoMa15_1_0_, snuser0_.myGeoMapPathEuropeName as myGeoMa16_1_0_, snuser0_.myOffer as myOffer1_0_, snuser0_.mySearch as mySearch1_0_, snuser0_.newsLetter as newsLetter1_0_, snuser0_.online as online1_0_, snuser0_.password as password1_0_, snuser0_.phone as phone1_0_, snuser0_.postCode as postCode1_0_, snuser0_.registerDate as registe24_1_0_, snuser0_.sex as sex1_0_, snuser0_.street as street1_0_, snuser0_.surname as surname1_0_, snuser0_.title as title1_0_, snuser0_.userName as userName1_0_, snuser0_.department_id as department33_1_0_, snuser0_.sNUsertype as sNUsertype1_0_ from sn_user snuser0_ where snuser0_.geoData_id=?
Hibernate: select picture0_.id as id2_0_, picture0_.article_id as article8_2_0_, picture0_.date as date2_0_, picture0_.flag as flag2_0_, picture0_.group_groupId as group9_2_0_, picture0_.marketItem_id as marketItem10_2_0_, picture0_.name as name2_0_, picture0_.photoAlbum_id as photoAlbum11_2_0_, picture0_.snUser_id as snUser12_2_0_, picture0_.title as title2_0_, picture0_.version as version2_0_ from picture picture0_ where picture0_.snUser_id=?
Hibernate: select payment0_.id as id30_0_, payment0_.accountHolder as accountH2_30_0_, payment0_.accountNr as accountNr30_0_, payment0_.bankCode as bankCode30_0_, payment0_.bankName as bankName30_0_, payment0_.cardHolder as cardHolder30_0_, payment0_.cardType as cardType30_0_, payment0_.ccNr as ccNr30_0_, payment0_.city as city30_0_, payment0_.country_id as country26_30_0_, payment0_.email as email30_0_, payment0_.fax as fax30_0_, payment0_.forename as forename30_0_, payment0_.mobile as mobile30_0_, payment0_.monthsPeriode as monthsP14_30_0_, payment0_.paymentFrom as payment15_30_0_, payment0_.paymentMethod as payment16_30_0_, payment0_.phone as phone30_0_, payment0_.postCode as postCode30_0_, payment0_.secret_code as secret19_30_0_, payment0_.seniorMemberBegin as seniorM20_30_0_, payment0_.seniorMemberEnd as seniorM21_30_0_, payment0_.snUser_id as snUser27_30_0_, payment0_.street as street30_0_, payment0_.surname as surname30_0_, payment0_.valid_date as valid24_30_0_, payment0_.version as version30_0_ from payment payment0_ where payment0_.snUser_id=?
Hibernate: select snusersett0_.id as id29_0_, snusersett0_.emailByNewContactRequest as emailByN3_29_0_, snusersett0_.emailByNewGroupFellowship as emailByN4_29_0_, snusersett0_.emailByNewMessage as emailByN5_29_0_, snusersett0_.emailasHTML as emailasH6_29_0_, snusersett0_.maskingForingLanPublicApp as maskingF7_29_0_, snusersett0_.myLanguageForMemberSearch as myLangua8_29_0_, snusersett0_.newsLetter as newsLetter29_0_, snusersett0_.preferMyLandsmanInSearch as preferM10_29_0_, snusersett0_.showMeForNonMember as showMeF11_29_0_, snusersett0_.showMeInSearch as showMeI12_29_0_, snusersett0_.showMyCommentsInSearch as showMyC13_29_0_, snusersett0_.showMyFriends as showMyF14_29_0_, snusersett0_.showMyGallery as showMyG15_29_0_, snusersett0_.showMyGroupFellowship as showMyG16_29_0_, snusersett0_.showMyInfoChange as showMyI17_29_0_, snusersett0_.showMyNewAnnouncement as showMyN18_29_0_, snusersett0_.showMyNewFriends as showMyN19_29_0_, snusersett0_.showMyNewPictures as showMyN20_29_0_, snusersett0_.showPrivateDataChange as showPri21_29_0_, snusersett0_.snUser_id as snUser22_29_0_, snusersett0_.usersettingtype as usersett1_29_0_ from user_setting snusersett0_ where snusersett0_.snUser_id=?
Hibernate: select geodata0_.id as id15_0_, geodata0_.coordinateLatitude as coordina3_15_0_, geodata0_.coordinateLongitude as coordina4_15_0_ from geoData geodata0_ where geodata0_.id=?
Hibernate: select marketitem0_.id as id6_0_, marketitem0_.acceptAGB as acceptAGB6_0_, marketitem0_.acceptDataPrivacy as acceptDa3_6_0_, marketitem0_.city as city6_0_, marketitem0_.country_id as country16_6_0_, marketitem0_.createDate as createDate6_0_, marketitem0_.description as descript6_6_0_, marketitem0_.email as email6_0_, marketitem0_.geoData_id as geoData17_6_0_, marketitem0_.marketCategory_id as marketC18_6_0_, marketitem0_.moneyId as moneyId6_0_, marketitem0_.postCode as postCode6_0_, marketitem0_.price as price6_0_, marketitem0_.seller_id as seller19_6_0_, marketitem0_.selling as selling6_0_, marketitem0_.sendEmail as sendEmail6_0_, marketitem0_.telephone as telephone6_0_, marketitem0_.title as title6_0_, marketitem0_.version as version6_0_ from market_item marketitem0_ where marketitem0_.geoData_id=?
Hibernate: select snuser0_.id as id1_0_, snuser0_.activated as activated1_0_, snuser0_.birthday as birthday1_0_, snuser0_.blog_id as blog30_1_0_, snuser0_.city as city1_0_, snuser0_.contactUpdate as contactU6_1_0_, snuser0_.country_id as country31_1_0_, snuser0_.email as email1_0_, snuser0_.fax as fax1_0_, snuser0_.forename as forename1_0_, snuser0_.geoData_id as geoData32_1_0_, snuser0_.interest as interest1_0_, snuser0_.job as job1_0_, snuser0_.language as language1_0_, snuser0_.memberSince as memberS13_1_0_, snuser0_.mobile as mobile1_0_, snuser0_.myGeoMapPathCountryName as myGeoMa15_1_0_, snuser0_.myGeoMapPathEuropeName as myGeoMa16_1_0_, snuser0_.myOffer as myOffer1_0_, snuser0_.mySearch as mySearch1_0_, snuser0_.newsLetter as newsLetter1_0_, snuser0_.online as online1_0_, snuser0_.password as password1_0_, snuser0_.phone as phone1_0_, snuser0_.postCode as postCode1_0_, snuser0_.registerDate as registe24_1_0_, snuser0_.sex as sex1_0_, snuser0_.street as street1_0_, snuser0_.surname as surname1_0_, snuser0_.title as title1_0_, snuser0_.userName as userName1_0_, snuser0_.department_id as department33_1_0_, snuser0_.sNUsertype as sNUsertype1_0_ from sn_user snuser0_ where snuser0_.geoData_id=?
Hibernate: select picture0_.id as id2_0_, picture0_.article_id as article8_2_0_, picture0_.date as date2_0_, picture0_.flag as flag2_0_, picture0_.group_groupId as group9_2_0_, picture0_.marketItem_id as marketItem10_2_0_, picture0_.name as name2_0_, picture0_.photoAlbum_id as photoAlbum11_2_0_, picture0_.snUser_id as snUser12_2_0_, picture0_.title as title2_0_, picture0_.version as version2_0_ from picture picture0_ where picture0_.snUser_id=?
Hibernate: select payment0_.id as id30_0_, payment0_.accountHolder as accountH2_30_0_, payment0_.accountNr as accountNr30_0_, payment0_.bankCode as bankCode30_0_, payment0_.bankName as bankName30_0_, payment0_.cardHolder as cardHolder30_0_, payment0_.cardType as cardType30_0_, payment0_.ccNr as ccNr30_0_, payment0_.city as city30_0_, payment0_.country_id as country26_30_0_, payment0_.email as email30_0_, payment0_.fax as fax30_0_, payment0_.forename as forename30_0_, payment0_.mobile as mobile30_0_, payment0_.monthsPeriode as monthsP14_30_0_, payment0_.paymentFrom as payment15_30_0_, payment0_.paymentMethod as payment16_30_0_, payment0_.phone as phone30_0_, payment0_.postCode as postCode30_0_, payment0_.secret_code as secret19_30_0_, payment0_.seniorMemberBegin as seniorM20_30_0_, payment0_.seniorMemberEnd as seniorM21_30_0_, payment0_.snUser_id as snUser27_30_0_, payment0_.street as street30_0_, payment0_.surname as surname30_0_, payment0_.valid_date as valid24_30_0_, payment0_.version as version30_0_ from payment payment0_ where payment0_.snUser_id=?
Hibernate: select snusersett0_.id as id29_0_, snusersett0_.emailByNewContactRequest as emailByN3_29_0_, snusersett0_.emailByNewGroupFellowship as emailByN4_29_0_, snusersett0_.emailByNewMessage as emailByN5_29_0_, snusersett0_.emailasHTML as emailasH6_29_0_, snusersett0_.maskingForingLanPublicApp as maskingF7_29_0_, snusersett0_.myLanguageForMemberSearch as myLangua8_29_0_, snusersett0_.newsLetter as newsLetter29_0_, snusersett0_.preferMyLandsmanInSearch as preferM10_29_0_, snusersett0_.showMeForNonMember as showMeF11_29_0_, snusersett0_.showMeInSearch as showMeI12_29_0_, snusersett0_.showMyCommentsInSearch as showMyC13_29_0_, snusersett0_.showMyFriends as showMyF14_29_0_, snusersett0_.showMyGallery as showMyG15_29_0_, snusersett0_.showMyGroupFellowship as showMyG16_29_0_, snusersett0_.showMyInfoChange as showMyI17_29_0_, snusersett0_.showMyNewAnnouncement as showMyN18_29_0_, snusersett0_.showMyNewFriends as showMyN19_29_0_, snusersett0_.showMyNewPictures as showMyN20_29_0_, snusersett0_.showPrivateDataChange as showPri21_29_0_, snusersett0_.snUser_id as snUser22_29_0_, snusersett0_.usersettingtype as usersett1_29_0_ from user_setting snusersett0_ where snusersett0_.snUser_id=?
Hibernate: select userrole0_.snUser_id as snUser1_1_, userrole0_.userRole_id as userRole2_1_, userrole1_.id as id3_0_, userrole1_.role_name as role2_3_0_, userrole1_.role_newmember as role3_3_0_, userrole1_.role_text as role4_3_0_, userrole1_.version as version3_0_ from sn_user_user_role userrole0_ left outer join user_role userrole1_ on userrole0_.userRole_id=userrole1_.id where userrole0_.snUser_id=?
Hibernate: select invoice0_.snUser_id as snUser7_1_, invoice0_.id as id1_, invoice0_.id as id19_0_, invoice0_.date as date19_0_, invoice0_.display as display19_0_, invoice0_.invoicePaidDate as invoiceP4_19_0_, invoice0_.send as send19_0_, invoice0_.snUser_id as snUser7_19_0_, invoice0_.version as version19_0_ from invoice invoice0_ where invoice0_.snUser_id=? order by invoice0_.date DESC
Hibernate: select userrole0_.snUser_id as snUser1_1_, userrole0_.userRole_id as userRole2_1_, userrole1_.id as id3_0_, userrole1_.role_name as role2_3_0_, userrole1_.role_newmember as role3_3_0_, userrole1_.role_text as role4_3_0_, userrole1_.version as version3_0_ from sn_user_user_role userrole0_ left outer join user_role userrole1_ on userrole0_.userRole_id=userrole1_.id where userrole0_.snUser_id=?
Hibernate: select invoice0_.snUser_id as snUser7_1_, invoice0_.id as id1_, invoice0_.id as id19_0_, invoice0_.date as date19_0_, invoice0_.display as display19_0_, invoice0_.invoicePaidDate as invoiceP4_19_0_, invoice0_.send as send19_0_, invoice0_.snUser_id as snUser7_19_0_, invoice0_.version as version19_0_ from invoice invoice0_ where invoice0_.snUser_id=? order by invoice0_.date DESC
Hibernate: select userrole0_.snUser_id as snUser1_1_, userrole0_.userRole_id as userRole2_1_, userrole1_.id as id3_0_, userrole1_.role_name as role2_3_0_, userrole1_.role_newmember as role3_3_0_, userrole1_.role_text as role4_3_0_, userrole1_.version as version3_0_ from sn_user_user_role userrole0_ left outer join user_role userrole1_ on userrole0_.userRole_id=userrole1_.id where userrole0_.snUser_id=?
Hibernate: select invoice0_.snUser_id as snUser7_1_, invoice0_.id as id1_, invoice0_.id as id19_0_, invoice0_.date as date19_0_, invoice0_.display as display19_0_, invoice0_.invoicePaidDate as invoiceP4_19_0_, invoice0_.send as send19_0_, invoice0_.snUser_id as snUser7_19_0_, invoice0_.version as version19_0_ from invoice invoice0_ where invoice0_.snUser_id=? order by invoice0_.date DESC
Hibernate: select userrole0_.snUser_id as snUser1_1_, userrole0_.userRole_id as userRole2_1_, userrole1_.id as id3_0_, userrole1_.role_name as role2_3_0_, userrole1_.role_newmember as role3_3_0_, userrole1_.role_text as role4_3_0_, userrole1_.version as version3_0_ from sn_user_user_role userrole0_ left outer join user_role userrole1_ on userrole0_.userRole_id=userrole1_.id where userrole0_.snUser_id=?
Hibernate: select invoice0_.snUser_id as snUser7_1_, invoice0_.id as id1_, invoice0_.id as id19_0_, invoice0_.date as date19_0_, invoice0_.display as display19_0_, invoice0_.invoicePaidDate as invoiceP4_19_0_, invoice0_.send as send19_0_, invoice0_.snUser_id as snUser7_19_0_, invoice0_.version as version19_0_ from invoice invoice0_ where invoice0_.snUser_id=? order by invoice0_.date DESC
...
and so on.

How can I optimize my hql query to get the user object in less time?

You might set the OneToMany or ManyToMany relationships lazy (I don't know if they are lazy per default with Hibernate Annotations)
And also check that the other objects don't eagerly fetch the User object.

I had a problem at home, writing a tutorial / proof of concept for using and migrating Hibernate on the Oracle Database XE edition's HR example schema.

I had Countries that referred to Departments, Departments that referred to Employees, and Employees that referred to other Employees as Manager.
I used the ToStringBuilder.reflectionToString to print out the entire object as debug information.
But due to the fact that I had everyting bidirectional, and Employees also referred to Departments, and back to Countries ... I got in a kind of lazy-get loop printing all the stuff, and had an OutOfMemoryError just on a logger.debug().

So, perhaps that's the case with your code there as well, everything gets fetched eagerly and separate queries are executed to fetch al information.

What might help as well, if the fetching happens per default or you need eager fetching, you can specify a kind of "fetch-mode" (don't know the Annotation equivalent of it) to "join" instead of "select".
That way, Hibernate tries to fetch the necessary information in one query using outer join clauses.

It might help to refine your mapping, sounds like the User object is map extensively with other collections or objects. If your goal is to load only the User object, it might make sense to make another mapping of User as plain vanilla. This will avoid loading unnecessary objects not needed at a specific use case.

Don't listen to those guys above, they are just jealous of your clearly superior schema design skills. It looks like you're not joining enough tables in your query - relational database queries work faster the more tables you join into a query (fact: it was in the Codd papers).

At least throw an eager collection load in there.

Or just tell your DBA to make an index to speed it up.

Stolley :-)

Do we have to take your post sarcastic about the joins?
I'm not quite sure ... what your meaning is over there.

But indeed, eager fetching might help, indexes will speed up the fetch but won't reduce the amount of queries.

zlzc2000, can you give us an UML of your object model or something, and how its associations are?