Achieve Named Criteria with multiple tables in EJB Data control
- by Deepak Siddappa
In EJB create
a named criteria using sparse xml and in named criteria wizard, only
attributes related to the that particular entities will be displayed.
So here we can filter results only on particular entity bean. Take a scenario where we need to create Named Criteria based on multiple tables using EJB. In BC4J we can achieve this by creating view object based on multiple tables. So in this article, we will try to achieve named criteria based on multiple tables using EJB.Implementation StepsCreate
Java EE Web Application with entity based on Departments and Employees,
then create a session bean and data control for the session bean.Create
a Java Bean, name as CustomBean and add below code to the file. Here in
java bean from both Departments and Employees tables three fields are
taken.
public class CustomBean {
private BigDecimal departmentId;
private String departmentName;
private BigDecimal locationId;
private BigDecimal employeeId;
private String firstName;
private String lastName;
public CustomBean() {
super();
}
public void setDepartmentId(BigDecimal departmentId) {
this.departmentId = departmentId;
}
public BigDecimal getDepartmentId() {
return departmentId;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public String getDepartmentName() {
return departmentName;
}
public void setLocationId(BigDecimal locationId) {
this.locationId = locationId;
}
public BigDecimal getLocationId() {
return locationId;
}
public void setEmployeeId(BigDecimal employeeId) {
this.employeeId = employeeId;
}
public BigDecimal getEmployeeId() {
return employeeId;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getFirstName() {
return firstName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getLastName() {
return lastName;
}
}
Open the sessionEJb file and add the below code to the session
bean and expose the method in local/remote interface and generate a data
control for that.
Note:- Here in the below code "em" is a EntityManager.
public List<CustomBean> getCustomBeanFindAll() {
String queryString =
"select d.department_id, d.department_name, d.location_id, e.employee_id, e.first_name, e.last_name from departments d, employees e\n" +
"where e.department_id = d.department_id";
Query genericSearchQuery = em.createNativeQuery(queryString, "CustomQuery");
List resultList = genericSearchQuery.getResultList();
Iterator resultListIterator = resultList.iterator();
List<CustomBean> customList = new ArrayList();
while (resultListIterator.hasNext()) {
Object col[] = (Object[])resultListIterator.next();
CustomBean custom = new CustomBean();
custom.setDepartmentId((BigDecimal)col[0]);
custom.setDepartmentName((String)col[1]);
custom.setLocationId((BigDecimal)col[2]);
custom.setEmployeeId((BigDecimal)col[3]);
custom.setFirstName((String)col[4]);
custom.setLastName((String)col[5]);
customList.add(custom);
}
return customList;
}
Open the DataControls.dcx file and create sparse xml for customBean.
In sparse xml navigate to Named criteria tab -> Bind Variable
section, create two binding variables deptId,fName.
In
sparse xml navigate to Named criteria tab ->Named criteria, create a
named criteria and map the query attributes to the bind variables.
In
the ViewController create a file jspx page, from data control palette
drop customBeanFindAll->Named
Criteria->CustomBeanCriteria->Query as ADF Query Panel with Table.
Run the jspx page and enter values in search form with departmentId as
50 and firstName as "M". Named criteria will filter the query of a data
source and display the result like below.
