| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
So far you've worked with a single view object that queries a single USERS table. In practice, many queries you'll need to work with will involve multiple tables that are related by foreign keys. There are two ways you can use view objects to handle this situation, you can either:
Join them in the main query to show additional descriptive information in each row of the main query result
Create separate view objects that query the related information and then link a "source" view object to one or more "target" view objects to form a master/detail hierarchy.
Figure 5-22 illustrates the different "shape" that these two options produce. The join is a single "flattened" result. The master/detail linked queries produce a multilevel result.
To create a read-only view object joining two tables, use the Create View Object wizard. As an example, you'll create a view object named OpenOrPendingServiceRequests that joins the SERVICE_REQUEST and USER tables. For each service request, you'll display the email of the user who created the request.
In step 1 ensure that you've selected Read-only Access, and in step 2 on the SQL Statement page, enter the SQL query statement that joins the desired tables. If you want interactive assistance to build up the right SQL statement, you can click on the Query Builder button.
As shown in Figure 5-23, on the Quick-pick objects page of the query builder dialog, you can see the tables in your schema, including the foreign keys that relate them to other tables. To include columns in the select list of the query, click on them in the Available list and shuttle them to the Selected list. Figure 5-23 shows the result of selecting the SVR_ID, PROBLEM_DESCRIPTION, and ASSIGNED_TO columns from the SERVICE_REQUESTS table, along with the EMAIL column from the USERS table. In the SERVICE_REQUESTS table, beneath the SVR_CREATED_BY_USR_FK foreign key, select the EMAIL column from the USERS table and the query builder automatically determines the required join clause for you.
On the WHERE Clause page of the query builder, shuttle the STATUS column into the WHERE clause box, and complete the job by adding the IN ('Open','Pending') yourself. Click OK in the query builder to create the following query:
Example 5-14 Creating a Query Using SQL Builder
SELECT
SERVICE_REQUESTS.SVR_ID SVR_ID,
SERVICE_REQUESTS.PROBLEM_DESCRIPTION PROBLEM_DESCRIPTION,
SERVICE_REQUESTS.ASSIGNED_TO ASSIGNED_TO,
USERS.EMAIL EMAIL
FROM
SERVICE_REQUESTS INNER JOIN USERS
ON SERVICE_REQUESTS.CREATED_BY = USERS.USER_ID
WHERE
SERVICE_REQUESTS.STATUS IN ('Open', 'Pending')
Notice the EMAIL column in the query. It represents the email of the person who created the service request, but its column name is not as descriptive as it could be. In Section 5.2.3.2, "Working with Queries That Include SQL Expressions", you learned one way to affect the default Java-friendly name of the view object attributes by assigning a column alias. Here you can adopt an alternative technique. You'll use one of the later panels in the Create View Object wizard to rename the view object attribute directly as part of the creation process. Renaming the view object here saves you from having to edit the view object again, when you already know the different attribute names that you'd like to use.
Click Next four times to get to the Attributes Settings page. Select the Email attribute in the Select Attribute dropdown list at the top of the page and change the value in the Name field to CreatedByEmail. Then click Finish to create the OpenOrPendingServiceRequests view object. An OpenOrPendingServiceRequests.xml component definition file is created to save the view object's declarative settings.
To test the new view object, edit the UserService application module and on the Data Model page, add an instance of the OpenOrPendingServiceRequests to the data model. Instead of accepting the default OpenOrPendingServiceRequests1 instance name, change the instance name to AllOpenOrPendingServiceRequests. After doing this, you can launch the Business Components Browser and verify that the join query is working as expected.
When your needs call for showing the user a set of master rows, and for each master row a set of coordinated detail rows, then you can create view links to define how you want the master and detail view objects to relate. Assume you want to link the UserList view object to the OpenOrPendingServiceRequests view object to create a master/detail hierarchy of users and the related set of open or pending service requests that have been assigned to them.
To create the view link, use the Create View Link wizard. The wizard is available from the New Gallery in the Business Tier > ADF Business Components category. In step 1, on the Name page provide a name for the view link and a package where its definition will reside. Given its purpose, a name like RequestsAssignedTo is a fine name, and for simplicity keep it in the same devguide.examples package as the view objects.
In step 2, on the View Objects page, select a "source" attribute to use from the view object that will act as the master. Figure 5-24 shows selecting the UserId attribute from the Users view object in this role. Next, select a corresponding destination attribute from the view object that will act as the detail. Since you want the detail query to show service requests that are assigned to the currently selected user, select the AssignedTo attribute in the OpenOrPendingServiceRequests to play this role. Finally, click Add to add the matching attribute pair to the table of source and destination attribute pairs below. If there were multiple attribute pairs required to define the link between master and detail, you could repeat these steps to add additional source/target attribute pairs. For this example, the one (UserId,AssignedTo) pair is all that's required.
In step 3, on the View Link SQL page, you can preview the view link SQL predicate that will be used at runtime to access the correlated detail rows from the destination view object for the current row in the source view object.
In step 4, on the View Link Properties page you control whether the view link represents a one-way relationship or a bidirectional one. Notice in Figure 5-25 that in the Destination group box for the OpenOrPendingServiceRequests view object, the Generate Accessor In View Object: Users box is checked. In contrast, in the Source group box for the Users view object, the Generate Accessor In View Object: OpenOrPendingServiceRequests box is not checked. By default, a view link is a one-way relationship that allows the current row of the source (master) to access a set of related rows in the destination (detail) view object. These checkbox settings indicate that you'll be able to access a detail collection of rows from the OpenOrPendingServiceRequests for the current row in the Users view object, but not vice versa. For this example, a default one-way view link will be fine, so leave the other checkbox unchecked.
The Accessor Name field in the destination group box indicates the name you can use to programmatically access the related collection of OpenOrPendingServiceRequests rows for the current row in Users. By default the accessor name will be OpenOrPendingServiceRequests, matching the name of the destination view object. To make it more clear that the related collection of service requests is a collection of requests that are assigned to the current user, as you can see in Figure 5-25 you can change the name of the accessor to AssignedRequests.
To create the view link, click Finish.
When you create a view link, JDeveloper creates the XML component definition file that represents its declarative settings and saves it in the directory that corresponds to the name of its package. In Section 5.10.2, the view link was named RequestsAssignedTo in the devguide.examples package, so the XML file created will be ./devguide/examples/RequestsAssignedTo.xml under the project's source path. This XML file contains the declarative information about the source and target attribute pairs you've specified.
In addition to saving the view link component definition itself, JDeveloper also updates the XML definition of the source view object in the view link relationship to add information about the view link accessor you've defined. As a confirmation of this, you can select the Users view object in the Application Navigator and inspect its details in the Structure window. As illustrated in Figure 5-26, you now see the new AssignedRequests accessor in the ViewLink Accessor category.
To work with view links effectively, there are a few more things you may need to know, including: that view link accessor attributes return a RowSet, how to access a detail collection using the view link accessor, and how to enable active master/detail coordination in the date model.
At runtime the getAttribute() method on a Row allows you to access the value of any attribute of a row in the view object's result set by name. The view link accessor behaves like an additional attribute in the current row of the source view object, so you can use the same getAttribute() method to retrieve its value. The only practical difference between a regular view attribute and a view link accessor attribute is its data type. Whereas a regular view attribute typically has a scalar data type with a value like 303 or ahunold, the value of a view link accessor attribute is a row set of zero or more correlated detail rows. Assuming that curUser is a Row from some instance of the Users view object, you can write a line of code to retrieve the detail row set of open or pending assigned requests:
RowSet reqs = (RowSet)curUser.getAttribute("AssignedRequests");
|
Note: If you generate the custom Java class for your view row, the type of the view link accessor will beRowIterator. Since at runtime the return value will always be a RowSet, it is safe to cast the view link attribute value to a RowSet. |
Once you've retrieved the RowSet of detail rows using a view link accessor, you can loop over the rows it contains using the same pattern used the view object's row set of results:step
while (reqs.hasNext()) {
Row curReq = reqs.next();
System.out.println("--> (" + curReq.getAttribute("SvrId") + ") " +
curReq.getAttribute("ProblemDescription"));
}
If you use JDeveloper's Refactor > Duplicate... functionality on the existing TestClient.java class, you can easily "clone" it to create a TestClient2.java class that you'll modify as shown in Example 5-15 to make use of these new techniques. Notice that the lines left in the main() method are setting a dynamic WHERE clause to restrict the UserList view object instance to show only users whose USER_ROLE has the value technician. The second change was enhancing the executeAndShowResults() method to access the view link accessor attribute and print out the request number (SvrId) and ProblemDescription attribute for each one.
Example 5-15 Programmatically Accessing Detail Rows Using the View Link Accessor
package devguide.examples.client;
import oracle.jbo.ApplicationModule;
import oracle.jbo.Row;
import oracle.jbo.RowSet;
import oracle.jbo.ViewObject;
import oracle.jbo.client.Configuration;
public class TestClient2 {
public static void main(String[] args) {
String amDef = "devguide.examples.UserService";
String config = "UserServiceLocal";
ApplicationModule am =
Configuration.createRootApplicationModule(amDef, config);
ViewObject vo = am.findViewObject("UserList");
// Add an extra where clause with a new named bind variable
vo.setWhereClause("user_role = :TheUserRole");
vo.defineNamedWhereClauseParam("TheUserRole", null, null);
vo.setNamedWhereClauseParam("TheUserRole", "technician");
// Show results when :TheUserRole = 'technician'
executeAndShowResults(vo);
Configuration.releaseRootApplicationModule(am, true);
}
private static void executeAndShowResults(ViewObject vo) {
System.out.println("---");
vo.executeQuery();
while (vo.hasNext()) {
Row curUser = vo.next();
// Access the row set of details using the view link accessor attribute
RowSet reqs = (RowSet)curUser.getAttribute("AssignedRequests");
long numReqs = reqs.getEstimatedRowCount();
System.out.println(curUser.getAttribute("UserId") + " " +
curUser.getAttribute("Email")+" ["+
numReqs+" requests]");
while (reqs.hasNext()) {
Row curReq = reqs.next();
System.out.println("--> (" + curReq.getAttribute("SvrId") + ") " +
curReq.getAttribute("ProblemDescription"));
}
}
}
}
Running TestClient2 shows the following results in the Log window. Each technician is listed, and for each technician that has some open or pending service requests, the information about those requests appears beneath their name.
--- 303 ahunold [0 requests] 304 bernst [2 requests] --> (102) Washing Machine does not turn on --> (108) Freezer full of frost 305 daustin [1 requests] --> (104) Spin cycle not draining 307 dlorentz [0 requests] 306 vpatabal [2 requests] --> (107) Fridge is leaking --> (112) My Dryer does not seem to be getting hot
If you run TestClient2 with debug diagnostics enabled, you will see the SQL queries that the view object performed. The view link WHERE clause predicate is used to automatically perform the filtering of the detail service request rows for the current row in the UserList view object.
You've seen that the process of defining a view link introduces a view link attribute in the source view object, which enables programmatic navigation to a row set of correlated details. In this scenario, the view link plays a passive role, simply defining the information necessary to retrieve the coordinated detail row set when your code requests it. The view link accessor attribute is present and programmatically accessible in any result rows from any instance of the view link's source view object. In other words, programmatic access does not require modifying the UserService application module's data model.
However, since master/detail user interfaces are such a frequent occurrence in enterprise applications, the view link can be also used in a more active fashion to avoid having to coordinate master/detail screen programmatically. You opt to have this active master/detail coordination performed by explicitly adding an instance of a view-linked view object to your application module's data model.
To accomplish this, edit the UserService application module and open the Data Model page. As shown in Figure 5-27, you'll see that the Available View Objects list now shows the OpenOrPendingServiceRequests view object twice: once on its own, and once as a detail view object via the RequestsAssignedTo view link.
To add a detail instance of a view object:
In the Data Model list on the right, select the instance of the Users view object in the Data Model list that you want to be the actively-coordinating master
The data model has only one instance of the Users view object named UserList, so select the UserList instance.
In the Available View Objects list, select the OpenOrPendingServiceRequests node that is indented beneath the Users view object.
Enter a name for the detail instance you're about to create in the Name field below the Available View Objects list. As shown in Figure 5-27, call the instance RequestsAssigned.
Click the Add Instance button > to add the detail instance to the currently selected master instance in the data model, with the name you've chosen.
After following these steps, your Data Model list will look like what you see in Figure 5-28.
The easiest way to see the effect of this active master/detail coordination is to launch the Business Components Browser on the UserService by choosing Test from its context menu in the Application Navigator. Figure 5-29 shows the browser window you will see. The data model tree shows the view link instance that is actively coordinating the UserList view object instance with the RequestsAssigned view object instance. It has the default view link instance name of RequestsAssignedTo1. Double-clicking this view link instance node in the tree opens the master/detail panel that you see in Figure 5-29. You'll see that when you use the toolbar buttons to navigate in the master view object — changing the view object's current row as a result — the coordinated set of details is automatically refreshed and the user interface stays in sync.
If you also double-click on the AllOpenOrPendingServiceRequests view object instance that you added earlier, a second tab will open to show its data. Notice that it is another instance of the same devguide.examples.Users view object; however, since it is not being actively coordinated by a view link, its query is not constrained by the current row in the UserList.
So far you've seen a view link that defines a basic master/detail relationship between two view objects. Keep in mind that by creating more view links you can achieve master/detail hierarchies of any complexity, including:
Multilevel master/detail/detail
Master with multiple (peer) details
Detail with multiple masters
The steps to define these more complex hierarchies are the same as the ones covered here, you just need to create it one view link at time.
