Hi,

I have a problem mapping a parent child relation with a one-to-many set. What I see is that a simple query returns duplicate of the parent, one for each child and the set of children only contains one element. To get rid of the duplicates I tried criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) which solved that problem but the set of AccessRoleReferenceDTO's still only contains one element (the first one found I guess). I've tried elaborating with outer-join attribute and other tips I found in the forum but nothing helps. I've found many similar topics in this matter on the forum but unfortunalty none of them has helped me. I'm a newbee on hibernate so probably it's me how has missunderstand something but I'd appreciate all tips to make this work.

Here's my mapping ( in short versions )

<class name="se.marakanda.maccess.entity.model.UserProfileDTO" table="SYTUSERPROFILE" >
<!-- Removed section for clarity-->

<set name="accessRoleReferencesSet" lazy="false" inverse="true" cascade="all-delete-orphan">
<key column="USERPROFILEDID" />
<one-to-many class="se.marakanda.maccess.entity.model.AccessRoleReferenceDTO"/>
</set>

</class>

<class name="se.marakanda.maccess.entity.model.AccessRoleReferenceDTO" table="SYTACL">
<!-- Removed section for clarity -->

<many-to-one name="userProfile" class="se.marakanda.maccess.entity.model.UserProfileLightDTO" not-null="true">
<column name="USERPROFILEDID" />
</many-to-one>

</class>

As you are a newbie I would strongly recommend that you post as many information about your environment (test code, hibernate.cfg.xml, mappings etc), as it is possible that the error has a totally different origin than what you think. I must admit I didn't quite understand your problem from your description.

A short question: in your mapping there is a UserProfileLightDTO and a UserProfileDTO class - what's the difference?

Erik

Ok, I'd explain my problem a little deeper.

I have a class UserProfileDTO that has a bunch of attributes, one of these is a set of AccessRoleReferenceDTO. In my db I can see that for a certain UserProfile pk, there is 2 AccessRoleReference's, Hence I a query or get for that specific UserProfileDTO should contain a set of two AccessROleReferenceDTO's, but it does not, it only contains the first one (by primary key). And if I remove the setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) explained earlier it will return duplicates of the UserProfileDTO (i.e. the same pk) but each will contains only one AccessRoleReferenceDTO ( though different ones ).

My problem is described in another (quite old) post quite well:
http://forum.hibernate.org/viewtopic.php?t=937117
But unfortunalty there was not real good answer on how to resolve it.

I realize that it's hard to explain, so I'll past in some code/config as well.

Oh, yes the UserProfileLightDTO is "lighter" version of the UserProfileDTO. Unfortunalty a legacy of old code ( before hibernateialization ) and to reduce the amount of refactorings when now introducing Hibernate, we've made UserProfileDTO extend UserProfileLightDTO and as much as possible kept the original signatures in the DTOs, jsut adding whatever hibernate needs. However, I realize now that the inverse="true" attribute in the UserProfileDTO mapping might not be correct since the AccessRoleReferenceDTO's userprofile reference is of type UserProfileLightDTO? I will check that.

Mapping files:
<class name="se.marakanda.maccess.entity.model.UserProfileDTO" table="SYTUSERPROFILE" >
<id name="did" type="long" unsaved-value="0" column="USERPROFILEDID">
<generator class="sequence">
<param name="sequence">SEQ_MACCESS_DEFAULT</param>
</generator>
</id>

<property name="partyshipDID" type="long" column="PARTYSHIPDID" not-null="true" length="22"/>
<property name="ownerName" type="java.lang.String" column="OWNERNAME" length="80" />
<property name="lastUsed" type="java.util.Date" column="LASTUSEDDATE" length="7" />
<property name="disabled" type="boolean" column="DISABLED" length="22" />

<set name="accessRoleReferencesSet" lazy="false" inverse="true" cascade="all-delete-orphan">
<key column="USERPROFILEDID" />
<one-to-many class="se.marakanda.maccess.entity.model.AccessRoleReferenceDTO"/>
</set>
<many-to-one name="realmUser" class="se.marakanda.maccess.entity.model.RealmUserDTO" cascade="all" not-null="true">
<column name="REALMUSERDID" />
</many-to-one>
</class>

<class name="se.marakanda.maccess.entity.model.FunctionReferenceDTO" table="SYTFUNCACCESS">
<id name="did" type="long" column="FUNCACCESSDID">
<generator class="sequence">
<param name="sequence">SEQ_MACCESS_DEFAULT</param>
</generator>
</id>

<many-to-one name="function" class="se.marakanda.maccess.entity.model.FunctionDTO" not-null="true">
<column name="FUNCDID" />
</many-to-one>
<many-to-one name="accessRole" class="se.marakanda.maccess.entity.model.AccessRoleDTO" not-null="true">
<column name="ACCESSROLEDID" />
</many-to-one>
</class>

Hibernate config:
<hibernate-configuration>
<session-factory name="java:hibernate/MAccessHibernateFactory">
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.url">jdbc:oracle:thin:@<removed ip address>:1521:utvdb9i</property>
<property name="hibernate.connection.username"><removed user name></property>
<property name="hibernate.connection.password"><removed password></property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.dialect">net.sf.hibernate.dialect.OracleDialect</property>
<!-- Use the Hibernate built-in pool for tests. -->
<!--<property name="connection.pool_size">1</property>-->

