First off, all my persistent objects extend PersistentObject so they can utilize some system properties common to all persistent objects (for example: created date, modified date, created by, modified by)

This is a sample as how i could call the query:



and here is the load function in my dao:


As you can see the SQL below, it's huge - and this is just to retrieve a user :) I have some really big objects that when i try to query, hibernate can't handle the SQL as i can't even imagine the length of the SQL being generated... Is there a way to simplify the SQL being generated?

Thanks,
~karokain

Hibernate version: 2.1.4

Mapping documents:
<?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>
<class
name="com.pcpower.amss.PersistentObject"
table="Registry"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="identity">
</generator>
</id>

<property
name="active"
type="boolean"
update="true"
insert="true"
access="property"
column="active"
length="1"
not-null="false"
/>

<many-to-one
name="createdBy"
class="com.pcpower.amss.security.objects.User"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="createdBy"
not-null="false"
/>

<property
name="createdDate"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="createdDate"
not-null="false"
/>

<many-to-one
name="modifiedBy"
class="com.pcpower.amss.security.objects.User"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="modifiedBy"
not-null="false"
/>

<property
name="modifiedDate"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="modifiedDate"
not-null="false"
/>

<many-to-one
name="parent"
class="com.pcpower.amss.PersistentObject"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="activeId"
not-null="false"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-PersistentObject.xml
containing the additional properties and place it in your merge dir.
-->

<joined-subclass
name="com.pcpower.amss.security.objects.Role"
table="Roles"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="id"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
length="50"
not-null="true"
/>

</joined-subclass>
<joined-subclass
name="com.pcpower.amss.security.objects.Client"
table="Clients"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="id"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
length="50"
not-null="true"
/>

</joined-subclass>
<joined-subclass
name="com.pcpower.amss.security.objects.User"
table="Users"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="id"
/>
<property
name="birthDate"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="birthDate"
length="10"
not-null="false"
/>

<property
name="cellular"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="cellular"
length="21"
not-null="false"
/>

<many-to-one
name="client"
class="com.pcpower.amss.security.objects.Client"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="client"
not-null="true"
/>

<many-to-one
name="department"
class="com.pcpower.amss.security.objects.Department"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="department"
not-null="false"
/>

<property
name="email"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="email"
length="75"
not-null="false"
/>

<property
name="govId"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="govId"
length="50"
not-null="true"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
length="75"
not-null="true"
/>

<property
name="password"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="password"
length="16"
not-null="true"
/>

<set
name="roles"
table="UserRoles"
lazy="false"
inverse="false"
cascade="none"
sort="unsorted"
>

<key
column="user_id"
>
</key>

<many-to-many
class="com.pcpower.amss.security.objects.Role"
column="role_id"
outer-join="auto"
/>

</set>

<property
name="telephone"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="telephone"
length="21"
not-null="false"
/>

<property
name="username"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="username"
length="16"
not-null="true"
/>

</joined-subclass>
<joined-subclass
name="com.pcpower.amss.security.objects.Department"
table="Departments"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="id"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
length="50"
not-null="true"
/>

</joined-subclass>

</class>

</hibernate-mapping>




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

