R2DBC Postgres SQL Enum Issue

Question

As part of learning r2DBC i have come across an issue facing with Enum conversion. I am using PostgreSQL here. When reading data for Film where rating is PG-13 and NC-17( anything with dash ) i am facing issues.

Below is my schema for table

create table film
(
film_id          integer       default nextval('film_film_id_seq'::regclass) not null
    constraint film_pkey
        primary key,
title            varchar(255)                                                not null,
description      text,
release_year     year,
language_id      smallint                                                    not null
    constraint film_language_id_fkey
        references language
        on update cascade on delete restrict,
rental_duration  smallint      default 3                                     not null,
rental_rate      numeric(4, 2) default 4.99                                  not null,
length           smallint,
replacement_cost numeric(5, 2) default 19.99                                 not null,
rating           mpaa_rating   default 'G'::mpaa_rating,
last_update      timestamp     default now()                                 not null,
special_features text[]
);

And the mpaa_rating is defined as

create type mpaa_rating as enum ('G', 'PG', 'PG-13', 'R', 'NC-17');

This is my code which registers the converters in my Configuration

@Configuration
@EnableTransactionManagement
@EnableR2dbcRepositories
@EnableR2dbcAuditing
public class DVDRentalDBConfiguration extends AbstractR2dbcConfiguration {

@Bean
public ConnectionFactory connectionFactory() {
    System.out.println("Initializing postgreSQL connection factory");
    return new PostgresqlConnectionFactory(
            PostgresqlConnectionConfiguration.builder()
                    .host("localhost")
                    .database("dvdrental")
                    .username("postgres")
                    .password("postgres")
                    .codecRegistrar(EnumCodec.builder().withEnum("mpaa_rating", Rating.class).build())
                    .build()
    );
}

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

@Bean
ReactiveTransactionManager transactionManager(ConnectionFactory connectionFactory) {
    System.out.println("Initializing postgreSQL connection factory");
    return new R2dbcTransactionManager(connectionFactory);
}
}

My code for retrieving is pretty simple

 private Mono<FilmModel> getFilmById(Long id) {
    return filmRepository.findById(id).switchIfEmpty(Mono.error(DataFormatException::new));
}

Adding the exception which is thrown https://gist.github.com/harryalto/bd51bbcdd081868c5064c808d08205e4

I tried researching stack overflow but couldn't figure out the issue. Any help is greatly appreciated.

It would help if you add the exception. – lkatiforis Dec 19 '21 at 00:40 — lkatiforis, Dec 19 '21 at 00:40

score 0 · Accepted Answer · answered Dec 20 '21 at 07:29

0

If you are using Spring Boot/Spring Data R2dbc to map table to POJO, you can skip the enum definition in Postgres, by default Spring Data R2dbc will handle the enum as varchar/char in db side, and use Enum in java POJO, check my example, and schema sql script and mapped entity class. Spring Boot registered mapping converter to convert them automatically.

If you would like to handle the Enum type yourself, check this example.

answered Dec 20 '21 at 07:29

Hantsy

8,006
7
64
109

the issue i am facing is something to do with reverse lookup for enums which is the actual problem. I have already looked at the links you have shared( thanks for the details and examples). But i dont think this would resolve the problem i am facing. – Harry Dec 20 '21 at 22:05
Understand your satiation, you can try to create your own `EnumCodec` instead of the built-in one to resolve this issue. – Hantsy Dec 21 '21 at 12:35
I exactly did that and now able to resolve the issue. Thanks @Hantsy – Harry Dec 21 '21 at 21:25

R2DBC Postgres SQL Enum Issue

1 Answers1