A lot of problems with hibernate and middlegen

[voigt]

Hello!

We are currently trying to build a new Java application on top of an
already existing database which cannot be modified, because an old
Delphi application is working with it. The database contains two major
design problems:

1. Some table and field names are lowercase. So we must use quotes in
queries, like "SELECT * FROM "lowercase_table" where "lowercase_field"
= XYZ"

2. Relations are implicit; this means we have quite many relations
between the tables, but these are not defined as FOREIGN KEY. So the
application must "know" what the relations are, it is not possible to
extract this info from the database schema itself.

3. Because of (2) the referential integrity cannot be violated. As you
might guess this results in quite many orphan entries, e.g. User A was
in Group B,
group B was deleted but the reference to group B is still there in
table User.

OK, that's it. At least these are the problems that I am aware of,
possibly there are more.


Now we want to build a new Java persistence layer, using Hibernate
and/or Enterprise Java Beans to make a new Web application that should
work with the same data as the Delphi application.

After some days playing around with Hibernate ddl2hbm and Middlegen I'm
now totally frustrated, because it seems that both tools cannot deal
with (1) and (2). Also it seems that no one else has ever faced the
same problems before, which makes me a little curious...

I really really hope that someone in this group can help, sorry if this
is the wrong group for the topic, please don't flame
Thank you very much in advance for any hints...
Bastian

 

[gimme_this_gimme_that]

Regarding item 1 ...

Did you name the table using the lowercase spelling in the hbm file and
still find that it uppercased the table name?

Regarding item 2 ...

This is the sort of thing that is "good" about hibernate. It encourages
you to clean up your DDL.

Anyway, nothing is keeping you from using these fields as normal fields
- except for the fact
that you don't see the tables being attached in the midgen viewer.

Regarding item 3 ...

In oracle you can create an exceptions table, then do something like

create table employee( login varchar2(12)) exceptions into exceptions;

That will make it so that you can create tables with the correct DDL,
copy from another table, and then get the rownums associated with
the rows that didn't come over. Then you can massage the data and
bring over or remove the stuff that should be orphaned.

If you want more info on that let me know, or search the oracle
database newsgroup for an example (that's where I got the idea).

 

[voigt]

Regarding item 1 ..
No, it's the other way round. I have my DDL and generated HBM out of it
with ddl2hbm from Hibernate tools. Now the spelling in the HBM is
indeed lowercase, but hibernate generates queries without quotes, so
that Firebird makes it uppercase again and throws an error.

Regarding item 2 ..
OK, nothing keeps me from doing that but how can I do a
"gimme-all-users-for-group A" then in Hibernate?? I think I will have
to clean up my database and then add all the foreign key constraints.

Regarding item 3 ..
I don't have Oracle here :-(
But thanks anyway!


Greetings
Bastian

 

[gimme_this_gimme_that]

Regarding item 1 ... I understood you generated the hbm from the ddl.

Have you added the foreign key constraints in your DDL and then found
that midgen doesn't recognize it?

 

[gimme_this_gimme_that]

Regarding item 2.

"how can I do a "gimme-all-users-for-group A" then in Hibernate?"

What's the problem? It's just a basic HQL query "from A as a where
a.userGroup='blahblah'" ?

 

[Chris Smith]

We are currently trying to build a new Java application on top of an
already existing database which cannot be modified, because an old
Delphi application is working with it. The database contains two major
design problems:

1. Some table and field names are lowercase. So we must use quotes in
queries, like "SELECT * FROM "lowercase_table" where "lowercase_field"
= XYZ"
http://www.hibernate.org/74.html#A10

2. Relations are implicit; this means we have quite many relations
between the tables, but these are not defined as FOREIGN KEY. So the
application must "know" what the relations are, it is not possible to
extract this info from the database schema itself.

Hibernate itself does not depend on the existence of foreign key
constraints in any way whatsoever. However, it does need to know about
these table relationships. Since it can't get that information from the
database, you'll need to manually edit the resulting mapping file to add
the associations.

Frankly, if you refuse to customize your mapping files, then I think
you're missing the point of using and O/R mapper with a relational
database in the first place. However, if you don't want to edit the
mapping file anyway, you can probably export the existing database
schema, import it into a new temporary database, make the changes via
SQL there, and then generate the mapping files from the temporary
database. However, one way or the other -- whether you choose to do it
in SQL, or in Hibernate mapping XML -- you simply can't get around the
need to actually provide that information that's missing in the database
itself.

3. Because of (2) the referential integrity cannot be violated. As you
might guess this results in quite many orphan entries, e.g. User A was
in Group B, group B was deleted but the reference to group B is still
there in table User.

Aside from the horror of trying to maintain this data, I don't see why
this would cause any problems for Hibernate.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Paolo via JavaKB.com]

At our company we have the exact same problem (2). And as newbies we are
trying without any success to bring this associations in the mapping files.
All examples are using primary/foreign keys and so their mapping fail when
adapting it to our case.

@voigt: did you have had success in adpating the mapping files without
altering the DB?

@Chris: could you point me /us to an example which does use a legacy DB
mentioned by voigt?

 

[Paolo via JavaKB.com]

At our company we have the exact same problem (2). And as newbies we are
trying without any success to bring this associations in the mapping files.
All examples are using primary/foreign keys and so their mapping fail when
adapting it to our case.

@voigt: did you have had success in adpating the mapping files without
altering the DB?

@Chris: could you point me /us to an example which does use a legacy DB
mentioned by voigt?