How do you set the schema name for sequences at deploy time when using JPA?

Question

For security reasons, our oracle db objects normally belong to a different schema than the logged in user. Eg. tables are in xx_core and the user we log in is xx_app_yy . In my persistence.xml I define a orm file so that I can specify the schema name at deploy time eg.: <mapping-file>xx_schema_orm.xml</mapping-file>

Then in the xx_schema_orm.xml I can define the object-owning-schema eg.:

<persistence-unit-metadata>
  <persistence-unit-defaults>
    <schema>xx_core</schema>
  </persistence-unit-defaults>
</persistence-unit-metadata>

This works great for tables, but I can't find the equivalent for sequences. It tries to use the sequence without the schema name and then I get an exception:

2010-10-14 03:04:05,423:DEBUG   could not get next sequence value [select xx_SEQ.nextval from dual]     - org.hibernate.util.JDBCExceptionReporter
java.sql.SQLException: ORA-02289: sequence does not exist

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

I've tried to set the schema name as part of the sequence name for a generator in the xx_schema_orm.xml, but could not get it working eg.:

<sequence-generator name="xx_SEQ_GEN"
sequence-name="xx_core.xx_SEQ"/>

Workarounds I may try:

• make a database SYNONYM for the sequences in the user's schema.
• stop using sequences and use some other way to generate ids.

Answer 1

Forgive me for asking the obvious, but you have granted select privilege on the sequences to the user that's trying to select from them, right?

Answer 2

In JPA 2.0:

• the @SequenceGenerator annotation and the equivalent sequence-generator element do allow to specify a schema (and catalog) name.
• the schema subelement should be honored by sequence generators as well.

But this doesn't apply to JPA 1.0.

I'll just quote the sections about the schema subelement to illustrate the differences (other relevant sections are mentioned in the references below). From the JPA 2.0 specification:

12.2.1.1 schema

The schema subelement applies to all entities, tables, secondary tables, join tables, collection tables, table generators, and sequence generators in the persistence unit.

The schema subelement is overridden by any schema subelement of the entity-mappings element; any schema element explicitly specified in the Table or SecondaryTable annotation on an entity or any schema attribute on any table or secondary-table subelement defined within an entity element; any schema element explicitly specified in a TableGenerator annotation or table-generator subelement; any schema element explicitly specified in a SequenceGenerator annotation or sequence-generator subelement; any schema element explicitly specified in a JoinTable annotation or join-table subelement; and any schema element explicitly specified in a CollectionTable annotation or collection-table subelement.

From the JPA 1.0 specification:

10.1.1.1 schema

The schema subelement applies to all entities, table generators, and join tables in the persistence unit.

The schema subelement is overridden by any schema subelement of the entity-mappings element; any schema element explicitly specified in the Table or SecondaryTable annotation on an entity or any schema attribute on any table or secondary-table subelement defined within an entity element; any schema element explicitly specified in a TableGenerator annotation or table-generator subelement; and any schema element explicitly specified in a JoinTable annotation or join-table subelement.

So, unless your provider offers some specific extensions, my suggestions are:

• Upgrade to JPA 2.0 if possible and using the schema subelement will do the trick ~or~
• Use a TableGenerator if you have to stick with JPA 1.0 ~or~
• Use an alias if this is possible (I don't know).

References

• JPA 1.0 Specification
  • Section 9.1.37 "SequenceGenerator Annotation"
  • Section 10.1.1.1 "schema"
  • Section 12.2.2.5 "sequence-generator"
• JPA 2.0 Specification
  • Section 11.1.44 "SequenceGenerator Annotation"
  • Section 12.2.1.1 "schema"
  • Section 12.2.2.5 "sequence-generator"