Hibernate and Oracle VARRAYS / NESTED TABLE

Question

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

I am currently using Hibernate 3 as my ORM structure, but I do not see how I can map Hibernate to the NESTED TABLE / VARRAY data type in my database.

I looked at defining custom types in Hibernate, without any success. (Can Hibernate process the Oracle keyword "COLUMN_VALUE" to disable the subtable?)

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

Thank you all for your help.

- TBW.

Answer 1

Hibernate UserType for Oracle TABLE OF NUMBERS. OracleNativeExtractor can be found here: https://community.jboss.org/wiki/MappingOracleXmlTypeToDocument . Replace the string YOUR_CUSTOM_ARRAY_TYPE 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 you found the best answer in your research, but this feature is not supported in Hibernate. Hibernate relies on standard JDBC to talk to the database, and these features are not part of the standard. These are Oracle extensions.

However, I can come up with a few workarounds:

1) Deploy your own UserType. With your specific type of user, you will be able to manipulate the values ​​provided by the database (or should be sent to the database). But this 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) Another option is to use JDBC directly using a working Hibernate. See this working example: https://github.com/hibernate/hibernate-core/blob/master/hibernate-core/src/test/java/org/hibernate/test/jdbc/GeneralWorkTest.java

However, I think you need to weigh the decisions and reevaluate if you really need a nested table.