select statement with in clause

[Tom Turelinckx]

(In reply to this topic (http://forum.springframework.org/showthread.php?t=10190), 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.