Spring JPA with Postgres ltree extension (~ '*.)

Question

Not working while getting

Message = could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract

pathToSearch = "a.b"

@Query(value = "SELECT * FROM my_class WHERE path ~ '*.:pathToSearch.*{1}'", nativeQuery = true)
List<MyClass> getAllChild(@Param("pathToSearch") String pathToSearch);

Working in Native SQL i.e
SELECT * FROM my_class WHERE path ~ '*.a.b.*{1}';

@Query(value = "SELECT * FROM my_class WHERE path ~ '*.?1.*{1}'", nativeQuery = true) List getAllChild(@Param("pathToSearch") String pathToSearch); — Ramanuj, Jun 05 '20 at 02:18
tried above still getting same error (Caused by: org.hibernate.exception.SQLGrammarException:) — Ramanuj, Jun 05 '20 at 02:20

score 1 · Answer 1 · answered Jun 05 '20 at 06:55

1

Please post complete stack traces formatted as code in the future. Stack traces carry way more information then just the error message. That said, I think I can correctly guess what is going on here.

It looks like you are trying to use :pathToSearch as a bind parameter. But you made it part of a string literal: '.:pathToSearch.{1}'` therefore no bind parameter replacement is happening.

What you have to do is to assemble the complete string *.a.b.*{1} from its parts *., :pathToSearch, and .*{1}.

You can do this in the database by using the concat operation

SELECT * FROM my_class WHERE path ~ '*.' || :pathToSearch || '.*{1}'

Or you do it on the java side using a SpEL expression:

SELECT * FROM my_class WHERE path ~ :#{'*.' + #pathToSearch + '.*{1}'}

answered Jun 05 '20 at 06:55

Jens Schauder

77,657
34
181
348

Great ! Can you show how to use SpEL with full example in this case ?, I have some confusion actually. – Eklavya Jun 05 '20 at 07:01
Just put the query in the `value` of `@Query`. Just be aware that I typed that query without being able to check it. So it might (i.e. probably does) contain mistakes that need some debugging. – Jens Schauder Jun 05 '20 at 07:34
Can you update your question with that information and a full stack trace? – Jens Schauder Jun 11 '20 at 12:28
Please do not ask questions in comments. Ask questions in questions. – Jens Schauder Jun 22 '20 at 18:19

score 0 · Answer 2 · answered Jun 23 '20 at 02:16

Use the alternative as below:

Query will give you all the child node of a path:

select path from test where path <@ 'a.b.c' and path <> 'a.b.c';

JPA:

@Query(value = "SELECT * FROM test WHERE path <@ CAST(:pathToSearch AS ltree) and path <> CAST(:pathToSearch AS ltree)", nativeQuery = true)
List<Test> getAllPath(@Param("pathToSearch") String pathToSearch);

Spring JPA with Postgres ltree extension (~ '*.)

2 Answers2