Postgres ltree in hibernate query

Question

I use Play Framework 1.2.5.3 and PostgreSQL 9.x.

I have class WorkArea as db model with field

@Column(columnDefinition = "ltree", insertable = false, updatable = false)
public String tree;

In the postgre it is a database table workareas with field tree (type ltree)

I want to use Hibernate query with postgres ltree operations. For example:

List<WorkArea> workAreas = WorkArea.find("tree @> :tree").bind("tree", otherArea.tree).fetch();

Or

List<WorkArea> workAreas = WorkArea.find("ltree_isparent(:tree, tree)").bind("tree", otherArea.tree).fetch();

How can I do that?

Answer 1

If you want to call a stored procedure you're going to need to use a native query.

Query query = JPA.em().createNativeQuery("some sql query :foo", WorkArea.class);
query.setParameter("foo", "bar");
List<WorkArea> results = query.getResultList();