Spring R2DBC, MariaDB, and JSON columns

Recently ran into a requirement to store JSON data in a MariaDB database column. With PostgreSQL, we have a lot of support already, but information on MariaDB (or MySQL) is very scarce.

After a bit of digging and lots and lots of reading, I managed to get the following going.
Using SpringBoot version 2.7.3, R2DBC as pulled in by the pom.xml

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>

The MariaDB table is declared as:

CREATE TABLE IF NOT EXISTS `customer`.`interview_response_data` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `interview_id` VARCHAR(45) NOT NULL,
  `score_data` JSON NULL,
  `ocr_data` JSON NULL,
  `score_overall_status` VARCHAR(45) NULL,
  `score_overall_value` DECIMAL(3,1) NULL,
  `score_reason_message` VARCHAR(1024) NULL,
  `score_needs_review_reason` VARCHAR(255) NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

The entity mapping is declared as:

@Table("interview_response_data")
@Data
@Builder
@ToString
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
@NoArgsConstructor
@AllArgsConstructor
public class InterviewResponseDataEntity {

     @Id
     @Column(name = "id", nullable = false)
     @EqualsAndHashCode.Include
     private Long id;

     @Column(name = "interview_id", nullable = false)
     private String interviewId;

     @Enumerated(EnumType.STRING)
     @Column(name = "score_overall_status")
     private IncodeScoreStatusEnum scoreOverallStatus;

     @Column(name = "score_data")
     private JsonNode scoreData;
...
}

The raw JSON data is coming from a call to Incode to retrieve customer OCR data.
A handy WebClient wrapper class will handle the GET call for us to retrieve different data structures.

     public <T> Mono<T> incodeGETRequest(
             final Function<UriBuilder, URI> uriBuilderFunction,
             final Class<T> modelImplementation,
             final String token) {
          return incodeWebClient
                  .get()
                  .uri(uriBuilderFunction)
                  .headers(httpHeaders -> httpHeaders.add("X-Incode-Hardware-Id", token))
                  .retrieve()
                  .onStatus(httpStatus -> (httpStatus.is4xxClientError() ||
                                  httpStatus.is5xxServerError() ||
                                  httpStatus.value() == 204),
                          clientResponse -> clientResponse.createException().flatMap(Mono::error)
                  )
                  .bodyToMono(modelImplementation);
     }

