Using Query By Example feature of DB Adapter - using SQL 'and' clause dynamically

QueryByExample option of the DB Adapter forms (infers) a SELECT statement at runtime, dynamically from the XML input that you provide while invoking the adapter service. So, it is not necessary to write a design time SELECT query.

Usecase
Say we have a query

SELECT * FROM departments WHERE location_id=1700 AND department_id=30

now, we require that if deparment id is supplied in the input only then the portion "AND department_id=30" will be used in the query.

To explain this, the default HR schema of the Oracle database and the DEPARTMENTS table in that schema can be used.

So, our input schema goes this way -

Here, we supply dept_id and loc_id as the input to fetch records using the DB Adapter service.

Configure the database adapter to use QueryByExample and select the table Departments

Our BPEL flow looks like  -

Assign the bpel inputs to the Departments collection elements of the adapter

Populate the output.
Now, test the composite first with input loc_id as 1700. You can see the output returns multiple records.
Now, test again with loc_id as 1700 and dept_id as 30. You can see the recods are filtered and the query returns a single record.

The working Jdev project can be found here .
[Note: You need to configure the datasource, connection pool and have a JNDI name jndi/hrS in the adapter configuration, to make this code functional w/o change]