Hibernate version: 2.1.4

Hello,

I have 3 tables, account, user_info, and user_url.
The user_info table has a column account_id, which is a FK referencing account.id
(the idea is to have multiple users share the same account (username/password) record)
The user_url table has a column user_id, which is a FK referencing user_info.id

The problem I'm having is that I see too many selects for essentially the same data.
(their difference is in bold below)

Mapping documents:

This is from Account.hbm.xml:

<one-to-one
name="userInfo"
class="com.example.model.UserInfo"
cascade="all"
outer-join="auto"
constrained="false"
property-ref="account"
/>

This is from UserInfo.hbm.xml:

<many-to-one
name="account"
class="com.example.model.Account"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="account_id"
not-null="true"
unique="true"
/>

This is from UserUrl.hbm.xml:

<many-to-one
name="userInfo"
class="com.example.model.UserInfo"
cascade="none"
outer-join="true"
update="true"
insert="true"
access="property"
column="user_id"
not-null="true"
unique="true"
/>


Name and version of the database you are using: PostgreSQL 8.0.3

The generated SQL (show_sql=true):

This is my Java/HQL:



When the above is executes, Hibernate executes a number of other SELECT statements, 3 of which are listed below:

select userinfo0_.id as id7_, userinfo0_.first_name as first_name7_, userinfo0_.last_name as last_name7_, userinfo0_.email as email7_, userinfo0_.href as href7_, userinfo0_.last_login_date as last_log6_7_, userinfo0_.login_count as login_co7_7_, userinfo0_.password_hint_answer as password8_7_, userinfo0_.create_date as create_d9_7_, userinfo0_.exp_date as
exp_date7_, userinfo0_.type as type7_, userinfo0_.referal as referal7_, userinfo0_.account_id as account_id7_, userinfo0_.plan_id as plan_id7_, userinfo0_.password_hint_id as passwor15_7_, userdemogr1_.id as id0_, userdemogr1_.city as city0_, userdemogr1_.postal_code as postal_c3_0_, userdemogr1_.user_id as user_id0_, userdemogr1_.country_id as country_id0_, userdemogr1_.state_id as state_id0_, userdemogr1_.gender_id as gender_id0_, country2_.id as id1_, country2_.iso2_code as iso2_code1_, country2_.name as name1_, country2_.print_name as print_name1_, country2_.iso3_code as iso3_code1_, country2_.numcode as numcode1_, state3_.id as id2_, state3_.name as name2_, state3_.abbr as abbr2_, gender4_.id as id3_, gender4_.gender as gender3_, account5_.id as id4_, account5_.username as username4_, account5_.password as password4_, account5_.status as status4_, servicepla6_.id as id5_, servicepla6_.name as name5_, servicepla6_.max_links as max_links5_, passwordhi7_.id as id6_, passwordhi7_.question as question6_ from user_info userinfo0_ left outer join user_demographic userdemogr1_ on userinfo0_.id=userdemogr1_.user_id left outer join country country2_ on userdemogr1_.country_id=country2_.id left outer join state state3_ on userdemogr1_.state_id=state3_.id left outer join gender gender4_ on userdemogr1_.gender_id=gender4_.id left outer join account account5_ on userinfo0_.account_id=account5_.id left outer join service_plan servicepla6_ on userinfo0_.plan_id=servicepla6_.id left outer join password_hint passwordhi7_ on userinfo0_.password_hint_id=passwordhi7_.id where userinfo0_.id=-2

And another one:

