Hibernate version: hibernate-3.0beta2, hibernate-annotations-3.0alpha2

Name and version of the database you are using: MySQL, MSSQL, Derby (I testing with all three of these and ideally whatever code I write will work with all of them by only changing hibernate.properties)

I am using Hibernate 3.0 and EJB annotations. What I want to do is have two databases. One database would contain objects like users/persons which would be shared between multiple applications. The other database would be specific to the application at hand (for example, I have one that is a reservation system and another one which deals with projects). This question is kind of annotations specific and kind of not. 1. I'm wondering if this can be done in the general case in a DBMS independent way. 2. I'm wondering how I can go about doing it using EJB annotations.

I tried using the @Table annotation which I noticed has schema and catalog attributes. What I'm wondering is what the difference is between these two attributes. I'm also wondering why there isn't a database attribute?

I tested it using the schema and catalog attributes with MySQL and found that it prepends the specified name to the name of the table with one or two underscores.

Maybe what I should be doing is using a single database with multiple schemas?

Note, the databases in question are on the same database server.

One thing that I could do which seems to work is just do the following. However, I'm not sure if that syntax is valid for all DBMSs.

@Table(name="databaseName.tableName")

The actual database you connect to should be specified by selecting an appropriate Session (for example there would be Session1 for the "users" database connection and Session2 for the "project"). The database information should probably not be entered as part of the annotations otherwise you're not longer database-independent.

I don't think having separate sessions for each database would work for me as the objects in different databases relate to each other. For example, say I have a User object in one database and a Project object in another database and users can have projects. I would need to do queries that join the objects. I guess maybe all the objects should be in a single database and just use different schemas to keep things organized.

The other thing that I've been wondering is if Hibernate has support for distributed databases? i.e. the ability to have multiple databases on multiple different servers and be able to issue queries that join tables on different servers.

it is not the job of an ORM to perform joins across separate databases.

Either do that your self (on top of multiple sessionfactories/sessions) or use a database that supports distribution.....

I know it's asking for a lot, I was just curious if it was possible.