Getting error when mapping PostgreSQL LTREE column in hibernate

3.7k views Asked by At

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?

4

There are 4 answers

3
arnabbiswas On BEST ANSWER

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;
0
IkayU On

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;
0
allenjom On

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>
0
Paula Sartori On

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