java - Error while mapping postgres arrays in Spring JPA

Question

I am using Spring JPA and postgres database. I have created the following table:

CREATE TABLE public.book
(
  id bigint NOT NULL DEFAULT nextval('book_id_seq'::regclass),
  title character varying(60) NOT NULL,
  year date,
  publisher character varying(30),
  author bigint[] NOT NULL,
  cathegory smallint[],
  CONSTRAINT book_primary_key PRIMARY KEY (id)
)

Here is DAO:

@Data
@NoArgsConstructor
@Entity
@Table(name = "book", schema = "public")
public class BookDao {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "auto_gen_book")
    @SequenceGenerator(name = "auto_gen_book", sequenceName = "book_id_seq", allocationSize = 1)
    private Long id;
    private String title;
    private LocalDateTime year;
    private String publisher;
    private long[] author;
    private short[] cathegory;

    public BookDao(String title, long[] author, short[] cathegory) {
        this.title = title;
        this.author = author;
        this.cathegory = cathegory;
    }
}

Here is a very simple controller:

@RestController
@RequestMapping("/book")
public class BookController {

    @Autowired
    BookService bookService;

    @RequestMapping("/{id}")
    public String getUserSurnameFromId(@PathVariable long id) {
        return bookService.getBookById(id).toString();
    }

    @RequestMapping("/all")
    public List<BookDao> getAllBooks() {
        return bookService.getAllBooks();
    }

    @RequestMapping("/save/{title}/{author}/{category}")
    public BookDao saveByTitleAuthorCategory(@PathVariable String title, @PathVariable long author, @PathVariable short category) {
        long[] authors = {author};
        short[] categories = {category};
        BookDao bookDao = new BookDao(title, authors, categories);
        return bookService.save(bookDao);
    }

}

I the controller service is used, but the only thing it does is invoking repository methods. No logic is yet implemented.

The problem occurs, when I am connecting to http://localhost:8090/book/all to list all records, I am getting following error:

2018-03-15 22:40:33.291 ERROR 8022 --- [nio-8090-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not deserialize; nested exception is org.hibernate.type.SerializationException: could not deserialize] with root cause

java.io.EOFException: null
    at java.base/java.io.ObjectInputStream$PeekInputStream.readFully(ObjectInputStream.java:2754) ~[na:na]
    at java.base/java.io.ObjectInputStream$BlockDataInputStream.readShort(ObjectInputStream.java:3249) ~[na:na]
    at java.base/java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:866) ~[na:na]
    at java.base/java.io.ObjectInputStream.<init>(ObjectInputStream.java:342) ~[na:na]
    at org.hibernate.internal.util.SerializationHelper$CustomObjectInputStream.<init>(SerializationHelper.java:309) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.internal.util.SerializationHelper$CustomObjectInputStream.<init>(SerializationHelper.java:299) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.internal.util.SerializationHelper.doDeserialize(SerializationHelper.java:218) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.internal.util.SerializationHelper.deserialize(SerializationHelper.java:287) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.fromBytes(SerializableTypeDescriptor.java:139) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.wrap(SerializableTypeDescriptor.java:114) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.wrap(SerializableTypeDescriptor.java:28) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.descriptor.sql.VarbinaryTypeDescriptor$2.doExtract(VarbinaryTypeDescriptor.java:60) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:261) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:247) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:333) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2854) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1747) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1673) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.getRow(Loader.java:1562) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:732) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.processResultSet(Loader.java:991) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:949) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2692) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2675) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2502) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1490) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72) ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:307) ~[spring-data-jpa-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:74) ~[spring-data-jpa-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:377) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:629) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:593) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.0.4.RELEASE.jar:2.0.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMetho

Answer 1

Just like @jeff-wang has pointed out, Postgres array types are not supported by JPA. Specific mapping implementations can be added manually, but @vlad-mihalcea has already provided sample implementation. You get it from maven central:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

More details can be found here: https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/

After resolving the dependency, an implementation for specific mapping needs to be added. Let us take Postgres bigint[] as an example. It can be mapped to for example Long[]. Firstly, we need to add descriptor for desired type:

import com.vladmihalcea.hibernate.type.array.internal.AbstractArrayTypeDescriptor;

public class LongArrayTypeDescriptor extends AbstractArrayTypeDescriptor<Long[]> {
    public static final LongArrayTypeDescriptor INSTANCE = new LongArrayTypeDescriptor();

    public LongArrayTypeDescriptor() {
        super(Long[].class);
    }

    protected String getSqlArrayType() {
        return "bigint";
    }
}

After that the actual mapping class:

import com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor;
import org.hibernate.type.AbstractSingleColumnStandardBasicType;
import org.hibernate.usertype.DynamicParameterizedType;

import java.util.Properties;

public class LongArrayType extends AbstractSingleColumnStandardBasicType<Long[]> implements DynamicParameterizedType {

    public static final LongArrayType INSTANCE = new LongArrayType();

    public LongArrayType() {
        super(ArraySqlTypeDescriptor.INSTANCE, LongArrayTypeDescriptor.INSTANCE);
    }

    public String getName() {
        return "long-array";
    }

    @Override
    protected boolean registerUnderJavaType() {
        return true;
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((LongArrayTypeDescriptor) getJavaTypeDescriptor()).setParameterValues(parameters);
    }
}

That being done, all that is left to do is to take our mappings into account in Spring configuration. I am keeping my data model configuration separately in annotation based form:

import ibdb.model.mappers.LongArrayType;
import ibdb.model.mappers.ShortArrayType;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;

import javax.persistence.MappedSuperclass;

@TypeDefs({
        @TypeDef(
                name = "long-array",
                typeClass = LongArrayType.class
        ),
        @TypeDef(
                name = "short-array",
                typeClass = ShortArrayType.class
        )
})
@MappedSuperclass
public class DaoConfig {
}

Now everything is ready to use. Example column annotated with newly added mapping in DAO definition looks like following:

@Type(
        type = "long-array"
)
@Column(
        columnDefinition = "bigint[]"
)
private Long[] author;