Hibernate/JPA/HSQL : How to create a Dialect mapping for User Type ARRAY

I have successfully created User Types with Postgres, and can read and write successfully.

@org.hibernate.annotations.Type(type = "com.xxx.datamodel.ext.FooType" )
@Column(name = "foo", nullable = false)
private int[] foo

@org.hibernate.annotations.Type(type = "com.xxx.datamodel.ext.BarType" )
@Column(name = "bar", nullable = false)
private double[] bar

However, when I try to use the HSQLDialect (for unit testing) I get:

Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003
at org.hibernate.dialect.TypeNames.get(TypeNames.java:79)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:104)
at org.hibernate开发者_JAVA百科.dialect.Dialect.getTypeName(Dialect.java:314)
at org.hibernate.mapping.Column.getSqlType(Column.java:205)
at org.hibernate.mapping.Table.sqlCreateString(Table.java:420)
at org.hibernate.cfg.Configuration.generateSchemaCreationScript(Configuration.java:895)
at org.hibernate.tool.hbm2ddl.SchemaExport.<init>(SchemaExport.java:105)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:353)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1341)
at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:867)
at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:669)
... 55 more

2003 is java.sql.Types.Array It looks like it fails while trying to create the schema before the tests, and I'm not sure how to tell HSQL to create the proper type/schema.

I found another somewhat related post that suggested I extend HSQLDialect and register a column type:

registerColumnType(Types.ARRAY, 
                   FooType.class.getCanonicalName());

registerColumnType(Types.ARRAY, 
                   BarType.class.getCanonicalName());

The problem with this approach is that there is only one mapping allowed per SQL Type and it doesn't resolve correctly between int[] and double[]. Not sure if this is even the correct approach. Perhaps there is some other way to override the schema creation process?

---

I solved it by working around it. I used Hibernate session.doWork(...) at this point to get a JDBC connection and did it with JDBC: http://docs.oracle.com/javase/tutorial/jdbc/basics/array.html

Hint: To define the array type (which you must do when you call

connection.createArrayOf(TYPENAME,Object[])

) you can consult this source code for the names of allowed types: http://grepcode.com/file/repo1.maven.org/maven2/postgresql/postgresql/9.0-801.jdbc4/org/postgresql/jdbc2/TypeInfoCache.java

(this is a hint from this answer: Updating ResultSets with SQL Array types in JDBC / PostgreSQL)

---

I solved this by working around it. HSQLDB does not support Arrays, at all. But Since all I need to do is to serialize and deserialize my Array's for my unit tests I can just convert everything to BLOBs...

To Do this I just modified my UserType objects to return an Array or a Blob depending on whether a static global flag is set, which defaults to using Arrays and uses Blobs when I set up HSQLDB.

---

Had this same issue for longer than I'd like to admit. Ended up adding this to my test package and test-specific profile:

package com.example.test;

import org.hibernate.dialect.PostgreSQL9Dialect;

import java.sql.Types;

public class PostgreSQLDialectArray extends PostgreSQL9Dialect {

    public PostgreSQLDialectArray() { 
        super();
        registerHibernateType(Types.ARRAY, "array");
        registerColumnType(Types.ARRAY, "integer[]" );
    }
}

---

spring:
  jpa:
    hibernate:
      ddl-auto: create-drop
    properties:
      hibernate:
        dialect: com.example.test.PostgreSQLDialectArray

Seems to be functioning as expected thus far.