Hibernate support for Oracle Array list joins

Question

Oracle supports a query syntax where a table is joined to an Oracle collection type (Nested table or VARRAY). This semantic can be used instead of the in (1,2,3) syntax in SQL, and allows an array of values to be bound to the query. This can be done using Oracle's JDBC driver.

This type of query is known as a Pickler Fetch. It is much more scalable than using SQL IN Lists. My application could have ~10,000 values in the collection.

My problem is that I am new to Hibernate (we are using Hibernate 3.2.5 and Spring 2.0.6) and do not see how this semantic can be implemented using Hibernate. Typically a JDBC implementation would work like this: Define a user defined type in the database using 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

This is purely person option from past projects, but the combination of Oracle, Hibernate and Spring is not stable. Hibernate's view of transactions doesn't match up with Oracle, and when things fail they tend to cascade upwards through the Spring components.

Answer 2

I researched further, and if we move to the current Hibernate version I could create my own JDBC DAO. I would have to implement the org.hibernate.jdbc.Work interface, but its doable. It would be working around hibernate. An example of the approach is here: http://www.informit.com/guides/content.aspx?g=java&seqNum=575

I'd still like to know if there is a better way to do this though. Especially since the Work Interface is not available in 3.2.5