Uploaded image for project: 'Hibernate ORM'
  1. Hibernate ORM
  2. HHH-7232

Hibernate ignores 'schema' attribute of @SequenceGenerator for Oracle sequences

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.1.2, 4.3.6
    • Fix Version/s: None
    • Component/s: hibernate-core
    • Labels:
      None
    • Environment:
      Hibernate 4.1.2.Final, Oracle 11gR2
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true

      Description

      I wrote a mapping for a sequence such as:

      @SequenceGenerator( schema="jaseadm", name="applicantSignupSequence", sequenceName="seq_applicant_signup" )

      And the generated SQL is as follows:

      DEBUG org.hibernate.SQL - select seq_applicant_signup.nextval from dual

      Which leads to this:

      WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 2289, SQLState: 42000
      ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-02289: sequence does not exist

        Activity

        Hide
        rmohta Rohit Mohta added a comment -

        In your persistence.xml or any other configuration where you place your properties like "hibernate.dialect" or "hibernate.hbm2ddl.auto", you can use "hibernate.default_schema" to point to the schema where the synonym resides.

        Show
        rmohta Rohit Mohta added a comment - In your persistence.xml or any other configuration where you place your properties like "hibernate.dialect" or "hibernate.hbm2ddl.auto", you can use "hibernate.default_schema" to point to the schema where the synonym resides.
        Hide
        don stevo Steve Schols added a comment -

        Hi Rohit, thanks for your reply.

        We have a hibernate.default_schema configured. For example, we have it configured to point to SCHEMA_1.
        Most of our entities reside in SCHEMA_1. But we have a few entities that need to be mapped to a legacy database schema SCHEMA_2.

        The entity itself is not a problem: there we can just use the @Table(name="TABLE_NAME", schema="SCHEMA_2") annotation.
        But the sequence, which we needed for entity identifier generation, also resides in SCHEMA_2. Apparently we can configure a sequence generator the following way:
        @SequenceGenerator(name = "sequenceGenerator", sequenceName = "SEQUENCE_NAME", schema = "SCHEMA_2")

        Apparently, testing the mapping, the sequence cannot be found. It appears to be searching the sequence in the configured default_schema, and not in the schema attribute that is given to the @SequenceGenerator annotation.
        The schema attriibute herein is ignored.

        Show
        don stevo Steve Schols added a comment - Hi Rohit, thanks for your reply. We have a hibernate.default_schema configured. For example, we have it configured to point to SCHEMA_1. Most of our entities reside in SCHEMA_1. But we have a few entities that need to be mapped to a legacy database schema SCHEMA_2. The entity itself is not a problem: there we can just use the @Table(name="TABLE_NAME", schema="SCHEMA_2") annotation. But the sequence, which we needed for entity identifier generation, also resides in SCHEMA_2. Apparently we can configure a sequence generator the following way: @SequenceGenerator(name = "sequenceGenerator", sequenceName = "SEQUENCE_NAME", schema = "SCHEMA_2") Apparently, testing the mapping, the sequence cannot be found. It appears to be searching the sequence in the configured default_schema, and not in the schema attribute that is given to the @SequenceGenerator annotation. The schema attriibute herein is ignored.
        Hide
        rmohta Rohit Mohta added a comment -

        Hi Steve,

        Are you using Spring Transaction or some other framework? If yes, please read on

        I am assuming you have multiple schemas in the same datasource. I see two workarounds depending on your use case

        (a) Can you use two EntityManager and JpaTransactionManager. Reference the transaction managers using the qualifier attribute in XML or equivalent in Javaconfig.
        Then annotate them accordingy and use them.

        (b) Make use of PrePersist annotation. Create a native SQL (get next value from sequence, just like Hibernate will do but with your schema name hardocded or picked from a property) and populate the value in the id attribute. This defeats the purpose of ORM, but might be useful in your case.

        Hope it helps

        Show
        rmohta Rohit Mohta added a comment - Hi Steve, Are you using Spring Transaction or some other framework? If yes, please read on I am assuming you have multiple schemas in the same datasource. I see two workarounds depending on your use case (a) Can you use two EntityManager and JpaTransactionManager. Reference the transaction managers using the qualifier attribute in XML or equivalent in Javaconfig. Then annotate them accordingy and use them. (b) Make use of PrePersist annotation. Create a native SQL (get next value from sequence, just like Hibernate will do but with your schema name hardocded or picked from a property) and populate the value in the id attribute. This defeats the purpose of ORM, but might be useful in your case. Hope it helps
        Hide
        johnlmorgan morgan added a comment -

        Hi,

        also exact same issue here. We are using Hibernate 4.3.6.Final, with Spring 4.1.4.RELEASE, on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production, JPA 2.1 api. We have two schemas in one datasource. I have annotated the Entity correctly:

        @Table(name = "APP_CHECK", schema = "APS_OTHER_DEV")

        I also reference the same schema in @SequenceGenerator, but the sql generated by Hibernate has no schema in it. select APP_CHECK_SEQ_ID.nextval from dual.. gives ORA-00942: table or view does not exist

        I'm not in a position to introduce the use of EntityManager.

        regards
        John

        Show
        johnlmorgan morgan added a comment - Hi, also exact same issue here. We are using Hibernate 4.3.6.Final, with Spring 4.1.4.RELEASE, on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production, JPA 2.1 api. We have two schemas in one datasource. I have annotated the Entity correctly: @Table(name = "APP_CHECK", schema = "APS_OTHER_DEV") I also reference the same schema in @SequenceGenerator, but the sql generated by Hibernate has no schema in it. select APP_CHECK_SEQ_ID.nextval from dual.. gives ORA-00942: table or view does not exist I'm not in a position to introduce the use of EntityManager. regards John
        Hide
        alprab al prab added a comment -

        This is true for Postgresql sequences too.
        The schema generator ignores the schema attribute of the @SequenceGenerator annotation so that the sequence gets generated in the default schema 'public'.

        Example:
        For:

        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ACR_SEQ")
        @SequenceGenerator(schema = "ACR", name = "ACR_SEQ", sequenceName = "ACR_SEQ", allocationSize = 1)
        @Column(name = "ID", updatable = false, nullable = false)
        protected long id;

        The sequence "ACR_SEQ" gets created in the Postgresql default schema 'public'.

        Show
        alprab al prab added a comment - This is true for Postgresql sequences too. The schema generator ignores the schema attribute of the @SequenceGenerator annotation so that the sequence gets generated in the default schema 'public'. Example: For: @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ACR_SEQ") @SequenceGenerator(schema = "ACR", name = "ACR_SEQ", sequenceName = "ACR_SEQ", allocationSize = 1) @Column(name = "ID", updatable = false, nullable = false) protected long id; The sequence "ACR_SEQ" gets created in the Postgresql default schema 'public'.

          People

          • Votes:
            7 Vote for this issue
            Watchers:
            12 Start watching this issue

            Dates

            • Created:
              Updated:

              Development