Thursday, April 19, 2012

SQL 'IN' clause using EJB Native Query

Consider a case where we need to retrieve employees where Department and Employees entities are associated by One to Many mapping, the retrieval is using complex queries with a complex result set.


Ex: JPQL with "IN clause" where we need to pass series of values in one parameter, say list type. As the Java Persistence Query Language does not support passing a list of items as an input/named parameter, we are limited by the JPQL language to use "IN clause" in named queries.


Here is one solution to overcome this limitation, this can be achieved by creating native SQL queries to run complex queries and also handle complex result sets.


First, create entities based on Department, Employees, then create a stateless session bean and data control for the session bean. Add the below code to the session bean and expose the method in local/remote interface and generate a data control for that.

Note: Parameters in the example below are hard coded.

The code below describes one way of creating a native query and defining a result set that can map to an entity. Notice that the result set is mapped to the Employees entity class.

Note:- Here in the below code "em" is a EntityManager.

 public List<Employees> NativeQuery() {
        String queryString =
            "select * from Employees where department_id in (10, 20, 40, 50) order by department_id ASC";
        return em.createNativeQuery(queryString,
                                    Employees.class).getResultList();
    }

Here is another way of creating a native query using custom code to generate a custom result set.

 public List<Employees> NativeQueryCustomCode() {
        String queryString =
            "select * from Employees where department_id in (10, 20, 40, 50) order by department_id ASC";
        Query genericSearchQuery =
            em.createNativeQuery(queryString, "InQuery");
        List resultList = genericSearchQuery.getResultList();
        Iterator resultListIterator = resultList.iterator();
        List<Employees> employeesList = new ArrayList();
        while (resultListIterator.hasNext()) {
            Object col[] = (Object[])resultListIterator.next();
            Employees employees = new Employees();
            BigDecimal employeeId = (BigDecimal)col[0];
            employees.setEmployeeId(employeeId.longValue());
            employees.setFirstName((String)col[1]);
            employees.setLastName((String)col[2]);
            employees.setEmail((String)col[3]);
            employees.setPhoneNumber((String)col[4]);
            employees.setJobId((String)col[6]);
            BigDecimal salary = (BigDecimal)col[7];
            employees.setSalary(salary.doubleValue());
            employees.setCommissionPct((Double)col[8]);

            Departments departments = new Departments();
            BigDecimal departmentId = (BigDecimal)col[10];
            departments.setDepartmentId(departmentId.longValue());
            employees.setDepartments(departments);

            employeesList.add(employees);
        }
        return employeesList;
}

In the ViewController create a file NativeQuery.jspx, from the DataControl palette drag and drop NativeQuery->Employees as ADF Read-only Table and select the columns to be displayed.
Run NativeQuery.jspx, Employees who belong to the departments with Id (10,20,30,40) should only be displayed.

No comments:

Post a Comment