Hi. I have what seems to be a rather unique problem. I am leading an initiative in my agency to overhaul a rather dated J2EE project which makes extensive use of embedded JSP Java, JDBC/SQL, and BMP entity beans.

The RDBMS belongs to the public branch of our state agency, and as such, our IT department is not allowed to make schema changes. However, we have several tables (phone, printer_users, security) that have a "user_id" column. This is, semantically, a foreign key to nothing, since we handle our web logins via a transient SSH session to our UNIX server.

However, I would like to have a "User" entity that knows about all the entities that refer to a particular user_id, for convenience's sake. Problem is, none of the tables has a complete set of all the user ids. I can collect the user ids using a SQL UNION statement, but as I cannot touch the database schema, I can't create a view for the entity to point to.

I played with the idea of using a NamedQuery or NamedNativeQuery for the User entity, but as there is no users table, I cannot use User in association mappings, and must invoke the query any time I want to convert a user id to a User object.

I've also thought about having PhoneUser extend PrintUser which extends SecurityUser, mapping user_id as the Id field, but again, no table's collection of user_ids is a complete superset or subset of any other, and to complicate things, in the security table the primary key is a composite of the table_name and user_id columns, and in the phone table, some duplicate records exist for a few user_ids.

Is there a way to simulate some kind of virtual table or view, not at the database level but visible as far as Hibernate is concerned, that I can map the User entity to? If I'm barking up the wrong tree here, any advice would be helpful. Thanks in advance.

Derek

Hibernate version: 3

Mapping documents: hibernate.cfg.xml and annotations

Code between sessionFactory.openSession() and session.close(): Using Seam to manage sessions

Name and version of the database you are using: Informix (version unknown)

You can use HSQL (hsqldb.org) with C-JDBC (c-jdbc.objectweb.org) and your existing DB.

OK - the problem with that approach is I then can't do table joins from tables in two different databases unless I use RAIDb2 - and then ridiculous amounts of time are spent copying data from the various tables to the HSQLDB database. So I can't do associations between entities coming from different databases, which is something I need to be able to do.

Am I not seeing an easier solution here?

Create the fake ones on HSQL.
Then use C-JDBC so that both your DBs look like a single DB.
Then you can do joins.

You mean create fake tables on HSQL? I was under the impression that C-JDBC required the data to already be present in both databases. Or does it just migrate the data that it needs so HSQLDB acts as a database cache??

It's still a massive task; we have over 250 tables and views, some with 50+ columns. And since HSQLDB runs by default in memory we'll have to recreate the schema every time we restart the app server. I could automate this with ResultSetMetaData but that's still pretty tricky.

From the Sequoia (C-JDBC) documentation:

"Distributed Joins are not supported".

I cannot do joins on separate databases, even with C-JDBC/Sequoia.