Do I have to set up foreign key constraint on database level to make one-to-many associations working?

I plan to map one user table and one user roles table in the mainframe db2 database. A user has one or many roles. And I set up undirectional one-to-many relationship in user mapping file. But When I try to retrieve getRoles() from user object, it is empty.

I think the problem is caused by the foreign key constraint is not enabled on password history table. And I can't get our DBA to do that because we don't own those two tables.


Is there any workaround for this kind of problem? HQL in mapping xml file?


Thanks!

No, you don't need any constraints at the DB level, though they are recommended. If you're not getting any values back, it's most likely to be a mapping problem.

Could you please give me an example about how to map tables having no DB-level constraint? I'm confused by this problem for a long time.

There is no change to the mapping: hibernate will continue operating as normal. The risk is that an expected constraint might be violated due to programming error, or manual changes to the DB, and then hibernate might barf due to (for example) unexpected missing rows.

As a basic example using User and Role classes:That's all you need for unidirectional one-to-many. For bidirectional, add a many-to-one mapping from Role back to User.

From the sounds of the names, you should consider whether or not a many-to-many mapping might be more appropriate. Can one Role be used by more than one User? If Role is something like Guest, Admin, etc. then it probably can be, in which case you probably want a unidirectional many-to-many mapping.

Thanks! User and roles should be many-to-many relation. Here is my problem. I will appreciate if you can help me figure it out.

Besides roles, I also want to map password history table. The following is the table structure.

table tupw_psw
{
UPW_USR_ID CHAR(8) NOT NULL,
UPW_EFF_DT DATE NOT NULL,
UPW_EXP_DT DATE NOT NULL,
UPW_PWS_CD CHAR(1),
UPW_HSU_TS TIMESTMP NOT NULL,
UPW_PSW_TX VARCHAR(60) NOT NULL
}

table tusr_user
{
USR_ID CHAR(8) NOT NULL,
USR_FST_NM CHAR(30) NOT NULL,
USR_INL_NM CHAR(4) NOT NULL,
USR_LST_NM CHAR(30) NOT NULL,
USR_MDL_NM CHAR(30),
USR_EMP_ID CHAR(20),
USR_ORN_ID INTEGER NOT NULL,
USR_EFF_DT DATE NOT NULL,
USR_EXP_DT DATE NOT NULL,
}


Tables are in OS390 mainframe db2. There are no constraint(primary key, foreign key) set up. And becuase our team don't own the tables, we are not allowed to change the table schema or doing anything


As you can see table tupw_psw is a password history table. So one user may have one or multiple passwords for different time frame. Naturally, we'd like to map this as one-to-many/many-to-one relationship. The following are mapping files.

<class name="Password"
table="tupw_psw" schema="qcpdt3a" lazy="true">

<composite-id>
<key-property name="usrId" column="upw_usr_id"/>
<key-property name="hsuTs" column="upw_hsu_ts"/>
</composite-id>

<property name="effDt"
column="upw_eff_dt"/>

<property name="expDt"
column="upw_exp_dt"/>

<property name="pwsCd"
column="upw_pws_cd"/>

<property name="pswTx"
column="upw_psw_tx"/>

<many-to-one name="user"
column="upw_usr_id" insert="false" update="false"
class="User"
not-null="true"/>

</class>


<class name="User"
table="tusr_user" schema="qcpdt3a" lazy="true">

<id name="id" column="usr_id">
</id>

<property name="fstNm"
column="usr_fst_nm"/>

<property name="inlNm"
column="usr_inl_nm"/>

<property name="lstNm"
column="usr_lst_nm"/>

<property name="mdlNm"
column="usr_mdl_nm"/>

<property name="empId"
column="usr_emp_id"/>

<property name="ornId"
column="usr_orn_id"/>

<property name="effDt"
column="usr_eff_dt"/>

<property name="expDt"
column="usr_exp_dt"/>


<set name="passwords" lazy="true" inverse="true" >
<key column="upw_usr_id"/>
<one-to-many class="Password"/>
</set>

</class>

The following is the User.java.

