JPA Postgres查询ltree路径

Question

使用Spring Boot和postgres。我在数据库中有分层数据，路径存储在一个ltree列中。我试图根据路径获取一个特定的对象，但在查询数据库时遇到了问题。

模型类：

@Entity
@Table(schema = "devschema", name = "family")

public class Family {

    @Id
    @Column(name = "member_id")
    private Long memId;

    @Column(name = "name")
    private String memName;

    @Column(name = "fam_path",  columnDefinition="ltree")
    private String familyPath;

    ... getters and setters
}

存储库类：

  public interface OrgRepository extends PagingAndSortingRepository <Family, Long>{

    public Family findByMemId(Long id);
    public Family findByMemName(String memName);

    @Query("select f from Family f where familyPath = ?1")
    public Family findByPath(String path);
    }

来自控制器的调用，将path作为名为path的字符串变量传入：

desiredMember =familyRepository.findByPath(路径)；

产生以下错误：

2016-02-15 20:41:06.430 ERROR 88677 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: operator does not exist: devschema.ltree = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 397
2016-02-15 20:41:06.449 ERROR 88677 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception  is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is  org.hibernate.exception.SQLGrammarException:  could not extract ResultSet] with root cause

org.postgresql.util.PSQLException: ERROR: operator does not exist: fhschema.ltree = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 397

我尝试将f转换为text，但无济于事。有谁有办法解决这个问题吗？

Answer 1

您选择了错误的运算符。

下面列出了@>、<@等可用的运算符：

postgres ltree documentation

Answer 2

PostgreSQL docs，https://www.postgresql.org/docs/current/static/ltree.html，说@>运算符需要gist索引。我发现在大型数据集上它的速度要慢得多。(我删除了它。)

所以我一直在使用我满意的波浪号运算符。它不需要gist索引。

select * from Family where familyPath ~ '*.Smith.*'

或者，如果您知道它总是路径的终点，请留下我们的最后一个星号：

select * from Family where familyPath ~ '*.Smith'