Search code examples
javaspringoraclejpaeclipselink

JPA: Map multiple Oracle users on single persistence unit

I am using EclipseLink 2.5.2 (JPA 2.1), Spring 4.1.5, and I am deploying on Weblogic 12 and Oracle 12c.

I need to deploy my application to use 2 schemas (2 users on the same DB). The first contains the application data, the second contains lookup date which will never change. This is a hard requirement set by my client (the lookup schema may be used by other applications), however I know that they will be on the same Oracle instance. My JPA data model contains entities from both schemas and references between them. Likewise, at the DB level there are FKs in the data schema to the lookup schema.

I would like to:

  • map my entities in a way that will abstract away the fact that they reside on a different schema (prefixing the generated SQL queries with the user will be sufficient)
  • build a war file that is portable (no schema is hardcoded)
  • avoid synonyms, they are hard to maintain and the 2 schemas have a couple of metadata tables with the same name

My current solution:

I have a single persistence unit with all the entities from both schemas. I added an orm.xml for the lookup entities, where I define their schema at build time through Maven:

 <entity class="my.package.lookup.ActionTaken">
    <table name="ACTION_TAKEN" schema="${db.lookup.username}"/>
 </entity>

I do this to avoid hardcoding the lookup schema in the @Table annotation on the lookup entities.

This works well, the generated SQL has the correct prefix for tables in the lookup schema. The problem is, However, as the lookup schema is defined at build time, the resulting war file is not portable.

Any thoughts on how to achieve this?

Some more thoughts:

  • I currently have a single persistence unit. I don't think that multiple persistence units would work well with entities from the first persistence unit referencing entities from the second.
  • I tried to have Spring filter the orm.xml file (i.e. I could define the lookup schema in a Spring profile), but Spring seems to be able to filter its own configuration only.
  • EclipseLink has is own Composite persistence unit, but I am ruling it out because:

Joins across tables in different data sources are not supported.

Solution

  • If you can use the same datasource to access the different schemas, then you can change the schema name using EclipseLink's customizers as described here: http://eclipse.org/eclipselink/documentation/2.5/jpa/extensions/a_customizer.htm . You will need to change the table/schema name on both the entity's descriptor as well as any 1:M and M:M mappings that use a join table.