Jpa composite key nullable columns

Question

I'm using Hibernate's JPA impl to model some tables. I'm having trouble mapping a table that:

• Has no primary key
• Has a unique index on 4 columns, 3 of which can be nullable

I tried to hack it and define the index as a composite Id, but since some columns are nullable this is not working properly. Is this possible with JPA/Hibernate?

Thanks

Bussard

Answer 1

A work arround is... You should implements your own UserType implementation and treats the null value to return a representative Object for this.

Look my example. The field is a nullable numeric, so my implementation is:

Looks like in HBM file:

   <key-property name="fieldName" type="mypackage.forUserTypes.DefaultLongType">
    <column name="FIELD_NAME" precision="10" scale="0" />
   </key-property>

...In Java:

public class DefaultLongType implements UserType {
private static final long serialVersionUID = 1L;

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#assemble(java.io.Serializable, java.lang.Object)
 */
public Object assemble(Serializable cached, Object owner)
        throws HibernateException {
    return null;
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#deepCopy(java.lang.Object)
 */
public Object deepCopy(Object value) throws HibernateException {
    return null;
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#disassemble(java.lang.Object)
 */
public Serializable disassemble(Object value) throws HibernateException {
    return null;
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#equals(java.lang.Object, java.lang.Object)
 */
public boolean equals(Object x, Object y) throws HibernateException {
    if (x == y) return true;
    if (x == null) return false;
    return x.equals(y);
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#hashCode(java.lang.Object)
 */
public int hashCode(Object x) throws HibernateException {
    return x == null ? 0 : x.hashCode();
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#isMutable()
 */
public boolean isMutable() {
    return false;
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
 */
public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws HibernateException, SQLException {
    final long value = rs.getLong(names[0]);
    if (rs.wasNull()) {
        return new Long(Long.MIN_VALUE);
    }
    return new Long(value);
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
 */
public void nullSafeSet(PreparedStatement st, Object value, int index)
        throws HibernateException, SQLException {
    Long l = (Long) value;
    if (l == null || l.longValue() == Long.MIN_VALUE) {
        st.setNull(index, Types.NUMERIC);
    }
    else {
        st.setLong(index, l.longValue());
    }
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#replace(java.lang.Object, java.lang.Object, java.lang.Object)
 */
public Object replace(Object original, Object target, Object owner)
        throws HibernateException {
    return original;
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#returnedClass()
 */
public Class returnedClass() {
    return Long.class;
}

/* (non-Javadoc)
 * @see org.hibernate.usertype.UserType#sqlTypes()
 */
public int[] sqlTypes() {
    final int[] args = { Types.NUMERIC };
    return args;
}

}

Hamnet

Answer 2

It seems that the column that is not nullable should be your primary key. No parts of a composite key should ever be nullable.

You will need to take the nullable properties and place them outside of your primary/composite key.

Also, this looks like a duplicate of Hibernate mapping a composite key with null values which came up as #3 when I googled "null composite key".

Carper

Notice that I can't put the non-nullable column as PK, since the column itself is not unique, only the combination of columns. I know this is bad design, but I don't have control over the DB model. – Bussard

Since the combination of the columns is always unique (wether there are null values or not) and the set of columns is never null (because of the not-nullable column) I thought that this could be done. – Bussard

Databases are not supposed to allow this, but some do. It is unfortunate that you do not have control over the database schema. Are you able to talk to the people that are in control of the schema? Sorry, but I do not know of a workaround/hack to make this work with hibernate. – Carper

Thanks for all the help, but unfortunately I do not have control over the schema, but I'll try and see if the owners can add a true PK. – Bussard

I am having the same problem. I have a @Embedded with 4 attributes (this is a view and I cannot control it). And some of them can be null, but the composition of them cannot be the same (ford,red,4,4), (ford,red,4,null) for example. How can I do? – Frazee