How to use CLOB with Hibernate and both Postgres and H2

Question

I'm trying to store a long text file into the DBMS. Here is my table definition :

@Entity
@Table(name = "foos")
public class Foo {
    private static final long serialVersionUID = 7111245814238004034L;
    @Id
    @GeneratedValue
    @Column(nullable = false, name = "id")
    private long id;

    @Lob
    @Column(nullable = false, name = "content", length = 100_000)
    private String content;

    public long getId() {
        return id;
    }

    public String getContent() {
        return content;
    }
}

And here is my liquibase changeset :

    <createTable tableName="foos">
        <column name="id" type="bigint" />
        <column name="content" type="clob" />
    </createTable>

This work fine with H2 but with Postgres, when I try load a Foo, I get :

org.postgresql.util.PSQLException: Bad value for type long : <content>
    org.postgresql.jdbc.PgResultSet.toLong(PgResultSet.java:2876)
    org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2083)
    org.postgresql.jdbc.PgResultSet.getClob(PgResultSet.java:452)
    org.postgresql.jdbc.PgResultSet.getClob(PgResultSet.java:439)
    org.apache.commons.dbcp2.DelegatingResultSet.getClob(DelegatingResultSet.java:675)
    org.apache.commons.dbcp2.DelegatingResultSet.getClob(DelegatingResultSet.java:675)
    org.hibernate.type.descriptor.sql.ClobTypeDescriptor$1.doExtract(ClobTypeDescriptor.java:44)
    org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243)

(saving a Foo to the database works though).

If I try to add another annotation to content :

@Type(type = "org.hibernate.type.TextType")

This works fine with Postgres (prod environnement) but not with H2 (test env) :

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [content] in table [public.war_contexts]; found [clob (Types#CLOB)], but expecting [varchar(2147483647) (Types#LONGVARCHAR)]
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateColumnType(AbstractSchemaValidator.java:159)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateTable(AbstractSchemaValidator.java:143)
    at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:42)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:89)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:68)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:192)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:314)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:468)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1237)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391)
    ... 81 more

Any idea on how to solve the issue on both DBMS (and bonus points if it works with MariaDB or Oracle)

EDIT:

tried those conneciton settings

jdbc:h2:mem:myDb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1

But it's not helping

Clishmaclaver

Actually there is no standard CLOB data type in PostgreSQL. H2 supports this data type, but TEXT in H2 is also mapped to CLOB. Hibernate schema validation complains too often about different, but still compatible data types; you need to disable it if you really want to use different databases, usually it's a bad idea to use different database systems for tests and production. – Vermis

What about testing? When I run my tests I get the exact same error – Urien

I had the same problem with Postgres and H2. Using @Column(columnDefinition = "clob") from this answer solved it. – Trachytic

Answer 1

Annotate your field with @Column(columnDefinition = "text") as follows:

@Lob
@Column(columnDefinition = "text")
private String content;

Then make sure that your column type is equal to the SQL text type!

My Spring datasource config looks as follows:

spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: sa
    password: sa
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect

I tested it with Hibernate 5 and it works both with Postgres and H2.

Sarmentum

in my case (hibernate 6.2.5), having @Lob @Column(columnDefinition = "text") String message; produces Bad value for type long : hello_world when querying the table. Without the columnDefinition, hibernate uses oid type for the column. With columnDefinition, it uses text, but still expects to read oid. – Rahman

actually it was hibernate 5.6.15. I also forgot to mention my database is PostgreSQL 11 – Rahman

@Rahman I added that I only tested it with Hibernate 5! – Sarmentum