Our project needs to support both Oracle and Postgres Dbs. And there may be more databases added to this list. So need a DB agnostic Hibernate configuration for BLOB and CLOB datatypes.
While Oracle was working fine with:
@Lob
@Column(name="column1")
private String str;
@Lob
@Column(name="column2")
private byte[] bytea;
Postgres started complaining with the same.
org.springframework.orm.hibernate4.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [insert into TABLE_NAME (TABLE_TYPE, TABLE_ID, TABLE_CONTENT_BIN, TABLE_CONTENT_CHAR) values (?, ?, ?, ?)]; SQL state [0A000]; error code [0]; could not insert: [com.project.EntityClass]; nested exception is org.hibernate.exception.GenericJDBCException: could not insert: [com.project.EntityClass]] Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [com.project.EntityClass] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3144) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3581) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:465) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258) at org.springframework.orm.hibernate4.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:104) ... 40 more Caused by: java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setCharacterStream(int, Reader, long) is not yet implemented. at org.postgresql.Driver.notImplemented(Driver.java:670) at org.postgresql.jdbc4.AbstractJdbc4Statement.setCharacterStream(AbstractJdbc4Statement.java:116) at org.hibernate.type.descriptor.sql.ClobTypeDescriptor$4$1.doBind(ClobTypeDescriptor.java:124) at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:90) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:286) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:281) at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:56) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2857) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3121) ... 48 more
Researched and found that people are suggesting annotating with @Type( type = "org.hibernate.type.StringClobType" ) and @Type( type = "org.hibernate.type.BinaryType" ). Both of which are Hibernate Annotations. However, we intend to use JPA annotations and this would need Hibernate at runtime, which seems to me as unnecessary.
So my question is if there is a way to handle BLOBs and CLOBs in a very generic way, which would not require us to branch codes for different DBs and without adding Hibernate specific annotations. Also, we do not use hibernate for table creation.
Postgres version- 9.4-1201-jdbc41
Oracle Version - 12.1.0.1
Hibernate Version -4.3.10.Final
