Hibernate Tools: Reverse engineering legacy db . . .

dgg · **Joined:** Thu Mar 27, 2008 4:53 pm **Posts:** 5

After years of JDBC, I've finally been given a chance to take the Hibernate plunge! We have a legacy database that resides on an AS/400 for which I'm generating entity and dao code using Hibernate 3.3.1 and the Hibernate Tools 3.2.3 plug-in for Eclipse 3.4.

The following is a snippet of a Subscriber.reveng.xml file I've built:

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>  
     <table-filter match-catalog="OUR_CATALOG" match-schema="CUSTFILES" match-name="CUSTOMERDB"/>  
     <table catalog="OUR_CATALOG" schema="CUSTFILES" name="CUSTOMERDB" class="Subscriber">  
         <primary-key>  
             <generator class="native"/>  
             <key-column name="RECORDID" property="id" type="long"/>  
         </primary-key>  
         <column name="RECSTS" property="RecordStatus"/>  
         <column name="CO#" property="CompanyNumber"/>  
         <column name="CYCLE#" property="CycleNumber"/>  
         <column name="BACCT#" property="AccountNumber"/>  
         <column name="BDCYY" property="DisconnectYear"/>  
         <column name="BDCMM" property="DisconnectMonth"/>  
         <column name="BDCDD" property="DisconnectDay"/>  
         <column name="FNAME" property="FirstName"/>  
         <column name="LNAME" property="LastName"/>  
         <column name="TYPSER" property="ServiceType"/>  
         <column name="ADLN1" property="AddressLine1"/>  
         <column name="ADLN2" property="AddressLine2"/>  
         <many more columns . . ./>  
     </table>  
</hibernate-reverse-engineering>

Among others, I used the reverse engineering feature of Hibernate Tools as part of my pro-Hibernate argument to persuade my employer to let me have a go at it. But to my disappointment, the customizations I've added to my Subscriber.reveng.xml file seem to have no effect whatsoever on the source code produced via Hibernate Tools & Eclipse.

The class specified in <table> is ignored so that the entity classes produced are Customerdb, CustomerdbHome, and CustomerdbId with every single field in the table defined as part of a composite key (the table in question has no unique key).

The customizations I've added for the column-to-property relationships and the primary key definition are also ignored. I realize that I can refactor the names, etc., but if I understand Java Persistence with Hibernate correctly, this should work. I thought maybe this behavior was specific to the AS/400, but I tried it using a very simple MySQL db with the same results.

What am I missing???

thanks in advance,
dgg

max · **Posted:** Sun Mar 15, 2009 1:44 pm

Please show your reveng.xml and persistence.xml or cfg.xml that you are saying is being ignored - thanks.

dgg · **Joined:** Thu Mar 27, 2008 4:53 pm **Posts:** 5

Hi Max -
Thanks for your reply. For now, I've removed all but the id and a subset of the fields to be over-ridden. These field names contain a pound sign (#) in the database so the generated variable names as well as the getters & setters are illegal. Note also that (as mentioned in original post) the table has no primary key so I'm attempting to force it to use the "RECORDID" column as the id. So here is what the current reveng.xml looks like:

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>
    <schema-selection match-catalog="OUR_CATALOG" match-schema="CUSTFILES" match-table="CUSTOMERDB"/>
    <table-filter match-catalog="OUR_CATALOG" match-schema="CUSTFILES" match-name="CUSTOMERDB"/>
    <table name="CUSTOMERDB" class="Subscriber">
        <primary-key>
            <generator class="native"/>
            <key-column name="RECORDID" property="id" type="int"/>
        </primary-key>
        <column name="CO#" property="CompanyNumber"/>
        <column name="CYCLE#" property="CycleNumber"/>
        <column name="BACCT#" property="AccountNumber"/>
        <column name="CRD#" property="CardNumber"/>
        <column name="DL#" property="DriversLicenseNumber"/>
    </table>
</hibernate-reverse-engineering>

And here are the contents of the 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>
        <property name="hibernate.connection.driver_class">com.ibm.as400.access.AS400JDBCDriver</property>
        <property name="hibernate.connection.password">word</property>
        <property name="hibernate.connection.url">jdbc:as400://devbox</property>
        <property name="hibernate.connection.username">user</property>
        <property name="hibernate.dialect">org.hibernate.dialect.DB2400Dialect</property>
        <property name="hibernate.default_schema">CUSTFILES</property>
    </session-factory>
</hibernate-configuration>

Expected output would be a class called "Subscriber" with all getters & setters as defined by the database and the reveng.xml.

thanks for your help,
dgg

