how to read all date from PostgreSQL with array field jpa/hibernate ejb

Question

i had problem to read all date from db PostgreSQL and jpa/hibernate ejb my table has array field see below :

   @Entity
   public class MyTable{
     private Long id;
     private String name;
     private String[] values;

     @Type(type = "com.usertype.StringArrayUserType")
     public String[] getValues(){
         return values;
     }

     public void setValues(String[] values){
         this.values = values;
     }
   }

and user type class like this :

package com.almasprocess.model.bl.en;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.*;
public class StringArrayUserType implements UserType {

    protected static final int[] SQL_TYPES = { Types.ARRAY };

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return this.deepCopy(cached);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (String[]) this.deepCopy(value);
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == null) {
            return y == null;
        }
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Object nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner)
            throws HibernateException, SQLException {
        if (resultSet.wasNull()) {
            return null;
        }
        if(resultSet.getArray(names[0]) == null){
            return new Integer[0];
        }

        Array array = resultSet.getArray(names[0]);
        String[] javaArray = (String[]) array.getArray();
        return javaArray;
    }

    @Override
    public void nullSafeSet(PreparedStatement statement, Object value, int index, SessionImplementor session)
            throws HibernateException, SQLException {
        Connection connection = statement.getConnection();
        if (value == null) {
            statement.setNull(index, SQL_TYPES[0]);
        } else {
            String[] castObject = (String[]) value;
            Array array = connection.createArrayOf("varchar", castObject);
            statement.setArray(index, array);
        }
    }

    @Override
    public Object replace(Object original, Object target, Object owner)       throws HibernateException {
        return original;
    }

    @Override
    public Class<String[]> returnedClass() {
        return String[].class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.ARRAY };
    }

}

and read date like this in ejb class :

 public List readMailBank(){
        String query = "select mt from mytable mt";
        TypedQuery<StringArrayUserType> typedQuery = em.createQuery(query , StringArrayUserType.class);
        List<StringArrayUserType> results = typedQuery.getResultList();
        return results;
    }

or like this sample code :

 public List readMailBank(){
        Type stringType = (Type) new TypeLocatorImpl(new TypeResolver()).custom(StringArrayUserType.class);
        Query query = em.createNativeQuery("select mt from mytable mt");
        query.unwrap(SQLQuery.class).addScalar("mb", (org.hibernate.type.Type) stringType);
        List<Mailbank>results =  query.getResultList();
        return results;
    }

but i had this error :

Caused by: java.lang.IllegalArgumentException: Type specified for TypedQuery [com.usertype.StringArrayUserType] is incompatible with query return type [class [Ljava.lang.String;]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.resultClassChecking(AbstractEntityManagerImpl.java:387) [hibernate-entitymanager-4.3.7.Final.jar:4.3.7.Final]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:344) [hibernate-entitymanager-4.3.7.Final.jar:4.3.7.Final]
    at org.jboss.as.jpa.container.AbstractEntityManager.createQuery(AbstractEntityManager.java:131) [wildfly-jpa-8.2.0.Final.jar:8.2.0.Final]

please help me to read all data from db and fills array in to my array field? thanks

score 0 · Answer 1 · answered Feb 20 '17 at 21:18

I think the problem with your first example is that you try to create your query for values in Java which is a String[], but your JPQL asks for MyTable values. Which classes are not extending each other, thus you get an exception about the type incompatibility.

Something like

select mt.values from mytable mt

should give you what you want. (If it doesn't work at once, here is some documentation about selecting values instead of entities.)

how to read all date from PostgreSQL with array field jpa/hibernate ejb

1 Answers1