1

I am new to JPA and JBoss, so sorry in advance if this is a stupid question.

I have application running on JBoss using EclipseLink that retrieves entities from various databases based on a JPQL string. This is working great, except one database contains multiple schema's. I would like to be able to specify which schema to use, at runtime, and select all entities that belong to that schema, without modifying the JPQL string.

To further complicate things, I would also like to join across schema's, in the same query. In this case, the JPQL query could change, or parameters could be set which specifies each entities schema.

After some searching I came across this:

JPA: How do I specify the table name corresponding to a class at runtime?

Which has a solution using ASM that dynamically modifies the Table annotation. I have tried to use this to modify the Table annotation's schema property but for the life of me I cannot get it to work; it seems that Eclipse Link uses a different class loader to init the entity classes that I don't know how to access and thus changing the annotation has no effect.

Another solution is have multiple xml configuration files for each schema. It seems inelegant; when I add a new entity I have to remember to add it to every schema's xml file. I also do not know how I can make this join across multiple schema's.

Is there any way of solving this problem? Am I missing something or is this pushing JPA beyond what it was intended for?

Community
  • 1
  • 1
user1309663
  • 105
  • 7

1 Answers1

3

You should have a different persistence unit per schema. You can use the same classes/mappings, you would just need an orm.xml file that defines the different default schema. The file doesn't need anything else in it, you can still map your objects in annotations, or in a separate orm.xml file.

To have a persistence unit with some objects in one schema, and some in another, you again could do this in an orm.xml file. You will just need to include the entities that use the non-default schema in the orm.xml file. EclipseLink always merges annotations and XML, so you can use both and only have the additions you need in xml.

EclipseLink also allows you to set a tableQualifier which defines the default schema. This can be set using a SessionCutsomizer on the Session's login.

James
  • 17,965
  • 11
  • 91
  • 146
  • This means I must define a new orm.xml file in a new persistence unit for every unique combination of schema's, right? I was hoping to avoid that as it will result in many persistence units, and orm.xml files. – user1309663 Apr 04 '12 at 06:18
  • 1
    Is it possible to somehow either get reference to the current session customizer or change the table qualifier at runtime? Something like: `DatabaseLogin login = server.getLogin();` `login.setTableQualifier("MySchema");` `session = server.acquireClientSession(login);` This doesn't work unfortunately. – user1309663 Apr 04 '12 at 08:44
  • change share configuration in a concurrent/shared system is never a good idea. If you had a different database user that defaulted to each different schema, then you would just need to pass the username in the pu properties when creating an emf – James Apr 04 '12 at 14:17
  • Thanks James. Unfortunately I cannot modify the database. I'm having issues with it not finding the different orm files (They just seem to be ignored), and it uses the default schema anyway. So, I moved the persistence.xml file and all entity classes into a separate jar file (the config was in the EAR file, and I was referencing it via jar-file and thought perhaps this was causing it to look for the orm files in the wrong place). This has taken me back a step though, now it cant even find the entities even though they are reference in class elements. – user1309663 Apr 10 '12 at 06:38