Hibernate 和 Oracle VARRAYS/NESTED TABLE

Hibernate and Oracle VARRAYS/NESTED TABLE

提问by TBW

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

Oracle 支持使用 VARRAYS 和 NESTED TABLE 数据类型，允许多值属性。( 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.

我目前使用 Hibernate 3 作为我的 ORM 框架，但我看不到如何将 Hibernate 映射到我的数据库中的 NESTED TABLE/VARRAY 数据类型。

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?)

我查看了在 Hibernate 中定义自定义类型，但没有成功。（Hibernate 甚至可以处理取消嵌套子表所需的“COLUMN_VALUE”Oracle 关键字吗？）

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

有谁知道如何在 Hibernate 中实现这些数据类型？

Thank you all for your help.

谢谢大家的帮助。

-- TBW.

- TBW。

回答by Marcin Jancewicz

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.

Oracle 数字表的 Hibernate 用户类型。OracleNativeExtractor 在这里找到：https://community.jboss.org/wiki/MappingOracleXmlTypeToDocument 。字符串 YOUR_CUSTOM_ARRAY_TYPE 替换为您的姓名。

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

回答by jpkrohling

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.

我希望我错了，希望您在研究中找到更好的答案，但 Hibernate 不支持此功能。Hibernate 依赖于标准的 JDBC 与数据库通信，而这些特性不是标准的一部分。它们是 Oracle 扩展。

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

1) 实现你自己的 UserType。使用您的特定用户类型，您将有机会操作数据库提供的值（或即将发送到数据库的值）。但这只有在 Oracle 将此值作为这些 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

2) 另一种选择是通过使用 Hibernate worker 直接使用 JDBC。请参阅此 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.

也就是说，我认为您必须权衡解决方案并重新评估您是否真的需要嵌套表。