How to store JSON file as a JSON datatype in mysql using Hibernate / Spring boot?

Question

I've tried several ways of storing a json file in a database but it ends up creating different columns for each entry.

I want to store it as a "json" type in a single column. Is it possible?

My json file.

users.json

[
  {
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "[email protected]",
    "address": {
      "street": "Kulas Light",
      "suite": "Apt. 556",
      "city": "Gwenborough",
      "zipcode": "92998-3874",
      "geo": {
        "lat": "-37.3159",
        "lng": "81.1496"
      }
    },
    "phone": "1-770-736-8031 x56442",
    "website": "hildegard.org",
    "company": {
      "name": "Romaguera-Crona",
      "catchPhrase": "Multi-layered client-server neural-net",
      "bs": "harness real-time e-markets"
    }
  },
...
]

It's a spring-boot application and I've the relevant controllers and services.

In my domain package. (Address and Company are Embeddable classes)

User.java

@Data
@AllArgsConstructor @NoArgsConstructor
@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String username;
    private String email;
    private String phone;
    private String website;

    @Embedded
    private Address address;

    @Embedded
    private Company company;
}

The main file (storing in the database)

TypeReference and ObjectMapper are from Jackson

@SpringBootApplication
public class JsondbApplication {
    public static void main(String[] args) {
        SpringApplication.run(JsondbApplication.class, args);
    }

    @Bean
    CommandLineRunner runner(UserService service) {
        return args -> {
            ObjectMapper mapper = new ObjectMapper();
            TypeReference<List<User>> reference = new TypeReference<List<User>>() {};
            InputStream stream = TypeReference.class.getResourceAsStream("/json/users.json");
            try {
                List<User> users = mapper.readValue(stream, reference);
                service.save(users);
                System.out.println("Saved!");
            } catch (Exception h) {
                System.out.println("Unable to save! " + h.getMessage());
            }
        };
    }

}

in mysql it creates different columns for id, name, username, ...

I want to store it in a single column as a json type using spring boot.

Please update your question with code you are using to save to database — Smile
– Smile, Commented Jan 28, 2020 at 5:07
Why do you want to store in single column? Wouldn't you want something like 2 columns - id and jsonObj? — Smile
– Smile, Commented Jan 28, 2020 at 5:27
I just want to know that can we store a complete JSON document as a JSON type ? — Ankit Sharma
– Ankit Sharma, Commented Jan 28, 2020 at 5:39

Smile · Accepted Answer · 2020-01-28 10:47:29Z

5

Your code reads json into list of User objects and persists to database. You need to write some custom logic to save it as json. There are multiple ways to do it.

You can do something like

1) Add another variable in User class say private String jsonData

2) In @PrePersist method, write serialization logic

3) Mark other attributes with @JsonInclude() - to include in Jackson @Transient - to ignore in the persistence in separate column. You might not want to add these annotations to id attribute as each json object will be stored against a specific id in database.

So, new attribute would be something like

@NonNull
@Column(columnDefinition = "JSON") // Only if Database has JSON type else this line can be removed
private String jsonData;

PrePersist:

@PrePersist
public void prePersist() {
    try {
        this.setJsonData(new ObjectMapper().writeValueAsString(this));
    } catch (JsonProcessingException e) {
        e.printStackTrace();
    }
}

EDIT: You might face issue with @Transient attributes being null in the @PrePersist method. In that case you can use a setter method. But you have to call this setter everytime any attribute is changed before the save call.

public void setJsonData(String jsonData) {
    // Method parameter jsonData is simply ignored
    try {
        this.jsonData = new ObjectMapper().writeValueAsString(this);
    } catch (JsonProcessingException e) {
        log.error(e.getMessage());
    }
}

edited Jan 28, 2020 at 10:47

answered Jan 28, 2020 at 5:54

Smile

4,1484 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ankit Sharma Over a year ago

using this creates "jsonData" as a "varchar". I want the whole document to be stored as "json" type dev.mysql.com/doc/refman/8.0/en/json.html

Smile Over a year ago

Try using @Column(columnDefinition = "JSON") for jsonData. Also, I have updated my answer to use @PrePersist, which is a cleaner solution.

Ankit Sharma Over a year ago

I faced the same issue today, for storing the data as json in datatype, database column needs to be of json type which is mapped with String type in code. I wrote a custom getter & setter for the field to convert object class to & from String using object mapper. Did not use pre persist. This answer was very useful to me. Thanks.

Raj Over a year ago

@AnkitSharma I have similar problem like you can you help me in this

Ankit Sharma Over a year ago

@X_R86 sure, please state your problem

Sachintha Dilshan · Accepted Answer · 2025-10-07 14:38:32Z

0

I did this by using the vladmihalcea library.

I added the

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-60</artifactId>
    <version>2.21.1</version>
</dependency>
to my pom.xml file.

2. Defined the attribute as follows

@Type(JsonType.class)
@Column(columnDefinition = "json")
List<String> images;

answered Oct 7 at 14:38

Sachintha Dilshan

94 bronze badges

Collectives™ on Stack Overflow

How to store JSON file as a JSON datatype in mysql using Hibernate / Spring boot?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related