java - 使用 ltree 本机命名查询休眠

问题描述

我正在尝试运行包含ltree函数和运算符的 PostgreSQL 本机查询。

这是定义：

@NamedNativeQuery(
            name = "pathSegmentQuery",
            query = "select ltree2text(okm_path) as okm_path, " +
                    "       index(okm_path, text2ltree(:lastSegment)) + 2 <> nlevel(okm_path) as haschild, " +
                    "       case " +
                    "         when index(okm_path, text2ltree(:lastSegment)) + 1 <> nlevel(okm_path) " +
                    "                 then ltree2text(subpath(okm_path, index(okm_path, text2ltree(:lastSegment)) + 1, 1)) " +
                    "           end as child " +
                    "from document " +
                    "where okm_path ~ :pathLike " +
                    "and " +
                    "index(okm_path, text2ltree(:path)) + 1 <> nlevel(okm_path) ",
            resultSetMapping = "pathSegmentQueryRSMapping")

像这样调用：

public List<PathSegment> getPathChildren(String path, String lastSegment) {
    Query query = entityManager.createNamedQuery("pathSegmentQuery");
    String pathLike = "'*." + path + ".*'";

    query.setParameter("path", path);
    query.setParameter("pathLike", pathLike);
    query.setParameter("lastSegment", lastSegment);

    return query.getResultList();
}

结果是错误ERROR: operator does not exist: ltree ~ character varying。

当我尝试直接针对数据库运行查询时，它运行正常：

select ltree2text(okm_path) as okm_path,
   index(okm_path, text2ltree('_root_')) + 2 <> nlevel(okm_path) as haschild,
   case
     when index(okm_path, text2ltree('_root_')) + 1 <> nlevel(okm_path)
             then ltree2text(subpath(okm_path, index(okm_path, text2ltree('_root_')) + 1, 1))
       end as child
from document
where
    okm_path ~ '*._root_.*'
and
    index(okm_path, text2ltree('_root_')) + 1 <> nlevel(okm_path)

从错误中可以明显看出 hibernate(?) 不喜欢 th 运算符右侧的类型~，但是正如您所看到的，我在后面的查询中使用了该字符串并且工作正常。

那么我需要对休眠查询做什么才能成功运行查询呢？

编辑：当我替换我时okm_path ~ :pathLike，"where okm_path ~ '*._root_.*' "我会得到：

org.postgresql.util.PSQLException: ERROR: syntax error at position 0错误

休眠：5.2.9.Final

PostgreSQL：9.2.23

标签： javapostgresqlhibernateltree

java - 使用 ltree 本机命名查询休眠

问题描述

解决方案

推荐阅读