Reverse engineering multiple schemas

twizansky · **Joined:** Mon Dec 22, 2008 7:26 pm **Posts:** 6

I am using hibernate tools 3.2.4 along with ant 1.6.5. I am trying to reverse engineer a database that contains 4 schemas. According to the documentation, all the schemas should be reverse engineered, unless I explicitly specify otherwise, using <schema-selection>. However, when I try this, I get no hbm or java files generated.

Here is my configuration file:

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>
      <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property>
     </session-factory>
</hibernate-configuration>

Note that when I change the url property to point to a specific schema, like so:

Code:

 <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/client</property>

it works but only for the "client" schema. I'm sure I am doing something wrong but I can't figure out what.

For completeness, here is my ANT build file:

Code:

<project name="Hibernate" default="codegen" basedir=".">
    <description>
        Hibernate the SWM database
    </description>

   <!-- set global properties for this build -->
   <property name="repo" location="${user.home}/.m2/repository/"/>
   <property name="resources" location="src/main/resources"/>
   <property name="src" location="src/main/java/com/structuralwealth/dsl/hibernate"/>
   <property name="base" location="src/main/java/com/structuralwealth/dsl/hibernate/base"/>
    <property name="target" location="target"/>
   <property name="java-src" location="src/main/java/"/>
   
   <!-- Define the repository path.  This is the path to all 3rd party dependencies -->
   <path id="repository">
      <fileset dir="${repo}/org/hibernate/hibernate-tools/3.2.0.ga" includes="*.jar"/>
      <fileset dir="${repo}/org/hibernate/hibernate/3.2.6.ga" includes="*.jar"/>
      <fileset dir="${repo}/commons-logging/commons-logging/1.1.1" includes="*.jar"/>
      <fileset dir="${repo}/dom4j/dom4j/1.6.1" includes="*.jar"/>
      <fileset dir="${repo}/cglib/cglib/2.1_3" includes="*.jar"/>
      <fileset dir="${repo}/asm/asm/1.5.3" includes="*.jar"/>
      <fileset dir="${repo}/commons-collections/commons-collections/2.1.1" includes="*.jar"/>
      <fileset dir="${repo}/mysql/mysql-connector-java/5.1.6" includes="*.jar"/>
      <fileset dir="${repo}/freemarker/freemarker/2.3.8" includes="*.jar"/>
      <fileset dir="${repo}/org/hibernate/jtidy/r8-20060801" includes="*.jar"/>
   </path>
   
   <!-- Define the hibernatetool task -->
   <taskdef name="hibernatetool"
         classname="org.hibernate.tool.ant.HibernateToolTask"
       classpathref="repository"/>
   
   <!-- Target to generate code based on existing cfg and hbm file. -->
     <target name="codegen" depends="cfggen, mapgen"
               description="Generate Java source code
                            from the Hibernate mapping files">
     
      <hibernatetool destdir="${java-src}" templatepath="${resources}">
          <classpath>
               <path location="${resources}"/>
          </classpath>
          <configuration configurationfile="${resources}/hibernate.cfg.xml"/>
          <hbmtemplate
             templatepath="${resources}"
             template="daotemplate.ftl"
             filepattern="{package-name}/{class-name}DAO.java">
              <property key="jdk5" value="true" />
              <property key="ejb3" value="false" />
          </hbmtemplate>
          <hbm2java/>
        </hibernatetool>
    </target>

   <!-- Target to generate hbm files from existing cfg and reverse engineering files. -->
    <target name="mapgen" depends="compile, cfggen"
               description="Generate Java source code
                            from the Hibernate mapping files">
        <hibernatetool destdir="${resources}">
          <classpath>
               <path location="${target}/classes"/>
          </classpath>
          <jdbcconfiguration 
             configurationfile="${resources}/hibernate.cfg.xml" 
             revengfile="${resources}/hibernate.reveng.xml"
             reversestrategy="com.structuralwealth.dsl.hibernate.CustomRevengStrategy"/>
          <hbm2hbmxml/>
        </hibernatetool>
    </target>

   <!-- Target to generate the hibernate.cfg.xml files. -->
    <target name="cfggen" depends="compile"
               description="Generate Java source code
                            from the Hibernate mapping files">
        <hibernatetool destdir="${resources}">
          <classpath>
               <path location="."/>
               <path location="${target}/classes"/>
          </classpath>
           <property key="package" value="com.structuralwealth.dsl.hibernate"/>
           <property key="schema" value="client"/>
          <jdbcconfiguration 
             configurationfile="${resources}/hibernate.jdbc.cfg.xml" 
             revengfile="${resources}/hibernate.reveng.xml"
             reversestrategy="com.structuralwealth.dsl.hibernate.CustomRevengStrategy"/>
          <hbm2cfgxml/>
        </hibernatetool>
    </target>

   <!-- Target to compile java code necessary for the build process. -->
    <target name="compile"
        description="compile the custom reverse engineering file " >
       
       <!-- Delete the contents of the base directory, so as to avoid compiling them in the 
           next step. -->
      <delete>
         <fileset dir="${base}" includes="*.java"/>
      </delete>
       
       <!-- Compile the java code from ${src} into ${build} -->
       <javac srcdir="${src}" destdir="${target}/classes" classpathref="repository" debug="true" />
    </target>

