how to use EnumCodec in r2dbc-postgresql

Question

I am trying to use EnumCodec from the latest version of r2dbc-postgresql (0.8.4) unsuccessfully, and I wondered if you could help me.

I use also spring-data-r2dbc version 1.1.1.

I took the exact example from the GitHub and created an enum type “my_enum” in my Postgres, and a table “sample_table” which contains ‘name’ (text) and ‘value’ (my_enum).

Then I did as in the example:

SQL:

CREATE TYPE my_enum AS ENUM ('FIRST', 'SECOND');

Java Model:

enum MyEnumType {
  FIRST, SECOND;
}

Codec Registration:

PostgresqlConnectionConfiguration.builder()
.codecRegistrar(EnumCodec.builder().withEnum("my_enum", MyEnumType.class).build());

I use DatabaseClient in order to communicate with the DB. I tried to insert using 2 methods:

databaseClient.insert().into(SampleTable.class)
.using(sampleTable).fetch().rowsUpdated();

or:

databaseClient.insert().into("sample_table")
.value("name", sampleTable.getName())
.value("value", sampleTable.getValue())
.then();

where SampleTable is:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table("sample_table")
@JsonIgnoreProperties(ignoreUnknown = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
public class SampleTable implements Serializable {
   private String name;
   @Column("value")
   @JsonProperty("value")
   private MyEnumType value;
}

But I get the same error using both:

column "value" is of type my_enum but expression is of type character varying

Can you please help me understand what I did wrong, or refer me to some working example? I appreciate your help!

mp911de mp911de · Accepted Answer · 2020-07-22T13:22:35

Spring Data considers enum values as values to be converted to String by default. You need to register a Converter that retains the type by writing the enum-type as-is.

@WritingConverter
class MyEnumTypeConverter implements Converter<MyEnumType, MyEnumType> {
    @Override
    public MyEnumType convert(MyEnumType source) {
        return source;
    }
}

Next, you need to register the converter. If you're using Spring Data R2DBC's AbstractR2dbcConfiguration, then override getCustomConverters():

class MyConfiguration extends AbstractR2dbcConfiguration {

    @Override
    protected List<Object> getCustomConverters() {
        return Collections.singletonList(new MyEnumTypeConverter());
    }

  // …
}

Alternatively, if you configure DatabaseClient standalone, then you need a bit more of code:

PostgresqlConnectionConfiguration configuration = PostgresqlConnectionConfiguration.builder()
        .codecRegistrar(EnumCodec.builder().withEnum("my_enum", MyEnumType.class).build())
        .host(…)
        .username(…)
        .password(…)
        .database(…).build();

R2dbcDialect dialect = PostgresDialect.INSTANCE;
DefaultReactiveDataAccessStrategy strategy = new DefaultReactiveDataAccessStrategy(dialect, Collections.singletonList(new MyEnumTypeConverter()));

DatabaseClient databaseClient = DatabaseClient.builder()
        .connectionFactory(new PostgresqlConnectionFactory(configuration))
        .dataAccessStrategy(strategy)
        .build();

However, there are two bugs in the R2DBC driver that prevent Spring Data from working as expected:

As temporary workaround, you can duplicate EnumCodec in your codebase and apply the fix from #302 until a new release of R2DBC Postgres is available.

how to use EnumCodec in r2dbc-postgresql

2 Answers