max · **Posted:** Mon Mar 16, 2009 5:11 pm

sorry I misread your initial posting.

What is actually generated ? i.e. the mappings ?

dgg · **Joined:** Thu Mar 27, 2008 4:53 pm **Posts:** 5

The output is:

Customerdb.java - Class has only a getCustomerdbId() method and a setCustomerdbId() method. I would expect the generated class to be Subscriber.java instead. Right?
CustomerdbId.java - Class has getters & setters that follow the getFieldName and setFieldName convention for all 106 fields in the database including those that I've attempted to explicitly over-ride in my reveng.xml. For example, I end up with getCo#() & setCo#() methods (which won't compile). As I understand(?) it, my reveng.xml file should produce a getCompanyNumber() and setCompanyNumber() method instead.
CustomerdbHome.java - The dao with standard methods.
Customerdb.hbm.xml - File contains mappings that match the CustomerdbId class generated, but those specified in my reveng.xml are ignored. For example:
Code:
<key-property name="co#" type="byte">
<column name="CO#" precision="2" scale="0" />
</key-property>
<key-property name="cycle#" type="short">
<column name="CYCLE#" precision="3" scale="0" />
</key-property>
<key-property name="bacct#" type="int">
<column name="BACCT#" precision="7" scale="0" />
</key-property>

Hope that makes sense.
thx,
dgg

max · **Posted:** Tue Mar 17, 2009 10:59 am

yes - and please show the full hbm.xml - at least the class and id part.

dgg · **Joined:** Thu Mar 27, 2008 4:53 pm **Posts:** 5

