0

I have a requirement wherein I need to support both Oracle and PostgreSQL. I have following hibernate mapping -

<id name="id" type="java.lang.Integer">
    <column name="id"  not-null="true" />
    <generator class="native">
        <param name="sequence">`OneTimeAccessToken_id_seq`</param>
    </generator>
</id>
<property name="resource" type="java.lang.String">
    <column name="`res`" not-null="true" />
</property>
<property name="expires" type="long">
    <column name="`expires`" not-null="true" />
</property>

However this does not work for Oracle, since it has that backquote. If I remove the backquote, it fails for PostgreSQL. Any inputs on how to provide support for both databases.

I am using org.hibernate.dialect.Oracle10gDialect against Oracle 11.2 version, it that right?

My problem are the backticks and not making sequence compatible across databases. I have already achieved that. I used backticks in the hbm files to make PostgreSQL create table/columns as per the character case I specified. If I remove those backticks, PostgreSQL translates everything to lowercase. Now with backticks in place, everything works fine with PostgreSQL, however, it creates issues with Oracle saying it cannot find table or view: "OneTimeAccessToken". I am trying to identify how I should solve this problem of maintaining table/column name cases and also make it work against Oracle.

devang
  • 5,376
  • 7
  • 34
  • 49

3 Answers3

2

there is a specialised generator for this

<generator class="sequence-identity">
    <param name="sequence">OneTimeAccessToken_id_seq</param>
</generator>
Firo
  • 30,626
  • 4
  • 55
  • 94
  • I am aware of the generator class. My problem is not the generator, my problem is the back-tick that is making the hbm file incompatible to be used with Oracle. If I remove it , it breaks for PostgreSQL. I understand that the dialect should handle it, however, in my case it is not. I am not sure why, need more pointers to investigate into. – devang Jul 31 '12 at 15:44
  • write your own idgenerator which inherits sequencegenerator and alters depending on the dialect the sequence name – Firo Aug 01 '12 at 06:19
  • BTW, this sounds like a Hibernate bug. Hibernate should be correctly quoting in both cases, you shouldn't have to insert your own quoting. Please file a Hibernate bug reporting that sequence generator names aren't properly quoted. – Craig Ringer Aug 06 '12 at 00:30
0

Hello I don't know whether you're open for annotation based configuration or not but it seems to work in this case. As I see it they've just specified the name of the sequence with no quotes of any kind so I hope this can work, I currently do not have a working PostgreSQL db (just installing it now) so I can't run my app with it and tell you for sure but again this should work. As for your second question as you can see here yes using Oracle10gDialect for version 11 is supposed to be OK.

Community
  • 1
  • 1
Scis
  • 2,934
  • 3
  • 23
  • 37
  • I did search through and got links that said Oracle10gDialect is fine. However, I am having issues with the back-tick. I cannot move to using annotations. I am already aware of the sequence thing. – devang Jul 31 '12 at 15:46
0

I wouldn't try and mix database dialects within the same SessionFactory. If you have two databases to support, you're going to have to use two different Hibernate configurations. This naturally solves the ID generation conflict as you'll just use different generators for classes in the Oracle domain vs. ones that are in the Postgres domain.

Matt Brock
  • 5,337
  • 1
  • 27
  • 26
  • I got the issue resolved. To solve the ID generation problem, I did not require 2 dialects, it solved by making changes as @Firo suggested. Secondly, to resolve the backtick issue, it was a problem with Liquibase DB version tool. Had to extend their OracleDatabase implementation to override their trivial functionality, and also extend OracleSnapshotGenerator implementation to override table name quoting for liquibase specific tables. – devang Aug 06 '12 at 16:06