spring-data-jpa 使用Spring Data jpa保存嵌套JSON

9w11ddsr 于 2022-11-10 发布在 Spring

关注(0)|答案(3)|浏览(520)

我想通过从头开始创建Entity类和存储库来保存MYSQL DB中的数据。我能够保存普通的字符串数据、整数数据，但难以保存复杂的JSON数据
例如：

[
    {
        "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" }
            ]
    }
]

我如何在MYSQL数据库中存储这样的JSON？我应该为每个嵌套元素创建类吗？

spring-data-jpa

来源：https://stackoverflow.com/questions/73448963/save-nested-json-using-spring-data-jpa

3条答案

按热度按时间

kzipqqlq1#

（我会考虑切换到NoSQL DB而不是MySQL，但好吧...）

//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;

赞(0）回复(0）举报 2022-11-10

yk9xbfzb2#

在 MySQL 中存储 JSON 是可能的。你可以根据列的大小使用这 3 种列类型。
对于实体类：

@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;

}

中的每一个
对于控制器方法：

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

格式
对于存储库类：

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

格式
在 MYSQL 中，它看起来像这样：

赞(0）回复(0）举报 2022-11-10

yvt65v4c3#

这取决于您是要将JSON存储为字符串，还是要将其转换为Map到实体的DTO示例，并使用存储库将其保存到数据库？如果您要将JSON存储为字符串，则它应该与任何其他字符串没有任何区别。如果您要将其存储为实体，则需要转换JSON（反序列化）到您的DTO中，然后将它们作为常规DTO使用。它们是如何创建的并不重要。我刚刚回答了非常类似的问题。请参见此处

赞(0）回复(0）举报 2022-11-10

我来回答

spring-data-jpa 使用Spring Data jpa保存嵌套JSON

3条答案

相关问题

热门标签

最新问答