Search code examples
springpostgresqlhibernatejpaltree

Spring JPA with Postgres ltree extension

I am trying to make ltree indexing work with Spring JPA. I have an entity that contains the path like this

@Type(type = "com.thomaster.ourcloud.model.filesystem.LTreeType")
@Column(columnDefinition="ltree")
private String relativePath;

The LTreeType class is a copy paste from here Getting error when mapping PostgreSQL LTREE column in hibernate.

The index is properly done in the database, the ltree extension is added. Still when I run the following query 

@Query(value = "SELECT * FROM file_system_element WHERE relative_path ~ lquery(:pathToSearch)", nativeQuery = true)
List<FileSystemElement> findAllByPath(@Param("pathToSearch") String pathToSearch);

I get org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111.

When I change the query to 

SELECT * FROM file_system_element WHERE relative_path ~ CAST(:pathToSearch AS lquery)
#OR TO THIS
SELECT * FROM file_system_element WHERE relative_path @> CAST(:pathToSearch AS ltree)
#OR TO THIS
SELECT * FROM file_system_element WHERE relative_path @> ltree(:pathToSearch)
#OR TO THIS
SELECT * FROM file_system_element WHERE ltree(relative_path) ~ lquery(:pathToSearch)

I get the same error.

For

SELECT * FROM file_system_element WHERE relative_path @> lquery(:pathToSearch)

I get

org.postgresql.util.PSQLException: ERROR: operator does not exist: ltree @> lquery

And for: 

SELECT * FROM file_system_element WHERE ltree(relative_path) ~ ltree(:pathToSearch)

I get: 

ERROR: operator does not exist: ltree ~ ltree

As you can see, I tried almost every combination, and still no result. I also tried the same queries as native queries on the entity manager, but it makes no difference. What am I missing here?

Solution

  • Solved it, it was my mistake. The query parameter was

    "folder.anotherFolder"

    instead of

    "folder.anotherFolder.*"