解决 JPA 中的 PostgreSQL JSON 类型不匹配异常

教程

1、简介

本文将带你了解在使用 JPA 与 PostgreSQL 交互时出现异常 “PSQLException error: column is of type json but the expression is of type character varying” 的原因，以及解决办法。

2、常见的原因

在 PostgreSQL 中，JSON 或 JSONB 数据类型用于存储 JSON 数据。但是，如果我们试图将字符串（character varyin）插入到期望使用 JSON 的列中，PostgreSQL 就会抛出 “column is of type json but expression is of type character varying” 错误。这种情况在使用 JPA 和 PostgreSQL 时尤其常见，因为 JPA 可能会尝试将字符串保存到 JSON 列，从而导致此错误。

3、异常演示

创建一个基本的 Spring Boot 项目，在 Maven pom.xml 文件中添加 PostgreSQL 依赖：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.1</version>
    <scope>runtime</scope>
</dependency>

创建一个映射到 student 表的 JPA 实体类：

@Entity
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String admitYear;

    @Column(columnDefinition = "json")
    private String address;

    // Getter / Setter 方法
}

如上， address 字段被映射到 student 表中的 address 列。而且，columnDefinition 属性被指定为 json，表明该列的类型是 JSON。

现在，尝试将一个 Student 对象保存到数据库中：

Student student = new Student();
student.setAdmitYear("2024");
// json 紫都城
student.setAddress("{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}");

Throwable throwable = assertThrows(Exception.class, () -> studentRepository.save(student));
assertTrue(ExceptionUtils.getRootCause(throwable) instanceof PSQLException);

如上，创建了一个 Student 对象，并将 address 字段设置为 JSON 字符串。然后，使用 studentRepository 对象的 save() 方法将此对象保存到数据库中。

然而，这会导致 PSQLException 异常：

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

出现此错误的原因是 JPA 尝试将字符串保存到 JSON 列，而这是不允许的。

4、使用 @Type 注解

要解决这个错误，需要正确处理 JSON 类型。

可以使用 hibernate-types 库提供的 @Type 注解。

首先，在 pom.xml 中添加 hibernate-types 依赖：

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

然后，更新实体，添加 @TypeDef 和 @Type 注解：

@Entity
@Table(name = "student_json")
@TypeDefs({
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class StudentWithTypeAnnotation {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String admitYear;

    @Type(type = "jsonb")
    @Column(columnDefinition = "json")
    private String address;

    // Getter / Setter 方法
}

如上，@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class) 注册了一个名为 JSONB 的自定义类型，该类型使用了 hibernate-types-52 库中的 JsonBinaryType 类。JsonBinaryType 处理 PostgreSQL 的 JSONB 数据类型，允许以 JSONB 的形式有效地存储和检索 JSON 数据。

@Type 注解用于为字段指定 Hibernate 的自定义类型。通过指定 @Type(type="jsonb")，我们告诉 Hibernate 使用通过 @TypeDef 注册的自定义类型 JSONB。该自定义类型可处理 PostgreSQL 中 Java 对象与 JSONB 数据之间的转换。

这种设置可确保在 PostgreSQL 中使用 JSONB 数据类型高效地存储和检索 JSON 数据：

StudentWithTypeAnnotation student = new StudentWithJson();
student.setAdmitYear("2024");
student.setAddress("{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}");
studentWithTypeAnnotationRepository.save(student);

StudentWithTypeAnnotation retrievedStudent = studentWithTypeAnnotationRepository.findById(student.getId()).orElse(null);

assertThat(retrievedStudent).isNotNull();
assertThat(retrievedStudent.getAddress()).isEqualTo("{\"postCode\":\"TW9 2SF\",\"city\":\"London\"}");

5、原生查询

此外，当使用 @Query 注解原生 SQL 查询将 JSON 数据插入 PostgreSQL 表时，也会遇到同样的错误。

创建一个原生的 insert 查询来演示这个错误：

@Query(value = "INSERT INTO student (admit_year, address) VALUES (:admitYear, :address) RETURNING *", nativeQuery = true)
Student insertJsonData(@Param("admitYear") String admitYear, @Param("address") String address);

当使用 JSON 字符串调用该方法时，就会出现异常：

Throwable throwable = assertThrows(Exception.class, () -> 
  studentRepository.insertJsonData("2024","{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}"));
assertTrue(ExceptionUtils.getRootCause(throwable) instanceof PSQLException);

要解决这个问题，需要在插入之前将 JSON 字符串转换为 JSONB 类型，以避免出现此错误：

public interface StudentWithTypeAnnotationRepository extends JpaRepository<StudentWithTypeAnnotation, Long> {
    @Query(value = "INSERT INTO student (admit_year, address) VALUES (:admitYear, CAST(:address AS JSONB)) RETURNING *", nativeQuery = true)
    StudentWithTypeAnnotation insertJsonData(@Param("admitYear") String admitYear, @Param("address") String address);
}

如上，使用 CAST(:address AS JSONB) 语法将 :address 参数转换为 JSONB 类型。

现在，测试一下这个方法：

StudentWithTypeAnnotation student = studentWithJsonRepository.insertJsonData("2024","{\"postCode\": \"TW9 2SF\", \"city\": \"London\"}");

StudentWithTypeAnnotation retrievedStudent = studentWithJsonRepository.findById(student.getId()).orElse(null);

assertThat(retrievedStudent).isNotNull();
assertThat(retrievedStudent.getAddress()).isEqualTo("{\"city\": \"London\", \"postCode\": \"TW9 2SF\"}");

6、总结

本文介绍了在使用 JPA 处理 PostgreSQL JSON 列时出现 PSQLException error “column is of type json but the expression is of type character varying” 异常的原因，以及解决办法。

Ref：https://www.baeldung.com/jpa-postgresql-json-type-mismatch-errors