如何使用 SpringBoot + JPA 存储 PostgreSQL jsonb？

how to store PostgreSQL jsonb using SpringBoot + JPA?

提问by Magno C

I'm working on a migration software that will consume unknown data from REST services.

我正在开发一种迁移软件，该软件将使用来自 REST 服务的未知数据。

I already think about use MongoDB but I decide to not use it and use PostgreSQL.

我已经考虑使用 MongoDB，但我决定不使用它并使用 PostgreSQL。

After read thisI'm trying to implement it in my SpringBoot app using Spring JPA but I don't know to map jsonbin my entity.

阅读本文后，我正在尝试使用 Spring JPA 在我的 SpringBoot 应用程序中实现它，但我不知道jsonb在我的实体中进行映射。

Tried thisbut understood nothing!

试过这个但什么都不明白！

Here is where I am:

这是我所在的地方：

@Repository
@Transactional
public interface DnitRepository extends JpaRepository<Dnit, Long> {

    @Query(value = "insert into dnit(id,data) VALUES (:id,:data)", nativeQuery = true)
    void insertdata( @Param("id")Integer id,@Param("data") String data );

}

and ...

和 ...

@RestController
public class TestController {

    @Autowired
    DnitRepository dnitRepository;  

    @RequestMapping(value = "/dnit", method = RequestMethod.GET)
    public String testBig() {
        dnitRepository.insertdata(2, someJsonDataAsString );
    }

}

and the table:

和表：

CREATE TABLE public.dnit
(
    id integer NOT NULL,
    data jsonb,
    CONSTRAINT dnit_pkey PRIMARY KEY (id)
)

How can I do this?

我怎样才能做到这一点？

Note: I don't want/need an Entity to work on. My JSON will always be String but I need jsonb to query the DB

注意：我不想/不需要实体来工作。我的 JSON 将始终是 String 但我需要 jsonb 来查询数据库

回答by Cepr0

Tried thisbut understood nothing!

试过这个但什么都不明白！

To fully work with jsonbin Spring Data JPA(Hibernate) project with Vlad Mihalcea's hibernate-typeslib you should just do the following:

要使用 Vlad Mihalcea 的hibernate-typeslibjsonb在Spring Data JPA(Hibernate) 项目中完全使用，您应该执行以下操作：

1) Add this lib to your project:

1）将此库添加到您的项目中：

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.2.2</version>
</dependency>

2) Then use its types in your entities, for example:

2）然后在你的实体中使用它的类型，例如：

@Data
@NoArgsConstructor
@Entity
@Table(name = "parents")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Parent implements Serializable {

    @Id
    @GeneratedValue(strategy = SEQUENCE)
    private Integer id;

    @Column(length = 32, nullable = false)
    private String name;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private List<Child> children;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private Bio bio;

    public Parent(String name, List children, Bio bio) {
        this.name = name;
        this.children = children;
        this.bio = bio;
    }
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Child implements Serializable {
    private String name;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Bio implements Serializable {
    private String text;
}

Then you will be able to use, for example, a simple JpaRepositoryto work with your objects:

然后你将能够使用，例如，一个简单JpaRepository的处理你的对象：

public interface ParentRepo extends JpaRepository<Parent, Integer> {
}

parentRepo.save(new Parent(
                     "parent1", 
                     asList(new Child("child1"), new Child("child2")), 
                     new Bio("bio1")
                )
);

Parent result = parentRepo.findById(1);
List<Child> children = result.getChildren();
Bio bio = result.getBio();

回答by M. Deinum

You are making things overly complex by adding Spring Data JPA just to execute a simple insert statement. You aren't using any of the JPA features. Instead do the following

通过添加 Spring Data JPA 只是为了执行简单的插入语句，您使事情变得过于复杂。您没有使用任何 JPA 功能。而是执行以下操作

1. Replace spring-boot-starter-data-jpawith spring-boot-starter-jdbc
2. Remove your DnitRepositoryinterface
3. Inject JdbcTemplatewhere you where injecting DnitRepository
4. Replace dnitRepository.insertdata(2, someJsonDataAsString );with jdbcTemplate.executeUpdate("insert into dnit(id, data) VALUES (?,to_json(?))", id, data);

1. 替换spring-boot-starter-data-jpa为spring-boot-starter-jdbc
2. 删除您的DnitRepository界面
3. 进样JdbcTemplate，你在那里注射DnitRepository
4. 替换dnitRepository.insertdata(2, someJsonDataAsString );为jdbcTemplate.executeUpdate("insert into dnit(id, data) VALUES (?,to_json(?))", id, data);

You were already using plain SQL (in a very convoluted way), if you need plain SQL (and don't have need for JPA) then just use SQL.

您已经在使用普通 SQL（以一种非常复杂的方式），如果您需要普通 SQL（并且不需要 JPA），那么只需使用 SQL。

Ofcourse instead of directly injecting the JdbcTemplateinto your controller you probably want to hide that logic/complexity in a repository or service.

当然，不是直接将 注入JdbcTemplate到您的控制器中，您可能希望在存储库或服务中隐藏该逻辑/复杂性。

回答by George Siggouroglou

For this case, I use the above tailored converter class, you are free to add it in your library. It is working with the EclipseLink JPA Provider.

对于这种情况，我使用上面定制的转换器类，您可以随意将其添加到您的库中。它与 EclipseLink JPA Provider 一起工作。

import com.fasterxml.Hymanson.core.JsonProcessingException;
import com.fasterxml.Hymanson.core.type.TypeReference;
import com.fasterxml.Hymanson.databind.ObjectMapper;
import org.apache.log4j.Logger;
import org.postgresql.util.PGobject;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Map;

@Converter
public final class PgJsonbToMapConverter implements AttributeConverter<Map<String, ? extends Object>, PGobject> {

    private static final Logger LOGGER = Logger.getLogger(PgJsonbToMapConverter.class);
    private static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public PGobject convertToDatabaseColumn(Map<String, ? extends Object> map) {
        PGobject po = new PGobject();
        po.setType("jsonb");

        try {
            po.setValue(map == null ? null : MAPPER.writeValueAsString(map));
        } catch (SQLException | JsonProcessingException ex) {
            LOGGER.error("Cannot convert JsonObject to PGobject.");
            throw new IllegalStateException(ex);
        }
        return po;
    }

    @Override
    public Map<String, ? extends Object> convertToEntityAttribute(PGobject dbData) {
        if (dbData == null || dbData.getValue() == null) {
            return null;
        }
        try {
            return MAPPER.readValue(dbData.getValue(), new TypeReference<Map<String, Object>>() {
            });
        } catch (IOException ex) {
            LOGGER.error("Cannot convert JsonObject to PGobject.");
            return null;
        }
    }

}

Usage example, for an entity named Customer.

用法示例，对于名为 的实体Customer。

@Entity
@Table(schema = "web", name = "customer")
public class Customer implements Serializable {

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

    @Convert(converter = PgJsonbToMapConverter.class)
    private Map<String, String> info;

    public Customer() {
        this.id = null;
        this.info = null;
    }

    // Getters and setter omitted.