Using IN clause is very common requirement when working with the ADF View Objects and rows have to be filtered on multiple values of the same attribute.
In this blog, Jobinesh shows a way to achieve this requirement using the bind variable. The same approach is not working when we have to use the multiple columns in IN clause like below:
select * from tableA where (col1,col2,col3) IN ()
We can use the following code to achieve this and have tested in ADF 11g.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import oracle.jbo.domain.Array; | |
| import oracle.jbo.domain.DomainContext; | |
| import oracle.sql.ArrayDescriptor; | |
| import oracle.sql.STRUCT; | |
| import oracle.sql.StructDescriptor; | |
| import java.sql.Connection; | |
| import java.sql.PreparedStatement; | |
| import java.sql.SQLException; | |
| import java.util.ArrayList; | |
| import java.util.HashMap; | |
| import java.util.List; | |
| /* create the DB object and table as below | |
| create or replace TYPE DB_OBJECT AS OBJECT( | |
| destcol1 varchar2(100 char), destcol2 number,destcol3 varchar2(100 char),destcol4 number ); | |
| create or replace TYPE DB_TABLE AS TABLE OF DB_OBJECT; | |
| */ | |
| public void setVOArrayBindVariable(){ | |
| Connection conn = null; | |
| PreparedStatement stmt = null; | |
| StructDescriptor structDescriptor = null; | |
| ArrayList<STRUCT> arrayList = new ArrayList<STRUCT>(); | |
| try{ | |
| stmt = getDBTransaction().createPreparedStatement("select 1 from dual", 0); | |
| conn = stmt.getConnection(); | |
| structDescriptor = StructDescriptor.createDescriptor("DB_OBJECT",conn); | |
| //loop through and populate your arrayList with the values to be used in IN clause | |
| for (int j = 0; j < 10; j++) { | |
| // constucting the Object to create struct definition | |
| Object[] o = new Object[]{getValue1(), getValue2(),getValue3(),getValue4()}; | |
| STRUCT item = new STRUCT(structDescriptor,conn,o); | |
| conn.createStruct("DB_OBJECT", o); | |
| arrayList.add(eventRowStruct); | |
| } | |
| if(eventDataIdList.size() > 0){ | |
| ArrayDescriptor tableArray = ArrayDescriptor.createDescriptor("DB_TABLE", conn); | |
| Array domainArray = new Array(tableArray,conn,arrayList.toArray()); | |
| //set the bind variable. your VO should have bind variable of type oracle.jbo.domain.Array | |
| vo.setbindEventDetailArray(domainArray); | |
| //execute the query | |
| vo.executeQuery(); | |
| } | |
| } catch (SQLException sqlex) { | |
| //do log the error | |
| } catch (Exception ex) { | |
| //do log the error | |
| } finally { | |
| try { | |
| stmt.close(); | |
| } catch (SQLException sqlex) { | |
| //do log the error | |
| } | |
| } | |
| } | |
| /* your getCriteriaItemClause() code look like below */ | |
| public String getCriteriaItemClause(){ | |
| //do the following only for your VC and VC item. details are omitted here | |
| String whereClause = "(1=1)"; | |
| String eoName = this.getEntityDef(0).getAliasName(); | |
| if(getbindDomainArray() != null){ | |
| whereCluase = | |
| " (" +eoName + ".col1,"+eoName + ".col2,"+eoName + ".col3,"+ eoName+".col4)" + | |
| " IN (SELECT destcol1, destcol2, destcol3, destcol4 FROM TABLE(:bindDomainArray))"; | |
| } | |
| return whereClause; | |
| } |
Siva's Blog 