Basically we can map a many-to-one with only one field but why can't we map a many to one with two fields. In the first mapping we have a table that we need to map accountnumber and timestampid as the key and then another that we need to map urn and timestampid to.

Are we using the right method of mapping. To me this should be doable if we can do it with one field why not two?


Hibernate version: 3

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://webster/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="bus.PatientDoctorListRegSDC" table="PatientDoctorListRegSDC">
<id name="recID" type="int" unsaved-value="null" >
<column name="RECID" sql-type="int" not-null="true"/>
<generator class="native" />
</id>
<property name="URN">
<column name="URN"/>
</property>
<property name="name">
<column name="NAME"/>
</property>
<property name="status">
<column name="STATUS"/>
</property>
<property name="location">
<column name="LOCATION"/>
</property>
<many-to-one name="attendDoctor" column="ATTENDDOCTOR" not-null="false"/>
<property name="admitDoctor">
<column name="ADMITDOCTOR"/>
</property>
<property name="familyDoctor">
<column name="FAMILYDOCTOR"/>
</property>
<property name="URN2">
<column name="URN2"/>
</property>
<property name="primCareDoctor">
<column name="PRIMCAREDOCTOR"/>
</property>
<property name="consultingDoctor">
<column name="CONSULTINGDOCTOR"/>
</property>
<property name="otherDoctor">
<column name="OTHERDOCTOR"/>
</property>
<set name="nurpc" inverse="true" cascade="all" order-by="timeStampID desc, MIInitDate" lazy="false">
<key>
<column name="ACCTNUMBER" not-null="true"/>
<column name="TIMESTAMPID" not-null="true"/>
</key>
<one-to-many class="bus.NurPC"/>
</set>
</class>
</hibernate-mapping>




<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://webster/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="bus.NurPC" table="NurPC">
<composite-id>
<key-property name="acctNumber" column="ACCTNUMBER"/>
<key-property name="timeStampID" column="TIMESTAMPID"/>
</composite-id>
<property name="MIInterventionDesc">
<column name="MIINTERVENTIONDESC"/>
</property>
<property name="MIStatus">
<column name="MISTATUS"/>
</property>
<property name="MIInitDate">
<column name="MIINITDATE"/>
</property>
<property name="MIInitUser">
<column name="MIINITUSER"/>
</property>
<property name="MITargetDate">
<column name="MITARGETDATE"/>
</property>
<property name="MICMPDate">
<column name="MICMPDATE"/>
</property>
<property name="MICMPUser">
<column name="MICMPUSER"/>
</property>
<property name="startDateAndTime">
<column name="STARTDATEANDTIME"/>
</property>
<property name="MISingleDirection">
<column name="MISINGLEDIRECTION"/>
</property>
<property name="MIProtocol">
<column name="MIPROTOCOL"/>
</property>
<many-to-one name="PatientDoctorListRegSDC" not-null="true" insert="false" update="false" >
<column name="RECID" />
</many-to-one>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

None because we are not that far.

Full stack trace of any exception that occurs:

Foreign key (FK4820E9E17A9F91F:NurPC [ACCTNUMBER,TIMESTAMPID])) must have same number of columns as the referenced primary key (PatientDoctorListRegSDC [RECID])

Name and version of the database you are using:

SQL Server 2000

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

You can, you require another element to accomplish this. The doc says:

column (optional): The name of the foreign key column. This may also be specified by nested <column> element(s)

So instead of:

<many-to-one name="attendDoctor" column="ATTENDDOCTOR" not-null="false"/>

You could have something like:

<many-to-one name="attendDoctor" not-null="false">
<column name="c1"/>
<column name="c2"/>
</many-to-one>

What about for a set. A set is basically a join and I can join on anything I want but Hibernate is not letting me join on the two fields in my set telling me is must match the number of columns in the primay key.

I think that's your database telling you you cannot have a foreign key map to only one of the columns in your two-column primary key. The FK has to point back to a unique row.

I think you have the relationship backward. The parent table has a single column primary key. Shouldn't the key be "recID"?

RecID is generated in each table when the data is loaded. I am loading the tables from another system. The joining informaiton is actually accountnumber and timestampid for most table and then some tables it is urn and timestampid. This is where I am having an issues and need to have two sets with different key columns. Every time I do this I get the number of primary key differance messages. Basically in sql i would just join the tables on these two columns and it would be over. So I want the a record in table a joined to table b on column 1 & 2, and a joined to table c on columns 5 & 6.

Does that make sense?

I guess I don't understand. Your child table at least appears to have just the single FK back to the parent table, referencing that generated id. How would you join them on the other columns - those aren't present in the parent.

I am sorry, this explination is difficult through posting. Ok The tables are loaded from another system. RecID is an autogenerated number and has no real meaning. What has meaning is the accountnumber, urn, and timestamp. The way that I can pull the data between any of my tables is through tese three fields. However not all three of the fields. so I may have accountnumber and time stamp for joining one table and urn and timestamp for joining another table. These joins will need to be one to many with a set. So how can I map two key fields in a one to many set that are not part of the primary key. I can't join the tables on RedID because it does not really mean anything.

