Mapping a complex tree-like Database Model

tradem · **Joined:** Mon Oct 06, 2003 1:59 am **Posts:** 52

Hi Folks,

I'm currently using Hibernate for a simple homegrown application and it works fine.

As a Hibernate beginner I'm don't know who powerful really hibernate is. The problem is: We have a very generative table model representing a graph mapped into relational tables. (It is not designed by me, I'm the poor person that have to implemend Java access methods.)

Can you give me a rule of thumb weather hibernate is sufficent or JDBC must be used. Should I post the DDL script?

Your query language seem to be very powerful. Much more powerful than stupid EJB-QL. Is it as powerful as standard SQL/Oracle SQL?

I really like to use hibernate as we don't have to rewrite Locking, Transaction Management and all other Fowler-PEAA stuf you already implemented.

Can you give me some suggestions/directions?

david · **Posted:** Mon Oct 06, 2003 4:42 am

Hibernate HQL is very powerful as it supports a large subset of functionality that maps to SQL. The Oracle dialect has more functionality than most of the dialects. Keep it simple but post more info and maybe we can help. The alternative is that if it cannot be mapped and queried then hibernate does not stop you from using JDBC to do the stuff that is not supported.

christian · **Posted:** Mon Oct 06, 2003 4:44 am

Well, since you didn't ask any specific question, I just say "Yes" to all of your posting. :)

HQL is an extension to SQL and should support all SQL constructs you like and even some database specific extras. You can use SQL functions in the WHERE clause from Oracle and do other nice things. Unfortunately, CONNECT BY, which would be handy for your tree problem, is not available.

tradem · **Joined:** Mon Oct 06, 2003 1:59 am **Posts:** 52

david wrote:

Hibernate HQL is very powerful as it supports a large subset of functionality that maps to SQL. The Oracle dialect has more functionality than most of the dialects. Keep it simple but post more info and maybe we can help. The alternative is that if it cannot be mapped and queried then hibernate does not stop you from using JDBC to do the stuff that is not supported.

Thx. Here is the DDL script if it helps you ;-). We won't use CONNECT BY or anything Oracle specific for trees in databases. The graph is build into the application.

Code:

PROMPT Creating Table 'BUSINESSOBJECTSCLASSIF_ED'
CREATE TABLE BUSINESSOBJECTSCLASSIF_ED
 (ED_ID_BUSINESSOBJECT INTEGER NOT NULL
 ,CL_ID_CLASSIFICATION INTEGER NOT NULL
 )
/

PROMPT Creating Table 'BUSINESSOBJECTS'
CREATE TABLE BUSINESSOBJECTS
 (ID_BUSINESSOBJECT INTEGER NOT NULL
 ,ID_CS INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,BARCODE VARCHAR2(100)
 ,CHANGER INTEGER
 ,CHANGED DATE
 ,DELETED DATE
 ,ID_ENTRY_NAME INTEGER
 ,DELETER INTEGER
 ,SHORT_TEXT VARCHAR2(100)
 ,ID_ENTRY_DESCRIPTION INTEGER
 )
/

PROMPT Creating Table 'EDGES'
CREATE TABLE EDGES
 (ID_BUSINESSOBJECT INTEGER NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,ID_CS INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,BARCODE VARCHAR2(100)
 ,SHORT_TEXT VARCHAR2(100)
 ,ID_ENTRY_NAME INTEGER
 ,CHANGER INTEGER
 ,CHANGED DATE
 ,DELETED DATE
 ,ID_ENTRY_DESCRIPTION INTEGER
 ,DELETER INTEGER
 ,ED_ID_BUSINESSOBJECT_FROM INTEGER NOT NULL
 ,ED_ID_BUSINESSOBJECT_TO INTEGER NOT NULL
 ,SORT INTEGER NOT NULL
 ,ED_NUMBER INTEGER NOT NULL
 ,AMOUNT INTEGER NOT NULL
 )
/

PROMPT Creating Table 'ATTRIBUTES_ATTRIBUTEVERIFIERS'
CREATE TABLE ATTRIBUTES_ATTRIBUTEVERIFIERS
 (ATR_ID_ATTRIBUTE INTEGER NOT NULL
 ,AV_ID_ATTRIBUTEVERIFIER INTEGER NOT NULL
 )
/

PROMPT Creating Table 'GROUPS'
CREATE TABLE GROUPS
 (ID_GROUP INTEGER NOT NULL
 ,NAME VARCHAR2(100) NOT NULL
 )
/

PROMPT Creating Table 'LANGUAGEMAPS'
CREATE TABLE LANGUAGEMAPS
 (ID_LANGUAGE INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,COUNTRY VARCHAR2(100)
 ,OPTIONAL VARCHAR2(100)
 ,CHANGED DATE
 ,LANGUAGE VARCHAR2(100)
 ,CHANGER INTEGER
 )
/

