Hi,

I fully read the document on http://www.hibernate.org/hib_docs/reference/html/. However, I still get problems in one-to-many mapping. I have two tables, OneCXN_User and USER_SMSISDN in Oracle 9i The relationship of OneCXN_User to USER_SMSISDN is one to many. I have created similar Objects for those tables in which OneCXN_User has a Set to collect all associate USER_SMSISDN records. However, when I query the OneCXN_User through Hibernate, the size of Set that constain USER_SMSISDN in OneCXN Object is 0 but I been confirmed that I have put testing data that has relationship already.

My xml is as following.
<class name="OneCXNUser" table="ONECXN_USER">
<id name="imsi" type="string" unsaved-value="null" >
<column name="IMSI" sql-type="varchar(20)" not-null="true"/>
<generator class="uuid.hex"/>
</id>
<property name="homewkID" type="int">
<column name="HOME_NETWORKID" sql-type="number(2)"
not-null="true"/>
</property>
<property name="pmsisdn">
<column name="PMSISDN" sql-type="varchar(20)"
not-null="false"/>
</property>
<set name="smsisdn" table="USER_SMSISDN" lazy="true"
cascade="all">
<key column="PMSISDN"/>
<one-to-many class="UserSMsisdn" />
</set>
</class>
<class name="UserSMsisdn" table="USER_SMSISDN">
<composite-id>
<key-property name="secwkID" type="int">
<column name="SEC_NETWORKID" sql-type="number(16)"
not-null="true"/>
</key-property>
<key-property name="smsisdn" type="string">
<column name="sMSISDN" sql-type="varchar(16)"
not-null="true"/>
</key-property>
</composite-id>

<many-to-one name="onecxnUser" column="PMSISDN"
class="OneCXNUser"
not-null="true" cascade="all"/>
</class>

My Java Bean class are as following
public class OneCXNUser {

private String imsi=null;
private int homewkID=0;
private HomeNetwork homewk=null;
private String pmsisdn=null;
private Set smsisdn=null;
getter/setter method...
}

public class UserSMsisdn {

private int secwkID=0;
private String pmsisdn=null;
private String smsisdn=null;
private OneCXNUser onecxnUser=null;
getter/setter method...

}

My Query are following
select oneCXNUser from OneCXNUser as oneCXNUser left outer join oneCXNUser.smsisdn as userSMsisdn

Output of Hiberate screen
15:28:34,247 INFO [STDOUT] Hibernate: select onecxnus0_.IMSI as IMSI, onecxnus0
_.HOME_NETWORKID as HOME_NET2_, onecxnus0_.PMSISDN as PMSISDN, onecxnus0_.HLR_GT_ADDR as HLR_GT_A4_ from ONECXN_USER onecxnus0_, USER_SMSISDN smsisdn1_ where on
ecxnus0_.IMSI=smsisdn1_.PMSISDN(+)
15:28:34,277 INFO [STDOUT] Hibernate: select onecxnus0_.IMSI as x0_0_ from ONECXN_USER onecxnus0_, USER_SMSISDN smsisdn1_ where onecxnus0_.IMSI=smsisdn1_.PMSIS
DN(+)
15:28:34,287 INFO [STDOUT] Hibernate: select user_sms0_.SEC_NETWORKID as SEC_NETW1___, user_sms0_.sMSISDN as sMSISDN__, user_sms0_.SEC_NETWORKID as SEC_NETWORK
ID, user_sms0_.sMSISDN as sMSISDN, user_sms0_.PMSISDN as PMSISDN from USER_SMSISDN user_sms0_ where user_sms0_.PMSISDN=?

Please help to point what's wrong on my setting that why I cannot return any USER_SMSISDN in the Set of OneCXNUser upon the query.

Thanks in advance.

Are you sure that association insert works fine.

USER_SMSISDN.PMSISDN must refer to ONECXN_USER.IMSI in your DB. You may mismatch with ONECXN_USER.PMSISDN.

Show your insert java code.
Check your getter and setters setSmsisdn and getSmsisdn

BTW your resquested query is select oneCXNUser from OneCXNUser as oneCXNUser left outer join fetch oneCXNUser.smsisdn as userSMsisdn

Thanks for your help.

The db schema is fine, USER_SMSISDN.PMSISDN must refer to ONECXN_USER.PMSISDN instead of ONECXN_USER.IMSI. The db schema is designed to be this, ONECXN_USER.IMSI is the key in ONECXN_USER. However, when a record of ONECXN_USER is created, all columns can be null including ONECXN_USER.PMSISDN. On the other hands, when a record in ONECXN_USER is updated the column of ONECXN_USER.PMSISDN and so will create a record in USER_SMSISDN.

Base on my current design and your feedback, I have amended my xml a little bit as following to use composite key to include ONECXN_USER.PMSISDN as one of key

<class name="com.primecreation.cxn.system.bean.OneCXNUser" table="ONECXN_USER">
<composite-id>
<key-property name="imsi" type="string">
<column name="IMSI" sql-type="varchar(20)" not-null="false"/>
</key-property>
<key-property name="pmsisdn" type="string">
<column name="PMSISDN" sql-type="varchar(20)" not-null="true"/>
</key-property>
</composite-id>
<property name="homewkID" type="int">
<column name="HOME_NETWORKID" sql-type="number(2)" not-null="true"/>
</property>
<set name="smsisdn" table="USER_SMSISDN" lazy="true" cascade="all" inverse="false">
<key column="PMSISDN"/>
<one-to-many class="com.primecreation.cxn.system.bean.UserSMsisdn" />
</set>
</class>

However, when I use the same query, it returns the following exception, please help again.

22:29:36,800 INFO [Binder] Mapping collection: .OneCXNUser.smsisdn -> USER_SMSISDN
22:29:36,800 INFO [Configuration] processing foreign key constraints
22:29:36,810 ERROR [STDERR] net.sf.hibernate.MappingException: Foreign key must have same number of columns as referenced primary key at net.sf.hibernate.mapping.ForeignKey.setReferencedTable(ForeignKey.java:33)
at net.sf.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:523)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:623)

Thanks for your help again

The collection <key> element is the foreign key to the owning object. Hence it must always have the same number of columns as the primary key of the owner. (In this case, the owning object has a composite key, so the collection key has more than one column.)


(The exception message is pretty clear about the problem, actually.)