title: Array with UserType in Hibernate and PostgreSQL --> MappingException
body: <p>I am trying to write a class implementing UserType to deal with arrays in Hibernate/JPA. 
  I used the following posts 
  <a href="https://stackoverflow.com/questions/1647583/mapping-a-postgres-array-with-hibernate/1647660#1647660">mapping a postgres array with hibernate</a> and <a href="https://stackoverflow.com/questions/5393334/hibernate-jpa-hsql-how-to-create-a-dialect-mapping-for-user-type-array/5412235#5412235">Hibernate/JPA/HSQL : How to create a Dialect mapping for User Type ARRAY</a> to build a solution but I cannot get it to work. 
  I created a new Spring Roo project just to test it. Here are the different files (all java classes are located in the package test):</p>
  
  <ul>
  <li><p>persistence.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="no"?&gt;
  &lt;persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"&gt;
    &lt;persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL"&gt;
      &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
      &lt;properties&gt;
      &lt;!--  &lt;property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/&gt; --&gt;
      &lt;property name="hibernate.dialect" value="test.PostgreSQLDialectArray"/&gt;
      &lt;!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database --&gt;
      &lt;property name="hibernate.hbm2ddl.auto" value="create"/&gt;
      &lt;property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/&gt;
      &lt;property name="hibernate.connection.charSet" value="UTF-8"/&gt;
      &lt;!-- Uncomment the following two properties for JBoss only --&gt;
      &lt;!-- property name="hibernate.validator.apply_to_ddl" value="false" /--&gt;
      &lt;!-- property name="hibernate.validator.autoregister_listeners" value="false" /--&gt;
      &lt;/properties&gt;
    &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre></li>
  <li><p>TestArray.java</p>
  
  <pre><code>package test;
  
  import java.math.BigInteger;
  import java.security.SecureRandom;
  
  import org.springframework.context.ApplicationContext;
  import org.springframework.context.support.ClassPathXmlApplicationContext;
  import org.springframework.roo.addon.javabean.RooJavaBean;
  import org.springframework.roo.addon.serializable.RooSerializable;
  import org.springframework.roo.addon.tostring.RooToString;
  
  @RooJavaBean
  @RooToString
  @RooSerializable
  public class TestArray {
  
  
       private static final long serialVersionUID = 1L;
       private SecureRandom random = new SecureRandom();
  
  
       public String nextSessionId()
       {
           return new BigInteger(130, random).toString(32);
       }
  
       public static void main(String[] args) {
           ApplicationContext context;
   context = new ClassPathXmlApplicationContext("classpath:META-INF/spring/applicationContext.xml");
           int[] array = new int[1428];
           TestArray test = new TestArray();
           Blabla blabla = new Blabla();
           int nb = 1428;
  
           for(int i = 0 ; i &lt; nb ; i++)
           array[i] = test.random.nextInt();
  
            //         blabla.setTest(array);
            //         blabla.persist();
            //        System.out.println(Arrays.toString(blabla.getTest()));
  
           System.out.println(java.sql.Types.ARRAY);
           System.out.println("Done");
       }
  }
  </code></pre></li>
  <li><p>Blabla.java</p>
  
  <pre><code>package test;
  
  import org.hibernate.annotations.Type;
  import org.springframework.roo.addon.entity.RooEntity;
  import org.springframework.roo.addon.javabean.RooJavaBean;
  import org.springframework.roo.addon.tostring.RooToString;
  
  @RooJavaBean
  @RooToString
  @RooEntity
  public class Blabla {
  
      @Type(type = "test.IntArrayUserType")
      private int[] array;
  }
  </code></pre></li>
  <li><p>PostgreSQLDialectArray</p>
  
  <pre><code>package test;
  
  import java.sql.Types;
  
  
  public class PostgreSQLDialectArray extends org.hibernate.dialect.PostgreSQLDialect{
  
      public PostgreSQLDialectArray() { 
          super(); 
          registerHibernateType(Types.ARRAY, "array"); 
      }
   }
  </code></pre></li>
  <li><p>IntArrayUserType.java (basically the same than in <a href="https://stackoverflow.com/questions/1647583/mapping-a-postgres-array-with-hibernate/1647660#1647660">mapping a postgres array with hibernate</a>)</p>
  
  <pre><code>package test;
  import java.io.Serializable;
  import java.sql.Array;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  
  import org.hibernate.HibernateException;
  import org.hibernate.usertype.UserType;
  
  
  
  public class IntArrayUserType implements UserType {
  
      protected static final int  SQLTYPE = java.sql.Types.ARRAY;
  
      private int[] toPrimitive(Integer[] array){
          int[] a = new int[array.length];
          for(int i = 0 ; i &lt; array.length ; i++)
              a[i] = array[i];
          return a;
      }
  
      private Integer[] toObject(int[] array){
          Integer[] a = new Integer[array.length];
          for(int i = 0 ; i &lt; array.length ; i++)
              a[i] = array[i];
          return a;
      }
  
      @Override
      public Object nullSafeGet(final ResultSet rs, final String[] names, final Object owner) throws HibernateException, SQLException {
          Array array = rs.getArray(names[0]);
          Integer[] javaArray = (Integer[]) array.getArray();
          return toPrimitive(javaArray);
      }
  
      @Override
      public void nullSafeSet(final PreparedStatement statement, final Object object, final int i) throws HibernateException, SQLException {
          System.out.println("test null safe set...");
          Connection connection = statement.getConnection();
  
          int[] castObject = (int[]) object;
          Integer[] integers = toObject(castObject);
          Array array = connection.createArrayOf("integer", integers);
  
          statement.setArray(i, array);
          System.out.println("test null safe set...");
      }
  
      @Override
      public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
          return cached;
      }
  
      @Override
      public Object deepCopy(final Object o) throws HibernateException {
          return o == null ? null : ((int[]) o).clone();
      }
  
      @Override
      public Serializable disassemble(final Object o) throws HibernateException {
          return (Serializable) o;
      }
  
      @Override
      public boolean equals(final Object x, final Object y) throws HibernateException {
          return x == null ? y == null : x.equals(y);
      }
  
      @Override
      public int hashCode(final Object o) throws HibernateException {
          return o == null ? 0 : o.hashCode();
      }
  
      @Override
      public boolean isMutable() {
          return false;
      }
  
      @Override
      public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
          return original;
      }
  
      @Override
      public Class&lt;int[]&gt; returnedClass() {
          return int[].class;
      }
  
      @Override
      public int[] sqlTypes() {
          return new int[] { SQLTYPE };
      }
  }
  </code></pre></li>
  </ul>
  
  <p>And now the stacktrace:</p>
  
  <pre><code>Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager' defined in class path resource [META-INF/spring/applicationContext.xml]: Cannot resolve reference to bean 'entityManagerFactory' while setting bean property 'entityManagerFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [META-INF/spring/applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
  at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)
  at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
  at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
  at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
  at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
  at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
  at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:580)
  at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
  at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
  at org.springframework.context.support.ClassPathXmlApplicationContext.&lt;init&gt;(ClassPathXmlApplicationContext.java:139)
  at org.springframework.context.support.ClassPathXmlApplicationContext.&lt;init&gt;(ClassPathXmlApplicationContext.java:83)
  at test.TestArray.main(TestArray.java:29)
  Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [META-INF/spring/applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1420)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
  at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
  at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
  at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
  at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
  at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
  ... 15 more
  Caused by: javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
  at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:915)
  at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:74)
  at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:225)
  at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:308)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
  at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
  ... 22 more
  Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003
  at org.hibernate.dialect.TypeNames.get(TypeNames.java:77)
  at org.hibernate.dialect.TypeNames.get(TypeNames.java:100)
  at org.hibernate.dialect.Dialect.getTypeName(Dialect.java:296)
  at org.hibernate.mapping.Column.getSqlType(Column.java:208)
  at org.hibernate.mapping.Table.sqlCreateString(Table.java:418)
  at org.hibernate.cfg.Configuration.generateSchemaCreationScript(Configuration.java:1099)
  at org.hibernate.tool.hbm2ddl.SchemaExport.&lt;init&gt;(SchemaExport.java:106)
  at org.hibernate.impl.SessionFactoryImpl.&lt;init&gt;(SessionFactoryImpl.java:372)
  at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
  at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:906)
  ... 27 more
  </code></pre>
  
  <p>So I guess that the dialect that I created is not used at all or is wrong but I don't know why. I think that the @Type annotation should do the mapping but I have seen some  tags but I do not know if they are needed in this example and if they are, where do I had them? It's been two days that I am stuck with this problem and I am getting desperate. Could you help me debug this program? Please.
  Thank you very much in advance.</p>
  
  <p><strong>Edit 0:</strong></p>
  
  <p>The persistence.xml file seem to find the correct dialect but the function getTypeName(2003) throws the errors above after doing registerHibernateType(Types.ARRAY, "array"). BTW, I am using Hibernate 3.6.4.Final and postgresql 8.4-702.jdbc3.</p>
  
  <p><strong>Edit 1:</strong></p>
  
  <p>I added the following line to PostgreSQLDialectArray constructor:</p>
  
  <pre><code>registerColumnType(Types.ARRAY, "integer[$l]" ); 
  </code></pre>
  
  <p>which seems to partially solve the problem. However, now I get another error:</p>
  
  <pre><code>2013-01-09 11:14:30,281 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: create table blabla (id int8 not null, array int[255], name varchar(255), test int4 not null, version int4, primary key (id))
  2013-01-09 11:14:30,282 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - ERREUR: erreur de syntaxe sur ou près de « array »
    Position: 40
  </code></pre>
  
  <p>Apparently, hibernate still does not know how to create a table with an array in it...</p>
  
  <p><strong>Edit 2:</strong></p>
  
  <p>It seems that postgresql didn't like the fact that my column was called "array". I change that and it worked. The table is created by hibernate with an array of integer. </p>
  
  <p>BUT I cannot save any array in it with hibernate because of a problem in the UserType implementation. Apparently, the creation of the array fails with createArrayOf. I am reading some threads on this matter telling to access the underlying connection instead of the wrapper. I think I am going to open a new thread linking to this one because this problem is quite different.</p>
  
  <p>The stacktrace:</p>
  
  <pre><code>Exception in thread "main" java.lang.AbstractMethodError: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array;
  at test.IntArrayUserType.nullSafeSet(IntArrayUserType.java:59)
  at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:140)
  at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2184)
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2430)
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2874)
  at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
  at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
  at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
  at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
  at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
  at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
  at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
  at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76)
  at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:467)
  at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
  at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
  at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$afterReturning$org_springframework_transaction_aspectj_AbstractTransactionAspect$3$2a73e96c(AbstractTransactionAspect.aj:78)
  at test.Blabla_Roo_Entity.ajc$interMethod$test_Blabla_Roo_Entity$test_Blabla$persist(Blabla_Roo_Entity.aj:56)
  at test.Blabla.persist(Blabla.java:1)
  at test.Blabla_Roo_Entity.ajc$interMethodDispatch1$test_Blabla_Roo_Entity$test_Blabla$persist(Blabla_Roo_Entity.aj)
  at test.TestArray.main(TestArray.java:39)
  </code></pre>
  
  <p><strong>Edit 3:</strong></p>
  
  <p>Finally, after the following modifications, the UserType for integer arrays works:</p>
  
  <ul>
  <li><p>Add this line in applicationContext.xml in:</p>
  
  <p><code>&lt;bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource"&gt;</code><br>
             <code>....</code> <br>
             <code>&lt;property name="accessToUnderlyingConnectionAllowed" value="true" /&gt;</code><br>
      <code>&lt;/bean&gt;</code><br></p></li>
  <li><p>Modify the nullSafeSet from IntArrayUserType</p>
  
  <pre><code>    @Override
      public void nullSafeSet(final PreparedStatement statement, final Object object, final int i) throws HibernateException, SQLException {
          Connection connection = statement.getConnection();
          int[] castObject = (int[]) object;
          Integer[] integers = toObject(castObject);
          Connection conn = ((DelegatingConnection) connection).getInnermostDelegate();
          Array array = conn.createArrayOf("integer", integers);
          statement.setArray(i, array);
      }
  </code></pre></li>
  </ul>
  
  <p>BUT there is still a problem when getting all the entries from the table blabla:
  The function findAllBlablas does not work properly and only returns the first entry...</p>
  
  <p><strong>Edit 4:</strong></p>
  
  <p>In fact, it worked great but the eclipse console was not able to print all data. That's all!</p>