Hibernate JPA DB2 Custom Data Type

Question

I am an unfortunate dev that needs to work with a very legacy database. The problem is, that the database contains custom datatypes:

CREATE DISTINCT TYPE SCHEMA.T_STUPID_ID AS
    SMALLINT WITH COMPARISONS
;

So I cannot change that, but need to get the data and query it. It appears that it does not work, cause when querying by Id (findOne) using spring data jpa (repository) I get the following error:

DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=4.19.26

which is : THE DATA TYPES OF THE OPERANDS OF AN OPERATION ARE NOT COMPATIBLE So it sounds like the queries do not work with custom types :/

I also tried this way:

@Query("select p from ImSickOfThisEntity p where cast(p.entityId as integer) 
= ?1 ")

But I got:

 DB2 SQL Error: SQLCODE=-461, SQLSTATE=42846, SQLERRMC=IPSDBO.T_PRODUCT_ID;SYSIBM.INTEGER

which translates to :

A VALUE WITH DATA TYPE source-data-type CANNOT BE CAST TO TYPE target-data-type

How to deal with such custom types? Google is not helpful, I can only find custom Java types with standard database column types....

Mamof · Accepted Answer · 2016-11-21 15:29:02Z

1

You'll need to implement a UserType and you can specify the sqlTypes.

public class SmallIntHibernateType implements UserType {

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

@Override
public Class returnedClass() {
    return Integer.class;
}

@Override
public boolean equals(Object o, Object o2) throws HibernateException {
    if (o == null && o2 == null) {
        return true;
    } else if (o == null) {
        return false;
    } else if (o2 == null) {
        return false;
    } else {
        return ((Integer) o).intValue() == ((Integer) o2).intValue();
    }
}

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

@Override
public Object nullSafeGet(ResultSet resultSet, String[] strings, SharedSessionContractImplementor sessionImplementor, Object o) throws HibernateException, SQLException {
    return resultSet.getInt(strings[0]);
}

@Override
public void nullSafeSet(PreparedStatement preparedStatement, Object value, int index, SharedSessionContractImplementor sessionImplementor) throws HibernateException, SQLException {
    if (value == null || value.equals(0)) {
        preparedStatement.setNull(index, Types.INTEGER);
        return;
    }

    if (!(value instanceof Integer)) {
        throw new UnsupportedOperationException("can't convert " + value.getClass());
    }

    preparedStatement.setInt(index, ((Integer) value));
}

@Override
public Object deepCopy(Object value) throws HibernateException {
    if (value == null) {
        return null;
    }

    if (!(value instanceof Integer)) {
        throw new UnsupportedOperationException("can't convert " + value.getClass());
    }

    return value;
}

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

@Override
public Serializable disassemble(Object value) throws HibernateException {
    if (!(value instanceof Integer)) {
        throw new UnsupportedOperationException("can't convert " + value.getClass());
    }

    return (Integer) value;
}

@Override
public Object assemble(Serializable cached, Object owner) throws HibernateException {
    if (!(cached instanceof Integer)) {
        throw new UnsupportedOperationException("can't convert " + cached.getClass());
    }

    return cached;
}

@Override
public Object replace(Object original, Object target, Object owner) throws HibernateException {
    return original;
}
}

Then on your entity you'll need to specify the using the type.

@Id
@Type(type = "com.data.system.SmallIntHibernateType")
private int id;

answered Nov 21, 2016 at 15:29

Mamof

1815 silver badges13 bronze badges

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

3 Comments

maslan Over a year ago

So if I am using the @Type annotation, what is the purpose of SmallIntHibernateType? Is it to be the where clause parameter in spring-data-jpa repository?

Mamof Over a year ago

The SmallIntHybernateType class is what actually does the work of telling hibernate what SQL type the column is and handle to convert it to your Java type. The @Type annotation is what points hibernate to look for your custom column type.

maslan Over a year ago

ok, that did not work, even without that, there is no problem with data retrieval. There is a problem with queries - the where clause does not work, as the DB2 states that its a bad type. The only solution I came with is a native query with cast in SpringDataJPA repository

Collectives™ on Stack Overflow

Hibernate JPA DB2 Custom Data Type

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related