Hibernate and Oracle VARRAYS/NESTED TABLE

Question

Oracle supports the use of VARRAYS and NESTED TABLE data types, allowing multivalued attributes. (http://www.orafaq.com/wiki/NESTED_TABLE)

I am currently using Hibernate 3 as my ORM framework, but I can't see how I can map Hibernate to a NESTED TABLE/VARRAY data type in my database.

I looked at defining custom types in Hibernate, with no success. (Can Hibernate even handle the "COLUMN_VALUE" Oracle keyword necessary to unnest the subtable?)

Does anyone know how to implement these data types in Hibernate?

Thank you all for your help.

-- TBW.

Answer 1

Hibernate's UserType for Oracle's TABLE OF NUMBERS. OracleNativeExtractor found here : https://community.jboss.org/wiki/MappingOracleXmlTypeToDocument . String YOUR_CUSTOM_ARRAY_TYPE replace with your name.

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.apache.commons.lang.ArrayUtils;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;    

public class ArrayUserType
    implements UserType, Serializable {

private static final OracleNativeExtractor EXTRACTOR = new OracleNativeExtractor();

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

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

@Override
public boolean equals(Object x, Object y) throws HibernateException {
    if (x == null && y == null) return true;
    else if (x == null && y != null) return false;
    else 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 Arrays.asList(ArrayUtils.toObject(((ARRAY) rs.getObject(names[0])).getLongArray()));
}

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
    ARRAY array = null;
    if (value != null) {
        Connection nativeConn = EXTRACTOR.getNativeConnection(st.getConnection());
        ArrayDescriptor descriptor =
                ArrayDescriptor.createDescriptor("YOUR_CUSTOM_ARRAY_TYPE", nativeConn);
        array = new ARRAY(descriptor, nativeConn, ((List<Long>) value).toArray(new Long[]{}));
    }

    st.setObject(1, array);
}

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

    return new ArrayList<Long>((List<Long>) value);
}

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

public Object assemble(Serializable _cached, Object _owner)
        throws HibernateException {
    return _cached;
}

public Serializable disassemble(Object _obj)
        throws HibernateException {
    return (Serializable) _obj;
}

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

Answer 2

I hope I'm wrong and that you find a better answer in your research, but this feature is not supported in Hibernate. Hibernate relies on standard JDBC to talk to a database and these features are not part of the standard. They are Oracle extensions.

That said, I can think of a few workarounds:

1) Implement your own UserType. With your specific user type, you'll have a chance to manipulate the values provided by the database (or about to be sent to the database). But that will only work if Oracle provides this value as one of these java.sql.Types: http://download.oracle.com/javase/1.5.0/docs/api/java/sql/Types.html

2) The other option is to use JDBC directly, through the use of a Hibernate worker. See this example of a Worker: https://github.com/hibernate/hibernate-core/blob/master/hibernate-core/src/test/java/org/hibernate/test/jdbc/GeneralWorkTest.java

That said, I think that you have to weight the solutions and re-evaluate if you really need a nested table.