将字符串属性映射到JSONB

huangapple
go评论111阅读模式
英文:

Map string properties to JSONB

问题

我一直在尝试使用JPA将我的字符串属性映射到PostgreSQL的JSONB列。我多次阅读了Vlad Mihalcea的完美文章，也看到了相关的问题和类似的问题。但是每当我尝试向表中插入数据时，我仍然会遇到这个异常org.postgresql.util.PSQLException: ERROR: column "json_property" is of type jsonb but expression is of type character varying。

更糟糕的是，所有这些类似问题中的建议在我改变了实体类并使其继承超类之后都变得无效了。现在的情况是这样的：
1）如果在我的子类上使用@TypeDef和@Type，它可以正常工作；
2）但我想使用抽象层，并将我在上面看到的那些注解应用到我的基础实体类上，之后异常就会告诉我'你好！我又来了'；

我的继承结构非常简单，如下所示：

基础实体类

  1. @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
  2. @MappedSuperclass
  3. public abstract class AbstractServiceEntity implements Serializable {
  5. private Integer id;
  7. @Id
  8. @GeneratedValue(strategy = GenerationType.IDENTITY)
  9. @Column(name = "id")
  10. public Integer getId() {
  11.     return id;
  12. }
  14. public void setId(Integer id) {
  15.     this.id = id;
  16. }

子实体类

  1. @Entity
  2. @Table(schema = "ref", name = "test_json_3")
  3. @NoArgsConstructor
  4. @AllArgsConstructor
  5. @Getter
  6. @Setter
  7. public class TestJson extends AbstractServiceEntity {
  9. @Type(type = "jsonb")
  10. @Column(columnDefinition = "jsonb")
  11. private String jsonProperty;

我的表

  1. create table ref.test_json_3
  2. (
  3. id serial primary key,
  4. json_property jsonb 
  5. )

更新
我成功地使用了JPA原生查询插入了记录，但我不得不将查询解包到Hibernate查询中。不确定这是否是将数据插入数据库的最方便的方式。我的问题仍然存在，我仍然需要您的帮助。下面是使用原生查询的示例。

带有结果的代码片段

  1. @Repository
  2. public class JpaTestRepository {
  4. @PersistenceContext
  5. private EntityManager entityManager;
  7. @Transactional
  8. public void insert(TestJson testJson) {
  9.     entityManager.createNativeQuery("INSERT INTO test_json_3 (json_property) VALUES (?)")
  10.             .unwrap(Query.class)
  11.             .setParameter(1, testJson.getJsonProperty(), JsonBinaryType.INSTANCE)
  12.             .executeUpdate();
  13. }
英文:

I've been trying map my string properties to Postgresql's JSONB using JPA. I did read perfect article by Vlad Mihalcea many times and also seen relative questions and problems with similar stuff. BUT I still have this exception org.postgresql.util.PSQLException: ERROR: column "json_property" is of type jsonb but expression is of type character varying every time when I'm trying insert something into my table.

And what even worse is - all these advices in similar questions were useful until I changed my entity class and made him inherits super class. And now situation is like this:

  1. If @TypeDef and @Type on my child class and it works great
  2. But I want use abstraction layer and set annotations, which I noticed above, to my base entity class and after that exception says me 'Hello! It's me again'

My hierarchy is pretty simple and here it is:

Base entity

  1. @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
  2. @MappedSuperclass
  3. public abstract class AbstractServiceEntity implements Serializable {
  5. private Integer id;
  7. @Id
  8. @GeneratedValue(strategy = GenerationType.IDENTITY)
  9. @Column(name = "id")
  10. public Integer getId() {
  11.     return id;
  12. }
  14. public void setId(Integer id) {
  15.     this.id = id;
  16. }

Child entity

  1. @Entity
  2. @Table(schema = "ref", name = "test_json_3")
  3. @NoArgsConstructor
  4. @AllArgsConstructor
  5. @Getter
  6. @Setter
  7. public class TestJson extends AbstractServiceEntity {
  9. @Type(type = "jsonb")
  10. @Column(columnDefinition = "jsonb")
  11. private String jsonProperty;

My table

  1. create table ref.test_json_3
  2. (
  3. id serial primary key,
  4. json_property jsonb 
  5. )

UPD
I've succesfully inserted record with JPA native query, but I had to unwrap my query into hibernate query. Not sure that it's the most convinient way to manage inserting data into DB. The my question is actual, I still need your help) Example with native query below.

Code snippent with result

  1. @Repository
  2. public class JpaTestRepository {
  4. @PersistenceContext
  5. private EntityManager entityManager;
  7. @Transactional
  8. public void insert(TestJson testJson) {
  9.     entityManager.createNativeQuery("INSERT INTO test_json_3 (json_property) VALUES (?)")
  10.             .unwrap(Query.class)
  11.             .setParameter(1, testJson.getJsonProperty(), JsonBinaryType.INSTANCE)
  12.             .executeUpdate();
  13. }

答案1

得分: 2

  1. 我终于找到了解决我的问题的方法。答案是 - 只需在 getter 方法中使用 `@Column(columnDefinition = "jsonb")` 和 `@Type(type = "jsonb")`，而不是在类属性中使用。
  3. **实体定义**
  5.     @Entity
  6.     @Table(schema = "ref", name = "test_json_3")
  7.     @NoArgsConstructor
  8.     @AllArgsConstructor
  9.     @Setter
  10.     public class TestJson extends AbstractServiceEntity {
  12.     private String jsonProperty;
  14.     @Type(type = "jsonb")
  15.     @Column(columnDefinition = "jsonb")
  16.     public String getJsonProperty() {
  17.         return jsonProperty;
  18.     }
英文:

Finally I found solution for my problem. Answer is - just use your @Column(columnDefinition = "jsonb") and @Type(type = "jsonb" via getters but not class properties.

entity definition

  1. @Entity
  2. @Table(schema = "ref", name = "test_json_3")
  3. @NoArgsConstructor
  4. @AllArgsConstructor
  5. @Setter
  6. public class TestJson extends AbstractServiceEntity {
  8. private String jsonProperty;
  10. @Type(type = "jsonb")
  11. @Column(columnDefinition = "jsonb")
  12. public String getJsonProperty() {
  13.     return jsonProperty;
  14. }

答案2

得分: 1

  1. @ColumnTransformer(write = "?::jsonb")
  2. private String jsonProperty;
英文:

Alternate solution for mapping String to Jsonb type. Just add the following annotation on your string.

  1. @ColumnTransformer(write = "?::jsonb")
  2. private String jsonProperty;

答案3

得分: 0

你可以尝试在TestJson类下添加@TypeDefs注解：

  1. @TypeDefs({
  2.     @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
  3. })
  4. public class TestJson extends AbstractServiceEntity {
英文:

You can try to add @TypeDefs under class TestJson:

  1. @TypeDefs({
  2. 		@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
  3. })
  4. public class TestJson extends AbstractServiceEntity {

huangapple
  • 本文由 发表于 2020年9月9日 02:57:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/63800038.html
  • hibernate
  • java
  • jpa
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定