Using PostgreSQL ltree type with a Java ORM layer

Question

I like to use ltree from PostgreSQL contrib in one of my projects, and i like to use some kind of ORM layer (Hibernate, EclipseLink) on top of the database. I didn't find anything useful about using this type with persistence. I guess i have to extend the current PostgreSQL dialect with a new type and the corresponding operators. However i don't really know where to start and what is the correct way to do this. ltree works very much like a string so guess i should start form a string representation.

Can somebody give me suggestions and/or links to examples which are doing similar things? I couldn't find a complete tutorial yet.

Answer 1

this:

@Column(name = "dir", nullable = false, columnDefinition = "ltree")
@Type(type = "ru.zen0n.hibernate.types.LTreeType")
private String path;

and this:

package ru.zen0n.hibernate.types;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class LTreeType implements UserType {

    @Override
    public int[] sqlTypes() {
        return  new int[] {Types.OTHER};
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return String.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException {
        return rs.getString(names[0]);
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        st.setObject(index, value, Types.OTHER);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return new String((String)value);
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable)value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        // TODO Auto-generated method stub
        return deepCopy(original);
    }

}

Answer 2

Here some pointers that may help you. It is not a complete answer but it is a bit too much for a comment.

Both Hibernate and EclipseLink implement the JPA standard but also have some extensions of their own and allow for custom behavior.

I haven't done anywork with ltree's in specific but I think you could just use the java String class and add an annotation to the column to overrule the normal column type used (this can be done in standard JPA).

@Column(columnDefinition="ltree")
private String myLtreeValue;

If a String an the Java side is not sufficient you can create your own class and write a converter class for it. You can see an example of that in this question. This would be persistence provider dependent.

When performing queries with conditions involving ltree values you would probably be best of using the JPA @NamedNativeQuery annotation to define queries involving the special operators. For an example see here