JPA JsonNode MySQL JSON Converter

How to implement a Spring Boot domain object / JPA entity that handles Stripe event webhook callback, using a custom @Convert to save to a MySQL database as a json type column.

(That should cover the buzzwords, Google bait and scare off anyone who doesn’t care.)

Introduction

I have a web app that was being updated to use the Stripe webhooks Payment API (https://stripe.com/docs/webhooks).

The app uses Spring Boot, JPA and MySQL. The approach was to store the event webhook callbacks in the database as they are received.

There is a Stripe Java library (https://github.com/stripe/stripe-java) that can be used for the event webhook callback requests. For many people this is a viable and probably better option. However I did not need access to the complex data objects and would rather deal with fewer dependencies and roll my own domain/entity. Caveat emptor etc.

The event webhook callbacks (https://stripe.com/docs/api/events) are JSON payloads with consistent simple attributes and two complex attribute objects, data and request.

Approach

The approach was to deserialize the JSON request via Jackson to a single domain object, storing the data and request objects as JsonNode java objects and json types in MySQL.

By maintaining the JSON structure, the domain model could be simplified to one object handling all the possible types of event webhook callback requests, while the JSON data still being available to the application in the Java domain and through SQL-JSON queries.

Trade-offs and Considerations

Now is a good time to discuss trade offs and considerations.

The MySQL JSON type is not as fast as more traditional datatypes. Performance will be slow when querying a JSON type column vs a more traditional table with tuned indexes. (I don’t care if that hurts the feelings of team NoSQL, it’s true.)

The query syntax for the JSON type is awkward. If extensive queries are planned for the data, be sure to test those queries for performance. For this application, these drawbacks were not a concern.

The Stripe Java libraries appear to be well engineered and cover all the subtleties of the event webhook callback requests. If you find yourself using the event object for complex business logic, it might be a better approach to use the Stripe Java libraries.

Details

The API for the event webhook callback request object definition:
https://stripe.com/docs/api/events/object

This event object defines the Java domain entity object and the MySQL database table.

Hibernate Types Library

One of the challenges was finding ‘prior art’ or implementations mapping from the Java JsonNode object in the domain entity to MySQL database json type. I assumed that this was a solved problem.

Many of the searches lead to Stack Overflow posts where the solution was to use the Hibernate Types library (https://github.com/vladmihalcea/hibernate-types).

I added the dependency, Types and configuration, but was unable to resolve the following error:

SqlExceptionHelper: SQL Error: 3144, SQLState: 22001
SqlExceptionHelper: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

While I am sure it works for some and I am sure I made some dumb error somewhere, I did not end up using the Hibernate Types library.

Implemented Solution

The implemented solution was a simple custom converter, applying the converter to the Java domain entity object with the @Convert annotation.

Java Domain Entity Object

The first step is to define the event webhook callback request object. The object is a pretty typical class, the one thing to note are the @Convert(converter = JsonNodeConverter.class) annotations.

// Imports ommitted

/**
 *
 */
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
@Entity
@Table(name = "WEBHOOK_EVENT")
public class WebhookEvent
{
  @JsonProperty("id")
  @Id
  @Column(name = "ID")
  private String id;

  @JsonProperty("object")
  @Column(name = "OBJECT")
  private String object;

  @JsonProperty("api_version")
  @Column(name = "API_VERSION")
  private String apiVersion;

  @JsonProperty("created")
  @Column(name = "CREATED")
  private Long created;

  @JsonProperty("data")
  @Column(name = "DATA")
  @Convert(converter = JsonNodeConverter.class)
  private JsonNode data;

  @JsonProperty("livemode")
  @Column(name = "LIVEMODE")
  @Type(type = "org.hibernate.type.NumericBooleanType")
  private boolean livemode;

  @JsonProperty("pending_webhooks")
  @Column(name = "PENDING_WEBHOOKS")
  private Integer pendingWebhooks;

  @JsonProperty("request")
  @Column(name = "REQUEST")
  @Convert(converter = JsonNodeConverter.class)
  private JsonNode request;

  @JsonProperty("type")
  @Column(name = "TYPE")
  private String type;

  // Constructors and accessors ommitted

}

AttributeConverter Implementation

The JsonNodeConverter class implements AttributeConverter, specifically the methods convertToDatabaseColumn() and convertToEntityAttribute(). These two methods do the conversion between the domains, to/from JsonNode and String. The String is then inserted into the MySQL json column.

package com.gordonturner.app.converter;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.persistence.AttributeConverter;

public class JsonNodeConverter implements AttributeConverter<JsonNode, String>
{
  private static final Logger logger = LoggerFactory.getLogger( JsonNodeConverter.class );

  /**
   * @param jsonNode
   * @return
   */
  @Override
  public String convertToDatabaseColumn(JsonNode jsonNode)
  {
    if( jsonNode == null)
    {
      logger.warn( "jsonNode input is null, returning null" );
      return null;
    }

    String jsonNodeString = jsonNode.toPrettyString();
    return jsonNodeString;
  }

  /**
   * @param jsonNodeString
   * @return
   */
  @Override
  public JsonNode convertToEntityAttribute(String jsonNodeString) {

    if ( StringUtils.isEmpty(jsonNodeString) )
    {
      logger.warn( "jsonNodeString input is empty, returning null" );
      return null;
    }

    ObjectMapper mapper = new ObjectMapper();
    try
    {
      return mapper.readTree( jsonNodeString );
    }
    catch( JsonProcessingException e )
    {
      logger.error( "Error parsing jsonNodeString", e );
    }
    return null;
  }

}

Database

The database table is managed via liquibase, but the SQL create statement for the webhook table is:

CREATE TABLE `WEBHOOK_EVENT` (
  `ID` varchar(255) NOT NULL,
  `OBJECT` varchar(255) NOT NULL,
  `API_VERSION` varchar(20) NOT NULL,
  `CREATED` bigint(20) NOT NULL,
  `DATA` json DEFAULT NULL,
  `LIVEMODE` int(11) NOT NULL DEFAULT '0',
  `PENDING_WEBHOOKS` int(11) DEFAULT NULL,
  `REQUEST` json DEFAULT NULL,
  `TYPE` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The two records worth noting are DATA and REQUEST, which are of type `json`.

MySQL JSON Query

As a quick example, here is how to select object.status from the DATA column:

SELECT ID,
DATA->"$.object.status" AS `OBJECT`
FROM `WEBHOOK_EVENT`;

Conclusion

The discovery of how to map from a JsonNode in the Java domain to the json MySQL type was a bit of a slog.

I am satisfied with the result while also minimizing third party libraries. I like the idea of keeping the ‘complex’ JSON object intact while mapping the base simple response attributes.

Tagged Java | Posted on Thursday, May 7th, 2020 | Comments Off on JPA JsonNode MySQL JSON Converter