PROMPT Creating Table 'PROPOSALGROUPS'
CREATE TABLE PROPOSALGROUPS
 (ID_PROPOSALGROUP INTEGER NOT NULL
 ,DT_ID_DATATYPE INTEGER NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,ID_ENTRY_NAME NUMBER NOT NULL
 ,CHANGER VARCHAR2(100)
 ,CHANGED DATE
 ,DELETER VARCHAR2(240)
 ,DTOPTIONS VARCHAR2(100)
 ,DELETED INTEGER
 )
/

PROMPT Creating Table 'DATATYPES'
CREATE TABLE DATATYPES
 (ID_DATATYPE INTEGER NOT NULL
 ,LOCALESPECIFIC INTEGER NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,CLASSNAME VARCHAR2(1000) NOT NULL
 ,CREATED DATE NOT NULL
 ,CHANGED DATE
 ,CHANGER INTEGER
 ,DT_COMMENT VARCHAR2(100)
 ,DELETER INTEGER
 ,DELETED DATE
 )
/

PROMPT Creating Table 'ATTRIBUTEVALUES'
CREATE TABLE ATTRIBUTEVALUES
 (ID_ATTRIBUTEVALUE INTEGER NOT NULL
 ,ATR_ID_ATTRIBUTE INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,VALUE VARCHAR2(100) NOT NULL
 ,ED_ID_BUSINESSOBJECT INTEGER
 ,BO_ID_BUSINESSOBJECT INTEGER
 ,UN_ID_UNIT INTEGER
 ,CHANGED DATE
 ,CHANGER INTEGER
 )
/

PROMPT Creating Table 'CLASSIFICATIONS'
CREATE TABLE CLASSIFICATIONS
 (ID_CLASSIFICATION INTEGER NOT NULL
 ,CL_ID_CLASSIFICATION INTEGER NOT NULL
 ,CHANGED DATE
 ,CREATED DATE NOT NULL
 ,INTERNAL NUMBER(1) NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,SORT INTEGER
 ,NAME INTEGER
 ,CHANGER INTEGER
 ,SHORTCUT VARCHAR2(10)
 ,DELETED DATE
 ,DELETER INTEGER
 )
/

PROMPT Creating Table 'DICTIONARY'
CREATE TABLE DICTIONARY
 (ID_ENTRY INTEGER NOT NULL
 ,ID_LANGUAGE INTEGER NOT NULL
 ,COLUMNIDENTIFIER INTEGER NOT NULL
 ,VALUE VARCHAR2(240) NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,CHANGED DATE
 ,CHANGER INTEGER
 )
/

PROMPT Creating Table 'PROPOSALS'
CREATE TABLE PROPOSALS
 (ID_PROPOSAL INTEGER NOT NULL
 ,PRG_ID_PROPOSALGROUP INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,VALUE VARCHAR2(100) NOT NULL
 ,SORT INTEGER NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,CHANGED DATE
 ,CHANGER INTEGER
 ,DELETED DATE
 ,SHORTCUT VARCHAR2(50)
 ,DELETER INTEGER
 )
/

PROMPT Creating Table 'ATTRIBUTEVERIFIERS'
CREATE TABLE ATTRIBUTEVERIFIERS
 (ID_ATTRIBUTEVERIFIER INTEGER NOT NULL
 ,REGEXP VARCHAR2(100) NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,CHANGED DATE
 ,CHANGER INTEGER
 )
/

PROMPT Creating Table 'UNITS'
CREATE TABLE UNITS
 (ID_UNIT INTEGER NOT NULL
 ,UNIT VARCHAR2(10) NOT NULL
 )
/

PROMPT Creating Table 'ATTRIBUTES'
CREATE TABLE ATTRIBUTES
 (ID_ATTRIBUTE INTEGER NOT NULL
 ,DT_ID_DATATYPE INTEGER NOT NULL
 ,PRG_ID_PROPOSALGROUP INTEGER NOT NULL
 ,PROPOSALMANDATORY INTEGER NOT NULL
 ,ID_ENTRY_NAME INTEGER NOT NULL
 ,CREATOR INTEGER NOT NULL
 ,CREATED DATE NOT NULL
 ,UN_ID_UNIT INTEGER
 ,CHANGED DATE
 ,CHANGER INTEGER
 ,DELETED DATE
 ,DELETER INTEGER
 ,DTOPTIONS VARCHAR2(100)
 )
/

PROMPT Creating Table 'BUSINESSOBJECTSCLASSIF_BO'
CREATE TABLE BUSINESSOBJECTSCLASSIF_BO
 (BO_ID_BUSINESSOBJECT INTEGER NOT NULL
 ,CL_ID_CLASSIFICATION INTEGER NOT NULL
 )
/

PROMPT Creating Table 'CGAASSIGNMENTS'
CREATE TABLE CGAASSIGNMENTS
 (ID_CGAASSIGNMENT INTEGER NOT NULL
 ,CL_ID_CLASSIFICATION INTEGER NOT NULL
 ,ATR_ID_ATTRIBUTE INTEGER NOT NULL
 ,GR_ID_GROUP INTEGER NOT NULL
 ,SORT INTEGER NOT NULL
 )
/