使用 Spring Data JPA 在 PostgreSQL 中存储和检索 JSON 数据，

2024-02-03

教程

1、概览

本文将带你全面了解如何使用 Spring Data JPA 在 PostgreSQL JSONB 列中存储、检索 JSON 数据。

2、VARCHAR 映射

本节将介绍如何使用 AttributeConverter 将 VARCHAR 类型的 JSON 值转换为自定义 Java POJO。

其目的是方便 Java 数据类型中的实体属性值与数据库列中的相应值之间的转换。

2.1、Maven 依赖

要创建 AttributeConverter，必须在 pom.xml 中加入 Spring Data JPA 依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>2.7.18</version>
</dependency>

2.2、数据表定义

数据库表定义如下：

CREATE TABLE student (
    student_id VARCHAR(8) PRIMARY KEY,
    admit_year VARCHAR(4),
    address VARCHAR(500)
);

student 表有三个字段，其中我们希望 address 列能存储具有以下结构的 JSON 值：

{
  "postCode": "TW9 2SF",
  "city": "London"
}

2.3、Entity 类

创建一个相应的 POJO 类，用 Java 表示 address 数据：

public class Address {
    private String postCode;

    private String city;

    // 构造函数、Getter、Setter 省略
}

接下来，创建一个实体类 StudentEntity，并将其映射到之前创建的 student 表：

@Entity
@Table(name = "student")
public class StudentEntity {
    @Id
    @Column(name = "student_id", length = 8)
    private String id;

    @Column(name = "admit_year", length = 4)
    private String admitYear;

    @Convert(converter = AddressAttributeConverter.class)
    @Column(name = "address", length = 500)
    private Address address;

    // 构造函数、Getter、Setter 省略
}

使用 @Convert 对 address 字段进行注解，并指定 AddressAttributeConverter 将 Address 实例转换为 JSON 表示形式。

2.4、AttributeConverter

我们将实体类中的 address 字段映射为数据库中的 VARCHAR 类型。但是，JPA 无法自动执行自定义 Java 类型和 VARCHAR 类型之间的转换。

AttributeConverter 提供了一种处理转换过程的机制，从而弥补了这一缺陷。每个 AttributeConverter 实现都必须定义两种转换方法。一个是将 Java 数据类型转换为相应的数据库数据类型，另一个是将数据库数据类型转换为 Java 数据类型：

@Converter
public class AddressAttributeConverter implements AttributeConverter<Address, String> {
    private static final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(Address address) {
        try {
            return objectMapper.writeValueAsString(address);
        } catch (JsonProcessingException jpe) {
            log.warn("Cannot convert Address into JSON");
            return null;
        }
    }

    @Override
    public Address convertToEntityAttribute(String value) {
        try {
            return objectMapper.readValue(value, Address.class);
        } catch (JsonProcessingException e) {
            log.warn("Cannot convert JSON into Address");
            return null;
        }
    }
}

convertToDatabaseColumn() 负责将实体字段值转换为相应的数据库列值，而 convertToEntityAttribute() 则负责将数据库列值转换为相应的实体字段值。

2.5、测试

创建一个测试用例，在数据库中持久化一个 Student 实例：

@Test
void whenSaveAnStudentEntityAndFindById_thenTheRecordPresentsInDb() {
    String studentId = "23876213";
    String postCode = "KT5 8LJ";

    Address address = new Address(postCode, "London");
    StudentEntity studentEntity = StudentEntity.builder()
      .id(studentId)
      .admitYear("2023")
      .address(address)
      .build();

    StudentEntity savedStudentEntity = studentRepository.save(studentEntity);

    Optional<StudentEntity> studentEntityOptional = studentRepository.findById(studentId);
    assertThat(studentEntityOptional.isPresent()).isTrue();

    studentEntity = studentEntityOptional.get();
    assertThat(studentEntity.getId()).isEqualTo(studentId);
    assertThat(studentEntity.getAddress().getPostCode()).isEqualTo(postCode);
}

运行测试，JPA 会执行以下 insert SQL：

Hibernate: 
    insert 
    into
        "public"
        ."student_str" ("address", "admit_year", "student_id") 
    values
        (?, ?, ?)
binding parameter [1] as [VARCHAR] - [{"postCode":"KT6 7BB","city":"London"}]
binding parameter [2] as [VARCHAR] - [2023]
binding parameter [3] as [VARCHAR] - [23876371]

如你所见，第一个参数已被 AddressAttributeConverter 从 Address 实例中成功转换，并绑定为 VARCHAR 类型。

3、JSONB 比 VARCHAR 更适合

现在，将 address 的列定义从 VARCHAR 改为 JSONB：

CREATE TABLE student (
    student_id VARCHAR(8) PRIMARY KEY,
    admit_year VARCHAR(4),
    address jsonb
);

你可能会有一个问题：既然 JSONB 本质上是字符串，那么在 PostgreSQL 中使用 JSONB 存储 JSON 比使用 VARCHAR 有什么好处？

JSONB 是一种指定的数据类型，用于在 PostgreSQL 中处理 JSON 数据。该类型以分解的二进制格式存储数据，由于需要进行额外的转换，因此在存储 JSON 时会产生一些开销。

