3

Our Java application have to use both SQL-Server and Oracle. IDENTITIES are used with SQL Server, and SEQUENCE-TRIGGER with Oracle.

Is there way in Hibernate to generate ID with both SEQUENCEs and IDENTIES ?

Thanks.

user2171669
  • 745
  • 1
  • 12
  • 22
  • So you have one entity which you are going to insert into 2 diferent datasources, one oracle and one ms sql, and want some of the fields to be generated for both datasources ? – Babl Jan 29 '15 at 19:10
  • No, there are two instances of application, every works with different database. – user2171669 Jan 29 '15 at 20:33

2 Answers2

3

As you have two different application instances you can try to use so called "native" generator which will automatically use correct generator for your datasource based on the dialect. So for oracle it will use SequenceGenerator and for MS SQL it will use the IdentityGenerator. The following code shows the usage (there may be some lags as I don't have MsSQL or Oracle running).

@Entity
@Table(name = "SOME_TABLE")
@SequenceGenerator(name = "SOME_SEQUENCE", allocationSize = 1, sequenceName = "SOME_SEQUENCE")
public class SomeEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "SOME_SEQUENCE")
    @Column(name = "ID", unique = true)
    private Long id;
}

As you can see we are passing the Oracle sequence name with parameters otherwise the sequence with name HIBERNATE_SEQUENCE will be used.

Babl
  • 7,446
  • 26
  • 37
  • You are right, to implement this we need to say to Hibernate: strategy = GenerationType.AUTO, but consider optional generator @SequenceGenerator(with seq params). Thanks – user2171669 Mar 31 '15 at 18:38
  • 2
    This no longer seems to work with Hibernate5.1.0, in MSSQL server it's looking for a table named 'some_sequence' rather than using identity column – Dev Blanked Mar 22 '16 at 05:15
  • I got it working for Hibernate 5.0.1 by setting `hibernate.id.new_generator_mappings` to TRUE for a oracle connection and FALSE on a MsSQL connection. See at http://stackoverflow.com/a/36182288 – mejoz Apr 18 '16 at 15:54
0

While I agree with the accepted text answer, I disagree with the code, at least for my versions. GenerationType.AUTO resulted in an invalid generation configuration for SQL Server for me (did not seem to select Identity). Here is my solution for dual database MSSQL / Oracle using Hibernate Core 5.4.30 + SQLServer2012Dialect + Oracle12cDialect:

    @Id
    @GenericGenerator(name = "MY_GENERATOR", strategy = "native",
        parameters = {
            @org.hibernate.annotations.Parameter(name = "schema", value="MY_SCHEMA"),
            @org.hibernate.annotations.Parameter(name = "sequence_name", value="MY_SEQUENCE")
        }
    )
    @GeneratedValue(generator = "MY_GENERATOR")
    private Long id;

You should be able to add other parameters such as allocation size to the parameter list, I believe.

Galen Howlett
  • 530
  • 4
  • 12