ADF Model: Generating and using 'in' clause in VO sql statement

We know how to create view criteria declartively, execute it programatically and use the query results as needed. But, creating a view criteria that uses 'in' clause is not possible declaratively. So, here we'll see how to form a query criteria that uses 'in' clause and also meets the performance standards.

Here, we'll see how to to form a query statement to use list of values using SQL 'in' clause.

Requirement: For example, we have a list of employee nos and we need to form an SQL like 'select * from emp where empno in (empno1, empno2, empno3, and so on)'. Here, we should be able to form a query that can accept 'n' (where 'n' can be dynamic) no. of employee nos and use bind variables instead of hard coded the query.

Solution: We don't have declarative way of forming query using 'in' clause. So, we have to do it programatically.

For e.g., if the Empno list has 4 employee ids, we have to form the query like

select * from emp where empno in (:empno1,:empno2,:empno3,:empno4)

OR

select * from emp where empno in (:1,:2,:3,:4)

In the above SQL stmts, the first one uses the named bind parameters while the second one uses positional parameters.

To achieve the above requirement, generate the 'in' clause programatically using the following methods.

Forming 'in' clause with named bind parameters:

    private String getInClauseWithParamNames(List ids) {
        //logic to form the in clause with multiple bind variables
        StringBuffer inClause = new StringBuffer();
        for (int i = 1; i < ids.size() + 1; i++) {
            inClause.append(":empno" + (i));
            if (i < ids.size()) {
                inClause.append(",");
            }
        }
        return inClause.toString();
    }

Forming 'in' clause with positional bind parameters:

    private String getInClause(List ids) {
        //logic to form the in clause with multiple bind variables
        StringBuffer inClause = new StringBuffer();
        for (int i = 1; i < ids.size() + 1; i++) {
            inClause.append(":" + (i));
            if (i < ids.size()) {
                inClause.append(",");
            }
        }
        return inClause.toString();
    }

Use the generated 'in' clause with dynamic bind variables in the SQL stment and set the where clause programatically with vo.setWhereClause() method. Now, pass values for the bind variables progamatically and execute the query. Sample code is given below:

Using 'in' clause with named bind parameters:

    public Row[] getEmployees1(List empIds) {
        ViewObjectImpl empVO = this.getEmpVO();
        String inClause = getInClauseWithParamNames(empIds);
        //setting the where cluase to use the generated in clause
        empVO.setWhereClause("EmpEO.EMPNO in (" + inClause + ")");
        //clearing all existing where clause params if any
        empVO.setWhereClauseParams(null);
        //setting values for all bind variables one by one in the in clause
        for (int i = 0; i < empIds.size(); i++) {
            //defining the named bind variables programatically
            empVO.defineNamedWhereClauseParam("empno" + (i + 1), null, null);
            //setting the value for each named bind variable
            empVO.setNamedWhereClauseParam("empno" + (i + 1), empIds.get(i));
        }
        empVO.setRangeSize(-1);
        //executing the query
        empVO.executeQuery();
        //returning the rows from query result
        return empVO.getAllRowsInRange();
    }

Using 'in' clause with positional bind parameters:

    public Row[] getEmployees(List empIds) {
        ViewObjectImpl empVO = this.getEmpVO();
        String inClause = getInClause(empIds);
        //setting the where cluase to use the generated in clause
        empVO.setWhereClause("EmpEO.EMPNO in (" + inClause + ")");
        //clearing all existing where clause params if any
        empVO.setWhereClauseParams(null);
        //setting values for all bind variables one by one in the in clause
        for (int i = 0; i < empIds.size(); i++) {
            //setting the value for each positional bind variable
            empVO.setWhereClauseParam(i, empIds.get(i));
        }
        empVO.setRangeSize(-1);
        //executing the query
        empVO.executeQuery();
        //returning the resultant rows
        return empVO.getAllRowsInRange();
    }

Sample method that forms list of empnos, calls the above methods, gets the required results and prints the results:

    public void sampleMethod() {
        //Forming a list of employee ids
        List<Long> empIds = new ArrayList<Long>();
        empIds.add(new Long(7499));
        empIds.add(new Long(7521));
        empIds.add(new Long(7566));
        empIds.add(new Long(7654));
        empIds.add(new Long(7698));
        empIds.add(new Long(7788));
        
        //Get employee rows from list of empIds
        //1. Using positional parameters
        //Row[] empRows = getEmployees(empIds);
        //2. Using named bind parameters
        Row[] empRows = getEmployees1(empIds);

        //iterating through the employee rows and printing the emp name
        for (int i = 0; i < empRows.length; i++) {
            Row empRow = empRows[i];
            System.out.println("Emp Name " + (i + 1) + ": " +
                               empRow.getAttribute("Ename"));
        }
    }

The above code is self-explanatory. You can download the sample application from here. Once downloaded, run/debug the DemoAM and execute the sampleMethod. You'll get the following result:

If you look at the log window, you can see the SQL query statements generated as below at runtime.

Generated SQL stmt with named bind variables:


Generated SQL stmt with positional bind variables:

This query statement uses bind variables instead of hard coded the statement and results in a prepared statement at runtime. Hence, the stmt will be compiled only once and the same will be reused for multiple calls. So, this is the most performant way of generating and executing the SQL programatically.