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?

Captain Man · Accepted Answer · 2016-12-29 15:03:36Z

8

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;

edited Dec 29, 2016 at 15:03

Captain Man

7,8946 gold badges55 silver badges83 bronze badges

answered Dec 29, 2016 at 14:47

arnabbiswas

962 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raj Over a year ago

Does this custom type implicitly getting picked up while persisting with the respective entities? I mean, does hibernate locates and loads the class specified by "type" if its implemented by "UserType"? Or, does the custom type need to be introduced to Hibernate in some way? To me, it does not seem to get picked up, until I used "org.hibernate.boot.model.TypeContributor" to introduce my custom type.

Jan Mares Over a year ago

@Raj or you can use @Type(type = "com.my.package.LTreeType")

Jan Mares Over a year ago

@Raj or one more way, put @TypeDef(name = "ltree", typeClass = LTreeType.class) on the entity class and then @Type(type="ltree")

Paula Sartori · Accepted Answer · 2019-05-16 16:42:59Z

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

allenjom · Accepted Answer · 2017-10-13 20:21:33Z

1

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>

answered Oct 13, 2017 at 20:21

allenjom

1372 silver badges5 bronze badges

Comments

IkayU · Accepted Answer · 2023-12-03 11:19:39Z

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;

Collectives™ on Stack Overflow

Getting error when mapping PostgreSQL LTREE column in hibernate

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related