Mapping String to JSON PG and CLob Oracle - rs.getBytes error

Hibernate ORM
1

Hi,

Implementing Hibernate on a legacy application deployed on Oracle and Postgres, I have encountered an error while trying to map an attribute with the following characteristics :slight_smile:

  • '@Lob
    I need this to map my String with a CLOB column in my table in Oracle
  • '@JdbcTypeCode(SqlTypes.JSON)
    I need this to map my String with an JSON column in my table in Postgres

So this is what happens at RunTime :

Working with Postgresql I have no problems everything is fine thanks to your great work !

Working with Oracle I have an error in the mapping when calling doExtract -
'rs.getBytes.

In fact Hibernate uses OracleJsonBlobJdbcType to map my attribute to the CLOB column in the table but this class is suitable to map a String to a BLOB thus recieving a binary array from the jdbc driver.

To work around this I had to create a OracleJsonClobJdbcType declaring :

  public int getJdbcTypeCode() {
    return CLOB;
  }
  public int getDefaultSqlTypeCode() {
    return JSON;
  }

By doing so I was able to implement the doExtract method using rs.getString and it worked fine for me.

Is there another way to work around this ? Just in case I can propose this solution to contribute.

Jean-Luc

2

Hi,
there is nothing to contribute. Oracle discourages mapping JSON as CLOB, which is why Hibernate ORM chose to default to BLOB. If your legacy schema requires mapping to CLOB, then you’ll simply need such a JdbcType implementation. Going forward, I would suggest you to consider switching to the built-in JSON type which is available since Oracle 21c.