I want to save data in MYSQL DB by creating Entity class and repository from scratch. I am able to save the normal String Data, Integer Data but struggling to save complex JSON data's

for instance:

    [
    {
        "id": "0001",
        "type": "donut",
        "name": "Cake",
        "ppu": 0.55,
        "batters":
            {
                "batter":
                    [
                        { "id": "1001", "type": "Regular" },
                        { "id": "1002", "type": "Chocolate" },
                        { "id": "1003", "type": "Blueberry" },
                        { "id": "1004", "type": "Devil's Food" }
                    ]
            },
        "topping":
            [
                { "id": "5001", "type": "None" },
                { "id": "5002", "type": "Glazed" },
                { "id": "5005", "type": "Sugar" },
                { "id": "5007", "type": "Powdered Sugar" },
                { "id": "5006", "type": "Chocolate with Sprinkles" },
                { "id": "5003", "type": "Chocolate" },
                { "id": "5004", "type": "Maple" }
            ]
    },
    {
        "id": "0002",
        "type": "donut",
        "name": "Raised",
        "ppu": 0.55,
        "batters":
            {
                "batter":
                    [
                        { "id": "1001", "type": "Regular" }
                    ]
            },
        "topping":
            [
                { "id": "5001", "type": "None" },
                { "id": "5002", "type": "Glazed" },
                { "id": "5005", "type": "Sugar" },
                { "id": "5003", "type": "Chocolate" },
                { "id": "5004", "type": "Maple" }
            ]
    },
    {
        "id": "0003",
        "type": "donut",
        "name": "Old Fashioned",
        "ppu": 0.55,
        "batters":
            {
                "batter":
                    [
                        { "id": "1001", "type": "Regular" },
                        { "id": "1002", "type": "Chocolate" }
                    ]
            },
        "topping":
            [
                { "id": "5001", "type": "None" },
                { "id": "5002", "type": "Glazed" },
                { "id": "5003", "type": "Chocolate" },
                { "id": "5004", "type": "Maple" }
            ]
    }
]

How can I store such JSON's in MYSQL Db? Should I Create Class for every nested element ?

CodePudding user response：

(I would consider to switch to a NoSQL DB instead of MySQL, but okay...)

//1.

create table users_json(
id int auto_increment primary key,
details json);
2. 
public interface SomeRepository extends JpaRepository<AnyEntity, Long> {

@Modifying(clearAutomatically = true)
@Query(value = "insert into users_json (details) values (:param) ", nativeQuery = true)
@Transactional
int insertValue(@Param("param") String param);}
3. 
anyRepository.insertValue("{ \"page\": \"1\" , \"name\": \"Zafari\", \"os\": \"Mac\", \"spend\": 100, \"resolution\": { \"x\": 1920, \"y\": 1080 } }");
4. 
SELECT id, details->'$.name' FROM users_json;

CodePudding user response：

Storing JSON in MySQL is possible. You can use these 3 column types depending upon the column size.

For your Entity class :

@Entity
@Getter
@Setter
public class Test {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "LONGTEXT") // can store upto 4GB
    private String longText;

    @Column(columnDefinition = "MEDIUMTEXT") // can store upto 64MB
    private String mediumText;

    @Column(columnDefinition = "TEXT") // can store upto 64KB
    private String text;
    
}

For your Controller method :

   @PostMapping(value = "/addData")
    public void addData(@RequestBody String payload) {
        testRepository.addData(payload);
    }

For your Repository Class:

@Repository
public interface TestRepository extends JpaRepository<Test,Integer> {


    @Modifying
    @Transactional
    @Query(value = "INSERT INTO test(text,medium_text,long_text) VALUE(?1,?1,?1)" ,nativeQuery = true)
    void addData(String payload);
}

In MYSQL it will look like this,

CodePudding user response：

It depends if you want to store your Json as String or do you want to convert it into DTO instances that are mapped to your entities and use repository to save them to DB? If you want to store JSON as a String than It shouldn't be any different from any other String. If you want to store it as Entities than you need to convert your JSON (de-serialize) into your DTOs and then work with them as regular DTOs. It doesn't matter how they where created. I just answered very similar question. Please see here