TEXT field that is compatible in mysql and hsqldb

问题:

I have an application that uses a mysql database but I would like to run the unit tests for the application in a hsqldb in-memory database. The problem is that some of my persistable model objects have fields which I have annotated as columnDefinition = "TEXT" to to force mysql to cater for long string values, but now hsqldb doesn't know what TEXT means. If I change it to CLOB, then hsqldb is fine but mysql fails.

Is there a standard column definition that I can use for long strings that is compatible with mysql AND hsqldb?

回答1:

What worked for me was to just enable MySQL compatibility mode by changing the connection URL to jdbc:hsqldb:mem:testdb;sql.syntax_mys=true

回答2:

You can use the same solution as offered in this post for PostgreSQL TEXT columns and HSQLDB with Hibernate:

Hibernate postgresql/hsqldb TEXT column incompatibility problem

As HSQLDB allows you to define TEXT as a TYPE or DOMAIN, this may be a solution if you find out how to execute a statement such as the one below before each test run with HSQLDB via Hibernate.

CREATE TYPE TEXT AS VARCHAR(1000000)

Update for HSQLDB 2.1 and later: This version support a MySQL compatibility mode. In this mode, the MySQL TEXT type is supported and translated to LONGVARCHAR. LONGVARCHAR is by default a long VARCHAR, but a property (sql.longvar_is_lob) allows it to be interpreted as CLOB. See:

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_sql_conformance

http://hsqldb.org/doc/2.0/guide/compatibility-chapt.html#coc_compatibility_mysql

回答3:

Not really. MySQL has TEXT and BLOB, with various size prefixes to indicate their maximum size. hsqldb only appears to have clob and various varchars. Most like you'd have to special case your tests depending on which database you're talking to.

If your text strings are short enough, you could use varchars, but those are limited to just under 64k in mysql, and that's the max size of a row, so the larger the varchar, the less space for other fields.

回答4:

You can also solve some issues at the JPA-vendor (Hibernate etc.) level.

With @Lob for example, the long/large type is determined in runtime based on the vendor (longvarchar/longtext MySql vs clob in H2).