Thanks

Can you post the DDL for the two tables? The parent you show in the example doesn't have the mapping columns. I think the answer isn't all that complex. Let's assume you have two tables:

table A:

A_PK INT not null; (primary key)
B1 INT not null; (virtual PK)
B2 INT not null; (virtual PK)
D
... other data

table B:

B_PK INT (not null) (primary key)
B1 INT not null; (virtual FK)
B2 INT not null; (virtual FK)
C VARCHAR(64) (some othe data)

Then in A's mapping you'd include the two column and in B you can reference A. Does that make sense? The columns obviously have to be in both tables or you can't create a join.

So for your example you'd have something like this:

<set name="nurpc" inverse="true" cascade="all" lazy="false">
<key>
<column name="C1" not-null="true"/>
<column name="C2" not-null="true"/>
</key>

Dougrand,

You are right on, except that my mapping does show the columns. If you look closly above you will see that:


Parent
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://webster/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="bus.PatientDoctorListRegSDC" table="PatientDoctorListRegSDC">
<id name="recID" type="int" unsaved-value="null" >
<column name="RECID" sql-type="int" not-null="true"/>
<generator class="native" />
</id>
<property name="URN">
<column name="URN"/>
</property>
<property name="name">
<column name="NAME"/>
</property>
<property name="status">
<column name="STATUS"/>
</property>
<property name="location">
<column name="LOCATION"/>
</property>
<many-to-one name="attendDoctor" column="ATTENDDOCTOR" not-null="false"/>
<property name="admitDoctor">
<column name="ADMITDOCTOR"/>
</property>
<property name="familyDoctor">
<column name="FAMILYDOCTOR"/>
</property>
<property name="URN2">
<column name="URN2"/>
</property>
<property name="primCareDoctor">
<column name="PRIMCAREDOCTOR"/>
</property>
<property name="consultingDoctor">
<column name="CONSULTINGDOCTOR"/>
</property>
<property name="otherDoctor">
<column name="OTHERDOCTOR"/>
</property>
<set name="nurpc" inverse="true" cascade="all" order-by="timeStampID desc, MIInitDate" lazy="false">
<key>
<column name="ACCTNUMBER" not-null="true"/>
<column name="TIMESTAMPID" not-null="true"/>
</key>
<one-to-many class="bus.NurPC"/>
</set>
</class>
</hibernate-mapping>

Child
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://webster/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="bus.NurPC" table="NurPC">
<composite-id>
<key-property name="acctNumber" column="ACCTNUMBER"/>
<key-property name="timeStampID" column="TIMESTAMPID"/>
</composite-id>
<property name="MIInterventionDesc">
<column name="MIINTERVENTIONDESC"/>
</property>
<property name="MIStatus">
<column name="MISTATUS"/>
</property>
<property name="MIInitDate">
<column name="MIINITDATE"/>
</property>
<property name="MIInitUser">
<column name="MIINITUSER"/>
</property>
<property name="MITargetDate">
<column name="MITARGETDATE"/>
</property>
<property name="MICMPDate">
<column name="MICMPDATE"/>
</property>
<property name="MICMPUser">
<column name="MICMPUSER"/>
</property>
<property name="startDateAndTime">
<column name="STARTDATEANDTIME"/>
</property>
<property name="MISingleDirection">
<column name="MISINGLEDIRECTION"/>
</property>
<property name="MIProtocol">
<column name="MIPROTOCOL"/>
</property>
<many-to-one name="PatientDoctorListRegSDC" not-null="true" insert="false" update="false" >
<column name="RECID" />
</many-to-one>
</class>
</hibernate-mapping>


Above is after several iterations and I have the fields that I want to join on as a composite id. I don't have to have them as a composite id. Your example is exactly what I am looking to do and when I do it I get an error

Foreign key (FK4820E9E17A9F91F:NurPC [ACCTNUMBER,TIMESTAMPID])) must have same number of columns as the referenced primary key (PatientDoctorListRegSDC [RECID])


Your example is 100% what I want to do but I keep getting errors. I will reconfigure the file exactly the way we have discussed and post the results in a little while.

Sorry this was so confusing.[/b]

Ok here is the final version of the mapping that should work but does not.

Parent




Child



This is the error message I get:

Foreign key (FK4820E9E17A9F91F:NurPC [ACCTNUMBER,TIMESTAMPID])) must have same number of columns as the referenced primary key (PatientDoctorListRegSDC [RECID])

I would think this would work just like a join.

I believe you'll need to give the inverse mapping in the <column> elements as well since you aren't mapping the default (which would be back to the primary key of the parent table). I think it will work if you do that.

Do you know of any examples that I can use. I was thinking this would be ok for a unidirectional mapping.

I don't have anything I'm afraid. I did look at the mapping dtd, but I don't see how to define the inverse columns with the key and column elements. So I'm at a bit of a loss. I've seen indicates for doing this in the annotations I use, so I believe it's possible since both should be equivalent.

I am using Java 1.4 so I can't use annotations. Can I see the annotation example?

Thanks