java - 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.

Answer 1

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());
    }
}