JPA JsonNode MySQL JSON Converter

tl;dr

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.)

Background and Approach

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.

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

Tested with the following versions:

  • Java Version 1.8.0_111
  • Spring Boot 2.2.0.RELEASE
  • Fasterxml Jackson Core 2.10.0
  • MySQL Server 5.7.29

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:

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.

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.

Database

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

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:

Conclusion and Commentary

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.

Leave a Reply

You must be logged in to post a comment.