public class User implement Serializable {

private String id;
private String fstNm;
private String inlNm;
private String lstNm;
private String mdlNm;
private String empId;
private Integer ornId;
private Date effDt;
private Date expDt;
private Integer temId;
private Integer isOrnId;

private Set passwords = new HashSet();

/**
* Default constructor.
*/
public User() {
}


/**
* @return Returns the effDt.
*/
public Date getEffDt() {
return effDt;
}
/**
* @param effDt The effDt to set.
*/
public void setEffDt(Date effDt) {
this.effDt = effDt;
}

.......
........
........

/**
* @return Returns the passwords.
*/
public Set getPasswords() {
return passwords;
}
/**
* @param passwords The passwords to set.
*/
public void setPasswords(Set passwords) {
this.passwords = passwords;
}

public void addPassword(Password password)
{
passwords.add(password);
}
}



However, I always get passwords size = 0 when I load a user object. Of course, I can always use query. But I prefer to use hibernate built in association to manage the relationship.



Thanks!

Have you checked that you're actually saving passwords before loading them? The first bug that I see in your code is this:You cannot access a mapped member using its fied: only the accessor and mutator methods are allowed do that. Change that code toSo it may be that in your test code, you were adding the passwords to the wrong set, the passwords were never getting saved, so when you go to reload the User object, the password set correctly has 0 items.

Does that explain what you're seeing?

Thanks for reply!

Actually the problem occurs even before that. Here is my test code.

public class Test {


public static void main(String[] args) {

SessionFactory sessionFactory;

System.out.println("Ready to build session factory");

try {
// Create the SessionFactory from hibernate.cfg.xml
sessionFactory = new Configuration().configure().buildSessionFactory();
} catch (Throwable ex) {
// Make sure you log the exception, as it might be swallowed
System.err.println("Initial SessionFactory creation failed." + ex);
throw new ExceptionInInitializerError(ex);
}

System.out.println("Ready to build session");
Session session = sessionFactory.openSession();

Transaction tran = session.beginTransaction();

User aUser = (User) session.load(User.class, "QCPI505");

System.out.println("User name is " + aUser.getLstNm());

if (aUser.getPasswords() != null)
{
System.out.println("aUser.getPasswords = " + aUser.getPasswords().size());

Iterator itr = aUser.getPasswords().iterator();

while (itr.hasNext())
{
Password password = (Password)itr.next();
System.out.println("passwords---" + password.getPswTx());

}
}
else
{
System.out.println("User's password size frrom realtionship name is 0");
}

tran.commit();
session.close();
sessionFactory.close();
}
}


No matter what I do, the size of passwords is always 0. And I know there are three password records existing in the table. I will try to set up a local db with the tables and set up foreign key constraint to see if it helps.

Inspect the SQL, too. See if running the SQL that hibernate generates results in the correct passwords being returned from the DB.

The relationship in the DB are definitely not required. They're not required for plain old selects, and that's all hibernate uses. The only thing foreign keys are used for is data integrity, to prevent dangling references, and only the DB gets involved in that.

Another question, I guess the column used as key column must be unique in the "one" side table, am I right? If so, suppose User has a composite key, how could we map it to Role? Many thanks!

Yes, the key must be unique on the one side for one-to-many. Otherwise it'd be many-to-many without a join table.

For composite keys:

I have tried setting up the tables on my local db2 instance and enabled the primary key and foreign key constraints. Nothing works. You are right. The problem is somewhere else. Can key be string type? Could it be the problem?

Yes, keys can be strings, but note that they're case sensitive.

Can you post the SQL that hibernate generates, and also the results you get when you a) manually run the generated SQL and b) run your own SQL that does what you want hibernate to do.

The following is the SQL hibernate generates for the one-to-many set.

Hibernate: select passwords0_.upw_usr_id as upw1_1_, passwords0_.upw_eff_dt as upw2_1_, passwords0_.upw_usr_id as upw1_1_0_, passwords0_.upw_eff_dt as upw2_1_0_, passwords0_.upw_exp_dt as upw3_1_0_, passwords0_.upw_pws_cd as upw4_1_0_, passwords0_.upw_hsc_trn_cd as upw5_1_0_, passwords0_.upw_hsc_src_cd as upw6_1_0_, passwords0_.upw_hsc_usr_id as upw7_1_0_, passwords0_.upw_hsc_ts as upw8_1_0_, passwords0_.upw_hsu_trn_cd as upw9_1_0_, passwords0_.upw_hsu_src_cd as upw10_1_0_, passwords0_.upw_hsu_ts as upw11_1_0_, passwords0_.upw_hsu_usr_id as upw12_1_0_, passwords0_.upw_psw_tx as upw13_1_0_, passwords0_.upw_non_exp_in as upw14_1_0_ from qcpdt3a.tupw_psw passwords0_ where passwords0_.upw_usr_id=?


