How to qualify sequence names in Hibernate?

Question

I'm having troubles when generating sequences for an oracle databese running under the same instance than other one, with the same data structure. Here is a fragment of my persistence.xml where I define different schemas according to the persistence unit:

<persistence-unit name="oracle_development" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.archive.autodetection" value="class" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
        <property name="hibernate.hbm2ddl.auto" value="update" />
        <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy" />
        <property name="hibernate.connection.charSet" value="UTF-8" />
        <property name="hibernate.show_sql" value="false" />
        <property name="hibernate.format_sql" value="false" />
        <property name="hibernate.connection.autocommit" value="false" />
        <property name="hibernate.ejb.entitymanager_factory_name"
            value="o11g" />
        <property name="hibernate.default_schema" value="devdatabase"/>
    </properties>
</persistence-unit>

<persistence-unit name="oracle_production" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.archive.autodetection" value="class" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
        <property name="hibernate.hbm2ddl.auto" value="update" />
        <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy" />
        <property name="hibernate.connection.charSet" value="UTF-8" />
        <property name="hibernate.show_sql" value="false" />
        <property name="hibernate.format_sql" value="false" />
        <property name="hibernate.connection.autocommit" value="false" />
        <property name="hibernate.ejb.entitymanager_factory_name"
            value="o11g" />
        <property name="hibernate.default_schema" value="proddatabase"/>
    </properties>
</persistence-unit>

Well, the tables are generated perfectly, once the table names in creating commands include the default schema as qualifier. But sequences are not generated in the 'proddatabase' if they're already created on 'devdatabase', in example... Any help?

Vlad Mihalcea · Answer 1 · 2021-01-08T16:08:56.667

The hibernate.hbm2ddl.auto=”update” is convenient but less flexible if you plan on adding functions or executing some custom scripts.

So, the most flexible approach is to generate the DDL scripts with "org.hibernate.tool.ant.HibernateToolTask" and then use a component to execute the scripts on context startup. The destroy scripts are called when the Spring context is closed.

The second approach is much more flexible, especially if you want to mix JPA Entity Model with jOOQ Table Model.

Needless to say that this is only an Integration testing concern since for the production environment we use Flyway. So, you shouldn't rely on Hibernate for managing your database schema, because it's riskier, less flexible and it doesn't play well with CI and CD.

Even if I agree, this is not the point. The point is trying to solve a problem when using those tools, which once available for using, it should work even if it's not the better approach. — Daniel Chaves, Nov 12 '14 at 19:04

How to qualify sequence names in Hibernate?

1 Answers1