Now we can have a service class that will retrieve the OCR data for us if it is provided with an InterviewId. This interviewId is obtained from Incode when you start the customer process. Part of that response is also a token to be used with subsequent data calls.

 @Override
     public Mono<InterviewResponseDataEntity> registerInterviewResults(String interviewId) {#edeaea

          var token = "eyJh...";

          Mono<JsonNode> scoreDataMono = incodeWebClientHandler.incodeGETRequest(builder -> builder.path(SCORE_DATA_ENDPOINT)
                          .queryParam("id", interviewId)
                          .build(),
                  JsonNode.class,
                  token
          );

          Mono<JsonNode> ocrDataMono = incodeWebClientHandler.incodeGETRequest(builder -> builder.path(OCR_DATA_ENDPOINT)
                          .queryParam("id", interviewId)
                          .build(),
                  JsonNode.class,
                  token
          );

          return scoreDataMono.zipWith(ocrDataMono)
                  .flatMap(objects -> {
                       var scoreData = objects.getT1();
                       var ocrData = objects.getT2();

                       log.info("Score data : {}", scoreData);
                       log.info("OCR Data   : {}", ocrData);

                       var interviewResponseDataEntity = new InterviewResponseDataEntity();
                       interviewResponseDataEntity.setInterviewId(interviewId);
                       interviewResponseDataEntity.setOcrData(ocrData);
                       interviewResponseDataEntity.setScoreData(scoreData);
...
return interviewResponseDataRepository.save(interviewResponseDataEntity);

This will retrieve our JSON data, from which we can then unpack what is needed and write to the database.

Before we can call the persist, we need to write converter utility classes for the conversion to and from JsonNode.

Note the @ReadingConverter – this will be used to convert the stored data in the database column to a JsonNode.

@Slf4j
@ReadingConverter
@AllArgsConstructor
public class JsonNodeReadingConverter implements Converter<String, JsonNode> {

     private ObjectMapper objectMapper;

     @Override
     public JsonNode convert(String source) {

          if (!(StringUtils.hasLength(source) && StringUtils.hasText(source))) {
               log.debug("Database column is null");
               return null;
          }

          try {
               return objectMapper.readValue(source, JsonNode.class);
          } catch (Exception ex) {
               log.error("Error converting json column data from database", ex);
          }
          return null;
     }

}

Note the @WritingConverter. This will be used to convert the JsonNode to a String to be written to the database column.

@Slf4j
@WritingConverter
@AllArgsConstructor
public class StringToJsonConverter implements Converter<JsonNode, String> {

     private final ObjectMapper objectMapper;

     @Override
     public String convert(JsonNode source) {

          if (source == null) {
               return null;
          }
          String response = null;
          try {
               response = objectMapper.writeValueAsString(source);
          } catch (JsonProcessingException e) {
               log.error("Error parsing JsonNode to database column", e);
          }
          return response;
     }

}

The above converters need to be registered with Spring, so write a small utility class to handle this.

@Slf4j
@Configuration
@RequiredArgsConstructor
public class ReactiveMariaDBConfig extends AbstractR2dbcConfiguration {

     @Value("${spring.r2dbc.username}")
     private String userName;

     @Value("${spring.r2dbc.password}")
     private String password;

     @Value("${spring.r2dbc.url}")
     private String url;

     private final ObjectMapper objectMapper;

     @Bean
     @Override
     public @NonNull MariadbConnectionFactory connectionFactory() {

          //r2dbc:mariadb://127.0.0.1:3306/customer
          String host = url.substring(url.indexOf("//") + 2, url.lastIndexOf(":"));
          int port = Integer.parseInt(url.substring(url.lastIndexOf(":") + 1, url.lastIndexOf("/")));
          String database = url.substring(url.lastIndexOf("/") + 1);

          log.debug("Setting data connectionFactory [host: {}, port: {}, database: {}]", host, port, database);

          return MariadbConnectionFactory.from(MariadbConnectionConfiguration.builder()
                  .password(password)
                  .username(userName)
                  .database(database)
                  .host(host)
                  .port(port)
                  .build());
     }

     @Bean
     @Override
     public @NonNull R2dbcCustomConversions r2dbcCustomConversions() {

          List<Converter<?, ?>> converters = new ArrayList<>();
          converters.add(new JsonNodeReadingConverter(objectMapper));
          converters.add(new StringToJsonConverter(objectMapper));
          return new R2dbcCustomConversions(getStoreConversions(), converters);

     }
}

The AbstractR2dbcConfiguration class requires us to override the ConnectionFactory method, so this is extracting the spring.r2dbc property values and then creates the connection factory.
The real workhorse here is the R2dbcCustomerConversions class. Running with a break point we can see about 22 converters already registered in the getStoreConversions() call. This now adds our two custom converters to the list.

And that is how easy it is. The fact that you need to register these converters is hidden somewhere deep! With JPA you have the following option to register the custom converter:

  @Convert(converter = JsonNodeConverter.class)
  private JsonNode data;

This annotation, however, does not work with R2DBC at the moment. We have to do the custom registration class to get this to work.

All this work makes it possible to run JSON_VALUE select statements on the data we have in the DB. Super handy on massive JSON payloads that you want to analyze later.
Sample SQL:

SELECT id,
 JSON_VALUE(ocr_data,'$.name.fullName' ) as fullName
FROM interview_response_data;

From my original blog posts here:
SpringKodierer.