I am new to hibernate and I have looked through the documentation and the forum and can't seem to find a solution to my problem. Not that this hasn't already been answered but I don't get it.

I have two tables Peis301 and Peis480h. Peis301 has a foreign key EMSSAN that references the primary key of Peis480h table column PA. Peis480h has a composite key PA, and PAID. So the set of the Peis301 object should contain all Peis480h objects where Peis301.EMSSAN == Peis480h.PA

I only want a unidirectional one-to-many assocation from Peis301 to Peis480h. I am using a set collection because all Peis480h objects returned for a Peis301 object are unique by the composite-key.

The set collection of the Peis301 object is always empty, but I can run the following HQL statement and get the correct results.
session.createQuery("from Peis480h p480 where p480.id.pa = " + p301.getEmpSSN()).list();

I get don't get an exceptions just an empty set and I am not sure where I have gone wrong in my mapping file.

Any help would be greatly appreciated!

Hibernate version:
3.0.5

Mapping documents:

Peis301 Object
<hibernate-mapping package="com.timptech.data">

<class name="Peis301" table="peis301">
<id name="empNum" column="EM" type="integer">
<generator class="assigned"/>
</id>

<property name="empSSN" column="EMSSAN" type="integer" />
<property name="empLastName" column="EMLNAM" type="string" />
<property name="empFirstName" column="EMFNAM" type="string" />
<property name="empMiddleName" column="EMMNAM" type="string" />
<property name="location" column="EMLOC" type="string" />
<set name="p480Set">
<key column="PA" foreign-key="EMSSAN" />
<one-to-many class="Peis480h" not-found="exception" />
</set>

</class>

</hibernate-mapping>

Peis480h Object
<hibernate-mapping package="com.timptech.data">
<class name="Peis480h" table="peis480h">
<composite-id name="id" class="P480CompKey">
<key-property name="pa" column="PA" type="java.lang.Integer"/>
<key-property name="patid" column="PATID" type="com.timptech.util.TrimString"/>
</composite-id>

<property name="padate" column="PADATE" type="java.lang.Integer" />

</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Iterator iter = session.createQuery("from Peis301").list().iterator();
while(iter.hasNext()) {
Peis301 p301 = (Peis301) iter.next();
System.out.println("\nempId: " + p301.getEmpNum());
System.out.println("EmpName: " + p301.getName());
System.out.println("Location: " + ((p301.getP127()== null)?"":p301.getLocName()));
System.out.println("Marital Status: " + p301.getMaritalStat());


//iterate through set of the Peis480h objects
Iterator setIter = p301.getP480Set().iterator();

if (setIter.hasNext()) {
String tags = "Tags: " +((Peis480h) setIter.next()).getId().getPatid();
while (setIter.hasNext()) {
Peis480h p480 = (Peis480h) setIter.next();
tags += ", " + p480.getId().getPatid();
}
System.out.println(tags);
}
}

Full stack trace of any exception that occurs:

N/A

Name and version of the database you are using:

MySQL 4.1.12a

The generated SQL (show_sql=true):

Hibernate: select p480set0_.PA as PA1_, p480set0_.PATID as PATID1_, p480set0_.PA as PA0_, p480set0_.PATID as PATID0_, p480set0_.PADATE as PADATE2_0_ from peis480h p480set0_ where p480set0_.PA=?


Debug level Hibernate log excerpt:

