Spring Boot 3.x中JPA原生LTREE查询ANY子句报错，求解决

问题描述 #

我尝试执行一条PostgreSQL LTREE查询，SQL语句如下：

SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY('{A}')

对应的Spring Boot 3.x代码如下：

String nativeSql = "SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(?1)";
Query query = entityManager.createNativeQuery(nativeSql, DeviceEntity.class);
query.setParameter(1, Arrays.asList("A"));

执行后触发错误：

Caused by: [CIRCULAR REFERENCE: org.postgresql.util.PSQLException: ERROR: op ANY/ALL (array) requires array on right side Position: 152]

请问哪里操作出错了？

问题原因 #

PostgreSQL的ANY()操作符要求右侧必须是PostgreSQL原生数组类型，但你直接传入Arrays.asList("A")时，JDBC驱动无法自动将其转换为PostgreSQL可识别的数组，数据库因此判定右侧不是合法数组，抛出错误。

解决方法 #

方法一：用PostgreSQL数组语法构造参数 #

修改SQL语句，使用array[]语法定义数组，同时传入单个或多个参数：

-- 单个元素
SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(array[?1])

String nativeSql = "SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(array[?1])";
Query query = entityManager.createNativeQuery(nativeSql, DeviceEntity.class);
query.setParameter(1, "A");

-- 多个元素场景示例
-- String nativeSql = "SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(array[?1, ?2])";
-- query.setParameter(1, "A");
-- query.setParameter(2, "B");

方法二：传入JDBC Array对象 #

通过EntityManager获取数据库连接，创建PostgreSQL兼容的Array对象后传入参数，适合动态元素数量的场景：

String nativeSql = "SELECT * FROM devices_resource_tree t WHERE t.path <@ ANY(?1)";
Query query = entityManager.createNativeQuery(nativeSql, DeviceEntity.class);

// 生成PostgreSQL数组对象
Connection conn = entityManager.unwrap(Connection.class);
Array pgArray = conn.createArrayOf("text", new String[]{"A"}); // 多个元素可扩展数组内容
query.setParameter(1, pgArray);

额外提示 #

确保传入的字符串符合LTREE类型的格式规范（比如用.分隔层级，例如A.B.C），否则即使数组格式正确，也可能出现匹配失败的情况。

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