如何在Spring JPA原生查询的WHERE IN子句中使用元组

问题背景 #

原生PostgreSQL双列IN查询可正常执行：

select * from collision_danger_pair_contexts 
where 
(first_target_id, second_target_id) 
in
( ('63efc3d9-8fc7-4b39-a9ce-b926dae4e104', '63efc3d9-8fc7-4b39-a9ce-b926dae4e105') );

但Spring Data JPA中使用List<UUID[]>或List<List<UUID>>作为参数的原生查询抛出错误：

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: record = bytea
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

需求是使用List<org.apache.commons.lang3.tuple.ImmutablePair<UUID, UUID>>作为参数完成查询。

错误原因 #

Spring Data JPA默认无法将数组、列表或ImmutablePair类型正确转换为PostgreSQL支持的行记录（record）类型，导致参数被解析为bytea类型，无法匹配record = record的比较运算符。

解决方案 #

方案1：使用Spring Data Tuple作为参数中转 #

借助Spring Data的Tuple类型作为参数元素，Spring会自动将其转换为PostgreSQL行记录：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.data.util.Tuple;
import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;
import org.apache.commons.lang3.tuple.ImmutablePair;

// Repository方法定义
@Query(nativeQuery = true, value = """
    SELECT * FROM collision_danger_pair_contexts ctx 
    WHERE (ctx.first_target_id, ctx.second_target_id) IN (:targetIds)
""")
List<CollisionDangerPairContext> findByTargetPairs(@Param("targetIds") List<Tuple> targetIds);

// 调用示例
List<ImmutablePair<UUID, UUID>> sourcePairs = ...;
List<Tuple> paramTuples = sourcePairs.stream()
    .map(pair -> Tuple.of(pair.getLeft(), pair.getRight()))
    .collect(Collectors.toList());
List<CollisionDangerPairContext> result = repository.findByTargetPairs(paramTuples);

方案2：改用JPQL查询（推荐，无需原生SQL） #

JPQL支持双列IN语法，且能直接适配ImmutablePair类型（前提是JPA实现如Hibernate支持该语法）：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.UUID;
import org.apache.commons.lang3.tuple.ImmutablePair;

// Repository方法定义
@Query("""
    SELECT ctx FROM CollisionDangerPairContext ctx 
    WHERE (ctx.firstTargetId, ctx.secondTargetId) IN :targetIds
""")
List<CollisionDangerPairContext> findByTargetPairs(@Param("targetIds") List<ImmutablePair<UUID, UUID>> targetIds);

// 调用示例
UUID id1 = UUID.fromString("63efc3d9-8fc7-4b39-a9ce-b926dae4e104");
UUID id2 = UUID.fromString("63efc3d9-8fc7-4b39-a9ce-b926dae4e105");
List<ImmutablePair<UUID, UUID>> pairs = List.of(ImmutablePair.of(id1, id2));
List<CollisionDangerPairContext> result = repository.findByTargetPairs(pairs);

注意：确保实体类的字段名（firstTargetId、secondTargetId）与数据库列名正确映射（可通过@Column注解调整）。

方案3：自定义Hibernate类型转换器 #

若必须使用原生查询，可自定义类型转换器将ImmutablePair<UUID, UUID>转换为PostgreSQL行记录：

1. 定义类型转换器类：

import org.apache.commons.lang3.tuple.ImmutablePair;
import org.hibernate.type.AbstractSingleColumnStandardBasicType;
import org.hibernate.type.descriptor.java.AbstractTypeDescriptor;
import org.hibernate.type.descriptor.java.MutabilityPlan;
import org.hibernate.type.descriptor.sql.VarcharTypeDescriptor;
import java.sql.Types;
import java.util.UUID;

public class ImmutablePairUUIDType extends AbstractSingleColumnStandardBasicType<ImmutablePair<UUID, UUID>> {
    public static final ImmutablePairUUIDType INSTANCE = new ImmutablePairUUIDType();

    public ImmutablePairUUIDType() {
        super(VarcharTypeDescriptor.INSTANCE, ImmutablePairUUIDJavaDescriptor.INSTANCE);
    }

    @Override
    public String getName() {
        return "immutable_pair_uuid";
    }

    @Override
    public int getSqlType() {
        return Types.VARCHAR;
    }
}

class ImmutablePairUUIDJavaDescriptor extends AbstractTypeDescriptor<ImmutablePair<UUID, UUID>> {
    public static final ImmutablePairUUIDJavaDescriptor INSTANCE = new ImmutablePairUUIDJavaDescriptor();

    protected ImmutablePairUUIDJavaDescriptor() {
        super(ImmutablePair.class, new MutabilityPlan<ImmutablePair<UUID, UUID>>() {
            @Override
            public ImmutablePair<UUID, UUID> deepCopy(ImmutablePair<UUID, UUID> value) {
                return value;
            }

            @Override
            public ImmutablePair<UUID, UUID> disassemble(ImmutablePair<UUID, UUID> value) {
                return value;
            }

            @Override
            public ImmutablePair<UUID, UUID> assemble(ImmutablePair<UUID, UUID> cached) {
                return cached;
            }
        });
    }

    @Override
    public String toString(ImmutablePair<UUID, UUID> value) {
        return String.format("('%s','%s')", value.getLeft(), value.getRight());
    }

    @Override
    public ImmutablePair<UUID, UUID> fromString(String string) {
        String[] parts = string.replaceAll("[()']", "").split(",");
        return ImmutablePair.of(UUID.fromString(parts[0].trim()), UUID.fromString(parts[1].trim()));
    }

    @Override
    public <X> X unwrap(ImmutablePair<UUID, UUID> value, Class<X> type, WrapperOptions options) {
        if (value == null) return null;
        if (String.class.isAssignableFrom(type)) return (X) toString(value);
        throw unknownUnwrap(type);
    }

    @Override
    public <X> ImmutablePair<UUID, UUID> wrap(X value, WrapperOptions options) {
        if (value == null) return null;
        if (String.class.isInstance(value)) return fromString((String) value);
        throw unknownWrap(value.getClass());
    }
}

2. 在Repository方法中指定自定义类型：

import org.hibernate.annotations.Type;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.UUID;
import org.apache.commons.lang3.tuple.ImmutablePair;

@Query(nativeQuery = true, value = """
    SELECT * FROM collision_danger_pair_contexts ctx 
    WHERE (ctx.first_target_id, ctx.second_target_id) IN (:targetIds)
""")
List<CollisionDangerPairContext> findByTargetPairs(
    @Param("targetIds") @Type(type = "com.yourpackage.ImmutablePairUUIDType") List<ImmutablePair<UUID, UUID>> targetIds);

注意替换com.yourpackage为实际的包路径。

内容的提问来源于stack exchange，提问作者ka3ak