DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Observability Architecture: Financial Payments Introduction
  • RBAC With API Gateway and Open Policy Agent (OPA)
  • File Upload Security and Malware Protection
  • Top 10 Pillars of Zero Trust Networks

Trending

  • Observability Architecture: Financial Payments Introduction
  • RBAC With API Gateway and Open Policy Agent (OPA)
  • File Upload Security and Malware Protection
  • Top 10 Pillars of Zero Trust Networks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Postgres and Oracle Compatibility with Hibernate

Postgres and Oracle Compatibility with Hibernate

Peter Butkovic user avatar by
Peter Butkovic
·
Mar. 26, 14 · Interview
Like (1)
Save
Tweet
Share
19.69K Views

Join the DZone community and get the full member experience.

Join For Free

Postgres and Oracle compatibility with Hibernate

There are situations your JEE application needs to support Postgres and Oracle as a Database.

Hibernate should do the job here, however, there are some specifics worth mentioning.

While enabling Postgres for application already running Oracle I came across following tricky parts:

  • BLOBs support,
  • CLOBs support,
  • Oracle not knowing Boolean type (using Integer) instead and
  • DUAL table.

These were the tricks I had to apply to make the @Entity classes running on both of these.

Please note I’ve used Postgres 9.3 with Hibernate 4.2.1.SP1.

BLOBs support

The problem with Postgres is that it offers 2 types of BLOB storage:

  • bytea - data stored in table
  • oid - table holds just identifier to data stored elsewhere

I guess in the most of the situations you can live with the bytea as well as I did. The other one as far as I’ve read is to be used for some huge data (in gigabytes) as it supports streams for IO operations.

Well, it sounds nice there is such a support, however using Hibernate in this case can make things quite problematic (due to need to use the specific annotations), especially if you try to achieve compatibility with Oracle.

To see the trouble here, see StackOverflow: proper hibernate annotation for byte[]

All- the combinations are described there:

annotation                   postgres     oracle      works on
-------------------------------------------------------------
byte[] + @Lob                oid          blob        oracle
byte[]                       bytea        raw(255)    postgresql
byte[] + @Type(PBA)          oid          blob        oracle
byte[] + @Type(BT)           bytea        blob        postgresql

where @Type(PBA) stands for: @Type(type="org.hibernate.type.PrimitiveByteArrayBlobType") and @Type(BT) stands for: @Type(type="org.hibernate.type.BinaryType").

These result in all sorts of Postgres errors, like:

ERROR: column “foo” is of type oid but expression is of type bytea

or

ERROR: column “foo” is of type bytea but expression is of type oid

Well, there seems to be a solution, still it includes patching of Hibernate library (something I see as the last option when playing with 3.rd party library).

There is also a reference to official blog post from the Hibernate guys on the topic: PostgreSQL and BLOBs. Still solution described in blog post seems not working for me and based on the comments, seems to be invalid for more people.

BLOBs solved

OK, so now the optimistic part.

After quite some debugging I ended up with the Entity definition like this :

@Lob
private byte[] foo;

Oracle has no trouble with that, moreover I had to customize the Postgres dialect in a way:

public class PostgreSQLDialectCustom extends PostgreSQL82Dialect {

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {
      return BinaryTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }
}

That’s it! Quite simple right? That works for persisting to bytea typed columns in Postgres (as that fits my usecase).

CLOBs support

The errors in misconfiguration looked something like this:

org.postgresql.util.PSQLException: Bad value for type long : ...

So first I’ve found (on String LOBs on PostgreSQL with Hibernate 3.6) following solution:

@Lob
@Type(type = "org.hibernate.type.TextType")
private String foo;

Well, that works, but for Postgres only.

Then there was a suggestion (on StackOverflow: Postgres UTF-8 clobs with JDBC) from to go for:

@Lob
@Type(type="org.hibernate.type.StringClobType")
private String foo;

That pointed me the right direction (the funny part was that it was just a comment to some answers). It was quite close, but didn’t work for me in all cases, still resulted in errors in my tests.

CLOBs solved

The important was @deprecation javadocs in the org.hibernate.type.StringClobType that brought me to working one:

@Lob
@Type(type="org.hibernate.type.MaterializedClobType")
private String foo;

That works for both Postgres and Oracle, without any further hacking (on Hibernate side) needed.

Boolean type

Oracle knows no Boolean type and the trouble is that Postgres does. As there was also some plain SQL present, I ended up In Postgres with error:

ERROR: column “foo” is of type boolean but expression is of type integer

I decided to enable cast from Integer to Boolean in Postgres rather than fixing all the plain SQL places (in a way found in Forum: Automatically Casting From Integer to Boolean):

update pg_cast set castcontext = 'i' where oid in ( select c.oid from pg_cast c inner join pg_type src on src.oid = c.castsource inner join pg_type tgt on tgt.oid = c.casttarget where src.typname like 'int%' and tgt.typname like 'bool%');

Please note you should run the SQL update by user with provileges to update catalogs (probably not your postgres user used for DB connection from your application), as I’ve learned on Stackoverflow: Postgres - permission denied on updating pg_catalog.pg_cast.

DUAL table

There is one more specific in the Oracle I came across. If you have plain SQL, in Oracle there is DUAL table provied (see more info on Wikipedia on that) that might harm you in Postgres.

Still the solution is simple. In Postgres create a view that would fill the similar purpose. It can be created like this:

create or replace view dual as select 1;

Conclusion

Well that should be it. Enjoy your cross DB compatible JEE apps.

PostgreSQL Hibernate Database Compatibility (chemical) sql

Published at DZone with permission of Peter Butkovic, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Observability Architecture: Financial Payments Introduction
  • RBAC With API Gateway and Open Policy Agent (OPA)
  • File Upload Security and Malware Protection
  • Top 10 Pillars of Zero Trust Networks

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: