select statement with in clause

[Tom Turelinckx]

(In reply to this topic, as I think it belongs in the Data Access forum rather than the Core Container one.)

Henry,

Since you're using Oracle, you could create a collection type:

CREATE TYPE ct_number AS TABLE OF NUMBER;

Then you can use a sql string like this:

SELECT ename
  FROM emp
 WHERE empno IN (SELECT column_value
                   FROM TABLE (CAST (? AS ct_number)))

Declare an array parameter for the query:

declareParameter(new SqlParameter(Types.ARRAY, "SCOTT.CT_NUMBER"));

And use an Oracle-specific SqlTypeValue implementation:

final int[] value = new int[]{120,121};
query.execute(new Object[]{new AbstractSqlTypeValue(){
    protected Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
        return new ARRAY(ArrayDescriptor.createDescriptor(typeName, con), con, value);
    }
}});

You'll probably need to configure a NativeJdbcExtractor on the JdbcTemplate used by the SqlQuery object.

Note that I haven't tried this code, but I've done something similar before. I suppose it's also possible to use more complex object types, like you'd need for the query in your first example.

Kind regards,
Tom.