Problems defining foreign keys on MyISAM engine by reveng.

Arnim · **Joined:** Fri Apr 25, 2008 4:22 am **Posts:** 6

Hello,
I'm trying to create some *.hbm and *.java files via reverse engineering.
My problem is, that the database I'm working with uses only MyISAM storage
engine.

It's a MySLQ database
5.0.45-community-nt MySQL Community Edition (GPL)
and I use the
mysql-connector-java-5.1.6-bin.jar.
also
hibernate3.jar
with the current
hibernate-tools.jar

I followed the instructions in the
Hibernate Tools
Reference Guide
Version: 3.2.0.beta10.

Because of the complexity of my database I try to learn with the following
simple example:

I have two tables:

Code:

CREATE TABLE  `test`.`child` (
  `CHILD_ID` int(11) NOT NULL auto_increment,
  `MOTHER` int(11) default NULL,
  PRIMARY KEY  (`CHILD_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

and

Code:

CREATE TABLE  `test`.`mother` (
  `MOTHER_ID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`MOTHER_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Because MyISAM doesn't supports foreign keys I include a revengfile in my
ant reveng build file.

Code:

 <hibernatetool destdir="${destdir}">
     <jdbcconfiguration configurationfile="${hibernate.cfg.xml}" revengfile="${hibernate.reveng.xml}" />
     <hbm2java jdk5="true"/>
     <hbm2hbmxml />
     <hbm2cfgxml />
 </hibernatetool>

The hibernate.reveng.xml file contains the following simple lines:

Code:

 <table name="child">
   <foreign-key constraint-name="MOTHER" foreign-table="mother">
     <column-ref local-column="MOTHER" foreign-column="MOTHER_ID"/>
     <many-to-one property="child"/>
   </foreign-key>
 </table>

Because I'm a newbie to Hibernate I have the following expectations about the
Child.hbm.xml output:

I expect something like this:

Code:

 <hibernate-mapping>
     <class name="Child" table="child" catalog="test">
         <id name="childId" type="java.lang.Integer">
             <column name="CHILD_ID" />
             <generator class="identity" />
         </id>

         <many-to-one name="mother" class="Mother" fetch="select">
             <column name="MOTHER" not-null="true" />
         </many-to-one>
     </class>
 </hibernate-mapping>

But what I get is this:

Code:

 <hibernate-mapping>
     <class name="Child" table="child" catalog="test">
         <id name="childId" type="java.lang.Integer">
             <column name="CHILD_ID" />
             <generator class="identity" />
         </id>

         <property name="mother" type="java.lang.Integer">
             <column name="MOTHER" />
         </property>
     </class>
 </hibernate-mapping>

Maybe my expectations are completely out of control or I made a simple or even
bigger error in reasoning.

I hope my explanations are clear and in advance thanks to everybody who reads my
post and gives me a hint about my mistakes or wrong expectations.

Arnim

Sukirtha · **Posted:** Sun Apr 27, 2008 3:28 am

Read http://www.hibernate.org/hib_docs/tools/reference/en/html/reverseengineering.html

section 5.2.4.3.

Your expectations of using foreign key is right. It can be used for DBs that do not support foreign keys.

Arnim · **Joined:** Fri Apr 25, 2008 4:22 am **Posts:** 6

@Sukirtha

Thanks for reading my post and your effort to help me.
But I think it isn't very useful to post a link to the tutorial i have marked as
the background of my work.
It is seriously possible that I missunderstood the tutorial or made a simple but
big newbie mistake, but thats the reason why I posted here to get help from some
experienced hibernate users.

Greets and best wishes

Arnim

Sukirtha · **Posted:** Mon Apr 28, 2008 1:45 am

Hello Arnim,
Sorry. Did not see that you were refering to the tools guide. I tried using your reverse engineering file and used the DDLs you gave to create tables in MySQL. It works for me.

Following are the output files I got

Code:

    <class name="Child" table="child">
        <id name="childId" type="int">
            <column name="CHILD_ID" />
            <generator class="assigned" />
        </id>
        <many-to-one name="child" class="Mother" fetch="select">
            <column name="MOTHER" />
        </many-to-one>
    </class>

Code:

    <class name="Mother" table="mother">
        <id name="motherId" type="int">
            <column name="MOTHER_ID" />
            <generator class="assigned" />
        </id>
        <set name="childs" inverse="true">
            <key>
                <column name="MOTHER" />
            </key>
            <one-to-many class="Child" />
        </set>
    </class>

Maybe its something to do with dialect.

Arnim · **Joined:** Fri Apr 25, 2008 4:22 am **Posts:** 6

Hello Sukirtha,

because I work with
MySQL version 5.0.45
I thought
org.hibernate.dialect.MySQL5Dialect
was the right choice.

I also tried
org.hibernate.dialect.MySQLMyISAMDialect
and
org.hibernate.dialect.MySQLDialect.
All three dialects gave me the same disappointing results.

What kind of dialect do you choose and can you explain your kind of choice?

Thanks and greets

Arnim

Sukirtha · **Posted:** Mon Apr 28, 2008 5:29 am

Strange.
I use MySQL 5.0

I used the same DDL and the revenge file that you gave.

All the three dialects work for me. I get the same result for all the three dialects.

Arnim · **Joined:** Fri Apr 25, 2008 4:22 am **Posts:** 6

Hello Sukirtha,

did you define any other properties I'm not aware of?

To avoid this, I post the complete files I use for reverse engineering.

This is my complete build.reveng.xml file:

Code:

<project name="HibernateMotherChildTest - Reverse Engineering" basedir="../../">
   <description>
      HibernateMotherChildTest build file
   </description>

   <!-- <hibernatetool> define ant task -->
   <!-- define source path -->
   <path id="toolslib">
      <path location="bin"/>
      <path location="libext/hibernate/asm.jar" />
      <path location="libext/hibernate/cglib-2.1.3.jar" />
      <path location="libext/hibernate/freemarker.jar" />
      <path location="libext/hibernate/hibernate3.jar" />
      <path location="libext/hibernate/hibernate-tools.jar" />
      <path location="libext/appache/commons-logging-1.0.4.jar" />
      <path location="libext/appache/commons-collections-2.1.1.jar" />
      <path location="libext/dom4j/dom4j-1.6.1.jar" />
      <path location="libext/mysql/mysql-connector-java-5.1.6-bin.jar" />
      <path location="libext/jtidy/tidy.jar" />
   </path>
   <!-- define hibernatetool task -->
   <taskdef name="hibernatetool"
      classname="org.hibernate.tool.ant.HibernateToolTask"
      classpathref="toolslib">
   </taskdef>
   
   <!-- create some POJOs via hbm2java and hibernatetool task -->
   <hibernatetool destdir="src">
      <jdbcconfiguration
         configurationfile="src/reveng/hibernate.cfg.xml"
         revengfile="src/reveng/hibernate.reveng.xml"
         />
      <hbm2java jdk5="true"/>
      <hbm2hbmxml />
      <hbm2cfgxml />
   </hibernatetool>
   
</project>

This is my complete hibernate.cfg.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
      "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>
      
      <!-- Database connection settings -->
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.connection.username">test</property>
        <property name="hibernate.connection.password">test</property>
        <property name="hibernate.default_schema">test</property>
      
      <!-- SQL dialect -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
      
    </session-factory>

</hibernate-configuration>

And last and least my hibernate.reveng.xml file:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering
   SYSTEM "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd" >

<hibernate-reverse-engineering>
   
   <table name="child">
      <foreign-key constraint-name="MOTHER" foreign-table="mother">
         <column-ref local-column="MOTHER" foreign-column="MOTHER_ID"/>
         <many-to-one property="child"/>
      </foreign-key>
   </table>
</hibernate-reverse-engineering>

DDLs are still the same and results are even still the same disappointing ones as before.

Thanks for your efforts!

Arnim

Sukirtha · **Posted:** Tue Apr 29, 2008 3:12 am

Hi Arnim,
I finally found out the problem and the solution for it. There is one configuration setting that you need to do in your hibernate.cfg.xml file.

Code:

I am not sure but My guess is that hibernate does not consider a foreign key unless a catalog is specified. If the catalog is not specified, hibernate assumes it to be a normal property. We overcome this by specifying a default catalog in the hibernate.cfg.xml or by specifying the catolog in the revenge.xml for individual tables and foreign keys.

Hope it helps you! :)

Arnim · **Joined:** Fri Apr 25, 2008 4:22 am **Posts:** 6

Hello Sukirtha,

your hint with the hibernate.default_catalog property solves my problems.
Now it works!

Thank you very much for your efforts!

P.S.: Maybe my first reaction to your first reply was a little bit harsh.
So I would like to apologise me. I hope it wasn't to annoying. Thanks again for your help.

Sukirtha · **Posted:** Tue Apr 29, 2008 4:18 am

:) No problem. Happy I could solve the issue.

Arnim · **Joined:** Fri Apr 25, 2008 4:22 am **Posts:** 6

Hello Hibernates,

after the fast and very competent help I got here I must expand my little
academic example, because I'm reaching against my limits.

I add another schema:

Code:

CREATE DATABASE `test_expanded` /*!40100 DEFAULT CHARACTER SET latin1 */;

The new schema contains the following table:

Code:

CREATE TABLE  `test_expanded`.`father` (
  `FATHER_ID` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  USING BTREE (`FATHER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

The before mentioned Hibernate Tool tutorial says with respect to schema reverse
engineering:

5.2.1. Schema Selection (<schema-selection>)
[url]
http://www.hibernate.org/hib_docs/tools ... ering.html
[/url]

Quote:

By default the reverse engineering will read all schemas and then use
<table-filter> to decide which tables get reverse engineered and which do not;
this makes it easy to get started but can be inefficient on databases with many
schemas.

Because of this I expected that the reverse engineering will include the new
schema and the new table and will create the Father.java and the Father.hbm.xml
files. But unfortunately it isn't so.

Therefore I expand my hibernate.reveng.xml file so it looks like this:

Code:

<hibernate-reverse-engineering>
        <schema-selection match-catalog="test" match-schema="test" />
        <schema-selection match-catalog="test_expanded" match-schema="test_expanded" match-table="father" />

        <table name="father"
                catalog="test_expanded"
                schema="test_expanded">
                <primary-key>
                        <key-column name="FATHER_ID" />
                </primary-key>
        </table>
        <table name="child">
                <foreign-key constraint-name="MOTHER" foreign-table="mother">
                        <column-ref local-column="MOTHER" foreign-column="MOTHER_ID"/>
                        <many-to-one property="mother"/>
                </foreign-key>
                <foreign-key constraint-name="FATHER" foreign-catalog="test_expanded" foreign-schema="test_expanded" foreign-table="father">
                        <column-ref local-column="FATHER" foreign-column="FATHER_ID" />
                        <many-to-one property="father" />
                </foreign-key>
        </table>
</hibernate-reverse-engineering>

As you can see I tried the
<schema-selection> from section 5.2.1. Schema Selection (<schema-selection>)
and the specific table configuration
<table> from section 5.2.4. Specific table configuration (<table>).

My expectations are:
Hibernate creates the Father.java and the Father.hbm.xml files.

The reality is:
No Father.java and no Father.hbm.xml files.
The Child.hbm.xml file is expanded by some lines.

Code:

<hibernate-mapping>
    <class name="Child" table="child">
        <id name="childId" type="java.lang.Integer">
            <column name="CHILD_ID" />

            <generator class="identity" />
        </id>

        <many-to-one name="mother" class="Mother" fetch="select">
            <column name="MOTHER" />
        </many-to-one>
        
        <property name="father" type="java.lang.Integer">
            <column name="FATHER" />
        </property>
    </class>
</hibernate-mapping>

But instead of

Code:

        <property name="father" type="java.lang.Integer">
            <column name="FATHER" />
        </property>

I expected something like this:

Code:

        <many-to-one name="father" class="Father" fetch="select">
            <column name="FATHER" />
        </many-to-one>

My first idea is that the hibernate.default_schema and the
hibernate.default_catalog lines in my hibernate.cfg.xml file are
more then default values.

Code:

<hibernate-configuration>

    <session-factory>
                <!-- Database connection settings -->
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306</property>
        <property name="hibernate.connection.username">test</property>
        <property name="hibernate.connection.password">test</property>
        <property name="hibernate.default_schema">test</property>
        <property name="hibernate.default_catalog">test</property>
                <!-- SQL dialect -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
    </session-factory>

</hibernate-configuration>

But when I remove
<property name="hibernate.default_catalog">test</property>
I'm at the starting point of this thread and when I remove
<property name="hibernate.default_schema">test</property>
I get the following error prompt:

BUILD FAILED
D:\Arbeitsbereich\eclipse-
workspace\HibernateMotherChildTest\src\reveng\build.reveng.xml:28:
org.hibernate.exception.GenericJDBCException: Could not get list of suggested
identity strategies from database. Probably a JDBC driver problem.

Thanks to all folks who read my long post 'til here and special thanks in
advance to all of those who try to solve my problem.

Arnim

laffuste · **Joined:** Thu May 17, 2012 10:47 pm **Posts:** 1

Thanks for this post, really usefull, info about FKs and MyISAM is scarce.

Just a comment:

At least when generating with annotations, tables names in reveng.xml are case sensitive!!!