Hibernate mapping between Postgres array of varchar and a Java/Kotlin collection of enum

Question

Basically everything is in the title.

I have a column in my DB which is a varchar[].

I really would like to map it to a Java/Kotlin enum. We've already got this working to fetch it as a list of Strings (through com.vladmihalcea:hibernate-types and StringArrayType), but not with a mapping to an enum. Do you know if this is possible?

Since we know how to map a varchar to an enum, and a varchar[] to a collection of String, I would be tempted to think that this should possible, but I didn't succeed yet.

Here would be a simple sample of my current configuration:

CREATE TABLE test(my_values varchar[]) ;
INSERT INTO test(my_values) values ('{VAL1, VAL2}')

@Entity
@Table(name = "test")
data class DbTest(
        @Column(name = "my_values")
        val myValues: List<Values>
)

enum class Values {
       VAL1, VAL2
}

I tried this: https://vladmihalcea.com/map-postgresql-enum-array-jpa-entity-property-hibernate/ which looks pretty good but you have to define the enum in the DB and we don't want that.

Thanks!

Strinder · Accepted Answer · 2023-11-24 10:50:53Z

5

If using Hibernate version > 6 this changed and should look like this:

@Type(StringArrayType.class)
@Column(
    name = "sensor_names",
    columnDefinition = "text[]"
)
private String[] sensorNames;

@Type(IntArrayType.class)
@Column(
    name = "sensor_values",
    columnDefinition = "integer[]"
)
private int[] sensorValues;

@Type(
    value = EnumArrayType.class,
    parameters = @Parameter(
        name = AbstractArrayType.SQL_ARRAY_TYPE,
        value = "sensor_state"
    )
)
@Column(
    name = "sensor_states",
    columnDefinition = "sensor_state[]"
)
private SensorState[] sensorStates;

Note: EnumArrayType comes from library HypersistenceUtils

edited Nov 24, 2023 at 10:50

answered Nov 23, 2023 at 10:30

Strinder

2,3302 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

z0mb1ek · Accepted Answer · 2022-04-02 23:45:46Z

1

Previous answer does not work for me, but this working:

    TypeDef(
      name = "enums-array",
      typeClass = ListArrayType::class,
      parameters = [Parameter(name = EnumArrayType.SQL_ARRAY_TYPE, value = "varchar")]
    )

answered Apr 2, 2022 at 23:45

z0mb1ek

1,00510 silver badges17 bronze badges

1 Comment

Strinder Over a year ago

This is no longer possible with Hibernate >6 since TypeDef has been removed (see answer above)

sjahan · Accepted Answer · 2020-09-11 07:22:46Z

0

I'm posting my solution, I didn't succeed to get a List<Values>, although I got an Array<Values> which was fine with me.

@Entity
@Table(name = "test")
@TypeDef(
        name = "values-array",
        typeClass = EnumArrayType::class,
        defaultForType = Array<Values>::class,
        parameters = [
          Parameter(
                  name = EnumArrayType.SQL_ARRAY_TYPE,
                  value = "varchar"
          )
        ]
)
data class DbTest(
        @Type(type = "values-array")
        @Column(name = "my_values", columnDefinition = "varchar[]")
        val myValues: Array<Values>
)

enum class Values {
       VAL1, VAL2
}

This is working like a charm and I can map my array to a list and vice versa quite easily, which is ok.

Hoping this will help someone someday ;)

answered Sep 11, 2020 at 7:22

sjahan

5,9804 gold badges24 silver badges46 bronze badges

1 Comment

Strinder Over a year ago

This is no longer possible with Hibernate >6 since TypeDef has been removed (see answer above)

Oleg Bolden · Accepted Answer · 2024-06-04 14:34:39Z

I'm just posting one more example for those who work with Kotlin and Hibernate > 6.

Recently I've migrated from Hibernate 5 to Hibernate 6 and had to ajust declarations for updated io.hypersistence:hypersistence-utils-hibernate-63:3.7.5

Namelly, I have Kotlin enum Causer which is mapped to simple postgres string array text[]:

...
    @Type(
        value = ListArrayType::class,
                parameters = [
            Parameter(
                name = EnumArrayType.SQL_ARRAY_TYPE,
                value = "text"
            )
        ]
    )
    @Column(
        name = "causer_list",
        columnDefinition = "text[]"
    )
    var causerList: List<Causer>? = null
...

I hope this will be usefull for someone else.

Anton Sakhno · Accepted Answer · 2024-08-01 10:33:51Z

0

Hibernate 6+ plain solution:

...
@JdbcTypeCode(SqlTypes.ARRAY)
@Enumerated(EnumType.STRING)
val statusGroup: List<Status>? = null
...

enum class Status {
    COMPLETED, DECLINED, PENDING
}

Or omit Enumerated if you are ok with enum ordinal in DB

answered Aug 1, 2024 at 10:33

Anton Sakhno

194 bronze badges

1 Comment

sankranti Nov 16 at 15:27

This does not work. It just creates "varchar array" column instead of "status_enum []".

Anton Tsyrkunou · Accepted Answer · 2024-12-17 14:54:29Z

0

For Hibernate version > 6 just add

@JdbcTypeCode(SqlTypes.ARRAY) List first;

@JdbcTypeCode(SqlTypes.ARRAY) List second;

answered Dec 17, 2024 at 14:54

Anton Tsyrkunou

1

1 Comment

Mark Rotteveel Dec 17, 2024 at 15:17

When answering older questions, please make sure you provide either a novel solution, or a significantly better explanation than existing answers. If an existing answer already has your preferred solution, consider upvoting that answer.

Collectives™ on Stack Overflow

Hibernate mapping between Postgres array of varchar and a Java/Kotlin collection of enum

6 Answers 6

Comments

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related