How to map a MySQL JSON column to a Java entity property using JPA and Hibernate

You don’t have to create all these types manually. You can simply get them via Maven Central using the following dependency:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-63</artifactId>
    <version>${hypersistence-utils.version}</version> 
</dependency> 

For more info, check out the Hibernate Types open-source project.

Now, to explain how it all works.

Assuming you have the following entity:

@Entity(name = "Book")
@Table(name = "book")
public class Book {
 
    @Id
    @GeneratedValue
    private Long id;
 
    @NaturalId
    private String isbn;
 
    @Type(JsonType.class)
    @Column(columnDefinition = "json")
    private String properties;
 
    //Getters and setters omitted for brevity
}

Notice two things in the code snippet above:

• the @Type is used to define a new custom Hibernate Type, json which is handled by the JsonType
• the properties attribute has a json column type, and it's mapped as a String

That's it!

Now, if you save an entity:

Book book = new Book();
book.setIsbn("978-9730228236");
book.setProperties(
    "{" +
    "   \"title\": \"High-Performance Java Persistence\"," +
    "   \"author\": \"Vlad Mihalcea\"," +
    "   \"publisher\": \"Amazon\"," +
    "   \"price\": 44.99" +
    "}"
);
 
entityManager.persist(book);

Hibernate is going to generate the following SQL statement:

INSERT INTO
    book 
(
    isbn, 
    properties, 
    id
) 
VALUES
(
    '978-9730228236', 
    '{"title":"High-Performance Java Persistence","author":"Vlad Mihalcea","publisher":"Amazon","price":44.99}',  
    1
)

And you can also load it back and modify it:

Book book = entityManager
    .unwrap(Session.class)
    .bySimpleNaturalId(Book.class)
    .load("978-9730228236");
     
book.setProperties(
    "{" +
    "   \"title\": \"High-Performance Java Persistence\"," +
    "   \"author\": \"Vlad Mihalcea\"," +
    "   \"publisher\": \"Amazon\"," +
    "   \"price\": 44.99," +
    "   \"url\": \"https://www.amazon.com/High-Performance-Java-Persistence-Vlad-Mihalcea/dp/973022823X/\"" +
    "}"
);

Hibernate taking caare of the UPDATE statement for you:

SELECT  b.id AS id1_0_
FROM    book b
WHERE   b.isbn = '978-9730228236'
 
SELECT  b.id AS id1_0_0_ ,
        b.isbn AS isbn2_0_0_ ,
        b.properties AS properti3_0_0_
FROM    book b
WHERE   b.id = 1    
 
UPDATE
    book 
SET
    properties = '{"title":"High-Performance Java Persistence","author":"Vlad Mihalcea","publisher":"Amazon","price":44.99,"url":"https://www.amazon.com/High-Performance-Java-Persistence-Vlad-Mihalcea/dp/973022823X/"}'
WHERE
    id = 1

All code is available on GitHub.

For anyone can't make @J. Wang answer work :

Try add this dependency(it's for hibernate 5.1 and 5.0, other version check here)

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-5</artifactId>
    <version>1.2.0</version>
</dependency>

And add this line to the entity

@TypeDef(name = "json", typeClass = JsonStringType.class)

So full version of the entity class :

@Entity
@Table(name = "some_table_name")
@TypeDef(name = "json", typeClass = JsonStringType.class)
public class MyCustomEntity implements Serializable {

   private static final long serialVersionUID = 1L;

   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;

   @Type( type = "json" )
   @Column( columnDefinition = "json" )
   private List<String> jsonValue;
}

I test the code with spring boot 1.5.9 and hibernate-types-5 1.2.0 .

If the values inside your json array are simple strings you can do this:

@Type( type = "json" )
@Column( columnDefinition = "json" )
private String[] jsonValue;

Heril Muratovic's answer is good, but I think the JsonToMapConverter should implement AttributeConverter<Map<String, Object>, String>, not AttributeConverter<String, Map<String, Object>>. Here is the code that works for me

@Slf4j
@Converter
public class JsonToMapConverter implements AttributeConverter<Map<String, Object>, String> {
    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Object> convertToEntityAttribute(String attribute) {
        if (attribute == null) {
            return new HashMap<>();
        }
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            return objectMapper.readValue(attribute, HashMap.class);
        } catch (IOException e) {
            log.error("Convert error while trying to convert string(JSON) to map data structure.", e);
        }
        return new HashMap<>();
    }

    @Override
    public String convertToDatabaseColumn(Map<String, Object> dbData) {
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            return objectMapper.writeValueAsString(dbData);
        } catch (JsonProcessingException e) {
            log.error("Could not convert map to json string.", e);
            return null;
        }
    }
}

In Kotlin, the following variation/combination of the above suggestions worked for me:

@Entity
@Table(name = "product_menu")
@TypeDef(name = "json", typeClass = JsonStringType::class)
data class ProductMenu(

    @Type(type = "json")
    @Column(name = "menu_json", columnDefinition = "json")
    @Convert(attributeName = "menuJson", converter = JsonToMapConverter::class)
    val menuJson: HashMap<String, Any> = HashMap()

) : Serializable



import com.fasterxml.jackson.core.JsonProcessingException
import com.fasterxml.jackson.databind.ObjectMapper
import org.slf4j.LoggerFactory
import java.io.IOException
import javax.persistence.AttributeConverter

class JsonToMapConverter : AttributeConverter<String, HashMap<String, Any>> {

    companion object {
        private val LOGGER = LoggerFactory.getLogger(JsonToMapConverter::class.java)
    }

    override fun convertToDatabaseColumn(attribute: String?): HashMap<String, Any> {
        if(attribute == null) {
            return HashMap()
        }
        try {
            val objectMapper = ObjectMapper()
            @Suppress("UNCHECKED_CAST")
            return objectMapper.readValue(attribute, HashMap::class.java) as HashMap<String, Any>
        } catch (e: IOException) {
            LOGGER.error("Convert error while trying to convert string(JSON) to map data structure.")
        }
        return HashMap()
    }

    override fun convertToEntityAttribute(dbData: HashMap<String, Any>?): String? {
        return try {
            val objectMapper = ObjectMapper()
            objectMapper.writeValueAsString(dbData)
        } catch (e: JsonProcessingException) {
            LOGGER.error("Could not convert map to json string.")
            return null
        }
    }
}

For anyone can't make @J. Wang answer work :

Try add this dependency

<dependency>
  <groupId>com.googlecode.json-simple</groupId>
  <artifactId>json-simple</artifactId>
  <version>1.1.1</version>
</dependency>

Creating converter

JSONObjectConverter.java

@Converter(autoApply = true)
public class JSONObjectConverter implements AttributeConverter<JSONObject, String> {

 private static final Logger logger = (Logger) LoggerFactory.getLogger(JSONArrayConverter.class);

 @Override
 public String convertToDatabaseColumn(JSONObject obj)
 {
    String data = null;
    try
    {
        data = obj.toString();
    }
    catch (final Exception e)
    {
        logger.error("JSON writing error", e);
    }

    return data;
 }

 @Override
 public JSONObject convertToEntityAttribute(String data)
 {
    JSONObject obj = null;

    try
    {
        Object temp = JSONValue.parse(data);
        obj = (JSONObject) temp ;
    }
    catch (final Exception e)
    {
        logger.error("JSON reading error", e);
    }

    return obj;
 }
}

Part of domain (entity-mapping) class

...

@Lob
@Column(name = "pano_data", columnDefinition = "JSON", nullable = true)
@Convert(converter = JSONObjectConverter.class)
private JSONObject panoData;

...

This solution perfectly works for me.