如何将Json对象与嵌套数组(包括Json对象)持久化到sql数据库中的一个表?

Question

我用Spring写应用程序

这是我的json:(它是一个json对象数组)

[{"id" : 643419352,
"status" : "removed_by_user",
"url" : "https://www.olx.pl/d/oferta/opona-12-1-2-x-2-1-4-etrto-62-203-detka-CID767-IDHxILu.html",
"created_at" : "2020-11-27 10:46:07",
"activated_at" : "2020-12-11 12:41:12",
"valid_to" : "2020-12-17 15:38:10",
"title" : "opona 12 1/2 " x 2 1/4 etrto 62-203 + dętka",
"description" : "opona w bardzo dobrym stanie + dętka, rozmiar 12 1/2 x 2 1/4 , dętka z zaworem samochodowym",
"category_id" : 1655,
"advertiser_type" : "private",
"external_id" : null,
"external_url" : null,
"contact" : {
"name" : "Damazy",
"phone" : "501474399"
},
"location" : {
"city_id" : 10609,
"district_id" : 301,
"latitude" : "51.80178",
"longitude" : "19.43928"
},
"images" : [ {
"url" : "https://ireland.apollo.olxcdn.com:443/v1/files/efa9any4ryrb-PL/image;s=1000x700"
} ],
"price" : {
"value" : "9",
"currency" : "PLN",
"negotiable" : false,
"budget" : false,
"trade" : false
},
"salary" : null,
"attributes" : [ {
"code" : "state",
"value" : "used",
"values" : null
} ],
"courier" : null
}, {
"id" : 643435839,
"status" : "removed_by_user",
"url" : "https://www.olx.pl/d/oferta/opona-4-80-4-00-8-do-taczki-nowa-CID628-IDHxN3p.html",
"created_at" : "2020-11-27 11:53:47",
"activated_at" : "2020-11-27 11:54:36",
"valid_to" : "2020-12-17 15:38:07",
"title" : "opona 4.80/4.00 - 8 do taczki nowa!!!",
"description" : "opona do taczki, nowa, nigdy nie używana, stan idealny.rnrozmiar 4.80/4.00-8. rnopona do taczki, nowa, nigdy nie używana, stan idealny.rnrozmiar 4.80/4.00-8.",
"category_id" : 1636,
"advertiser_type" : "private",
"external_id" : null,
"external_url" : null,
"contact" : {
"name" : "Damazy",
"phone" : "501474399"
},
"location" : {
"city_id" : 10609,
"district_id" : 301,
"latitude" : "51.80178",
"longitude" : "19.43928"
},
"images" : [ {
"url" : "https://ireland.apollo.olxcdn.com:443/v1/files/qmvssagjnq1r2-PL/image;s=1000x700"
} ],
"price" : {
"value" : "9",
"currency" : "PLN",
"negotiable" : false,
"budget" : false,
"trade" : false
},
"salary" : null,
"attributes" : [ {
"code" : "state",
"value" : "new",
"values" : null
} ],
"courier" : null
}]

这是我的实体类广告:

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
public class Advert {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long ident;
private int id;
private String status;
private String url;
private String created_at;
private String activated_at;
private String valid_to;
private String title;
@Lob
private String description;
private int category_id;
private String advertiser_type;
private Long external_id;
private String external_url;
private String salary;
private String attributes;
private String courier;
@Embedded
private Location location;
@Embedded
private Contact contact;
@Embedded
private Price price;
private String images;

和saveads方法:

@RequestMapping("/saveadverts")
public String saveAdverts() throws IOException {
HttpEntity<String> requestEntity = entity.requestEntityProvider();
String url = "https://www.olx.pl/api/partner/adverts";
ResponseEntity<JsonNode> responseEntity = template.exchange(url, HttpMethod.GET, requestEntity, JsonNode.class);
String adverts = responseEntity.getBody().get("data").toString();
objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
try {
Advert[] array = objectMapper.readValue(adverts, Advert[].class);
for(Advert a : array) {
advertRepository.save(a);
}
} catch (Exception e) {
System.out.println(e);
}
return "index";
}

我想做的是解析json到实体对象，并将所有广告对象保存到sql数据库中的一个表。方法执行在这一行异常停止:

Advert[] array = objectMapper.readValue(adverts, Advert[].class);

我得到这个错误信息:

com.fasterxml.jackson.databind.exc。MismatchedInputException:无法从数组值(tokenJsonToken.START_ARRAY)中反序列化类型为java.lang.String的值at[来源:(StringReader);line: 24, column: 14](通过引用链:java.lang.Object[][0]->pl.vida.model.Advert["images"])

请注意"images"涉及json对象的嵌套数组。请帮忙，我花了一个星期的时间，但没有结果。由于

Answer 1

com.fasterxml.jackson. databindd . exe。MismatchedInputException:无法从数组值(token JsonToken.START_ARRAY)中反序列化java.lang.String类型的值[来源:(StringReader);line: 24, column: 14](通过引用链:java.lang.Object[][0]- pl.vida.model.Advert["images"])

你正在得到上面的异常，因为你试图将对象数组转换为字符串，这是不可能的。看到你的JSONimages&attributes是对象数组。

"images": [{ "url": "https://ireland.apollo.olxcdn.com:443/v1/files/qmvssagjnq1r2-PL/image;s=1000x700" }],
"attributes": [{ "code": "state", "value": "new", "values": null }]

和在你的Advert类你已经创建了images&attributes作为字符串类型

private String attributes;
private String images;

一般来说，对于array类型的对象，我们将字段设置为List或Set，如果字段是List/Set，那么我们需要为它们创建单独的类并映射为OneToMany关系。因此，创建单独的类意味着将创建单独的表，但你不希望有多个表。您希望将所有数据存储在单个表中。在正常情况下，这是不可能的，但如果我们编写一些额外的配置类，那么我们可以达到您的要求。这些调整是Hibernate自己提供的。

基本上，Hibernate提供了一些内置类型，如String, Integer, Float, Date, Timezone等。在这里，您可以查看内置类型的完整列表。但是根据我们的需求，我们也可以创建自定义类型。因此，为了存储array类型的数据，Hibernate没有提供任何内置类型。因此，我们将创建一个自定义类型。

解决方案:所以我们想要存储一个对象数据数组，我们可以很容易地将它存储在com.fasterxml.jackson.databind.JsonNode对象中。但是Hibernate不支持这个类作为字段类型。因此，为了支持这个类，我们需要编写2个额外的类，即JsonNodeStringType.java&JsonNodeStringDescriptor.

JsonNodeStringType.java

public class JsonNodeStringType extends AbstractSingleColumnStandardBasicType<JsonNode> implements DiscriminatorType<JsonNode> {
public static final JsonNodeStringType INSTANCE = new JsonNodeStringType();
public JsonNodeStringType() {
super(VarcharTypeDescriptor.INSTANCE, JsonNodeStringDescriptor.INSTANCE);
}
@Override
public String getName() {
return "JsonNode";
}
@Override
public JsonNode stringToObject(String xml) {
return fromString(xml);
}
@Override
public String objectToSQLString(JsonNode value, Dialect dialect) {
return ''' + toString(value) + ''';
}
}

JsonNodeStringDescriptor.java

public class JsonNodeStringDescriptor extends AbstractTypeDescriptor<JsonNode> {
public static final ObjectMapper mapper = new ObjectMapper();
public static final JsonNodeStringDescriptor INSTANCE = new JsonNodeStringDescriptor();
public JsonNodeStringDescriptor() {
super(JsonNode.class, ImmutableMutabilityPlan.INSTANCE);
}
@Override
public String toString(JsonNode value) {
try {
return mapper.writeValueAsString(value);
} catch (JsonProcessingException e) {
e.printStackTrace();
}
return null;
}
@Override
public JsonNode fromString(String string) {
try {
return mapper.readTree(string);
} catch (JsonProcessingException e) {
e.printStackTrace();
}
return null;
}
@Override
public <X> X unwrap(JsonNode value, Class<X> type, WrapperOptions options) {
if (value == null) {
return null;
}
if (String.class.isAssignableFrom(type)) {
return (X) toString(value);
}
throw unknownUnwrap(type);
}
@Override
public <X> JsonNode wrap(X value, WrapperOptions options) {
if (value == null) {
return null;
}
if (String.class.isInstance(value)) {
return fromString(value.toString());
}
throw unknownWrap(value.getClass());
}
}

现在我们的Advert类看起来就像

import org.hibernate.annotations.Type;
@Setter
@Getter
@ToString
@Entity
@Table(name = "advert")
@TypeDef(name = "JsonNode", typeClass = JsonNodeStringType.class)
public class Advert {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ident", unique = true, nullable = false)
private Long ident;
private int id;
private String status;
private String url;
private String created_at;
private String activated_at;
private String valid_to;
private String title;
@Lob
private String description;
private int category_id;
private String advertiser_type;
private Long external_id;
private String external_url;
private String salary;
private String courier;
@Embedded
private Location location;
@Embedded
private Contact contact;
@Embedded
private Price price;
@Type(type = "JsonNode")
private JsonNode images;
@Type(type = "JsonNode")
private JsonNode attributes;
}

开始。如果你执行下面的代码，它可以完美地工作。

String advertsString = "[ { "id": 643419352, "status": "removed_by_user", "url": "https://www.olx.pl/d/oferta/opona-12-1-2-x-2-1-4-etrto-62-203-detka-CID767-IDHxILu.html", "created_at": "2020-11-27 10:46:07", "activated_at": "2020-12-11 12:41:12", "valid_to": "2020-12-17 15:38:10", "title": "opona 12 1/2 \" x 2 1/4 etrto 62-203 + dętka", "description": "opona w bardzo dobrym stanie + dętka, rozmiar 12 1/2 x 2 1/4 , dętka z zaworem samochodowym", "category_id": 1655, "advertiser_type": "private", "external_id": null, "external_url": null, "contact": { "name": "Damazy", "phone": "501474399" }, "location": { "city_id": 10609, "district_id": 301, "latitude": "51.80178", "longitude": "19.43928" }, "images": [ { "url": "https://ireland.apollo.olxcdn.com:443/v1/files/efa9any4ryrb-PL/image;s=1000x700" } ], "price": { "value": "9", "currency": "PLN", "negotiable": false, "budget": false, "trade": false }, "salary": null, "attributes": [ { "code": "state", "value": "used", "values": null } ], "courier": null }, { "id": 643435839, "status": "removed_by_user", "url": "https://www.olx.pl/d/oferta/opona-4-80-4-00-8-do-taczki-nowa-CID628-IDHxN3p.html", "created_at": "2020-11-27 11:53:47", "activated_at": "2020-11-27 11:54:36", "valid_to": "2020-12-17 15:38:07", "title": "opona 4.80/4.00 - 8 do taczki nowa!!!", "description": "opona do taczki, nowa, nigdy nie używana, stan idealny.\r\nrozmiar 4.80/4.00-8. \r\nopona do taczki, nowa, nigdy nie używana, stan idealny.\r\nrozmiar 4.80/4.00-8.", "category_id": 1636, "advertiser_type": "private", "external_id": null, "external_url": null, "contact": { "name": "Damazy", "phone": "501474399" }, "location": { "city_id": 10609, "district_id": 301, "latitude": "51.80178", "longitude": "19.43928" }, "images": [ { "url": "https://ireland.apollo.olxcdn.com:443/v1/files/qmvssagjnq1r2-PL/image;s=1000x700" } ], "price": { "value": "9", "currency": "PLN", "negotiable": false, "budget": false, "trade": false }, "salary": null, "attributes": [ { "code": "state", "value": "new", "values": null } ], "courier": null } ]";
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
Advert[] adverts = objectMapper.readValue(advertsString, Advert[].class);
for (Advert advert : adverts) {
Advert saved = advertRepository.save(advert);
System.out.println("saved " + saved.getIdent());
}

我希望你的问题得到解决，你已经被困了一个星期。如果您不想手动创建这些类型的描述符，您可以按照本文的方法将其用作外部依赖项。