解决JPA中PostgreSQL JSON类型不匹配错误

1. 简介

本文将探讨使用JPA操作PostgreSQL时常见的PSQLException错误："column is of type json but the expression is of type character varying"。我们将分析错误原因、常见触发场景，并演示解决方案。

2. 常见原因

PostgreSQL使用JSON或JSONB数据类型存储JSON数据。当尝试将字符串(character varying)插入JSON类型列时，PostgreSQL会抛出类型不匹配错误。这在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;

    // getters and setters
}

⚠️ 注意：address字段通过columnDefinition = "json"声明为JSON类型。现在尝试保存Student对象：

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

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

**执行保存操作时抛出PSQLException**：

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

根本原因：JPA尝试将字符串直接存入JSON列，而PostgreSQL要求显式类型转换。

4. 使用@Type注解解决

✅ 推荐方案：使用hibernate-types库提供的@Type注解处理JSON类型。首先添加依赖：

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

更新实体类添加类型定义：

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

    // Getters and Setters
}

关键点解析：

@TypeDef注册名为jsonb的自定义类型，使用JsonBinaryType处理PostgreSQL的JSONB数据
@Type(type = "jsonb")指定Hibernate使用该自定义类型
JsonBinaryType自动处理Java对象与PostgreSQL JSONB的转换

测试保存和查询：

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数据时也会遇到相同错误。错误示例：

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

调用测试：

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

✅ 解决方案：在SQL中使用CAST显式转换类型：

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)将参数强制转换为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列时的类型不匹配问题。核心方案包括：

使用@Type注解配合hibernate-types库自动处理类型转换
在原生SQL中通过CAST显式转换JSON字符串为JSONB类型

这两种方案都能确保JSON数据在PostgreSQL中高效存储和检索。完整示例代码可在GitHub获取。

Persistence

REST

Security