Getting error when mapping PostgreSQL LTREE column in hibernate

Question

I am trying to map postgresql ltree column in hibernate as follows:

In entity :

private String path;

@Column(name="org_path", columnDefinition="ltree")
public String getPath() {
   return path;

Table structure:

CREATE TABLE relationship (
    relationship_id int4 NOT NULL,
    parent_organization_id uuid NOT NULL,
    child_organization_id uuid NOT NULL,
    org_path ltree NOT NULL,
    CONSTRAINT relationship_pk PRIMARY KEY (relationship_id),
    CONSTRAINT organization_fk3 FOREIGN KEY (parent_organization_id) REFERENCES organization(organization_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT organization_fk4 FOREIGN KEY (child_organization_id) REFERENCES  organization(organization_id) ON DELETE RESTRICT ON UPDATE RESTRICT
)

Getting the following error:

wrong column type encountered in column [org_path] in table [relationship]; found [“schemaName"."ltree" (Types#OTHER)], but expecting [ltree (Types#VARCHAR)]

Can anyone help how to resolve this issue?

Answer 1

Implement a custom LTreeType class in Java as follows:

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);
    }

}

And annotate the Entity class as follows:

    @Column(name = "path", nullable = false, columnDefinition = "ltree")
    @Type(type = "LTreeType")
    private String path;

Answer 2

If you're here in 2023, the above codes will need to be updated based on 2023 updates to Hibernate (v 6.1.7). Below code worked for me for the LTreeType class:

    public class LTreeType implements UserType<String>{
    @Override
    public int getSqlType() {
        return Types.OTHER;
    }

    @Override
    public Class<String> returnedClass() {
        return String.class;
    }

    @Override
    public boolean equals(String s, String j1) throws HibernateException{
        return s.equals(j1);
    }

    @Override
    public int hashCode(String s) throws HibernateException{
        return s.hashCode();
    }

    @Override
    public String nullSafeGet(ResultSet resultSet, int i, SharedSessionContractImplementor sharedSessionContractImplementor, Object o) throws HibernateException, SQLException {
        return resultSet.getString(i);
    }

    @Override
    public void nullSafeSet(PreparedStatement preparedStatement, String s, int i, SharedSessionContractImplementor sharedSessionContractImplementor) throws SQLException {
        preparedStatement.setObject(i, s, Types.OTHER);
    }

    @Override
    public String deepCopy(String s) throws HibernateException{
        if (s == null)
            return null;
        if (! (s instanceof String))
            throw new IllegalStateException("Expected String, but got: " + s.getClass());
        return s;
    }

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

    @Override
    public Serializable disassemble(String s) throws HibernateException{
        return (Serializable)s;
    }

    @Override
    public String assemble(Serializable serializable, Object o) throws HibernateException{
        return serializable.toString();
    }

    @Override
    public String replace(String s, String j1, Object o) {
        return deepCopy(s);
    }

    @Override
    public long getDefaultSqlLength(Dialect dialect, JdbcType jdbcType) {
        return UserType.super.getDefaultSqlLength(dialect, jdbcType);
    }

    @Override
    public int getDefaultSqlPrecision(Dialect dialect, JdbcType jdbcType) {
        return UserType.super.getDefaultSqlPrecision(dialect, jdbcType);
    }

    @Override
    public int getDefaultSqlScale(Dialect dialect, JdbcType jdbcType) {
        return UserType.super.getDefaultSqlScale(dialect, jdbcType);
    }

    @Override
    public JdbcType getJdbcType(TypeConfiguration typeConfiguration) {
        return UserType.super.getJdbcType(typeConfiguration);
    }

    @Override
    public BasicValueConverter<String, Object> getValueConverter() {
        return UserType.super.getValueConverter();
    }

And then in the @Entity class, do something like

    @Column(name="tree_path", nullable = false, columnDefinition="ltree")
    @Type(LTreeType.class)
    private String path;

Answer 3

I had fits until I also created an LQueryType just like the class @arnabbiswas provided for LTreeType. My code only knows about Strings, but Postgres does not know how to use ltree with Strings. The types and operations are:

ltree ~ lquery
ltree @> ltree

So my Kotlin JPA is like this:

val descendantIds = treeRepo.findAllDescendantIds("*.$id.*{1,}")
. . .
@Query(
    "SELECT node_id FROM tree WHERE path ~ CAST(:idQuery AS lquery);"
    , nativeQuery = true)
fun findAllDescendantIds(@Param("idQuery") idQuery: String): Array<Long>

Answer 4

just add this modifications on @anarbbswas code and then it will work fine

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

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

@Override
public Object deepCopy(Object value) throws HibernateException {
    if (value == null)
        return null;
    if (! (value instanceof String))
        throw new IllegalStateException("Expected String, but got: " + value.getClass());
    return value;
}