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

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. — Evgenij Ryazanov
– Evgenij Ryazanov, Commented May 27, 2020 at 15:04
What about testing? When I run my tests I get the exact same error — Alejandro
– Alejandro, Commented May 5, 2021 at 15:36
I had the same problem with Postgres and H2. Using @Column(columnDefinition = "clob") from this answer solved it. — peterrhodesdev
– peterrhodesdev, Commented Aug 18, 2021 at 11:50

times29 · Accepted Answer · 2023-09-30 07:16:47Z

8

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.

edited Sep 30, 2023 at 7:16

answered Mar 10, 2022 at 16:38

times29

3,4613 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

trogper Over a year ago

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.

trogper Over a year ago

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

times29 Over a year ago

@trogper I added that I only tested it with Hibernate 5!

Collectives™ on Stack Overflow

How to use CLOB with Hibernate and both Postgres and H2

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related