Announcement Announcement Module
Collapse
No announcement yet.
select statement with in clause Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • select statement with in clause

    (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:

    Code:
    CREATE TYPE ct_number AS TABLE OF NUMBER;
    Then you can use a sql string like this:

    Code:
    SELECT ename
      FROM emp
     WHERE empno IN (SELECT column_value
                       FROM TABLE (CAST (? AS ct_number)))
    Declare an array parameter for the query:

    Code:
    declareParameter(new SqlParameter(Types.ARRAY, "SCOTT.CT_NUMBER"));
    And use an Oracle-specific SqlTypeValue implementation:

    Code:
    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.
    Last edited by Rod Johnson; Jan 18th, 2006, 11:40 AM.
Working...
X