• EJB
    April 19, 2012

Display Lookup values from related ejb entities using JOIN FETCH

Guest Author
JOIN FETCH - The purpose of JOIN FETCH is to fetch the related objects from the database in a single query. So in this article, I'm trying to explain how can we use jpql JOIN FETCH and expose those attributes to EJB data control layer.

Take a scenario, where we need to build ADF tree
based on departments,employees and location tables. While displaying
the tree, root node should display departmentName along with city
attribute which is stored in related object Location table.

Model Diagram:


In BC4J, this
scenario can be implemented using Entity Objects facility provided in
View Object layer. These entity objects are used by the view object for
accessing the related objects attributes and will be exposed in data
control layer automatically.

Same behavior can be implemented in EJB using JOIN FETCH. Using
this query improves the efficiency of iteration over the result Departments objects because it eliminates the need for retrieving the associated Location objects separately.So in single query related objects attributes is also fetched from the database.

You can download the sample workspace from here
[Runs with Oracle JDeveloper (11g R2) + HR Schema]

Implementation Steps

Java EE Web Application with entities based on Departments, Employees
and Location table, then create a session bean and data control for the
session bean.

Open the departments entity and alter the named query as below.

@NamedQuery(name = "Departments.findAll",
query = "select o from Departments o join fetch o.locations")

Note:- The query above returns Departments instances and guarantees that the
locations attributes will already be fetched in the returned instances.

we need to expose the location attribute values to data control, create
a Transient variable called "city" and add the below code in department

private String city;

public String getCity() {
return this.locations.getCity();

Note: Can create sample java client to check whether the city
attribute value is coming in departments instance before proceeding to
data control.

In the ViewController create index.jspx page, from data control palette
drop departmentsFindAll->Tree as ADF Tree and in edit tree bindings select the attributes as shown in below image.


Run the index.jspx page. Now notice root node will display departmentName along with city attribute value also.


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.