Incorrect field order in generated mapping

DC · **Joined:** Fri Jul 08, 2005 8:09 am **Posts:** 28

Hi,

I have a problem with the eclipse plugin generated mappings of composite keys. The key in question contains two fields. The problem is that in the primary key mapping these fields are in a different order than in the many-to-one mapping on the other table. This results in no errors but a query did return an empty result because the parameters where bound in the wrong order.

On the database, the order of the columns as well as the order within the primary key and the foreign key associations is always the same. Only in the mapping of the primary key (of the master table) the order is reversed. The correct order is schedule_key, request_key (see excerpts below).

What can I do to avoid such things in the future? They are hard to find...

Hibernate version: 3.05, Plugin 3.0 alpha 4a

Mapping documents:

Master table with wrong field order in primary key:

Code:

    <class name="mint.hibernate.tables.Course" table="COURSE" schema="...">
        <composite-id name="id" class="mint.hibernate.tables.CourseId">
            <key-many-to-one name="request" class="mint.hibernate.tables.Request">
                <column name="REQUEST_KEY" precision="11" scale="0" />
            </key-many-to-one>
            <key-many-to-one name="schedule" class="mint.hibernate.tables.Schedule">
                <column name="SCHEDULE_KEY" precision="11" scale="0" />
            </key-many-to-one>
        </composite-id>
...

Detail table with correct order in many-to-many:

Code:

    <class name="mint.hibernate.tables.CourseTopic" table="COURSE_TOPIC" schema="...">
        <id name="courseTopicKey" type="long">
            <column name="COURSE_TOPIC_KEY" precision="11" scale="0" />
            <generator class="assigned" />
        </id>
        <many-to-one name="course" class="mint.hibernate.tables.Course">
            <column name="SCHEDULE_KEY" precision="11" scale="0" not-null="true" />
            <column name="REQUEST_KEY" precision="11" scale="0" not-null="true" />
        </many-to-one>
...

Name and version of the database you are using: Ora 9

The generated SQL (show_sql=true):

Code:

SELECT   this_.course_topic_key AS course1_0_,
         this_.schedule_key AS schedule2_6_0_,
         this_.request_key AS request3_6_0_, this_.topic_key AS topic4_6_0_,
         this_.modified_by AS modified5_6_0_,
         this_.topic_template_key AS topic6_6_0_,
         this_.start_date AS start7_6_0_, this_.end_date AS end8_6_0_,
         this_.start_unit AS start9_6_0_, this_.end_unit AS end10_6_0_,
         this_.scheduled_order AS scheduled11_6_0_, this_.flags AS flags6_0_,
         this_.shift_key AS shift13_6_0_, this_.remarks AS remarks6_0_,
         this_.modified_date AS modified15_6_0_,
         this_.join_group_key AS join16_6_0_, this_.trainees AS trainees6_0_,
         this_.ctype_version_key AS ctype18_6_0_,
         this_.trainees_vacancies AS trainees19_6_0_,
         this_.topic_start AS topic20_6_0_, this_.topic_end AS topic21_6_0_
    FROM ....course_topic this_
   WHERE (this_.schedule_key = ? AND this_.request_key = ?)
ORDER BY this_.start_date ASC, this_.start_unit ASC

max · **Posted:** Thu Jul 21, 2005 8:39 am

report it in the jira (together with the SQL DDL to create the schema for reveng)

max · **Posted:** Thu Jul 21, 2005 8:40 am

its important that you check that your foreign key constraint list the columns in the right order also...

DC · **Joined:** Fri Jul 08, 2005 8:09 am **Posts:** 28

The field order on the database is correct in column order, primary key order and foreign key order. The mapping for foreign key is correct. Only the mapping for the primary key is incorrect (in this case).

Quote:

report it in the jira (together with the SQL DDL to create the schema for reveng)

Do you mean the DDL which creates the tables and keys as I have them now, or what schema export would output? (I am not using schema export)

max · **Posted:** Thu Jul 21, 2005 8:49 am

the SQL DDL that created it.

It is important since that is what the database reports to us via the metadata - and if the foreignkey is defined in reverse then you will get it in reverse...

DC · **Joined:** Fri Jul 08, 2005 8:09 am **Posts:** 28

Posted here

max · **Posted:** Thu Jul 21, 2005 9:30 am

btw. i just chekked - i cannot make this happen on hsqldb because if i order the columns in reverse on the constraint I get an sql syntax error.

so i will need the SQL ddl and db version to hunt it down (if it is a bug ;)

DC · **Joined:** Fri Jul 08, 2005 8:09 am **Posts:** 28

To reiterate this, on the database the relevant fields are in the same order, everywhere in the keys and the table column lists. Nevertheless I wonder why you get a syntax error, because the syntax should be ok as it does not distinguish the order of two column names (that is a job for semantics).