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 ```java org.springframework.boot spring-boot-starter-data-r2dbc ``` The MariaDB table is declared as: ```java 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: ```java @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. ```java public Mono incodeGETRequest( final Function uriBuilderFunction, final Class 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. ```java @Override public Mono registerInterviewResults(String interviewId) {#edeaea var token = "eyJh..."; Mono scoreDataMono = incodeWebClientHandler.incodeGETRequest(builder -> builder.path(SCORE_DATA_ENDPOINT) .queryParam("id", interviewId) .build(), JsonNode.class, token ); Mono 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. ```java @Slf4j @ReadingConverter @AllArgsConstructor public class JsonNodeReadingConverter implements Converter { 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. ```java @Slf4j @WritingConverter @AllArgsConstructor public class StringToJsonConverter implements Converter { 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. ```java @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> 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: ```java @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: ```sql SELECT id, JSON_VALUE(ocr_data,'$.name.fullName' ) as fullName FROM interview_response_data; ``` From my original blog posts here: [SpringKodierer](https://springkodierer.com/blog/spring-r2dbc-mariadb-and-json-columns/).

kappaj2

Spring R2DBC, MariaDB, and JSON columns

Join Our Newsletter. No Spam, Only the good stuff.