hibernate with ltree native named query

Question

I am trying to run PostgreSQL native query that contains ltree functions and operators.

Here's the definition:

@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")

invoked like:

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();
}

result is error ERROR: operator does not exist: ltree ~ character varying.

when I try to run the query directly against database it runs ok:

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)

from the error it's obvious that hibernate(?) dislikes the type on the right side of th ~ operator, but as you can see, I am using the string in the later query and works fine.

So what do I need to do with hibernate query to run the query successfully?

EDIT: when I replace okm_path ~ :pathLike for "where okm_path ~ '*._root_.*' " I will be given:

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

hibernate: 5.2.9.Final

postgresql: 9.2.23

score 3 · Accepted Answer · answered Dec 27 '18 at 21:27

3

it turned out that there is lquery() function that needs to be called when you do operations against lquery.

so my query translates to

...
where okm_path ~ lquery(:pathLike)
...

and this solves the problem

answered Dec 27 '18 at 21:27

greengold

1,184
3
18
43

Nivas · Answer 2 · 2018-12-26T15:37:20.253

0

The error

operator does not exist: ltree ~ character varying

should be read as

operator does not exist: <left_data_type> <operator> <right_data_type> varying

Which means the operator is not defined for these data types. This happens when, for example, the left side of the operator is a integer and the right side a varchar, the error that time would be ERROR: operator does not exist: integer = character varying.

The problem here is when you set the value for right side,

query.setParameter("pathLike", pathLike)

pathLike is a string. So Postgres sees this as comparing a ltree to a string. When you execute the SQL directly the right hand side is taken as a ltree expression than a string.

I am not sure if this will work but can you try ltree can be directly cast to a varchar, but can you try this?:

query.setParameter("pathLike", pathLike, Hibernate.OBJECT)

See also Java type in JDBC to Postgres ltree

edited Dec 26 '18 at 15:37

answered Dec 26 '18 at 15:11

Nivas

18,126
4
62
76

when i convert leftside ltree to `ltree2text(okm_path) ~ '*._root_.*'` it gives out `[2201B] ERROR: invalid regular expression: quantifier operand invalid`. I need `lquery` on the rightside. but there is no function like `text2lquery` so hibernate cannot understand it somehow gives wrong information to postgre – greengold Dec 26 '18 at 15:19
@greengold I see. I think the problem is, when you pass `*._root_.*` from hibernate it is passed as a string (because `pathLike` is a string in `query.setParameter("pathLike", pathLike)`)whereas when you execute the query directly (or hardcode the values) the param is taken as a ltree expression rather than a string. I am not too familiar with ltrees so I am not sure if there is a way to set the ltree expression explicitly. – Nivas Dec 26 '18 at 15:23
@greengold Can you try `query.setParameter("pathLike", pathLike, Hibernate.OBJECT)` ? See also https://stackoverflow.com/questions/21447077/java-type-in-jdbc-to-postgres-ltree – Nivas Dec 26 '18 at 15:38
firstly, `Query` accepts only `TemporalType` as a third parameter. in the thread you linked, third parameter is being set to prepared statement instead. then, `TemporalType` has only date, time, timestamp enums defined in it so no really matches my case. I have edited my question and added what happens when I leave out `:pathLike` parameter of the query and hardcode some value directly. – greengold Dec 26 '18 at 16:16

hibernate with ltree native named query

2 Answers2