Below is the generated Customerdb.hbm.xml in its entirety. Sorry - it's pretty lengthy.
dgg

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 Mar 17, 2009 10:33:04 AM by Hibernate Tools 3.2.4.GA -->
<hibernate-mapping>
    <class name="persistence.Customerdb" table="CUSTOMERDB" catalog="OUR_CATALOG">
        <comment>Customer Records</comment>
        <composite-id name="id" class="persistence.CustomerdbId">
            <key-property name="recsts" type="char">
                <column name="RECSTS" length="1" />
            </key-property>
            <key-property name="co#" type="byte">
                <column name="CO#" precision="2" scale="0" />
            </key-property>
            <key-property name="cycle#" type="short">
                <column name="CYCLE#" precision="3" scale="0" />
            </key-property>
            <key-property name="bacct#" type="int">
                <column name="BACCT#" precision="7" scale="0" />
            </key-property>
            <key-property name="bdcyy" type="byte">
                <column name="BDCYY" precision="2" scale="0" />
            </key-property>
            <key-property name="bdcmm" type="byte">
                <column name="BDCMM" precision="2" scale="0" />
            </key-property>
            <key-property name="bdcdd" type="byte">
                <column name="BDCDD" precision="2" scale="0" />
            </key-property>
            <key-property name="fname" type="string">
                <column name="FNAME" length="30" />
            </key-property>
            <key-property name="lname" type="string">
                <column name="LNAME" length="30" />
            </key-property>
            <key-property name="typser" type="char">
                <column name="TYPSER" length="1" />
            </key-property>
            <key-property name="adln1" type="string">
                <column name="ADLN1" length="25" />
            </key-property>
            <key-property name="adln2" type="string">
                <column name="ADLN2" length="25" />
            </key-property>
            <key-property name="adln3" type="string">
                <column name="ADLN3" length="25" />
            </key-property>
            <key-property name="adcity" type="string">
                <column name="ADCITY" length="20" />
            </key-property>
            <key-property name="adst" type="string">
                <column name="ADST" length="2" />
            </key-property>
            <key-property name="adzip" type="int">
                <column name="ADZIP" precision="9" scale="0" />
            </key-property>
            <key-property name="ctnpa" type="short">
                <column name="CTNPA" precision="3" scale="0" />
            </key-property>
            <key-property name="ctnxx" type="short">
                <column name="CTNXX" precision="3" scale="0" />
            </key-property>
            <key-property name="ctline" type="short">
                <column name="CTLINE" precision="4" scale="0" />
            </key-property>
            <key-property name="remind" type="char">
                <column name="REMIND" length="1" />
            </key-property>
            <key-property name="crdrat" type="char">
                <column name="CRDRAT" length="1" />
            </key-property>
            <key-property name="btccfg" type="char">
                <column name="BTCCFG" length="1" />
            </key-property>
            <key-property name="crcd#" type="big_decimal">
                <column name="CRCD#" precision="20" scale="0" />
            </key-property>
            <key-property name="ccnam" type="string">
                <column name="CCNAM" length="20" />
            </key-property>
            <key-property name="cecc" type="byte">
                <column name="CECC" precision="2" scale="0" />
            </key-property>
            <key-property name="ceyy" type="byte">
                <column name="CEYY" precision="2" scale="0" />
            </key-property>
            <key-property name="cemm" type="byte">
                <column name="CEMM" precision="2" scale="0" />
            </key-property>
            <key-property name="cedd" type="byte">
                <column name="CEDD" precision="2" scale="0" />
            </key-property>
            <key-property name="crlmt" type="big_decimal">
                <column name="CRLMT" precision="6" />
            </key-property>
            <key-property name="nockfg" type="char">
                <column name="NOCKFG" length="1" />
            </key-property>
            <key-property name="cgpdfg" type="char">
                <column name="CGPDFG" length="1" />
            </key-property>
            <key-property name="pmtitv" type="char">
                <column name="PMTITV" length="1" />
            </key-property>
            <key-property name="rmdtyp" type="byte">
                <column name="RMDTYP" precision="2" scale="0" />
            </key-property>
            <key-property name="bilcpy" type="byte">
                <column name="BILCPY" precision="2" scale="0" />
            </key-property>
            <key-property name="cname" type="string">
                <column name="CNAME" length="30" />
            </key-property>
            <key-property name="siccod" type="big_decimal">
                <column name="SICCOD" precision="7" scale="3" />
            </key-property>
            <key-property name="prtanm" type="char">
                <column name="PRTANM" length="1" />
            </key-property>
            <key-property name="prtpbl" type="char">
                <column name="PRTPBL" length="1" />
            </key-property>
            <key-property name="prtebl" type="char">
                <column name="PRTEBL" length="1" />
            </key-property>
            <key-property name="cruser" type="string">
                <column name="CRUSER" length="10" />
            </key-property>
            <key-property name="crdate" type="int">
                <column name="CRDATE" precision="8" scale="0" />
            </key-property>
            <key-property name="chuser" type="string">
                <column name="CHUSER" length="10" />
            </key-property>
            <key-property name="chdate" type="int">
                <column name="CHDATE" precision="8" scale="0" />
            </key-property>
            <key-property name="cumrkt" type="string">
                <column name="CUMRKT" length="10" />
            </key-property>
            <key-property name="matax1" type="string">
                <column name="MATAX1" length="3" />
            </key-property>
            <key-property name="matax2" type="string">
                <column name="MATAX2" length="3" />
            </key-property>
            <key-property name="matax3" type="string">
                <column name="MATAX3" length="3" />
            </key-property>
            <key-property name="matax4" type="string">
                <column name="MATAX4" length="3" />
            </key-property>
            <key-property name="matax5" type="string">
                <column name="MATAX5" length="3" />
            </key-property>
            <key-property name="matax6" type="string">
                <column name="MATAX6" length="3" />
            </key-property>
            <key-property name="matax7" type="string">
                <column name="MATAX7" length="3" />
            </key-property>
            <key-property name="matax8" type="string">
                <column name="MATAX8" length="3" />
            </key-property>
            <key-property name="matax9" type="string">
                <column name="MATAX9" length="3" />
            </key-property>
            <key-property name="mataxa" type="string">
                <column name="MATAXA" length="3" />
            </key-property>
            <key-property name="mataxb" type="string">
                <column name="MATAXB" length="3" />
            </key-property>
            <key-property name="mataxc" type="string">
                <column name="MATAXC" length="3" />
            </key-property>
            <key-property name="mataxd" type="string">
                <column name="MATAXD" length="3" />
            </key-property>
            <key-property name="mataxe" type="string">
                <column name="MATAXE" length="3" />
            </key-property>
            <key-property name="btcc" type="byte">
                <column name="BTCC" precision="2" scale="0" />
            </key-property>
            <key-property name="btyy" type="byte">
                <column name="BTYY" precision="2" scale="0" />
            </key-property>
            <key-property name="btmm" type="byte">
                <column name="BTMM" precision="2" scale="0" />
            </key-property>
            <key-property name="btdd" type="byte">
                <column name="BTDD" precision="2" scale="0" />
            </key-property>
            <key-property name="ctnpa2" type="short">
                <column name="CTNPA2" precision="3" scale="0" />
            </key-property>
            <key-property name="ctnxx2" type="short">
                <column name="CTNXX2" precision="3" scale="0" />
            </key-property>
            <key-property name="ctlin2" type="short">
                <column name="CTLIN2" precision="4" scale="0" />
            </key-property>
            <key-property name="aunam1" type="string">
                <column name="AUNAM1" length="30" />
            </key-property>
            <key-property name="aunam2" type="string">
                <column name="AUNAM2" length="30" />
            </key-property>
            <key-property name="aunam3" type="string">
                <column name="AUNAM3" length="30" />
            </key-property>
            <key-property name="dl#" type="string">
                <column name="DL#" length="30" />
            </key-property>
            <key-property name="cexpor" type="char">
                <column name="CEXPOR" length="1" />
            </key-property>
            <key-property name="cudf01" type="string">
                <column name="CUDF01" length="10" />
            </key-property>
            <key-property name="cudf02" type="string">
                <column name="CUDF02" length="10" />
            </key-property>
            <key-property name="cudf03" type="string">
                <column name="CUDF03" length="10" />
            </key-property>
            <key-property name="cudf04" type="string">
                <column name="CUDF04" length="10" />
            </key-property>
            <key-property name="cudf05" type="string">
                <column name="CUDF05" length="10" />
            </key-property>
            <key-property name="cudf06" type="string">
                <column name="CUDF06" length="10" />
            </key-property>
            <key-property name="cudf07" type="string">
                <column name="CUDF07" length="10" />
            </key-property>
            <key-property name="cudf08" type="string">
                <column name="CUDF08" length="10" />
            </key-property>
            <key-property name="cudf09" type="string">
                <column name="CUDF09" length="10" />
            </key-property>
            <key-property name="cudf10" type="string">
                <column name="CUDF10" length="10" />
            </key-property>
            <key-property name="cudf11" type="string">
                <column name="CUDF11" length="30" />
            </key-property>
            <key-property name="cudf12" type="string">
                <column name="CUDF12" length="30" />
            </key-property>
            <key-property name="cudf13" type="string">
                <column name="CUDF13" length="30" />
            </key-property>
            <key-property name="cudf14" type="string">
                <column name="CUDF14" length="30" />
            </key-property>
            <key-property name="cudf15" type="string">
                <column name="CUDF15" length="30" />
            </key-property>
            <key-property name="cudf16" type="string">
                <column name="CUDF16" length="30" />
            </key-property>
            <key-property name="cudf17" type="string">
                <column name="CUDF17" length="30" />
            </key-property>
            <key-property name="cudf18" type="string">
                <column name="CUDF18" length="30" />
            </key-property>
            <key-property name="cudf19" type="string">
                <column name="CUDF19" length="30" />
            </key-property>
            <key-property name="cudf20" type="string">
                <column name="CUDF20" length="30" />
            </key-property>
            <key-property name="mxccam" type="big_decimal">
                <column name="MXCCAM" precision="11" />
            </key-property>
            <key-property name="emladr" type="string">
                <column name="EMLADR" length="75" />
            </key-property>
            <key-property name="ppacct" type="char">
                <column name="PPACCT" length="1" />
            </key-property>
            <key-property name="pduedt" type="date">
                <column name="PDUEDT" length="10" />
            </key-property>
            <key-property name="ppdys" type="short">
                <column name="PPDYS" precision="3" scale="0" />
            </key-property>
            <key-property name="paybyach" type="char">
                <column name="PAYBYACH" length="1" />
            </key-property>
            <key-property name="adrchgdat" type="timestamp">
                <column name="ADRCHGDAT" length="26" />
            </key-property>
            <key-property name="emlchgdat" type="timestamp">
                <column name="EMLCHGDAT" length="26" />
            </key-property>
            <key-property name="mktoptout" type="char">
                <column name="MKTOPTOUT" length="1" />
            </key-property>
            <key-property name="recordid" type="int">
                <column name="RECORDID" />
            </key-property>
            <key-property name="updatedby" type="int">
                <column name="UPDATEDBY" />
            </key-property>
            <key-property name="updateddt" type="date">
                <column name="UPDATEDDT" length="10" />
            </key-property>
            <key-property name="deleted" type="short">
                <column name="DELETED" />
            </key-property>
            <key-property name="deletedby" type="int">
                <column name="DELETEDBY" />
            </key-property>
            <key-property name="deleteddt" type="date">
                <column name="DELETEDDT" length="10" />
            </key-property>
        </composite-id>
    </class>
</hibernate-mapping>

max · **Posted:** Thu Mar 19, 2009 8:56 am

Ok - set hibernate.default_catalog to OUR_CATALOG or set catalog="OUR_CATALOG" on the <table>

dgg · **Joined:** Thu Mar 27, 2008 4:53 pm **Posts:** 5

That worked! I had tried that before but obviously not with the right combination of table-filter and schema-selection criteria.

Thanks so much - I really appreciate it!
dgg