Hibernate support for combining Oracle Array

Question

Oracle supports query syntax when a table is connected to an Oracle collection type (nested table or VARRAY). This semantics can be used instead of the in (1,2,3) syntax in SQL and allows you to bind an array of values ​​to the query. This can be done using the Oracle JDBC driver.

This type of request is called Pickler Fetch. This is much more scalable than using SQL IN Lists. My application can have ~ 10,000 values ​​in a collection.

My problem is that I am new to Hibernate (we use Hibernate 3.2.5 and Spring 2.0.6) and do not see how this semantics can be implemented using Hibernate. Typically, a JDBC implementation will work as follows: Define a custom type in the database using the CREATE type in SQL * Plus CREATE OR REPLACE TYPE NUMBER_LIST_TYPE AS TABLE of number;

In Java:

import java.sql.*; import oracle.sql.ArrayDescriptor; import oracle.sql.ARRAY; import oracle.jdbc.*; /* The oracle collection is described */ ArrayDescriptor oracleCollection = ArrayDescriptor.createDescriptor("NUMBER_LIST_TYPE",conn); PreparedStatement stmt = conn.prepareStatement( " SELECT ename,empno FROM emp " +" WHERE empno IN ( " +" SELECT * FROM TABLE( CAST ( ? as NUMBER_LIST_TYPE ) ) " +" ) " ); /* define our java array */ int[] javaArray1 = { 7369,7566,7782 }; /* define our oracle array */ ARRAY jdbcArray1 = new ARRAY (oracleCollection, conn, javaArray1); /* bind that array to our statement bind variable */ stmt.setObject(1,jdbcArray1); /* execute the query and browse the result */ ResultSet r=stmt.executeQuery(); while(r.next()){ System.out.println( "\t"+"\t"+r.getString(2)+": "+r.getString(1)); } 

Now, how can I implement something like this using Hibernate?

Answer 1

I explored further, and if we move on to the current version of Hibernate, I can create my own JDBC DAO. I would have to implement the org.hibernate.jdbc.Work interface, but this is doable. It will be in sleep mode. An example of this approach is: http://www.informit.com/guides/content.aspx?g=java&seqNum=575

I would still like to know if there is a better way to do this. Moreover, the working interface is not available in 3.2.5

Answer 2

This is a purely individual version from past projects, but the combination of Oracle, Hibernate and Spring is unstable. Hibernation of transactions does not match Oracle, and when things go wrong, they tend to cascade up through Spring components.