select userinfo0_.id as id7_, userinfo0_.first_name as first_name7_, userinfo0_.last_name as last_name7_, userinfo0_.email as email7_, userinfo0_.href as href7_, userinfo0_.last_login_date as last_log6_7_, userinfo0_.login_count as login_co7_7_, userinfo0_.password_hint_answer as password8_7_, userinfo0_.create_date as create_d9_7_, userinfo0_.exp_date as
exp_date7_, userinfo0_.type as type7_, userinfo0_.referal as referal7_, userinfo0_.account_id as account_id7_, userinfo0_.plan_id as plan_id7_, userinfo0_.password_hint_id as passwor15_7_, userdemogr1_.id as id0_, userdemogr1_.city as city0_, userdemogr1_.postal_code as postal_c3_0_, userdemogr1_.user_id as user_id0_, userdemogr1_.country_id as country_id0_, userdemogr1_.state_id as state_id0_, userdemogr1_.gender_id as gender_id0_, country2_.id as id1_, country2_.iso2_code as iso2_code1_, country2_.name as name1_, country2_.print_name as print_name1_, country2_.iso3_code as iso3_code1_, country2_.numcode as numcode1_, state3_.id as id2_, state3_.name as name2_, state3_.abbr as abbr2_, gender4_.id as id3_, gender4_.gender as gender3_, account5_.id as id4_, account5_.username as username4_, account5_.password as password4_, account5_.status as status4_, servicepla6_.id as id5_, servicepla6_.name as name5_, servicepla6_.max_links as max_links5_, passwordhi7_.id as id6_, passwordhi7_.question as question6_ from user_info userinfo0_ left outer join user_demographic userdemogr1_ on userinfo0_.id=userdemogr1_.user_id left outer join country country2_ on userdemogr1_.country_id=country2_.id left outer join state state3_ on userdemogr1_.state_id=state3_.id left outer join gender gender4_ on userdemogr1_.gender_id=gender4_.id left outer join account account5_ on userinfo0_.account_id=account5_.id left outer join service_plan servicepla6_ on userinfo0_.plan_id=servicepla6_.id left outer join password_hint passwordhi7_ on userinfo0_.password_hint_id=passwordhi7_.id where userinfo0_.account_id=-2

And another one:
select userdemogr0_.id as id7_, userdemogr0_.city as city7_, userdemogr0_.postal_code as postal_c3_7_, userdemogr0_.user_id as user_id7_, userdemogr0_.country_id as country_id7_, userdemogr0_.state_id as state_id7_, userdemogr0_.gender_id as gender_id7_, userinfo1_.id as id0_, userinfo1_.first_name as first_name0_, userinfo1_.last_name as last_name0_, userinfo1_.email as email0_, userinfo1_.href as href0_, userinfo1_.last_login_date as last_log6_0_, userinfo1_.login_count as login_co7_0_, userinfo1_.password_hint_answer as password8_0_, userinfo1_.create_date as create_d9_0_, userinfo1_.exp_date as exp_date0_, userinfo1_.type as type0_, userinfo1_.referal as referal0_, userinfo1_.account_id as account_id0_, userinfo1_.plan_id as plan_id0_, userinfo1_.password_hint_id as passwor15_0_, account2_.id as id1_, account2_.username as username1_, account2_.password as password1_, account2_.status as status1_, servicepla3_.id as id2_, servicepla3_.name as name2_, servicepla3_.max_links as max_links2_, passwordhi4_.id as id3_, passwordhi4_.question as question3_, country5_.id as id4_, country5_.iso2_code as iso2_code4_, country5_.name as name4_, country5_.print_name as print_name4_, country5_.iso3_code as iso3_code4_, country5_.numcode as numcode4_, state6_.id as id5_, state6_.name as name5_, state6_.abbr as abbr5_, gender7_.id as id6_, gender7_.gender as gender6_ from user_demographic userdemogr0_ left outer join user_info userinfo1_ on userdemogr0_.user_id=userinfo1_.id left outer join account account2_ on userinfo1_.account_id=account2_.id left outer join service_plan servicepla3_ on userinfo1_.plan_id=servicepla3_.id left outer join password_hint passwordhi4_ on userinfo1_.password_hint_id=passwordhi4_.id left outer join country country5_ on userdemogr0_.country_id=country5_.id left outer join state state6_ on userdemogr0_.state_id=state6_.id left outer join gender gender7_ on userdemogr0_.gender_id=gender7_.id where userdemogr0_.user_id=-2


It looks like I have something mapped wrong that causes Hibernate to execute a number of queries for what is really the same user (account.account_id=-2 and user_info.user_id=-2 are the same user), plus my HQL doesn't even ask for this data.

What also worries me is the size size of these SELECTs, with a whole bunch of other tables being references and JOINed. Things like "country" or "state" tables, "password_hint" table, etc. They are all somehow related to one another via FKs, but how do I tell Hibernate where to stop loading?

What am I doing wrong?
I'd be happy to provide more information.

Thanks,
Otis

Hello,

I was wondering if anyone had any ideas why these extra SELECTs might be happening, even though my HQL is as simple as:



The extra SELECTs don't happen because I try to retrieve additional entities via the loaded entity (e.g. customer.getOrders() type of thing). The extra SELECTs are triggered by that HQL above alone (verified by running my code in debugger).

Thanks,
Otis