Array with UserType in Hibernate and PostgreSQL --> MappingException

Question

I am trying to write a class implementing UserType to deal with arrays in Hibernate/JPA. I used the following posts mapping a postgres array with hibernate and Hibernate/JPA/HSQL : How to create a Dialect mapping for User Type ARRAY 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):

persistence.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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">
  <persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
    <!--  <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> -->
    <property name="hibernate.dialect" value="test.PostgreSQLDialectArray"/>
    <!-- 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 -->
    <property name="hibernate.hbm2ddl.auto" value="create"/>
    <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
    <property name="hibernate.connection.charSet" value="UTF-8"/>
    <!-- Uncomment the following two properties for JBoss only -->
    <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
    <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
    </properties>
  </persistence-unit>
</persistence>

TestArray.java

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 < 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");
     }
}

Blabla.java

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;
}

PostgreSQLDialectArray

package test;

import java.sql.Types;


public class PostgreSQLDialectArray extends org.hibernate.dialect.PostgreSQLDialect{

    public PostgreSQLDialectArray() { 
        super(); 
        registerHibernateType(Types.ARRAY, "array"); 
    }
 }

IntArrayUserType.java (basically the same than in mapping a postgres array with hibernate)

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 < 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 < 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<int[]> returnedClass() {
        return int[].class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { SQLTYPE };
    }
}

And now the stacktrace:

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.<init>(ClassPathXmlApplicationContext.java:139)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(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.<init>(SchemaExport.java:106)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:372)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:906)
... 27 more

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.

Edit 0:

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.

Edit 1:

I added the following line to PostgreSQLDialectArray constructor:

registerColumnType(Types.ARRAY, "integer[$l]" );

which seems to partially solve the problem. However, now I get another error:

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

Apparently, hibernate still does not know how to create a table with an array in it...

Edit 2:

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.

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.

The stacktrace:

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)

Edit 3:

Finally, after the following modifications, the UserType for integer arrays works:

Add this line in applicationContext.xml in:

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
....
<property name="accessToUnderlyingConnectionAllowed" value="true" />
</bean>

Modify the nullSafeSet from IntArrayUserType

    @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);
    }

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

Edit 4:

In fact, it worked great but the eclipse console was not able to print all data. That's all!

Alex · Accepted Answer · 2013-01-10 15:58:10Z

I answered my own question in all my edits. I still post this comment in case other people get stuck like me so they can see that this problem was answered. However, the last problem with findAllBlablas remains. If I find a fix, I'll edit my question again.

Rollozuh · Answer 2 · 2013-04-26 11:25:39Z

Beside your solution in Edit 3, it works perfect when you don't use connection-pooling like dbcp or c3p0. - You are working with dbcp.

A possible solution, when using c3p0 would be to use proxool. With this, there shouldn't be any problems, because proxool can handle createArrayOf().

Another would be this:

    C3P0ProxyConnection connection = (C3P0ProxyConnection) _statement.getConnection();
    Method method = Connection.class.getMethod("createArrayOf", new Class[] { String.class, Object[].class });
    Array array = (Array) connection.rawConnectionOperation(
        method, C3P0ProxyConnection.RAW_CONNECTION, new Object[] {"integer", intArray});
    _statement.setArray(_i, array);

You need to get the underlying function from the jdbc-class. Therefor get it with getMethod and use it with reflection.

asked	1 year ago
viewed	2977 times
active	1 year ago

current community

your communities

more stack exchange communities

Array with UserType in Hibernate and PostgreSQL --> MappingException

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged java arrays spring hibernate postgresql or ask your own question.

Linked

Hot Network Questions

current community

your communities

more stack exchange communities

Array with UserType in Hibernate and PostgreSQL --> MappingException

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged java arrays spring hibernate postgresql or ask your own question.

Linked

Related

Hot Network Questions