Reverse Engineering MySQL tables with composite foreign keys

rachid_c · **Posted:** Mon Jun 04, 2007 11:41 am

Hibernate version: 3.2.2

Hibernate Tools version: 3.2.0.beta9a

Name and version of the database: MySQL version 5.1.17

The problem I am having is that the hibernate tools do not seem to be recognizing thecomposite foreign keys that I have in my tables and seem to be treating them as just regular properties ... to illustrate more clearly what I mean below are a couple of my tables and the resulting hibernate mapping files:

The tables:

Code:

CREATE TABLE USER_ROLE (
     INTERNAL_NAME VARCHAR(255),
     DISPLAY_NAME_F VARCHAR(255),
     DESCRIPTION_E VARCHAR(255),
     DESCRIPTION_F VARCHAR(255),
     USER_ROLE_ID INT NOT NULL,
     USER_ROLE_RS_CODE INT NOT NULL,
     USER_ROLE_RS_DATE DATETIME NOT NULL,
     USER_ROLE_RS_USER VARCHAR(35) NOT NULL,
     DISPLAY_NAME_E VARCHAR(255) NOT NULL,
     CONSTRAINT USER_ROLE_PID PRIMARY KEY (USER_ROLE_ID,USER_ROLE_RS_CODE))ENGINE=INNODB ;


CREATE TABLE USER (
     USER_ID INT NOT NULL,
     USER_RS_CODE INT NOT NULL,
     USER_RS_DATE DATETIME NOT NULL,
     USER_RS_USER VARCHAR(35) NOT NULL,
     USERNAME VARCHAR(35) NOT NULL,
     PASSWORD VARCHAR(35) NOT NULL,
     USER_ROLE_ID INT,
     USER_ROLE_RS_CODE INT,
     USER_IS_ACTIVE BOOLEAN NOT NULL,
     CONSTRAINT USER_PID PRIMARY KEY (USER_ID,USER_RS_CODE),
     CONSTRAINT PRIMARY_ROLE_FK FOREIGN KEY(USER_ROLE_ID,USER_ROLE_RS_CODE) REFERENCES USER_ROLE(USER_ROLE_ID,USER_ROLE_RS_CODE))ENGINE=INNODB ;

Here are the generated mapping files:

UserRole.hbm.xml:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 4, 2007 11:12:30 AM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="reverseRFI.UserRole" table="user_role">
        <comment></comment>
        <composite-id name="id" class="reverseRFI.UserRoleId">
            <key-property name="userRoleId" type="int">
                <column name="USER_ROLE_ID" />
            </key-property>
            <key-property name="userRoleRsCode" type="int">
                <column name="USER_ROLE_RS_CODE" />
            </key-property>
        </composite-id>
        <property name="internalName" type="string">
            <column name="INTERNAL_NAME">
                <comment></comment>
            </column>
        </property>
        <property name="displayNameF" type="string">
            <column name="DISPLAY_NAME_F">
                <comment></comment>
            </column>
        </property>
        <property name="descriptionE" type="string">
            <column name="DESCRIPTION_E">
                <comment></comment>
            </column>
        </property>
        <property name="descriptionF" type="string">
            <column name="DESCRIPTION_F">
                <comment></comment>
            </column>
        </property>
        <property name="userRoleRsDate" type="timestamp">
            <column name="USER_ROLE_RS_DATE" length="19" not-null="true">
                <comment></comment>
            </column>
        </property>
        <property name="userRoleRsUser" type="string">
            <column name="USER_ROLE_RS_USER" length="35" not-null="true">
                <comment></comment>
            </column>
        </property>
        <property name="displayNameE" type="string">
            <column name="DISPLAY_NAME_E" not-null="true">
                <comment></comment>
            </column>
        </property>
    </class>
</hibernate-mapping>

User.hbm.xml:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 4, 2007 11:12:30 AM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="reverseRFI.User" table="user">
        <comment></comment>
        <composite-id name="id" class="reverseRFI.UserId">
            <key-property name="userId" type="int">
                <column name="USER_ID" />
            </key-property>
            <key-property name="userRsCode" type="int">
                <column name="USER_RS_CODE" />
            </key-property>
        </composite-id>
        <property name="userRsDate" type="timestamp">
            <column name="USER_RS_DATE" length="19" not-null="true">
                <comment></comment>
            </column>
        </property>
        <property name="userRsUser" type="string">
            <column name="USER_RS_USER" length="35" not-null="true">
                <comment></comment>
            </column>
        </property>
        <property name="username" type="string">
            <column name="USERNAME" length="35" not-null="true">
                <comment></comment>
            </column>
        </property>
        <property name="password" type="string">
            <column name="PASSWORD" length="35" not-null="true">
                <comment></comment>
            </column>
        </property>
        <property name="userRoleId" type="java.lang.Integer">
            <column name="USER_ROLE_ID">
                <comment></comment>
            </column>
        </property>
        <property name="userRoleRsCode" type="java.lang.Integer">
            <column name="USER_ROLE_RS_CODE">
                <comment></comment>
            </column>
        </property>
        <property name="userIsActive" type="byte">
            <column name="USER_IS_ACTIVE" not-null="true">
                <comment></comment>
            </column>
        </property>
    </class>
