Spring Data R2DBC custom conversions does not work with json array

Question

I'm trying to get custom conversions of Spring Data R2DBC working to deal json_array fields.

I have a MySQL table created by the SQL below.

create table prefecture
(
    id         bigint unsigned auto_increment,
    region_ids json       not null,
    created_at datetime   not null default current_timestamp,
    updated_at datetime   not null default current_timestamp on update current_timestamp,
    constraint prefecture_pk
        primary key (id)
);

--- initial data
INSERT INTO prefecture
VALUES (null, "1", "TEST", json_array(1), now(), now());

Then, I defined an entity which corresponds that table like

data class Prefecture(
    @Id
    val id: Int? = null,
    val regionIds: RegionIds,
)

data class RegionIds(
    val list: List<Int>
)

According to this doc, I found that registering a custom converter which corresponds a source type and a target type is needed, so I implemented a converter and register it like below.

@Configuration
class R2dbcConfiguration {
    @Bean
    fun customConversions(): R2dbcCustomConversions {
        val converters: MutableList<Converter<*, *>?> = ArrayList()
        converters.add(RegionIdsReadConverter())
        return R2dbcCustomConversions(converters)
    }
}

# guessing Row is not appropriate type to source?
@ReadingConverter
class RegionIdsReadConverter : Converter<Row, RegionIds> {
    override fun convert(source: Row): RegionIds {
        val regionIdsString = source.get("region_ids", String::class.java)
        val mapper = ObjectMapper()
        return RegionIds(mapper.readValue(regionIdsString, object : TypeReference<List<Int>>() {}))
    }
}

Then, I tried to get data via ReactiveCrudRepository, but I got the error:

Could not read property private final jp.foo.bar.models.test.RegionIds jp.foo.bar.models.test.Prefecture.regionIds from column region_ids!
at org.springframework.data.r2dbc.convert.MappingR2dbcConverter.readFrom(MappingR2dbcConverter.java:177) ~[spring-data-r2dbc-1.2.8.jar:1.2.8]
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):
    |_ checkpoint ⇢ Handler jp.foo.bar.controllers.PrefectureController#getAllPrefectures() [DispatcherHandler]

Also, I tried to use Custom Codecs of r2dbc-mysql, however, it is not unable because the fix related to this feature is not released yet.

I'm using

• MySQL 5.7
• spring-boot-starter-data-r2dbc
• r2dbc-mysql 0.8.2.RELEASE

Am I missing or misunderstanding something?

Answer 1

Finally, I found workaround of this issue.

First, I removed RegionIdsReadConverter(which is corresponded to field of Prefecture entity) and added a converter corresponds to Prefecture(entity itself).

@ReadingConverter
class PrefectureReadConverter : Converter<Row, Prefecture> {
    override fun convert(source: Row): Prefecture {
        val id = source.get("id", Long::class.java)
        val regionIdsString = source.get("region_ids", String::class.java)
        val mapper = ObjectMapper()
        val regionIds = mapper.readValue(regionIdsString, object : TypeReference<List<Int>>() {})
        return Prefecture(id = id?.toInt(), code = code!!, name = name!!, regionIds = regionIds)
    }
}

Also, I modified the definition of the Prefecture entity slightly.

data class Prefecture(
    @Id
    val id: Int? = null,
    val regionIds: List<Int>, // eliminate wrapper class RegionIds because it's not necessary anymore
)

Then, I changed the way of registering converters to:

@Configuration
class R2dbcConfiguration(
    private val connectionFactory: ConnectionFactory
) : AbstractR2dbcConfiguration() {
    override fun connectionFactory() = connectionFactory
    override fun getCustomConverters() = mutableListOf<Any>(PrefectureReadConverter())
}

If you implement the way that I posted on my question, you may see errors:

org.springframework.data.mapping.MappingException: Could not read property private final boolean xxx.yyy from column zzz!
Caused by: org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.lang.Byte] to type [boolean]

According to this, it seems that predefined converters for r2dbc dialects will not be available if you don't inherit AbstractR2dbcConfiguration class when registering custom converters.

To persist entities, I also had to implement WritingConverter:

@WritingConverter
class PrefectureWriteConverter : Converter<Prefecture, OutboundRow> {
    override fun convert(source: Prefecture): OutboundRow {
        val row = OutboundRow()
        with(row) {
            put("id", Parameter.fromOrEmpty(source.id?.toLong(), Long::class.java))
            put("region_ids", Parameter.from(ObjectMapper().writeValueAsString(source.regionIds)))
        }
        return row
    }
}

and registered it.