</project>

And my reverse engineering file:

Code:

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

<hibernate-reverse-engineering>
   <type-mapping>
      <sql-type jdbc-type="BIGINT" hibernate-type="java.lang.Long"
         not-null="false">
      </sql-type>
      <sql-type jdbc-type="DECIMAL" hibernate-type="java.lang.Double"
         not-null="false">
      </sql-type>
   </type-mapping>
   <table-filter match-name=".*" match-catalog=".*" />
   <table-filter 
      package="com.structuralwealth.dsl.hibernate.base"
      match-name=".*" />   
</hibernate-reverse-engineering>

Thanks

max · **Posted:** Wed Dec 31, 2008 4:22 am

you say it only works for "client" schema but I don't understand what is the part that is *not* working ?

twizansky · **Joined:** Mon Dec 22, 2008 7:26 pm **Posts:** 6

There are four schemas in the database and client is one of them. Right now, client is the only schema for which hbm and java files are produced. I would like to automatically generate classes for all the tables in all four schemas, including cross-schema references. Is this possible, or am I trying to do something that hibernate wasn't meant to do?

Thanks

max · **Posted:** Thu Jan 01, 2009 1:28 pm

Not sure if mysql only exposes the schema you are connected to by default.

try add a schema-selection with .* to force multi schema reading.

also check if you got default_schema set somewhere that would automatically limit the reading to that schema.

twizansky · **Joined:** Mon Dec 22, 2008 7:26 pm **Posts:** 6

Sorry for the late reply. I was away.

I tried adding the schema selection tag with .* but it didn't help. After reading up on-line it appears that MySQL (unlike other DBs) does not expose multiple schemas to reverse engineering because the schema must be specified as part of the connection URL. Does this make sense?

In the meantime I managed to get around this by writing a script that goes into the xml files after they are generated and changes the relevant <property> tags to <many-to-one>. This works but is really ugly.

Do you have any sugestions?

Thanks

max · **Posted:** Thu Jan 08, 2009 4:05 am

I haven't tried multi dbs on mysql so you might be right - mysql tend to be a bit quirky with respect to their metadata ;(

Weird that you can have foreign keys between multiple schemas at runtime but they won't expose the metadata for it.

prbk · **Joined:** Thu Oct 09, 2014 8:25 pm **Posts:** 1

I was (well still having some trouble) with something like this. I found some answers in a hibernate tools bugtracker of all places... There is an additional param you can set with the JDBC URL:

Code:

url = 'jdbc:mysql://localhost:3306/?nullCatalogMeansCurrent=false

From the mysql doc here: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Quote:

When DatabaseMetadataMethods ask for a 'catalog' parameter, does the value null mean use the current catalog? (this is not JDBC-compliant, but follows legacy behavior from earlier versions of the driver)
Default: true

While this will generate classes from the schema, it doesn't seem to be generating the mappings across schemas. e.g.
I have a [code]online.user[code], [code]backend.project[code], and a [code]backend.user_project[code] in my database (and a number of other tables of course), but reveng doesn't seem to be creating the mappings between the 2...

For the user_project table, there are additional columns (not sure if thats causing problems, but maybe...) but it doesn't seem to work for other mappings. e.g. there is a table like [code]online.designation[code] that has FK for the user & the a project. The user column is getting mapped correctly to a User property, but the project column is getting just getting mapped to an Integer in the generated files.

FWIW my config files are pretty generic at this point (and I'm pretty new to hibernate)