I have a general question about the development status regarding support for various db platforms.
Some background...

I am writing a standalone system using Spring 2.0 RC1's JPA support + Hibernate 3.2 RC1, Hibernate EntityManager, and Annotations (Hibernate/JDK1.5). It works well against HSQLDB, but does not work with Oracle 9i or PostgreSQL 8.1

I regularly download and use current snapshots of all of these frwks and I am using the latest snapshots of both frameworks as of 6-May-2006. (I frequently build the hibernate frwks on a mac.)

My DAO design follows the example below fairly closely.
<http://blog.hibernate.org/cgi-bin/blosxom.cgi/2005/09/08#genericdao>
But I do not deal directly with the Hibernate Session or EntityManager API. Instead I reference these objects through the Spring 2.0 JPA (i.e. the jpaTemplate )

I can generate and populate tables and indexes with the spring jpa + hibernate APIs using all 3 of these databases, but I cannot fetch data from Oracle 9i or PostgreSQL 8.1
With HSQLDB, all works fine!

I'm worried that I've done something terribly wrong in my design. Or, I need to wait until Spring and Hibernate work on support for Oracle & PostgreSQL (and Sybase, SQLServer, etc...)

A silly question but have to ask: have the hibernate3 frwks developers been developing primarily against HSQLDB, and are just getting to work on supporting other databases?


I have asked the same question of the spring people.

Yep, a very silly question.

Did you set your Dialect properly?
And btw. what's really the problem?

I do set the Dialect properly for each db I'm trying to use.

The problem is this: I can save and merge new entity-objects using the JPA/Hibernate/Annotations frwks against HSQLDB, Oracle, PostgreSQL.

I cannot fetch objects from Oracle and PostgreSQL.

find* methods return null when I use either of these dbs.
No exceptions are thrown.

Later today, I will examine the sql... copy it into and run in a sql client
to see if there is something wrong with the generated sql.

I finally figured out what was happening.

I was using the correct Dialect subclass. What I was doing wrong was incorrectly mapping / annotating a couple of OneToOne entity associations.

While using HSQLDB -- for too long -- I was getting away with mapping a couple of one-to-one associations using shared a primary keys, but not using inheritance and it's JOIN strategy.

In HSQLDB, the shared primary keys (identity, generated by default) were always equal:
A.id = B.id
And the sql joins were always returning the expected results.

When I finally tried the app on Oracle, I did not get away with this.
When a new A -- with B child -- was persisted: A.id = 1 B.id = 2

A.id != B.id and I would not get any results from a join on the presumedly shared primary keys on both entities. The pk generated for a new child entity would get a value: Parent.id + 1.

I corrected the problems for the two cases as follows:

I fixed the 1st case by refactoring one of the entities into an existing inheritance hierarchy, using the JOIN strategy, and the primary keys are shared. I put AuthenticatedUser between Person and User in the existing hierarchy:

Person <-ext--- AuthenticatedUser (acegi) <-ext--- User
Now: P.id == AU.id == U.id (Oracle)

The 2nd case was solved by putting a foreign-key in parent entity, pointing to the primary-key of the child entity, and re-mapping the one-to-one association between the two entities. I should have done in the first place for proper normalization.

............

But this brings up a pk generation issue I need to study.

I am -- and always want -- to use the AUTO pk generation schema with any db I use.

In HSQLDB, it does appear that each table does have it's own identity generator. In Oracle, I see that my app/hibernate uses a single sequence for generating ID's for ALL tables. I wonder if that had something to do with this behaviour. I would like to use a separate sequence for each oracle table. I know this issue is addressed in the hibernate docs and I'll check it out.


Cheers,
Stan