6

I'm trying make an entity work with Oracle (11.2) and PostgreSQL(9.4) in a Spring Boot (1.4.4) application.

My entity contains a long text field (over 4000 characters). The appropriate data type in Oracle is CLOB and the corresponding type in PostgreSQL is TEXT.

I'm able to make it work with PostgreSQL

@Column(name = "LONG_TEXT", columnDefinition="TEXT")
private String longText;

However it would fail with Oracle in hibernate validation stage since CLOB requires @Lob annotation.

The following code works with Oracle

@Lob
@Column(name = "LONG_TEXT")
private String longText;

However this time it fails when reading from PostgreSQL with the following exception:

PSQLException: Large Objects may not be used in auto-commit mode

Stack overflow suggests performing queries in transactions. Disregarding questionable requirement to invoke transaction in select queries, adding @Transactional to query methods didn't help.

Any thoughts are more than welcomed.

Community
  • 1
  • 1
Boris
  • 443
  • 8
  • 15
  • I don't think there is a solution other than creating an identical entity that has the same `@Table` annotation but differs at the `@Lob`, but just to be clear: you have two persistence units sharing entity types or one app that uses different databases on different servers? – coladict Mar 21 '17 at 15:46
  • Did you try [`@Lob @Type(type="org.hibernate.type.StringClobType")`](https://hibernate.atlassian.net/browse/HHH-6105)? There was also a custom hibernate dialect workaround somewhere in SO. – pozs Mar 21 '17 at 15:48
  • @coladict - It's one app that needs to support two different databases. Regarding creating different entities, is there a way doing that that would not result in changes in the entire code base? – Boris Mar 21 '17 at 15:50
  • @pozs - I did try it. No effect on Oracle and same exception in PostgreSQL. – Boris Mar 21 '17 at 15:55
  • @Boris ah, you may need the `org.hibernate.type.MaterializedClobType` as `StringClobType` may be deprecated (depends on your hibernate version). – pozs Mar 21 '17 at 15:58
  • `StringClobType` doesn't exist any more, so maybe try `@Type(type="org.hibernate.type.ClobType")`. The PU builder chooses an override for Postgres when checking the dialect. – coladict Mar 21 '17 at 15:59
  • `org.hibernate.type.MaterializedClobType` didn't as well. Will thy the `org.hibernate.type.ClobType`. – Boris Mar 21 '17 at 16:04
  • @coladict - `@Type(type="org.hibernate.type.ClobType")` results in `java.lang.ClassCastException: java.lang.String cannot be cast to java.sql.Clob` – Boris Mar 21 '17 at 16:21

1 Answers1

0

The solution we came to is to adopt the @Lob and @Transactional approach.

The main issue was with the placement of the @Transactional annotation, causing the PSQLException. Once fixed, we were able to work with both types of DBs.

Boris
  • 443
  • 8
  • 15