How to store an object in PostgreSQL as JSON with MyBatis (Mapper XML)

Thing you want to do

I want to store Object as JSONB type in PostgreSQL when Spring + Mybatis (SQL statement is defined by Mapper XML)

manner

This time I will cover two.

How to come up with

Once on the Java side, make it a String and cast it to JSONB in the SQL statement to be stored. Use com.fasterxml.jackson.databind.ObjectMapper for Object → String.

SQL caller

Object data = hoge(); //Data you want to store
ObjectMapper mapper = new ObjectMapper();
String objectJSON = mapper.writeValueAsString(data)
sqlClient.insert(objectJSON)

MapperXML

<insert id="insert">
    insert into hogetable (data) values (#{objectJSON}::jsonb);
</insert>

If the String is in JSON format, it will be stored.

A little worrisome

Speaking of working with this, it works, but if possible, I would like to keep it as an object in Java. It is cumbersome for the caller to bother to format it so that it is easy for the caller to use.

Therefore, on the Java side, keep the object as it is and convert it to JSON format in ** Mapper XML. ** This will solve the problems mentioned earlier.

How to make MyBatis side process in ObjectMapper

First, design the Mapper interface as follows. The point is that in order to execute com.fasterxml.jackson.databind.ObjectMapper.writeValueAsString in Mapper XML, ʻObjectMapper` is also undertaken.

Mapper interface

insert(@Param("objectMapper") ObjectMapper objectMapper, @Param("object") Object object);

The caller simply passes ʻObjectMapper` and the Object you want to store.

SQL caller

Object data = hoge(); //Data you want to store
ObjectMapper mapper = new ObjectMapper();
sqlClient.insert(mapper, data)

Mapper XML also uses the ** bind expression. ** **

MapperXML

<insert id="insert">
    <bind name="objectJSON" value="objectMapper.writeValueAsString(object)" />
    insert into hogetable (data) values ('${objectJSON}'::jsonb)
</insert>

In the bind expression, the value written in value is assigned to the variable specified by name. At this time, not only the value itself but also the ** expression can be written in the value. ** **

Subsequent sql statements can be referenced with $ {variable name}. However, $ {variable name} is just a replacement, so you need to enclose it in single quotes to make it a string (text type in PostgreSQL). Also, you shouldn't really use $ {}. It's just a replacement that makes it vulnerable to ** SQL injection. ** **

Actually, I should use the # {} that solves that problem, but I couldn't refer to it well ...

A little worrisome

After all, I achieved it because the purpose of this article is to move the Object → String mapping from the Java side to the Mybatis side.

However, since the caller prepares the mapper, I don't think it can be said that the processing has been completely transferred. If there is a change in the processing content on the Mybatis side, the Java side must also be changed. When trying to implement a change-resistant architecture, ** I don't want to rely on external connections. ** **

In that respect, it would be nice if Mybatis could prepare a mapper. If the caller can prepare the mapper, the caller only has to pass the data itself. If I can find a way to do that, I'll update it again.