11:56:41,698 DEBUG DefaultInitializeCollectionEventListener:42 - initializing collection [com.timptech.data.Peis301.p480Set#127]
11:56:41,708 DEBUG DefaultInitializeCollectionEventListener:47 - checking second-level cache
11:56:41,708 DEBUG DefaultInitializeCollectionEventListener:59 - collection not cached
11:56:41,708 DEBUG Loader:1426 - loading collection: [com.timptech.data.Peis301.p480Set#127]
11:56:41,708 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
11:56:41,708 DEBUG SQL:324 - select p480set0_.PA as PA1_, p480set0_.PATID as PATID1_, p480set0_.PA as PA0_, p480set0_.PATID as PATID0_, p480set0_.PADATE as PADATE2_0_ from peis480h p480set0_ where p480set0_.PA=?
Hibernate: select p480set0_.PA as PA1_, p480set0_.PATID as PATID1_, p480set0_.PA as PA0_, p480set0_.PATID as PATID0_, p480set0_.PADATE as PADATE2_0_ from peis480h p480set0_ where p480set0_.PA=?
11:56:41,708 DEBUG AbstractBatcher:378 - preparing statement
11:56:41,708 DEBUG IntegerType:59 - binding '127' to parameter: 1
11:56:41,718 DEBUG AbstractBatcher:306 - about to open ResultSet (open ResultSets: 0, globally: 0)
11:56:41,718 DEBUG Loader:718 - result set contains (possibly empty) collection: [com.timptech.data.Peis301.p480Set#127]
11:56:41,718 DEBUG CollectionLoadContext:85 - uninitialized collection: initializing
11:56:41,718 DEBUG Loader:405 - processing result set
11:56:41,718 DEBUG Loader:429 - done processing result set (0 rows)
11:56:41,718 DEBUG AbstractBatcher:313 - about to close ResultSet (open ResultSets: 1, globally: 1)
11:56:41,718 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
11:56:41,728 DEBUG AbstractBatcher:416 - closing statement
11:56:41,728 DEBUG Loader:528 - total objects hydrated: 0
11:56:41,728 DEBUG CollectionLoadContext:262 - 1 collections were found in result set for role: com.timptech.data.Peis301.p480Set
11:56:41,728 DEBUG CollectionLoadContext:206 - collection fully initialized: [com.timptech.data.Peis301.p480Set#127]
11:56:41,728 DEBUG CollectionLoadContext:272 - 1 collections initialized for role: com.timptech.data.Peis301.p480Set
11:56:41,728 DEBUG PersistenceContext:789 - initializing non-lazy collections
11:56:41,728 DEBUG Loader:1450 - done loading collection
11:56:41,728 DEBUG DefaultInitializeCollectionEventListener:61 - collection initialized

Hi,

The relation from Peis301 to Peis480h cannot work because you declare that the Primary key of Peis480h is a Composite key, so the foreign key of Peis301 should also be composite (Composite key, PA + PATID).

Etienne.

thanks for responding etienne,

I am not sure exactly what you mean. Is there a way in the Peis301 mapping file to specify a composite key to the Peis480h table. I looked in the reference guide and I can't see how to do it. Other than the following:

<set name="p480Set">
<key foreign-key="EMSSAN">
<column name="PA" />
<column name="PATID" />
</key>
<one-to-many class="Peis480h" not-found="exception" />
</set>

Is this what you mean?

Only the column PA is referenced by EMSSAN in Peis301 not PATID so I am not sure how to make the mapping file correspond.

Can you post some sample code?

I did some one-to-many relationship with composite key.

I use a component "AccountPositionId" for containing the accountPosition keys. It is a simple Pojo with the key 2 fields (Instrument and AccountSummary). Read this: http://www.hibernate.org/hib_docs/v3/reference/en/html/components.html#components-compositeid

This is some sample of my code, sorry it is a bit more complexe, because it use some Hibernate Pojos inside the key Pojo.

This mapping was generated by the Hibernate Tool (for Eclipse 3.1), the mapping is a bit complexe but it is well done. I think you should try it it to generate all your mapping and all your Pojo using a reverse engineering on your tables. Download the lastest tool (alpha 5) at : http://www.hibernate.org/255.html.





Hope it help.

Etienne.

Thanks again etienne for responding.

I took your suggestion and tried to create the mapping files with the hibernate tools. For some reason the hibernate tool it is not gathering the correct schema information so the mapping files are not getting set up with any associations. I also tried to use the MyEclipse hibernate plugin but it didn't work either.

Also thanks for posting the mapping examples, you are right they are complex! I followed what you did and I am now getting a mapping exception on a foreign key.

For now with all the frustrations I have had I think I will just use Hibernate for Inserts, Updates, and Deletes and do all the queries just using SQL.

Thanks again.

Firstly, if you feel the tools are not generating the mappings correctly - post a question with the details to the tools forum. Gives us the opportunity to improve the tools further (and get into a beta cycle).
Secondly, HQL is great. In my book, one of Hibernates best features. I would not give up on it so easily.

BTW: If you wanted to see what another tool generates for your schema. Try Middlegen.

For the tool to work, you need to declare the foreign keys and the primary keys constraints in your database (using your DDL, SQL table creation).

Maybe you should try first with some trivial example: One table with a PK, 2 table with a PK and a FK. If you have a complexe database you will save a lot of time when it will work! Many days at least. I try it on Mysql, DB2 and Sybase with the same result.

Be sure that the Hibernate tools have the rights (database rights) to read the meta-data table. Check that your Eclipse project is not under ClearCase (or any source control) preventing you to create file or folder without "check-outing" a folder...

Be sure also that you have all the required plugins-lib installed in your Eclipse 3.1 as mention in the Tool installation pages. Usualy, the automatic update of Eclipse will load most of them. Check in "Manage Configuration" if the tools is installed correctly.

Ragards,

Etienne.

Thanks again for those who replied to my question. I worked a little harder to resolve the problem and this is what I came up with.

The mapping files that I had originally generated was correct the only problem was with the foreign-key declaration. Instead of the foreign-key attribute I needed to use the property-ref attribute. I found, by scanning the logging output more closely, that I was joining on the primary key of the Peis301 instead of the foreign key emssan. No wonder it was returning an empty set. So for anyone this would benefit the mapping file for the Peis301 object using a set collection with a one-to-many relationship on a Peis480h object is as follows: