Java 如何解决 H2 中的 JSON 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39620317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can solve JSON column in H2
提问by earandes
I use in application MySQL 5.7 and I have JSON columns. When I try running my integration tests don't work because the H2 database can't create the table. This is the error:
我在应用程序 MySQL 5.7 中使用，并且我有 JSON 列。当我尝试运行我的集成测试时不起作用，因为 H2 数据库无法创建表。这是错误：
2016-09-21 16:35:29.729 ERROR 10981 --- [ main] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: create table payment_transaction (id bigint generated by default as identity, creation_date timestamp not null, payload json, period integer, public_id varchar(255) not null, state varchar(255) not null, subscription_id_zuora varchar(255), type varchar(255) not null, user_id bigint not null, primary key (id))
2016-09-21 16:35:29.730 ERROR 10981 --- [ main] org.hibernate.tool.hbm2ddl.SchemaExport : Unknown data type: "JSON"; SQL statement:
This is the entity class.
这是实体类。
@Table(name = "payment_transaction")
public class PaymentTransaction extends DomainObject implements Serializable {
@Convert(converter = JpaPayloadConverter.class)
@Column(name = "payload", insertable = true, updatable = true, nullable = true, columnDefinition = "json")
private Payload payload;
public Payload getPayload() {
return payload;
}
public void setPayload(Payload payload) {
this.payload = payload;
}
}
And the subclass:
和子类：
public class Payload implements Serializable {
private Long userId;
private SubscriptionType type;
private String paymentId;
private List<String> ratePlanId;
private Integer period;
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public SubscriptionType getType() {
return type;
}
public void setType(SubscriptionType type) {
this.type = type;
}
public String getPaymentId() {
return paymentId;
}
public void setPaymentId(String paymentId) {
this.paymentId = paymentId;
}
public List<String> getRatePlanId() {
return ratePlanId;
}
public void setRatePlanId(List<String> ratePlanId) {
this.ratePlanId = ratePlanId;
}
public Integer getPeriod() {
return period;
}
public void setPeriod(Integer period) {
this.period = period;
}
}
And this converter for insert in database:
这个用于插入数据库的转换器：
public class JpaPayloadConverter implements AttributeConverter<Payload, String> {
// ObjectMapper is thread safe
private final static ObjectMapper objectMapper = new ObjectMapper();
private Logger log = LoggerFactory.getLogger(getClass());
@Override
public String convertToDatabaseColumn(Payload attribute) {
String jsonString = "";
try {
log.debug("Start convertToDatabaseColumn");
// convert list of POJO to json
jsonString = objectMapper.writeValueAsString(attribute);
log.debug("convertToDatabaseColumn" + jsonString);
} catch (JsonProcessingException ex) {
log.error(ex.getMessage());
}
return jsonString;
}
@Override
public Payload convertToEntityAttribute(String dbData) {
Payload payload = new Payload();
try {
log.debug("Start convertToEntityAttribute");
// convert json to list of POJO
payload = objectMapper.readValue(dbData, Payload.class);
log.debug("JsonDocumentsConverter.convertToDatabaseColumn" + payload);
} catch (IOException ex) {
log.error(ex.getMessage());
}
return payload;
}
}
采纳答案by toolforger
JSON support was added to H2 after the question was asked, with version 1.4.200 (2019-10-14).
提出问题后，H2 中添加了 JSON 支持，版本为 1.4.200 (2019-10-14)。
However, you rarely need a JSON data type in a database. JSON essentially is just a potentially very long string, so you can use CLOB which is available on most databases.
但是，您很少需要数据库中的 JSON 数据类型。JSON 本质上只是一个可能很长的字符串，因此您可以使用大多数数据库上都可用的 CLOB。
You do need the JSON data type if you need an SQL function that operates on them, and then only if the database insists that its JSON functions operate on a JSON type instead of on a CLOB. Such functions tend to be database-dependent though.
如果您需要对它们进行操作的 SQL 函数，那么您确实需要 JSON 数据类型，并且仅当数据库坚持其 JSON 函数对 JSON 类型而不是 CLOB 进行操作时才需要。不过，这些函数往往依赖于数据库。
回答by Olivier Garand
I have solved the problem using TEXT type in H2. One must create a separate database script to create schema in H2 for tests and replace the JSON type by TEXT.
我已经在 H2 中使用 TEXT 类型解决了这个问题。必须创建一个单独的数据库脚本来在 H2 中为测试创建模式，并用 TEXT 替换 JSON 类型。
It is still a problem since if you use Json function in queries, you will not be able to test those while with H2.
这仍然是一个问题，因为如果您在查询中使用 Json 函数，您将无法在使用 H2 时测试它们。
回答by n00dle
I just came across this problem working with the JSONBcolumn type - the binary version of the JSONtype, which doesn't map to TEXT.
我刚刚在使用JSONB列类型时遇到了这个问题- 类型的二进制版本JSON，它没有映射到TEXT.
For future reference, you can define a custom type in H2 using CREATE DOMAIN, as follows:
为了将来参考，您可以在 H2 中使用 定义自定义类型CREATE DOMAIN，如下所示：
CREATE domain IF NOT EXISTS jsonb AS other;
This seemed to work for me, and allowed me to successfully test my code against the entity.
这似乎对我有用，并允许我针对实体成功测试我的代码。
Source: https://objectpartners.com/2015/05/26/grails-postgresql-9-4-and-jsonb/
来源：https: //objectpartners.com/2015/05/26/grails-postgresql-9-4-and-jsonb/
回答by Alex
H2 does not have the JSON data type.
H2 没有 JSON 数据类型。
In MySQL the JSON type is just an alias for the LONGTEXT data type so the actual data type for the column will be LONGTEXT.
在 MySQL 中，JSON 类型只是 LONGTEXT 数据类型的别名，因此列的实际数据类型将是 LONGTEXT。
回答by madz
In my case we were dealing with PostgreSQLjsonbtype in production and H2for our tests.
就我而言，我们正在处理PostgreSQLjsonb生产和H2测试中的类型。
I could not test @n00dle 's solution because apparently spring does not support executing a SQLscript before Hibernate's ddl-auto=updatefor our tests so I used another way to solve this.
我无法测试@ n00dle的解决方案，因为显然春不支持执行SQL脚本之前Hibernate的ddl-auto=update对于我们的测试，所以我用另一种方式来解决这个问题。
Here is a gistfor it.
这是它的要点。
The overall idea is to create two package-infofiles.
One for production and the other for tests and register different types (JsonBinaryType.classfor production and TextType.classfor tests) to handle them differently for PostgreSQLand H2
总体思路是创建两个package-info文件。一个用于生产，另一个用于测试和注册不同的类型（JsonBinaryType.class用于生产TextType.class用于测试），以不同的方式处理它们的PostgreSQL和H2
回答by jchrbrt
A workaround is actually to create a custom column data type in H2 for the jsonb type, and put the query in the datasource url like this:
一种解决方法实际上是在 H2 中为 jsonb 类型创建自定义列数据类型，并将查询放在数据源 url 中，如下所示：
spring.datasource.url=jdbc:h2:mem:testdb;INIT=create domain if not exists jsonb as text;MODE=PostgreSQL"
Now for tests and integration tests in particular, it would be preferable to use the same DB than your application, via TestContainers
现在特别是对于测试和集成测试，最好通过TestContainers使用与您的应用程序相同的数据库
回答by while true
My problem was with JSONB since H2 does not support it as was already mentioned.
我的问题是 JSONB，因为 H2 不支持它，如前所述。
One more problem is that when you insert a json, H2 transforms it into a json object stringwhich makes Hymanson serialization fail. ex: "{\"key\": 3}" instead of {"key": 3} . One solution is to use FORMAT JSON when inserting the json, but then you need to have duplicate insert files if you are using flyway, for example.
还有一个问题是，当你插入一个 json 时，H2 将它转换成一个json 对象字符串，这使得 Hymanson 序列化失败。例如： "{\"key\": 3}" 而不是 {"key": 3} 。一种解决方案是在插入 json 时使用 FORMAT JSON，但是例如，如果您使用 flyway，则您需要有重复的插入文件。
Inspired by the @madzanswer I came across with this solution:
受到@madz回答的启发，我遇到了这个解决方案：
Create a custom JsonbType (on production - e.g. main/java/com/app/types/JsonbType.java)
创建自定义 JsonbType（在生产中 - 例如 main/java/com/app/types/JsonbType.java）
import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
public class JsonbType extends JsonBinaryType {
private static final long serialVersionUID = 1L;
}
Create a custom JsonbType (on tests - e.g. test/java/com/app/types/JsonbType.java)
创建自定义 JsonbType（在测试中 - 例如 test/java/com/app/types/JsonbType.java）
import com.vladmihalcea.hibernate.type.json.JsonStringType;
public class JsonbType extends JsonStringType {
private static final long serialVersionUID = 1L;
@Override
public String getName() {
return "jsonb";
}
}
Create an alias type from JSONB to JSON only on tests (h2):
仅在测试 (h2) 上创建从 JSONB 到 JSON 的别名类型：
-- only on H2 database
CREATE TYPE "JSONB" AS TEXT;
note: I'm using flyway which make it easy to do but you can follow @jchrbrtsuggestion
注意：我正在使用 flyway 这使得它很容易做到，但您可以遵循@jchrbrt建议
Finally you declare the type on your entity model, as follows:
最后，您在实体模型上声明类型，如下所示：
import com.app.types.JsonbType;
@TypeDef(name = "jsonb", typeClass = JsonbType.class)
@Entity(name = "Translation")
@Table(name = "Translation")
@Data
public class Translation {
@Type(type = "jsonb")
@Column(name="translations")
private MySerializableCustomType translations;
}
}
That's it. I hope it helps someone.
就是这样。我希望它可以帮助某人。
回答by Elias J?rgensen
I am in the same situation as @madz, where we use Postgres in production and H2 for unit tests. In my case i found a bit more simple solution, i think. We use Liquibase for database migrations, so here i made a conditional migration only to be run on H2, where i change the column type to H2's "other" type.
我和@madz处于相同的情况，我们在生产中使用 Postgres，在单元测试中使用 H2。就我而言，我认为我找到了一个更简单的解决方案。我们使用 Liquibase 进行数据库迁移，所以在这里我做了一个只在 H2 上运行的条件迁移，我将列类型更改为 H2 的“其他”类型。
With the other type, H2 just stores it in the database and doesn't think twice about how the data is formatted etc. This does require however that you are not doing anything with the JSON directly in the database, and only in your application.
对于另一种类型，H2 只是将其存储在数据库中，并且不会考虑数据的格式等。但是，这确实要求您不要直接在数据库中对 JSON 执行任何操作，而只能在您的应用程序中执行任何操作。
My migration looks like this:
我的迁移如下所示：
# Use other type in H2, as jsonb is not supported
- changeSet:
id: 42
author: Elias J?rgensen
dbms: h2
changes:
- modifyDataType:
tableName: myTableName
columnName: config
newDataType: other
Along with this, i added the following to my test datasource:
与此同时，我在我的测试数据源中添加了以下内容：
INIT=create domain if not exists jsonb as text;