事实上，与 VARCHAR 相比，JSONB 提供了更多的功能，使其成为在 PostgreSQL 中存储 JSON 数据的更有利选择。

3.1、验证

JSONB 类型会对存储值进行数据验证，确保列值是有效的 JSON。PostgreSQL 拒绝任何插入或更新无效 JSON 值数据的尝试。

测试插入一个无效的 JSON 值，其中 address 列的 city 属性末尾缺少双引号：

INSERT INTO student(student_id, admit_year, address) 
VALUES ('23134572', '2022', '{"postCode": "E4 8ST, "city":"London}');

在 PostgreSQL 中执行此 SQL 会出现验证错误，表明 JSON 无效：

SQL Error: ERROR: invalid input syntax for type json
  Detail: Token "city" is invalid.
  Position: 83
  Where: JSON data, line 1: {"postCode": "E4 8ST, "city...

3.2、查询

PostgreSQL 支持在 SQL 查询中使用 JSON 列进行查询。JPA 支持使用本地查询（nativeQuery）来检索数据库中的记录。在 Spring Data 中，可以定义一个自定义查询方法来查找 Student 列表：

@Repository
public interface StudentRepository extends CrudRepository<StudentEntity, String> {
    @Query(value = "SELECT * FROM student WHERE address->>'postCode' = :postCode", nativeQuery = true)
    List<StudentEntity> findByAddressPostCode(@Param("postCode") String postCode);
}

此查询是本地 SQL 查询，用于检索数据库中 address JSON 属性的 postCode 值等于所提供参数的所有 Student 实例。

3.3、索引

JSONB 支持 JSON 数据索引。这使 JSONB 在根据 JSON 列中的 key 或属性查询数据时具有显著优势。

可以对 JSON 列应用各种类型的索引，包括 GIN、HASH 和 BTREE。GIN 适用于复杂数据结构的索引，包括数组和 JSON。当处理 < 和 >= 等范围运算符时，BTREE 可以实现高效查询。

例如，如果我们经常需要根据 address 中的 postCode 属性检索数据，那么可以创建以下索引：

CREATE INDEX idx_postcode ON student USING HASH((address->'postCode'));

4、JSONB 映射

当数据库列定义为 JSONB 时，无法应用相同的 AttributeConverter。

应用在启动时会出现以下异常：

org.postgresql.util.PSQLException: ERROR: column "address" is of type jsonb but expression is of type character varying

即使更改 AttributeConverter 类的定义，使用 Object 作为转换后的列值而不是 String，情况仍然如此。

@Converter 
public class AddressAttributeConverter implements AttributeConverter<Address, Object> {
    // 转换方法的实现
}

应用依然启动异常，表示不支持该类型：

org.postgresql.util.PSQLException: Unsupported Types value: 1,943,105,171

这表明 JPA 本身不支持 JSONB 类型。不过，底层 JPA 实现 Hibernate 确实支持 JSON 自定义类型，允许将复杂类型映射到 Java 类。

4.1、Maven 依赖

我们需要为 JSONB 转换定义一个自定义类型。不过，不必重新发明轮子，因为已有一个 Hypersistence Utils 库。

Hypersistence Utils 是 Hibernate 的通用工具库。它的功能之一是为 PostgreSQL 和 Oracle 等不同数据库定义 JSON 列类型映射。因此，我们只需在 pom.xml 中加入这个额外的依赖项即可：

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-55</artifactId>
    <version>3.7.0</version>
</dependency>

4.2、更新实体类

Hypersistence Utils 定义了不同的自定义类型，这些类型取决于数据库。在 PostgreSQL 中，使用 JsonBinaryType 类来定义 JSONB 列类型。在实体类中，使用类注解 @TypeDef 定义自定义类型，然后通过 @Type 将定义的类型应用到 address 字段：

@Entity
@Table(name = "student")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class StudentEntity {
    @Id
    @Column(name = "student_id", length = 8)
    private String id;

    @Column(name = "admit_year", length = 4)
    private String admitYear;

    @Type(type = "jsonb")
    @Column(name = "address", columnDefinition = "jsonb")
    private Address address;

    // Get、Set 省略
}

在使用 @Type 的情况下，不再需要对 address 字段应用 AttributeConverter。来自 Hypersistence Utils 的自定义类型会为我们处理转换任务，使我们的代码更加简洁。

4.3. 测试

完成所有这些更改后，再次运行 Student 持久化测试用例：

Hibernate: 
    insert 
    into
        "public"
        ."student" ("address", "admit_year", "student_id") 
    values
        (?, ?, ?)
binding parameter [1] as [OTHER] - [Address(postCode=KT6 7BB, city=London)]
binding parameter [2] as [VARCHAR] - [2023]
binding parameter [3] as [VARCHAR] - [23876371]

你可以看到，JPA 执行了与之前相同的 insert SQL，只是第一个参数绑定为 OTHER 而不是 VARCHAR。这表明这次 Hibernate 将参数绑定为 JSONB 类型。

5、总结

本文介绍了如何使用 Spring Data Jpa 在 PostgreSQL 中存储和检索 JSON 数据，还介绍了 VARCHAR 和 JSONB 类型之间的区别。

Ref：https://www.baeldung.com/spring-boot-jpa-storing-postgresql-jsonb