1

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.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?

RubioRic
  • 2,442
  • 4
  • 28
  • 35
user671435
  • 79
  • 1
  • 8
  • PostgreSQL and HSQLDB dialects do not map the ARRAY type (nor does any other dialect I've seen). The behaviour change may be due to JDBC drivers different reporting of ARRAY type support, and Hibernate expecting to find a mapping when ARRAY type is supported by the driver. – fredt Mar 22 '11 at 16:18
  • a slight bit of confusion: Postgres Dialect does support ARRAY, once you create a user type: /* * (non-Javadoc) * @see org.hibernate.usertype.UserType#returnedClass() */ @Override @SuppressWarnings("unchecked") public Class returnedClass() { return Double[].class; } /* * (non-Javadoc) * @see org.hibernate.usertype.UserType#sqlTypes() */ @Override public int [] sqlTypes() { return SQL_TYPES; //Types.ARRAY } – user671435 Mar 22 '11 at 17:46
  • @user671435 what's `SQL_TYPES` ? While Hibernate can be made to work with PostgreSQL arrays, what's problematic is during **schema generation** – Hendy Irawan Aug 19 '14 at 11:40

3 Answers3

1

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)

Community
  • 1
  • 1
alfonx
  • 6,936
  • 2
  • 49
  • 58
0

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.

user671435
  • 79
  • 1
  • 8
  • HSQLDB fully supports arrays, for example `CREATE TABLE T(arr INT ARRAY); INSERT INTO T VALUES ARRAY[1,2,3];` and supports `PreparedStatement#setArray(int i, java.sql.Array array)`. The problem is in the interaction with Hibernate, which should be researched and fixed. – fredt Mar 24 '11 at 00:25
  • I also had to create a custom hibernate Dialect that extends the HSQLDialect that mapped the Sql.ARRAY type to varbinary. – user671435 Mar 25 '11 at 20:23
0

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.

Charly
  • 881
  • 10
  • 19