1、简介 {#1简介}
本文将带你了解在使用 JPA 与 PostgreSQL 交互时出现异常 "PSQLException error: column is of type json but the expression is of type character varying" 的原因,以及解决办法。
2、常见的原因 {#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、异常演示 {#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 注解 {#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、原生查询 {#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、总结 {#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