</hibernate-mapping>

Thanks for any help.

max · **Posted:** Mon Jun 04, 2007 12:16 pm

looks like a bug.

the ids are correctly included in the composite-id but for some reason also included int he property list (they should actually be non-updatable many-to-ones)

rachid_c · **Posted:** Mon Jun 04, 2007 1:50 pm

Thanks max ...

I have narrowed down the problem a bit ... I removed all tables from my database ... I removed the composite key from my User and User_Role tables and created only these two tables in MySQL.

But the problem still exists ...

So the only tables I have in my database are now as follows:

Code:

DROP TABLE IF EXISTS RFI.USER_ROLE ;
CREATE TABLE RFI.USER_ROLE (
     INTERNAL_NAME VARCHAR(255),
     DISPLAY_NAME_F VARCHAR(255),
     DESCRIPTION_E VARCHAR(255),
     DESCRIPTION_F VARCHAR(255),
     USER_ROLE_ID INT NOT NULL,
     USER_ROLE_RS_DATE DATETIME NOT NULL,
     USER_ROLE_RS_USER VARCHAR(35) NOT NULL,
     DISPLAY_NAME_E VARCHAR(255) NOT NULL,
     CONSTRAINT USER_ROLE_PID PRIMARY KEY (USER_ROLE_ID))ENGINE=INNODB ;

DROP TABLE IF EXISTS RFI.USER ;
CREATE TABLE RFI.USER (
     USER_ID INT NOT NULL,
     USER_RS_DATE DATETIME NOT NULL,
     USER_RS_USER VARCHAR(35) NOT NULL,
     USERNAME VARCHAR(35) NOT NULL,
     PASSWORD VARCHAR(35) NOT NULL,
     USER_ROLE_ID INT,
     USER_IS_ACTIVE BOOLEAN NOT NULL,
     CONSTRAINT USER_PID PRIMARY KEY (USER_ID),
     CONSTRAINT PRIMARY_ROLE_FK FOREIGN KEY(USER_ROLE_ID) REFERENCES RFI.USER_ROLE(USER_ROLE_ID))ENGINE=INNODB ;

The generated hibernate files are very similar to the ones in my original post so I will not repeat them again (they are just missing the columns that I removed from the tables).

I have used both INNODB and MyISAM MySQL Engines (MyISAM seems to have problems with enforcing foreign key constaraints) and both have this problem. Could it be just MySQL? (I have done something similar using Microsoft SQL Server and it worked fine.)

Thanks again for your help.

max · **Posted:** Mon Jun 04, 2007 2:00 pm

hmm could be.

could you test on ms sql server and let me know if it works on that one ?

rachid_c · **Posted:** Mon Jun 04, 2007 2:36 pm

Ok I tried on MS SQL Server both with and without Composite Keys and it works fine in both cases. So it seems like a MySQL problem ... or could it be a problem with the MySQL JDBC driver that I am using? Would you know if there is a "recomended" MySQL JDBC driver to use with hibernate?

Thanks for your help.

max · **Posted:** Mon Jun 04, 2007 2:50 pm

the latest.

max · **Posted:** Mon Jun 04, 2007 2:51 pm

the latest.

btw. i can be casing/bug in our code caused by mysql "jdbc driver uncertanty" ,)

would be great if you could create a jira issue that reproduces this + shows what works under mssql (both schema and the output you consider ok when using sql server)

leopinho · **Joined:** Sun Sep 30, 2007 2:58 pm **Posts:** 26

I have the same problem using mysql-connector-java-5.0.7 and MySql 5... any solution?

max · **Posted:** Wed Oct 03, 2007 1:04 pm

double check the driver classname used; anyone else reporting "class not found" is because of missing jar or typo in driver class name.

euchredog · **Joined:** Fri Apr 11, 2008 1:38 pm **Posts:** 9

I ran into this problem as well. I checked an re-checked my drivers and everything seemed like it should work, but it didn't.

My problem ended up being related to a mysql bug. Although mysql is not supposed to be case-sensitive anymore, there appears to be some case-sensitivity left in some of the APIs.

Check your hibernate.cfg.xml file. In your hibernate.connection.url, is your database name uppercase? If so, try lowercase. See if that helps. It did for me.