JDBC - Is there support for array of enums on postgres?

[rafaelrenanpacheco]

I have the following enum in a postgres database and the respective Java enum:

CREATE TYPE modality AS ENUM (
  'FOOTBALL',
  'VOLLEYBALL'
);

public enum Modality {

    FOOTBALL,
    VOLLEYBALL;
}

And an entity that have an array of modalities:

CREATE TABLE court (
  id UUID PRIMARY KEY,
  modalities modality[] NOT NULL
);

@Getter
@Setter
@MappedEntity
public class Court {

    @Id
    private UUID id;

    @MappedProperty(definition = "modality[]", type = DataType.OBJECT)
    private Modality[] modalities;
}

The problem happens when trying to persist this entity, I'm getting errors like this:

Caused by: org.postgresql.util.PSQLException: Cannot cast an instance of [Lcom.temp.court.Modality; to type Types.ARRAY
        at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1059)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
        at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setValue(JdbcQueryStatement.java:183)

Removing the @MappedProperty results in another error:

Caused by: org.postgresql.util.PSQLException: ERROR: malformed array literal: "FOOTBALL"
  Detail: Array value must start with "{" or dimension information.
  Where: unnamed portal parameter $5 = '...'

Changing everything to Set instead of array gives another one:

Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.RegularEnumSet. Use setObject() with an explicit Types value to specify the type to use.
        at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1064)

From the Micronaut Data docs and this PR (#1501) it looks like enum is well supporter on Micronaut Data JDBC for postgres, but it is not clear if it works on array without having a custom attribute converter for every enum in the project. Is the entity configuration wrong in this example? I've tried a bunch of @MappedProperty combinations without any success as well.

[dstepanov]

Try to define a converter, see

https://github.com/micronaut-projects/micronaut-data/blob/2a9d77eec4da2a7bc150a6eaefdec48cd59967fd/data-jdbc/src/test/groovy/io/micronaut/data/jdbc/postgres/PostgresEnumSpec.groovy#L117C1-L128C2

[rafaelrenanpacheco]

A converter worked as expected, thanks! For reference, these was the changes required (and a few questions after it):

Entity

// ...

@MappedProperty(converter = ModalityConverter.class)
private Modality[] modalities;

Enum

@TypeDef(type = DataType.OBJECT)
public enum Modality {

// ...

Converter

@Singleton
public class ModalityConverter implements AttributeConverter<Modality[], Array> {

    @Override
    public Array convertToPersistedValue(Modality[] entityValue, ConversionContext context) {
        JdbcConversionContext jdbcConversionContext = (JdbcConversionContext) context;

        try {
            return jdbcConversionContext.getConnection().createArrayOf("modality", entityValue);
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to convert to database array", e);
        }
    }

    @Override
    public Modality[] convertToEntityValue(Array persistedValue, ConversionContext context) {
        try {
            String[] rawValues = (String[]) persistedValue.getArray();

            return Stream.of(rawValues)
                    .map(Modality::valueOf)
                    .toArray(Modality[]::new);
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to convert to java array", e);
        }
    }
}

I made a version of this using Object instead of the specific enum array, and it worked fine and in a generic way for the convertToPersistedValue method. The first parameter of createArrayOf, which represents the database type, I could get from the annotation's definition property on the entity.

The convertToEntityValue on the other hand was pain to make it generic when using Object, because I couldn't find a way to identify the underlying Java type Modality[] to make the stream toArray generic. Any hint on how to get the property type?

Also, if it is possible to make convertToEntityValue generic, wouldn't micronaut-data be able to provide this out of the box? This way micronaut-data will work with enum arrays without the need of writing custom converters.

And the @TypeDef on the enum seemed off because I'm already forcing the conversion using @MappedProperty(converter. It would be nice to not have to set that annotation on the enum because setting it to Object doesn't mean much when reading the code.

[dstepanov]

The convertToEntityValue on the other hand was pain to make it generic when using Object, because I couldn't find a way to identify the underlying Java type Modality[] to make the stream toArray generic. Any hint on how to get the property type?

Also, if it is possible to make convertToEntityValue generic, wouldn't micronaut-data be able to provide this out of the box? This way micronaut-data will work with enum arrays without the need of writing custom converters.

The ConversionContext should be also ArgumentConversionContext where you can access the type

And the @typedef on the enum seemed off because I'm already forcing the conversion using @MappedProperty(converter. It would be nice to not have to set that annotation on the enum because setting it to Object doesn't mean much when reading the code.

We still need the type to know which driver method to use to see the value. You can set the type in MappedProperty instead of the enum

[rafaelrenanpacheco]

Thanks @dstepanov!

I'll look into the ArgumentConversionContext to try to make it generic 👍

[rafaelrenanpacheco]

Got it working 🎉

I'm sure there are better ways to handle the generic/reflection stuff, and even some way to avoid those @SuppressWarnings, but its working as expected (and missing a lot of null checks):

@MappedProperty(definition = "modality", converter = EnumsConverter.class)
private Modality[] modalities;

@Singleton
public class EnumsConverter implements AttributeConverter<Object, Array> {

    @Override
    public Array convertToPersistedValue(Object entityValue, ConversionContext context) {
        JdbcConversionContext jdbcConversionContext = (JdbcConversionContext) context;
        String databaseType = context.getAnnotation(MappedProperty.class).stringValue("definition").orElse(null);

        try {
            return jdbcConversionContext.getConnection().createArrayOf(databaseType, (Object[]) entityValue);
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to convert to database array", e);
        }
    }

    @Override
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Object convertToEntityValue(Array persistedValue, ConversionContext context) {
        try {
            String[] rawValues = (String[]) persistedValue.getArray();
            ArgumentConversionContext<?> argumentContext = (ArgumentConversionContext<?>) context;
            Class<?> enumClass = argumentContext.getArgument().getType().getComponentType();

            return Stream.of(rawValues)
                    .map(value -> Enum.valueOf((Class<Enum>) enumClass, value))
                    .toArray(size -> (Enum[]) java.lang.reflect.Array.newInstance(enumClass, size));
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to convert from database array", e);
        }
    }
}