Hi everyone,

I am recently working with Hibernate and have got a problem.

First I have got a table in postgresql created with a primary key as below.
CREATE TABLE SB_USER_ROLES(
userid INTEGER NOT NULL,
roleid INTEGER NOT NULL,
FOREIGN KEY (userid) REFERENCES SB_USERS(userid) ON DELETE CASCADE,
FOREIGN KEY (roleid) REFERENCES SB_ROLES(roleid) ON DELETE CASCADE
);

userid and roleid in table SB_USER_ROLES are not unique.

I created the mapping file as below.
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping auto-import="true">
<class name="SBRole" table="SB_USER_ROLES">
<id name="id" type="long" column="oid">
<generator class="sequence">
<param name="sequence">oid</param>
</generator>
</id>

<property name="userid" type="integer">
<column name="USERID" not-null="true"/>
</property>

<property name="roleid" type="integer">
<column name="ROLEID" not-null="true"/>
</property>

</class>

And the SBRole class has get/setUserid/Roleid() methods.

There is no problem for me to query the database using the statement below:
Query q = session.createQuery("from SBRole");

But I could not insert rows into the table and the useful error message seems to be:
Caused by: org.postgresql.util.PSQLException: ERROR: relation "oid" does not exist
SBRole sbr1 = new SBRole();
sbr1.setUserid(10);
sbr1.setRoldid(10);
session.save(sbr1);
transaction.commit();

Could anybody let me know how to solve the problem? In fact I am afraid the <id> part of my mapping file is wrong. Thanks.

X. Yang

Hi
You dont have a column called OID so obviously you mapping file is wrong and thatswhy it is throwing that error. Just try genearting the class file and xml file using MiddleGen Tool. It should give you a clear picture

Thanks

Thank you for your reply. I tried MiddleGen in the past two days and made it work for normal table with a primary key. But still I have problem as described below.

Table 1, userid is the primary key
Table 2, roleid is the primary key
Table 3, userid and roleid are both foreign keys and that all in this table

I tried to use MiddleGen to generate mapping files for the above three tables and found composite-id should be used. Then when I tried to generate the java code, it was complained that composite-id is not correct. In fact it shouldn't be empty. I wonder how to set up the composite-id correctly. Also I want to know in this case, is it necessary to include Table 1 and 2 for MiddleGen. Thanks.

X. Yang

MiddleGen will only be able to infer database relations correctly if they are set up correctly in the first place - it can't work magic. It sounds like you have a table that has no primary key - is this correct?

You say:


... and then you go on to present DDL that defines no primary key, thus when MiddleGen reverse engineers your schema, it can't define any columns in the primary key, because there aren't any there!

So you are saying that it is possible to have multiple rows with exactly the same pair of (userid,roleid)? Or should this table have a primary key defined as a primary key on both the userid and roleid columns?

Make both the UserID and Role ID as primary key in the Third table , Then generate the mappings and class file It should work fine.