When I manually run and pass the correct userid, it brings up the results I want. See following.

<Table Align = Center VAlign = Center Border = 1 CellSpacing = 1 CellPadding = 1 Width = 90%><Tr><Td Align = Center><B>UPW1_1_</B></Td><Td Align = Center><B>UPW2_1_</B></Td><Td Align = Center><B>UPW1_1_0_</B></Td><Td Align = Center><B>UPW2_1_0_</B></Td><Td Align = Center><B>UPW3_1_0_</B></Td><Td Align = Center><B>UPW4_1_0_</B></Td><Td Align = Center><B>UPW5_1_0_</B></Td><Td Align = Center><B>UPW6_1_0_</B></Td><Td Align = Center><B>UPW7_1_0_</B></Td><Td Align = Center><B>UPW8_1_0_</B></Td><Td Align = Center><B>UPW9_1_0_</B></Td><Td Align = Center><B>UPW10_1_0_</B></Td><Td Align = Center><B>UPW11_1_0_</B></Td><Td Align = Center><B>UPW12_1_0_</B></Td><Td Align = Center><B>UPW13_1_0_</B></Td><Td Align = Center><B>UPW14_1_0_</B></Td></Center></Tr>

<Tr><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-02-28</Td><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-02-28</Td><Td Align = Right>2006-03-27</Td><Td Align = Left>T</Td><Td Align = Right>0</Td><Td Align = Right>0</Td><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-04-06 14:57:36.872</Td><Td Align = Right>0</Td><Td Align = Right>0</Td><Td Align = Right>2006-04-06 14:57:36.872</Td><Td Align = Left>QCPI600 </Td><Td Align = Left>%E3%A2%B5%5Dv%9C%DA%3B%F0%D9T%7C%D5kbxi%1Ch%CA</Td><Td Align = Right>0</Td></Tr>

<Tr><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-03-28</Td><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-03-28</Td><Td Align = Right>2006-04-27</Td><Td Align = Left>T</Td><Td Align = Right>0</Td><Td Align = Right>0</Td><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-04-06 14:57:43.141</Td><Td Align = Right>0</Td><Td Align = Right>0</Td><Td Align = Right>2006-04-06 14:57:43.141</Td><Td Align = Left>QCPI600 </Td><Td Align = Left>%E3%A2%B5%5Dv%9C%DA%3B%F0%D9T%7C%D5kbxi%1Ch%CB</Td><Td Align = Right>0</Td></Tr>

<Tr><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-04-28</Td><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-04-28</Td><Td Align = Right>9999-12-31</Td><Td Align = Left>T</Td><Td Align = Right>0</Td><Td Align = Right>0</Td><Td Align = Left>QCPI600 </Td><Td Align = Right>2006-04-06 14:57:48.138001</Td><Td Align = Right>0</Td><Td Align = Right>0</Td><Td Align = Right>2006-04-06 14:57:48.138001</Td><Td Align = Left>QCPI600 </Td><Td Align = Left>%E3%A2%B5%5Dv%9C%DA%3B%F0%D9T%7C%D5kbxi%1Ch%CC</Td><Td Align = Right>0</Td></Tr>

</Table>


However, I found a way to get around this problem. I have created a instance variable String type usrid in User.java and getter and setter methods. And added the following mapping to the User.hbm.xml.

<property name="usrid" column="usr_id" insert="false" update="false"/>

And I have changed the one-to-many set to the following

<set name="passwords" lazy="false" >
<key column="upw_usr_id" property-ref="usrid"/>
<one-to-many class="Password"/>
</set>


And then everything works! Is this the supposed way to work?

Heh, I think that you forgot to use the "Preview" button.

Ok, well I guess that if it works it works. Does it work if you use your original mapping with a property-ref="usrId" in the key element? Probably not, but if it does, you can save yourself that extra bit of memory required to hold upw_usr_id twice.