解决异常 PSQLException: Operator Does Not Exist: Character Varying = UUID

2024-07-01

1、简介

本文将带你了解 JPA 与 PostgreSQL 交互时出现 PSQLException 异常：“Operator Does Not Exist: Character Varying = UUID” 的原因，以及如何处理和避免该异常。

2、异常原因

PostgreSQL 区分 Character Varying（字符串）和 UUID 数据类型。这种区分要求在这些类型之间进行比较时进行 显式类型转换。因此，当我们尝试直接将 UUID 值与字符串（VARCHAR）列进行比较时，PostgreSQL 会抛出异常，因为它缺乏用于这种特定类型比较的操作符。

举个例子，有一个 User 实体，其 varchar 列为 uuid：

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

    @Column(columnDefinition = "varchar")
    private UUID uuid;

    // Gette / Setter 方法省略
}

当尝试使用 UUID 值查询数据库时，会出现异常：

// UUID
UUID testId = UUID.fromString("c3917b5b-18ed-4a84-a6f7-6be7a8c21d66");
User user = new User();
user.setUuid(testId);
user.setName("John Doeee");
userRepository.save(user);

Throwable throwable = assertThrows(InvalidDataAccessResourceUsageException.class,
  () -> userRepository.findByUuid(testId),
  "Expected ERROR: operator does not exist: character varying = uuid");
assertTrue(getRootCause(throwable) instanceof PSQLException);

3、修复异常

要解决这个问题，需要确保正确处理 Character Varying（字符串）和 UUID 值之间的比较。

3.1、使用 CAST() 函数

可以使用 PostgreSQL 中的 CAST() 函数，在 JPA 查询中明确地将 UUID 值转换为字符串。

CAST() 函数是 PostgreSQL 的内置函数，允许将一种数据类型的值转换为另一种数据类型。这可以确保 PostgreSQL 正确处理 UUID 值与 varchar 类型之间的比较。

使用示例如下：

@Query("SELECT u FROM User u WHERE u.uuid = CAST(:uuid AS text)")
Optional<User> findByUuidWithCastFunction(@Param("uuid") UUID uuid);

通过将字符串值转换为 UUID，可以确保数据库能正确执行比较：

UUID testId = UUID.fromString("c3917b5b-18ed-4a84-a6f7-6be7a8c21d66");

Optional<User> userOptional = userRepository.findByUuidWithCastFunction(testId);
assertThat(userOptional.isPresent(), is(true));
assertThat(userOptional.get().getUuid(), equalTo(testId));

生成的 SQL 查询如下，包含了 CAST(:id AS text) 操作：

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.name as name2_0_,
        user0_.uuid as uuid3_0_ 
    from
        user_tbl user0_ 
    where
        user0_.uuid=cast(? as text)

cast(? as text) 显示了如何使用 CAST() 函数将 UUID 参数转换为 text 类型，以确保与 PostgreSQL 中的 varchar 列兼容。

3.2、使用自定义转换器（Converter）

除了使用 SQL 函数直接转换外，还可以使用 JPA 中的 @Converter 注解为 UUID 对象和 varchar 列定义一个自定义转换器。该转换器用于 UUID 对象与其在数据库中的字符串表示之间的无缝转换。

首先，实现 UUIDConverter 类，该类实现了 AttributeConverter<UUID, String>：

@Converter
public class UUIDConverter implements AttributeConverter<UUID, String> {
    @Override
    public String convertToDatabaseColumn(UUID uuid) {
        return uuid.toString();
    }

    @Override
    public UUID convertToEntityAttribute(String dbData) {
        return UUID.fromString(dbData);
    }
}

接下来，可以在 JPA 实体中的 UUID 字段上使用 @Convert 注解：

@Entity
public class UserWithConverter{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Convert(converter = UUIDConverter.class)
    @Column(columnDefinition = "varchar")
    private UUID uuid;

    // Getter/ Setter 方法省略
}

@Convert 注解配置 JPA 在将实体持久化到数据库时自动将 UUID 字段转换为字符串（VARCHAR）类型，而在检索时则反之：

Optional<UserWithConverter> userOptional = userWithConverterRepository.findByUuid(testId);
assertThat(userOptional.isPresent(), is(true));
assertThat(userOptional.get().getUuid(), equalTo(testId));

4、总结

本文介绍了在使用 JPA 和 Postgres 中出现异常 PSQLException “operator does not exist: character varying = uuid” 的原因，以及解决办法。

Ref：https://www.baeldung.com/java-psqlexception-operator-does-not-exist-character-varying-uuid