[JAVA] 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.

Recommended Posts

How to store an object in PostgreSQL as JSON with MyBatis (Mapper XML)
How to use Java enums (Enum) in MyBatis Mapper XML
How to store an object in PostgreSQL as JSON with MyBatis (Mapper XML)
Format XML in Java
How to pass an object to Mapper in MyBatis without arguments
How to pass an object to Mapper in MyBatis without arguments
How to use Java enums (Enum) in MyBatis Mapper XML
Mapping to a class with a value object in How to MyBatis
Convert ruby object to JSON format
How to store an object in PostgreSQL as JSON with MyBatis (Mapper XML)
JAVA object mapping library
19 Corresponds to object creation
How to pass an object to Mapper in MyBatis without arguments
Mapping to a class with a value object in How to MyBatis
What is an immutable object? [Explanation of how to make]
How to request a CSV file as JSON with jMeter
How to use the same Mapper class in multiple data sources with Spring Boot + MyBatis
How to use MyBatis Mapper annotation
[Problem solving] SAXReader: How to read an xml file with an external DTD file specified in an offline environment
How to crop an image with libGDX
[Java] Output the result of ffprobe -show_streams in JSON and map it to an object with Jackson
Android development, how to check null in the value of JSON object
How to assemble JSON directly in Jackson
An error occurred when executing a function from MyBatis with the OUT parameter set to CURSOR in PostgreSQL.
How to store data simultaneously in a model associated with a nested form (Rails 6.0.0)
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to insert all at once with MyBatis
[Java] How to substitute Model Mapper in Jackson
How to solve an Expression Problem in Java
[Rails] How to build an environment with Docker
How to build an executable jar in Maven
[Rails] How to use PostgreSQL in Vagrant environment
How to use git with the power of jgit in an environment without git commands
I want to return an object in CSV format with multi-line header & filter in Java
How to make an image partially transparent in Processing
How to implement UICollectionView in Swift with code only
How to sort in ascending / descending order with SQLite
How to call functions in bulk with Java reflection
How to switch Tomcat context.xml with WTP in Eclipse
How to push an app developed with Rails to Github
How to delete a new_record object built with Rails
How to use Z3 library in Scala with Eclipse
How to make an almost static page with rails
Understand how to use Swift's JSON Decoder in 3 minutes
Organized how to interact with the JDK in stages
How to output standard from an array with forEach
[How to insert a video in haml with Rails]
How to delete untagged images in bulk with Docker
How to store Rakuten API data in a table
How to use JDD library in Scala with Eclipse
How to query Array in jsonb with Rails + postgres
How to write an external reference key in FactoryBot
[Rails] How to display an image in the view
How to use the getter / setter method (in object orientation)
How to use JSON data in WebSocket communication (Java, JavaScript)
How to change a string in an array to a number in Ruby
How to get values in real time with TextWatcher (Android)
How to retrieve the hash value in an array in Ruby
How to store a string from ArrayList to String in Java (Personal)
What happened in "Java 8 to Java 11" and how to build an environment
How to specify db when creating an app with rails
How to use ArgumentMatchers such as Mockito's any () in Kotlin
How to make an app using Tensorflow with Android Studio
Serializing org.joda.time.DateTime to JSON in ISO 8601 format comes with milliseconds
What is an immutable object? [Explanation of how to make]
How to set up a proxy with authentication in Feign
How to register as a customer with Square using Tomcat
[Ruby] How to batch convert strings in an array to numbers
[Java] How to search for a value in an array (or list) with the contains method