The generated SQL (show_sql=true):
Hibernate: select user0_.id as id6_, user0_.birthDate as birthDate3_6_, user0_.cellular as cellular3_6_, user0_.client as client3_6_, user0_.department as department3_6_, user0_.email as email3_6_, user0_.govId as govId3_6_, user0_.name as name3_6_, user0_.password as password3_6_, user0_.telephone as telephone3_6_, user0_.username as username3_6_, user0__1_.active as active0_6_, user0__1_.createdBy as createdBy0_6_, user0__1_.createdDate as createdD4_0_6_, user0__1_.modifiedBy as modifiedBy0_6_, user0__1_.modifiedDate as modified6_0_6_, user0__1_.activeId as activeId0_6_, client1_.id as id0_, client1_.name as name2_0_, client1__1_.active as active0_0_, client1__1_.createdBy as createdBy0_0_, client1__1_.createdDate as createdD4_0_0_, client1__1_.modifiedBy as modifiedBy0_0_, client1__1_.modifiedDate as modified6_0_0_, client1__1_.activeId as activeId0_0_, persistent2_.id as id1_, case when persistent2__1_.id is not null then 1 when persistent2__2_.id is not null then 2 when persistent2__3_.id is not null then 3 when persistent2__4_.id is not null then 4 when persistent2_.id is not null then 0 end as clazz_1_, persistent2_.active as active0_1_, persistent2_.createdBy as createdBy0_1_, persistent2_.createdDate as createdD4_0_1_, persistent2_.modifiedBy as modifiedBy0_1_, persistent2_.modifiedDate as modified6_0_1_, persistent2_.activeId as activeId0_1_, persistent2__1_.name as name1_1_, persistent2__2_.name as name2_1_, persistent2__3_.birthDate as birthDate3_1_, persistent2__3_.cellular as cellular3_1_, persistent2__3_.client as client3_1_, persistent2__3_.department as department3_1_, persistent2__3_.email as email3_1_, persistent2__3_.govId as govId3_1_, persistent2__3_.name as name3_1_, persistent2__3_.password as password3_1_, persistent2__3_.telephone as telephone3_1_, persistent2__3_.username as username3_1_, persistent2__4_.name as name5_1_, persistent3_.id as id2_, case when persistent3__1_.id is not null then 1 when persistent3__2_.id is not null then 2 when persistent3__3_.id is not null then 3 when persistent3__4_.id is not null then 4 when persistent3_.id is not null then 0 end as clazz_2_, persistent3_.active as active0_2_, persistent3_.createdBy as createdBy0_2_, persistent3_.createdDate as createdD4_0_2_, persistent3_.modifiedBy as modifiedBy0_2_, persistent3_.modifiedDate as modified6_0_2_, persistent3_.activeId as activeId0_2_, persistent3__1_.name as name1_2_, persistent3__2_.name as name2_2_, persistent3__3_.birthDate as birthDate3_2_, persistent3__3_.cellular as cellular3_2_, persistent3__3_.client as client3_2_, persistent3__3_.department as department3_2_, persistent3__3_.email as email3_2_, persistent3__3_.govId as govId3_2_, persistent3__3_.name as name3_2_, persistent3__3_.password as password3_2_, persistent3__3_.telephone as telephone3_2_, persistent3__3_.username as username3_2_, persistent3__4_.name as name5_2_, department4_.id as id3_, department4_.name as name5_3_, department4__1_.active as active0_3_, department4__1_.createdBy as createdBy0_3_, department4__1_.createdDate as createdD4_0_3_, department4__1_.modifiedBy as modifiedBy0_3_, department4__1_.modifiedDate as modified6_0_3_, department4__1_.activeId as activeId0_3_, department5_.id as id4_, department5_.name as name5_4_, department5__1_.active as active0_4_, department5__1_.createdBy as createdBy0_4_, department5__1_.createdDate as createdD4_0_4_, department5__1_.modifiedBy as modifiedBy0_4_, department5__1_.modifiedDate as modified6_0_4_, department5__1_.activeId as activeId0_4_, department6_.id as id5_, department6_.name as name5_5_, department6__1_.active as active0_5_, department6__1_.createdBy as createdBy0_5_, department6__1_.createdDate as createdD4_0_5_, department6__1_.modifiedBy as modifiedBy0_5_, department6__1_.modifiedDate as modified6_0_5_, department6__1_.activeId as activeId0_5_ from Users user0_ inner join Registry user0__1_ on user0_.id=user0__1_.id left outer join Clients client1_ on user0_.client=client1_.id left outer join Registry client1__1_ on client1_.id=client1__1_.id left outer join Registry persistent2_ on client1__1_.activeId=persistent2_.id left outer join Roles persistent2__1_ on persistent2_.id=persistent2__1_.id left outer join Clients persistent2__2_ on persistent2_.id=persistent2__2_.id left outer join Users persistent2__3_ on persistent2_.id=persistent2__3_.id left outer join Departments persistent2__4_ on persistent2_.id=persistent2__4_.id left outer join Registry persistent3_ on persistent2_.activeId=persistent3_.id left outer join Roles persistent3__1_ on persistent3_.id=persistent3__1_.id left outer join Clients persistent3__2_ on persistent3_.id=persistent3__2_.id left outer join Users persistent3__3_ on persistent3_.id=persistent3__3_.id left outer join Departments persistent3__4_ on persistent3_.id=persistent3__4_.id left outer join Departments department4_ on persistent3__3_.department=department4_.id left outer join Registry department4__1_ on department4_.id=department4__1_.id left outer join Departments department5_ on persistent2__3_.department=department5_.id left outer join Registry department5__1_ on department5_.id=department5__1_.id left outer join Departments department6_ on user0_.department=department6_.id left outer join Registry department6__1_ on department6_.id=department6__1_.id where user0_.id=?
Hibernate: select roles0_.role_id as role_id__, roles0_.user_id as user_id__, role1_.id as id0_, role1_.name as name1_0_, role1__1_.active as active0_0_, role1__1_.createdBy as createdBy0_0_, role1__1_.createdDate as createdD4_0_0_, role1__1_.modifiedBy as modifiedBy0_0_, role1__1_.modifiedDate as modified6_0_0_, role1__1_.activeId as activeId0_0_, user2_.id as id1_, user2_.birthDate as birthDate3_1_, user2_.cellular as cellular3_1_, user2_.client as client3_1_, user2_.department as department3_1_, user2_.email as email3_1_, user2_.govId as govId3_1_, user2_.name as name3_1_, user2_.password as password3_1_, user2_.telephone as telephone3_1_, user2_.username as username3_1_, user2__1_.active as active0_1_, user2__1_.createdBy as createdBy0_1_, user2__1_.createdDate as createdD4_0_1_, user2__1_.modifiedBy as modifiedBy0_1_, user2__1_.modifiedDate as modified6_0_1_, user2__1_.activeId as activeId0_1_, client3_.id as id2_, client3_.name as name2_2_, client3__1_.active as active0_2_, client3__1_.createdBy as createdBy0_2_, client3__1_.createdDate as createdD4_0_2_, client3__1_.modifiedBy as modifiedBy0_2_, client3__1_.modifiedDate as modified6_0_2_, client3__1_.activeId as activeId0_2_, persistent4_.id as id3_, case when persistent4__1_.id is not null then 1 when persistent4__2_.id is not null then 2 when persistent4__3_.id is not null then 3 when persistent4__4_.id is not null then 4 when persistent4_.id is not null then 0 end as clazz_3_, persistent4_.active as active0_3_, persistent4_.createdBy as createdBy0_3_, persistent4_.createdDate as createdD4_0_3_, persistent4_.modifiedBy as modifiedBy0_3_, persistent4_.modifiedDate as modified6_0_3_, persistent4_.activeId as activeId0_3_, persistent4__1_.name as name1_3_, persistent4__2_.name as name2_3_, persistent4__3_.birthDate as birthDate3_3_, persistent4__3_.cellular as cellular3_3_, persistent4__3_.client as client3_3_, persistent4__3_.department as department3_3_, persistent4__3_.email as email3_3_, persistent4__3_.govId as govId3_3_, persistent4__3_.name as name3_3_, persistent4__3_.password as password3_3_, persistent4__3_.telephone as telephone3_3_, persistent4__3_.username as username3_3_, persistent4__4_.name as name5_3_, persistent5_.id as id4_, case when persistent5__1_.id is not null then 1 when persistent5__2_.id is not null then 2 when persistent5__3_.id is not null then 3 when persistent5__4_.id is not null then 4 when persistent5_.id is not null then 0 end as clazz_4_, persistent5_.active as active0_4_, persistent5_.createdBy as createdBy0_4_, persistent5_.createdDate as createdD4_0_4_, persistent5_.modifiedBy as modifiedBy0_4_, persistent5_.modifiedDate as modified6_0_4_, persistent5_.activeId as activeId0_4_, persistent5__1_.name as name1_4_, persistent5__2_.name as name2_4_, persistent5__3_.birthDate as birthDate3_4_, persistent5__3_.cellular as cellular3_4_, persistent5__3_.client as client3_4_, persistent5__3_.department as department3_4_, persistent5__3_.email as email3_4_, persistent5__3_.govId as govId3_4_, persistent5__3_.name as name3_4_, persistent5__3_.password as password3_4_, persistent5__3_.telephone as telephone3_4_, persistent5__3_.username as username3_4_, persistent5__4_.name as name5_4_, department6_.id as id5_, department6_.name as name5_5_, department6__1_.active as active0_5_, department6__1_.createdBy as createdBy0_5_, department6__1_.createdDate as createdD4_0_5_, department6__1_.modifiedBy as modifiedBy0_5_, department6__1_.modifiedDate as modified6_0_5_, department6__1_.activeId as activeId0_5_, department7_.id as id6_, department7_.name as name5_6_, department7__1_.active as active0_6_, department7__1_.createdBy as createdBy0_6_, department7__1_.createdDate as createdD4_0_6_, department7__1_.modifiedBy as modifiedBy0_6_, department7__1_.modifiedDate as modified6_0_6_, department7__1_.activeId as activeId0_6_, department8_.id as id7_, department8_.name as name5_7_, department8__1_.active as active0_7_, department8__1_.createdBy as createdBy0_7_, department8__1_.createdDate as createdD4_0_7_, department8__1_.modifiedBy as modifiedBy0_7_, department8__1_.modifiedDate as modified6_0_7_, department8__1_.activeId as activeId0_7_ from UserRoles roles0_ inner join Roles role1_ on roles0_.role_id=role1_.id left outer join Registry role1__1_ on role1_.id=role1__1_.id left outer join Users user2_ on role1__1_.createdBy=user2_.id left outer join Registry user2__1_ on user2_.id=user2__1_.id left outer join Clients client3_ on user2_.client=client3_.id left outer join Registry client3__1_ on client3_.id=client3__1_.id left outer join Registry persistent4_ on client3__1_.activeId=persistent4_.id left outer join Roles persistent4__1_ on persistent4_.id=persistent4__1_.id left outer join Clients persistent4__2_ on persistent4_.id=persistent4__2_.id left outer join Users persistent4__3_ on persistent4_.id=persistent4__3_.id left outer join Departments persistent4__4_ on persistent4_.id=persistent4__4_.id left outer join Registry persistent5_ on persistent4_.activeId=persistent5_.id left outer join Roles persistent5__1_ on persistent5_.id=persistent5__1_.id left outer join Clients persistent5__2_ on persistent5_.id=persistent5__2_.id left outer join Users persistent5__3_ on persistent5_.id=persistent5__3_.id left outer join Departments persistent5__4_ on persistent5_.id=persistent5__4_.id left outer join Departments department6_ on persistent5__3_.department=department6_.id left outer join Registry department6__1_ on department6_.id=department6__1_.id left outer join Departments department7_ on persistent4__3_.department=department7_.id left outer join Registry department7__1_ on department7_.id=department7__1_.id left outer join Departments department8_ on user2_.department=department8_.id left outer join Registry department8__1_ on department8_.id=department8__1_.id where roles0_.user_id=?

have you read about lazy loading, proxies, outerjoin eager fetching etc. in the docs ?

if not - then please do, if yes, then do it again ;)

hahah, thanks for the tip - i'll read up on that... just needed a starting place!

Thanks,
~karokain