How to map Java/Kotlin string array and Postgres SQL array with JPA and hibernate

Question

I have a field in Postgres in array type:

 categories    | text[]         |           |          |

How can I declare the field in the entity class?

I have tried below in Product.kt file but it throws error [ERROR]:ERROR: relation "product_categories" does not exist

@ElementCollection
@Column(name = "categories")
var categories: List<String>? = emptyList()

My purpose is to save the string array as one field of the entity, I am not trying to do anything related "One to Many".

If you know any Java version solution and it works, I will accept the answer as well. Thanks

For anyone who need the Kotlin version, I have posted all details as an answer. — Haifeng Zhang, Oct 22 '20 at 14:12

Haifeng Zhang · Answer 1 · 2020-10-22T14:09:32.240

Adam's answer works for projects written in Java. As my project is written in Kotlin, I share my version below and hope it helps and people can use it right away other than spending time to convert:

Useful resource: How to map Java and SQL arrays with JPA and Hibernate

Postgres:

alter table product add column categories text[];

Gradle:

implementation("com.vladmihalcea:hibernate-types-52:2.10.0")

Product.kt

import com.vladmihalcea.hibernate.type.array.StringArrayType
import org.hibernate.annotations.*
import javax.persistence.*
import javax.persistence.Entity


@TypeDefs(
    TypeDef(name = "string-array", typeClass = StringArrayType::class)
)
@Entity
class Product(
        var name: String,

        @Type(type = "string-array")
        @Column(name = "categories", columnDefinition = "text[]")
        var categories: Array<String>? = arrayOf(),
)

There're 2 differences between Java and Kotlin:

The array type in Kotlin is Array<String> other than String[], I tried List<String> as well but it didn't work.
TypeDefs,TypeDef is different, no @ sign.

score 2 · Accepted Answer · answered Oct 22 '20 at 07:12

This won't work with @ElementCollection as it will always try to use another table. What you need is a custom type instead. For this you'll need this dependency:

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

then you can define your own types:


import com.vladmihalcea.hibernate.type.array.StringArrayType

@TypeDefs({
    @TypeDef(
        name = "string-array", 
        typeClass = StringArrayType.class
    )
})
@Entity
class YourEntity {

    @Type( type = "string-array" )
    @Column(
        name = "categories", 
        columnDefinition = "character varying[]"
    )
    var categories: Array<String>;
}

I haven't tried this with a List though, as this maps to an Array. More info in this answer.

Thank you Adam. It is the right direction and I have converted to Kotlin version and everything works fine! — Haifeng Zhang, Oct 22 '20 at 13:56

score 0 · Answer 3 · answered Oct 17 '22 at 05:52

Apart from the one mentioned by Haifeng, for Kotlin, there is another option to use ListArrayType instead of StringArrayType so you do not need to care about converting list to array.

I met another issue retrieving the data, I had java.lang.ArrayStoreException: arraycopy: type mismatch: can not copy [Ljava.lang.String;[] into java.lang.String[]\n. Just for those who may also encounter this, my issue was the data storing in DB is not array but array of array.

How to map Java/Kotlin string array and Postgres SQL array with JPA and hibernate

3 Answers3