<!-- Use EHCache but not the query cache.
<property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
<property name="cache.use_query_cache">false</property>
<property name="cache.use_minimal_puts">false</property>
-->

<!-- Print SQL to stdout. -->
<property name="show_sql">true</property>

<mapping resource="se/marakanda/maccess/entity/model/AccessRoleDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/AccessRoleLightDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/AccessRoleReferenceDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/CredentialDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/FunctionDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/FunctionReferenceDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/PasswordHistoryDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/RealmUSerDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/UserProfileDTO.hbm.xml"/>
<mapping resource="se/marakanda/maccess/entity/model/UserProfileLightDTO.hbm.xml"/>

</session-factory>
</hibernate-configuration>

DAO Code
public UserProfileDTO[] findUserProfiles(UserProfileSearchDTO searchObject) throws PersistenceException
{
Session session = HibernateSessionFactory.getInstance().getSession();
try {
Criteria criteria = session.createCriteria(UserProfileDTO.class);
Criteria realmUserCrit = criteria.createCriteria("realmUser");
if (searchObject.getUserProfileDID()!=null) {
criteria.add(Expression.eq("did",searchObject.getUserProfileDID()));
}
if (searchObject.getUserId()!=null) {
realmUserCrit.add(Expression.like("userId",searchObject.getUserId()));
}
if (searchObject.getDisabled()!=null) {
realmUserCrit.add(Expression.eq("disabled",Boolean.FALSE));
}
if (searchObject.getPartyshipDID()!=null) {
criteria.add(Expression.eq("pertyshipDID",searchObject.getPartyshipDID()));
}
if (searchObject.getPartyshipDIDs()!=null && searchObject.getPartyshipDIDs().length>0 ) {
criteria.add(Expression.in("partyshipDID",searchObject.getPartyshipDIDs()));
}
if (searchObject.getPassword()!=null) {
realmUserCrit.add(Expression.like("pwd",searchObject.getPassword()));
}
if (searchObject.getAccessRoleDID()!=null || searchObject.getRoleName()!=null || searchObject.getRoleValidAt()!=null) {
Criteria accessRoleRefCrit = criteria.createCriteria("accessRoleReferencesSet");
Criteria accessRoleCrit = accessRoleRefCrit.createCriteria("accessRole");
if (searchObject.getAccessRoleDID()!=null) {
accessRoleCrit.add(Expression.eq("did",searchObject.getAccessRoleDID()));
}
if (searchObject.getRoleName()!=null) {
accessRoleCrit.add(Expression.like("accessRole",searchObject.getRoleName()));
}
if (searchObject.getRoleValidAt()!=null) {
if (searchObject.getSkipNullInRoleValidAt()!=null && searchObject.getSkipNullInRoleValidAt().booleanValue()) {
accessRoleRefCrit.add(Expression.le("validFrom",searchObject.getRoleValidAt()));
accessRoleRefCrit.add(Expression.ge("validTo",searchObject.getRoleValidAt()));
} else {
accessRoleRefCrit.add(Expression.or(Expression.isNull("validFrom"),Expression.le("validFrom",searchObject.getRoleValidAt())));
accessRoleRefCrit.add(Expression.or(Expression.isNull("validTo"),Expression.ge("validTo",searchObject.getRoleValidAt())));
}
}
}
if (searchObject.getPrincipal()!=null) {
if (!(searchObject.getPrincipal() instanceof MecpPrincipal)) {
searchObject.setPrincipal(new MecpPrincipal(searchObject.getPrincipal().toString()));
}
realmUserCrit.add(Expression.like("userId",((MecpPrincipal)searchObject.getPrincipal()).getName()));
if("marakanda".equals(((MecpPrincipal)searchObject.getPrincipal()).getContext())) {
// Default context! Include also null values in search
criteria.add(Expression.or(Expression.isNull("ownerName"),Expression.like("ownerName",((MecpPrincipal)searchObject.getPrincipal()).getContext())));
} else {
criteria.add(Expression.like("ownerName",((MecpPrincipal)searchObject.getPrincipal()).getContext()));
}
}
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
List results = criteria.list();
return (UserProfileDTO[]) results.toArray(new UserProfileDTO[0]);
} catch (HibernateException e) {
throw new PersistenceException(e);
}
}

I refactored my code and made the reference from AccessRoleReferenceDTO a real UserProfileDTO instead of a UserProfileLightDTO. Now I end upp with an eternal loop in hibernate, where this query seams to loop forever:

SELECT accessrole0_.userprofiledid AS userprof4___,
accessrole0_.acldid AS acldid__, accessrole0_.acldid AS acldid1_,
accessrole0_.validfrom AS validfrom1_,
accessrole0_.validuntil AS validuntil1_,
accessrole0_.userprofiledid AS userprof4_1_,
accessrole0_.accessroledid AS accessro5_1_,
accessrole1_.accessroledid AS accessro1_0_,
accessrole1_.accessrole AS accessrole0_,
accessrole1_.isdefaultrole AS isdefaul3_0_,
accessrole1_.companydid AS companydid0_
FROM sytacl accessrole0_, sytaccessrole accessrole1_
WHERE accessrole0_.userprofiledid = ?
AND accessrole0_.accessroledid = accessrole1_.accessroledid(+)

Any ideas someone?