使用Spring Boot和JPA存储PostgreSQL JSONB数据

1. 概述

本文将深入探讨如何在PostgreSQL的JSONB列中存储JSON数据。我们将通过JPA实现两种方案：首先使用VARCHAR类型存储JSON数据并配合AttributeConverter转换，然后对比JSONB与VARCHAR的差异，最后实现JSONB类型的映射。

2. VARCHAR映射方案

2.1 Maven依赖

实现AttributeConverter需要添加Spring Data JPA依赖：

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

2.2 数据表定义

创建示例表，其中address列存储JSON格式的地址数据：

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

address列存储的JSON结构示例：

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

2.3 实体类设计

创建地址POJO类：

public class Address {
    private String postCode;
    private String city;
    // 构造方法、getter和setter
}

实体类映射：

@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
}

2.4 AttributeConverter实现

AttributeConverter用于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("无法将Address转换为JSON");
            return null;
        }
    }

    @Override
    public Address convertToEntityAttribute(String value) {
        try {
            return objectMapper.readValue(value, Address.class);
        } catch (JsonProcessingException e) {
            log.warn("无法将JSON转换为Address");
            return null;
        }
    }
}

⚠️ 关键点：每个AttributeConverter必须实现两个转换方法：

convertToDatabaseColumn()：Java类型 → 数据库类型
convertToEntityAttribute()：数据库类型 → Java类型

2.5 测试验证

@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();

    studentRepository.save(studentEntity);

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

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

插入日志显示转换成功：

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]

3. JSONB vs VARCHAR优势对比

3.1 数据验证

✅ JSONB强制JSON格式校验，无效数据会直接报错：

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

错误示例：

SQL Error: ERROR: invalid input syntax for type json
  Detail: Token "city" is invalid.
  Position: 83

3.2 查询能力

✅ 支持原生JSON查询：

@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);
}

3.3 索引支持

✅ JSONB支持多种索引类型：

GIN：适合复杂结构（数组/JSON）
HASH：仅支持=操作
BTREE：支持范围查询（<, >=）

示例索引创建：

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

4. JSONB映射实现

4.1 依赖配置

❌ 踩坑提示：直接使用AttributeConverter会报错：

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

解决方案：添加Hypersistence Utilities依赖：

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

4.2 实体类改造

使用JsonBinaryType替换AttributeConverter：

@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;
    // getter和setter
}

⚠️ 注意：@TypeDef和@Type在Hibernate 6中已废弃

4.3 测试验证

插入日志显示参数类型变为OTHER：

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]

5. 总结

本文对比了PostgreSQL中存储JSON的两种方案：

VARCHAR方案：通过AttributeConverter实现简单转换
JSONB方案：使用Hypersistence Utilities实现原生类型映射

✅ 推荐JSONB方案，因为具备：

强制JSON格式校验
高效的查询能力
灵活的索引支持

完整代码示例可在GitHub获取。

Persistence

REST

Security