CODEWITHSUNDEEP
hibernatepostgresqlhsqldb
Nemanja
Nemanja

Reputation: 343

Hibernate postgresql/hsqldb TEXT column incompatibility problem

I have a problem using Hibernate and PostgreSQL for production and HSQLDB for testing.
I am using top-down approach letting Hibernate create database schema.
I am also using annotations; mapping part of hibernate.cfg.xml only contains lines like
<mapping class="package.subpackage.ClassName" />
Hibernate defaults String variables to character varying(255) on PostgreSQL which is not sufficient for me in some cases, so I have to redefine some columns manually using
@Column(columnDefinition = "TEXT").
But, TEXT type is invalid for HSQLDB, so those tables can not be created.

Can anyone help to solve this?

Upvotes: 13

Views: 14914

Answers (6)

Sam
Sam

Reputation: 556

Yes, just try on blow to make HSQLDB run in PostgreSQL compatibility mode.

jdbc.url=jdbc:h2:mem:mydb;sql.syntax_pgs=true

Upvotes: 0

terrance.a.snyder
terrance.a.snyder

Reputation: 601

To get H2 to work in compatability mode with PostgreSQL (useful for junit testing).

# JDBC Driver
jdbc.driverClassName=org.h2.Driver
jdbc.url=jdbc:h2:mem:play;MODE=PostgreSQL;TRACE_LEVEL_SYSTEM_OUT=2;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE;INIT=CREATE TABLE IF NOT EXISTS PG_CLASS (RELNAME text, RELKIND text);
jdbc.username=sa
jdbc.password=

# general hibernate options
hibernate.database=h2
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

The create table PG_CLASS is required to allow Hibernate/JPA to correctly function. But other than that - pretty seamless.

Upvotes: 4

G. Demecki
G. Demecki

Reputation: 10596

Agree with @fredt. TEXT data type isn't standard SQL type, but extension that some engine supports.

To enable PostgreSQL compatibility mode use sql.syntax_pgs=true in your connection parameters.

Upvotes: 10

fredt
fredt

Reputation: 24372

HSQLDB 2.1 and later has a PostgreSQL compatibility mode and supports the TEXT data type in this mode.

Upvotes: 6

Szymon Lipiński
Szymon Lipiński

Reputation: 28634

Yes, you have a really big problem.

DON'T USE ONE DATABASE ENGINE FOR TESTING, AND ANOTHER FOR PRODUCTION.

You can hit upon problems you've never dreamed about.

Upvotes: -9

Don Roby
Don Roby

Reputation: 41135

The easiest way to deal with this specific issue is probably to not use the columnDefinition at all and instead to explicitly specify the column length with (for example)

@Column(length=10000)

It might also be that you could instead map it with @Lob(type = LobType.CLOB)

but I'm not sure that is supported properly in HSQLDB. In Postgres it should give you your TEXT type.

Upvotes: 12

Related Questions