Persisting a JSON Object using Hibernate and JPA

Question

I am trying to store a JSON object in MySQL database in spring boot. I know I am doing something wrong but I a can't figure out what it is because I am fairly new to Spring.

I have a rest endpoint where I get the following JSON object (via HTTP PUT) and I need to store it in database so that the user can fetch it later (via HTTP GET).

{
  "A": {
    "Name": "Cat",
    "Age": "1"
  },
  "B": {
    "Name": "Dog",
    "Age": "2"
  },
  "C": {
    "Name": "Horse",
    "Age": "1"
  }
}

Note that in the above case The number of keys in the object may vary, Due to that requirement I am using a HashMap to catch the object in the controller.

@RequestMapping(method = RequestMethod.POST)
    public String addPostCollection(@RequestBody HashMap<String, Animal> hp) {

        hp.forEach((x, y) -> {
            postRepository.save(hp.get(x));
        });

        return "OK";

    }

As you can see in the method, I can iterate the HashMap and persist each Animal object in db. But I am looking for a way to persist the entire HashMap in a single record. I have did some reading and they suggest me to use a @ManyToMany mapping.

Can anyone point me in a direction to persist the HashMap in a different way? (or is using the @ManyToMany the only and right way to do this?)

Finis

"entire HashMap in a single record" ?? Or entire hashmap in a single db transaction? – Boltzmann

I meant single record. – Finis

Answer 1

Maven dependency

The first thing you need to do is to set up the following Hibernate Types Maven dependency in your project pom.xml configuration file:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

Domain model

Let's assume you have the following entity:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    typeClass = JsonType.class, 
    defaultForType = JsonNode.class
)
public class Book {

    @Id
    @GeneratedValue
    private Long id;

    @NaturalId
    private String isbn;

    @Column(columnDefinition = "jsonb")
    private JsonNode properties;

    //Getters and setters omitted for brevity
}

Notice the @TypeDef is used to instruct Hibernate to map the JsonNode object using the JsonType offered by the Hibernate Types project.

Testing time

Now, if you save an entity:

Book book = new Book();
book.setIsbn( "978-9730228236" );
book.setProperties(
    JacksonUtil.toJsonNode(
        "{" +
        "   \"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:

Session session = entityManager.unwrap( Session.class );
 
Book book = session
    .bySimpleNaturalId( Book.class )
    .load( "978-9730228236" );
 
LOGGER.info( "Book details: {}", book.getProperties() );
 
book.setProperties(
    JacksonUtil.toJsonNode(
        "{" +
        "   \"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
 
-- Book details: {"price":44.99,"title":"High-Performance Java Persistence","author":"Vlad Mihalcea","publisher":"Amazon"}
 
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

Radioisotope

Thank you for the tip! However, can you make this work with H2 database which does not know any JSON type? I use Spring Boot and H2 for testing. Perhaps overriding H2 dialect somehow to treat JSON type as text? But did not figure how.. Thanks – Encincture

@VladMihalcea My integration tests do indeed use a MySQL database, but using MySQL for my JPA unit tests would be painful and absurd. – Houseraising

Answer 2

You can use FasterXML (or similar) to parse the Json into an actual object (you need to define the class) and use Json.toJson(yourObj).toString() to retrieve the Json String. It also simplifies working with the objects since your data class may also have functionality.

Luculent

Answer 3

Your JSON is well structered, so usually theres no need to persist the entire map in one single record. You won't be able to use the Hibernate/JPA query functions and a lot more.

If you really want to persist the entire map in one single record, you could persist the map in its string representation and, as already proposed, use a JSON parser like Jackson to rebuild your HashMap

@Entity
public class Animals {

  private String animalsString;

  public void setAnimalsString(String val) {
    this.animalsString = val;
  }

  public String getAnimalsString() {
    return this.animalsMap;
  }

  public HashMap<String, Animal> getAnimalsMap() {
    ObjectMapper mapper = new ObjectMapper();
    TypeReference<HashMap<String,Animal>> typeRef = new TypeReference<HashMap<String,Animal>>() {};
    return mapper.readValue(animalsString, typeRef); 
  }

}

Your animal class:

public class Animal {

  private String name;
  private int age;

  /* getter and setter */
  /* ... */
}

And you could change your controller method to

@RequestMapping(method = RequestMethod.POST)
public String addPostCollection(@RequestBody String hp) {
  Animals animals = new Animals();
  animals.setAnimalsString(hp);
  animalsRepository.save(hp);
  return "OK";
}

Vanhoose

I think this is the best option for me. Saving the Objects as string and map it to a Map. – Finis

Answer 4

One animal is one record. You are saving more records, not one record. You can commit more records in one transaction. See: How to persist a lot of entities (JPA)

Stewpan

Answer 5

I am using spring-boot 3.1.3 (hibernate 6.2.7) and the following worked for me:

import jakarta.persistence.*;
import lombok.*;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;

import java.sql.Timestamp;

@Getter
@Setter
@Table(name = "my_table")
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class MyTable  {
  @Column(name = "sequence_id")
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Id
  private Integer sequenceId;

    
  private Timestamp timestamp;

  @Column(name = "payload", columnDefinition = "jsonb")
  @JdbcTypeCode(SqlTypes.JSON)
  private MyJsonPayloadClass payload;

  private String name;


}

I also had to add this dependency to my pom.xml:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-60</artifactId>
    <version>2.21.1</version>
</dependency>

Corrigan