| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g Release 3 (10.1.3.0) Part Number B25947-02 |
|
|
View PDF |
| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g Release 3 (10.1.3.0) Part Number B25947-02 |
|
|
View PDF |
This chapter describes how to query from the database using SQL queries encapsulated by ADF view objects.
This chapter includes the following sections:
Section 5.3, "Using a View Object in an Application Module's Data Model"
Section 5.5, "Testing View Objects Using the Business Components Browser"
Section 5.6, "Working Programmatically with View Object Query Results"
Section 5.7, "How to Create a Command-Line Java Test Client"
Section 5.8, "Filtering Results Using Query-By-Example View Criteria"
Section 5.11, "Generating Custom Java Classes for a View Object"
A view object is Oracle ADF component that encapsulates a SQL query and simplifies working with its results. By the end of this chapter, you'll understand all the concepts illustrated in Figure 5-1:
You define a view object by providing a SQL query
You use view object instances in the context of an application module that provides the database transaction for their queries
You can link a view object to one or more others to create master/detail hierarchies
At runtime, the view object executes your query and produces a row set of rows
Each row is identified by a corresponding row key
You iterate through the rows in a row set using a row set iterator
You can filter the row set a view object produces by applying a set of query-by-example criteria rows
Note:
To experiment with a working version of the examples in this chapter, download the
DevGuideExamples workspace from the Example Downloads page at http://otn.oracle.com/documentation/jdev/b25947_01/ and see the QueryingDataWithViewObjects project.View objects can be used for reading data as well as updating data. This chapter focuses on working with read-only data using view objects. In Chapter 7, "Building an Updatable Data Model With Entity-Based View Objects", you'll learn how to create view objects that can handle updating data.
To create a view object, use the Create View Object wizard. The wizard is available from the New Gallery in the Business Tier > ADF Business Components category. If it's the first component you're creating in the project, the Initialize Business Components Project dialog appears to allow you to select a database connection. These examples assume that you are working with a connection named SRDemo for the SRDEMO schema.
As shown in Figure 5-2, provide a package name, a view object name, and indicate that you want this view object to manage data with read-only access. The figure illustrates creating a view object named Users in the devguide.examples package.
In step 2 of the wizard (the SQL Statement page), paste in any valid SQL statement into the Query Statement box or click Query Builder to use the interactive query builder. Figure 5-3 shows a query to retrieve a few columns of user information from the USERS table ordered by EMAIL.
Note:
If you see an Entity Objects page instead of the SQL Statement page shown here, go back to step 1 and ensure that you've selected Read-only Access.Since the query does not reference any bind variables, you can skip step 3 Bind Variables page for now. In Section 5.9, "Using Named Bind Variables", you'll add a bind variable and see how to work with it in the query.
In addition to the SQL query information, a view object captures information about the names and datatypes of each expression in its query's SELECT list. As you'll see in Section 5.6, "Working Programmatically with View Object Query Results", you can use these view object attribute names to access the data from any row in the view object's result set by name. You could directly use the SQL column names as attribute names in Java, but typically the SQL names are all uppercase and often comprised of multiple, underscore-separated words. The view object wizard converts these SQL-friendly names to Java-friendly ones.
In step 4 on the Attribute Mappings page, as shown in Figure 5-4 you can see how the SELECT list column names correspond to the more Java-friendly view object attribute names that the wizard has created by default. Each part of an underscore-separated column name like SOME_COLUMN_NAME is turned into a capitalized word in the attribute name like SomeColumnName. While the view object attribute names correspond to the underlying query columns in the SELECT list, the attribute names at the view object level need not match necessarily. You can later rename the view object attributes to any names that might be more appropriate without changing the underlying query.
Note:
You'll see throughout the ADF Business Components wizards and editors, that the default convention is to use "CamelCapped" attribute names, beginning with a capital letter and using upper-case letters in the middle of the name to improve readability when the name comprises multiple words.Click Finish at this point to create the view object.
When you create a view object, JDeveloper first describes the query to infer the following from the columns in the SELECT list:
The Java-friendly view attribute names (e.g. USER_ID -> UserId)
The SQL and Java data types of each attribute
JDeveloper then creates the XML component definition file that represents the view objects's declarative settings and saves it in the directory that corresponds to the name of its package. In the example above, the view object was named Users in the devguide.examples package, so that the XML file created will be ./devguide/examples/Users.xml under the project's source path. This XML file contains the SQL query you entered, along with the names, datatypes, and other properties of each attribute. If you're curious to see its contents, you can see the XML file for the view object by selecting the view object in the Application Navigator and looking in the corresponding Sources folder in the Structure window. Double-clicking the Users.xml node will open the XML in an editor so that you can inspect it.
Note:
If your IDE-level Business Components Java generation preferences so indicate, the wizard may also create an optional custom view object class
UsersImpl.java and/or a custom view row class UsersRowImpl.java class.Typically you create one view object for each SQL query your application will perform.
After you've created a view object, you can edit any of its settings by using the View Object Editor. Choose the Edit menu option on the context menu in the Application Navigator, or double-click the view object, to launch the dialog. By opening the different panels of the editor, you can adjust the SQL query, change the attribute names, add named bind variables, add UI controls hints, control Java generation options, and other settings that are described in later chapters.
If your SQL query includes a calculated expression like this:
select USER_ID, EMAIL,
SUBSTR(FIRST_NAME,1,1)||'. '||LAST_NAME
from USERS
order by EMAIL
use a SQL alias to assist the Create View Object wizard in naming the column with a Java-friendly name:
select USER_ID, EMAIL,
SUBSTR(FIRST_NAME,1,1)||'. '||LAST_NAME AS USER_SHORT_NAME
from USERS
order by EMAIL
As shown in Figure 5-5, by selecting a particular attribute name in the View Object Editor, you can see and change the values of the declarative settings that control its runtime behavior. One important property is the Type in the Query Column section. This property records the SQL type of the column, including the length information for VARCHAR2 columns and the precision/scale information for NUMBER columns. The JDeveloper editors try to infer the type of the column automatically, but for some SQL expressions the inferred value might be VARCHAR2(255). You can update the Type value for such attributes to reflect the correct length if you know it. For example, VARCHAR2(30) which shows as the Type for the FirstName attribute in Figure 5-5 means that it has a maximum length of 30 characters. For a NUMBER column, you would indicate a Type of NUMBER(7,2) for an attribute that you want to have a precision of 7 digits and a scale of 2 digits after the decimal.
Any view object you create is a reusable component that can be used in the context of one or more application modules to perform the query it encapsulates in the context of that application module's transaction. The set of view objects used by an application module defines its data model, in other words, the set of data that a client can display and manipulate through a user interface. To start simple, create an application module and use the single view object you created above in the application module's data model.
To create an application module, use the Create Application Module wizard. The wizard is available from the New Gallery in the Business Tier > ADF Business Components category. As shown in Figure 5-6, provide a package name, and an application module name. The figure shows creating an application module UserService in the devguide.examples package.
In step 2 on the Data Model page, Figure 5-7 illustrates that initially the data model is empty. That is, it contains no view object instances yet. The Available View Objects list shows all available view objects in your project, organized by package.
To add an instance of a view object to the data model, first select it in the Available list. The Name field below the list shows the name that will be used to identify the next instance of that view object that you add to the data model. By typing in a different name before pressing the add instance button >, you can change the name to be anything you like. Finally, to add an instance of the selected view object to the data model, identified by the instance name shown below, click the add instance button (>).
Assuming you decide on the instance name of UserList, Figure 5-8 shows what the Data Model page would look like after adding the instance. The Instance Name field below the selected view object instance in the Data Model list allows you to see and change the instance name if necessary. The Definition field displays the fully-qualified name of the view object component that will be used to create this view object instance at runtime. You see as expected that the definition that will be used is devguide.examples.Users view object.
It is important to understand the distinction between a view object component and a view object instance. The easiest way to understand the distinction is to first consider a visual example. Imagine that you need to build a Java user interface containing two buttons. Using JDeveloper's visual editor, you might create the page shown in Figure 5-9 by using the Component Palette to select the JButton component and click to add a JButton component to your panel. Repeating that same step a second time, you can drop another button onto the panel. You are designing a custom JPanel component that uses two instances of the JButton component. The panel does not own the JButton class, it's just using two instances of it.
If you were to peek into the Java code of this new panel you're designing, you'd notice that there are two member fields of type JButton to hold a reference to the two instances of the button the panel is using. To distinguish the two instances in the code, one member field is named myButton, and the other member field is named anotherButton:
Example 5-1 Two Instances of a JButton Component
public class Panel1 extends JPanel implements JUPanel {
private JButton myButton = new JButton(); // First instance
private JButton anotherButton = new JButton(); // Second instance
// etc.
}
Even though the application module is a nonvisual component, you can still apply this same intuition about components, instances, and distinct member names to help understand the concept better. While designing an application module, you use instances of a view object component to define its data model. Figure 5-10 shows a JDeveloper business components diagram of a UserService application module. Just as the panel in Example 5-1 contained two instances of the JButton component with member names of myButton and anotherButton to distinguish them, your application module contains two instances of the Users view object component, with member names of UserList and AnotherUserList to distinguish them. At runtime, the two JButton instances are both based on the same definition — which explains why they both have the same set of properties and both exhibit JButton-like behavior. However the values of their properties like Position and Text are different. So too for the different instances of the Users view object in your UserService application module. At runtime, both instances share the same Users view object component definition — ensuring they have the same attribute structure and Users view object behavior — however, each might be used independently to retrieve data about different users. For example, some of the runtime properties like an additional filtering WHERE clause or the value of a bind variable might be different on the two distinct instances.
Figure 5-10 Designing an Application Module That Contains Two Instances of the Users View Object Component
Besides the obvious fact that one example is a visual panel while the other is a nonvisual data model component, the only logical difference is how the instances and member names are defined. In the visual panel in Example 5-1, you saw that the two member fields holding the distinct JButton instances were declared in code. In contrast, the UserService application module defines its member view object instances in its XML component definition file:
When you create an application module, 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 the example in Figure 5-6, the application module was named UserService in the devguide.examples package, so the XML file created will be ./devguide/examples/UserService.xml under the project's source path. This XML file contains the information needed at runtime to recreate the view object instances in the application module's data model. If you're curious to see its contents, you can see the XML file for the application module by selecting the view object in the Application Navigator and looking in the corresponding Sources folder in the Structure window. Double-clicking the UsersService.xml node will open the XML in an editor so that you can inspect it.
Note:
If your IDE-level Business Components Java generation preferences so indicate, the wizard may also create an optional custom application module classUsersServiceImpl.java.After you've created an application module, you can edit any of its settings by using the Application Module Editor. Select the Edit menu option on the context menu in the Application Navigator to launch the application module. By opening the different panels of the editor, you can adjust the view object instances in the data model, control Java generation options, and other settings you'll learn about in later chapters.
Since it can be convenient to define and use multiple sets of runtime configuration properties each application module supports multiple, named runtime configurations. When you create an application module, JDeveloper creates a default set of runtime configuration properties for the application module. For an application module named YourService, its default set of configuration properties will be named YourServiceLocal. These settings are stored in an XML file named bc4j.xcfg in a subdirectory named common, relative to where the application module's XML component definition resides. For example, when you created the UserService application module above in the devguide.examples package, JDeveloper creates the file bc4j.xcfg in the ./devguide/examples/common directory under the project's source path.
You can use the application module configuration manager to edit existing configurations or create new ones. To access the configuration manager, select the desired application module in the Application Navigator and choose Configurations...from the context menu. The Configuration Manager dialog appears, as shown in Figure 5-11. You can see the default UserServiceLocal configuration for the UserService application module. Any additional configurations you create, or any configuration properties you edit, are saved in the same bc4j.xcfg.
Click the Edit button in the Configuration Manager to edit a specific configuration. As shown in Figure 5-12, this editor allows you to configure the database connection information, a number of pooling and scalability settings, and a final tab of remaining properties that aren't covered by the first two tabs. All of the runtime properties and their meanings are covered in the JDeveloper online help.
One of the many powerful, built-in features of the ADF Business Components is the ability to define control hints on attributes. Control hints are additional attribute settings that the view layer can use to automatically display the queried information to the user in a consistent, locale-sensitive way. JDeveloper manages storing the hints in a way that is easy to localize for multi-lingual applications.
To add attribute control hints for the attributes of the UserList view object, open the View Object Editor and expand the Attributes node in the left-side tree to reveal the list of the view object's attributes. As shown in Figure 5-13, by selecting a particular attribute name like UserId and selecting the Control Hints tab, you can enter a value for its Label Text hint like "Id". You can also set the Format Type to Number, and enter a Format mask of 00000. You could select the other attributes in turn to define Label Text hints like "Email Address", "Given Name", and "Surname" for the Email, FirstName, and LastName attributes respectively.
Note:
Java defines a standard set of format masks for numbers and dates that are different from those used by the Oracle database's SQL and PL/SQL languages. For reference, see the Javadoc for the
java.text.DecimalFormat and java.text.SimpleDateFormat classes.When you define attribute control hints for a view object, JDeveloper creates a standard Java message bundle file in which to store them. The file is specific to the view object component to which it's related, and it is named accordingly. For the UserList view object in the devguide.examples package, the message bundle file created will be named UserListRowImplMsgBundle.java and it will be created in the devguide.examples.common subpackage. By selecting the UserList component in the Application Navigator, you'll see that this new file gets added to the Sources folder in the Structure window that shows the group of implementation files for each business component. Example 5-3 shows how the control hint information appears in the message bundle file. The first entry in each String array is a message key; the second entry is the locale-specific String value corresponding to that key.
Example 5-3 View Object Component Message Bundle Class Stores Locale-Sensitive Control Hints
package devguide.examples.common;
import oracle.jbo.common.JboResourceBundle;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// ---------------------------------------------------------------------
public class UsersRowImplMsgBundle extends JboResourceBundle {
static final Object[][] sMessageStrings =
{
{ "UserId_LABEL", "Id" },
{ "UserId_FMT_FORMATTER", "oracle.jbo.format.DefaultNumberFormatter" },
{ "UserId_FMT_FORMAT", "00000" },
{ "Email_LABEL", "Email Address" },
{ "FirstName_LABEL", "Given Name" },
{ "LastName_LABEL", "Surname" }
};
Internationalizing the model layer of an application built using ADF Business Components entails producing translated versions of each component message bundle file. For example, the Italian version of the UsersRowImplMsgBundle message bundle would be a class named UsersRowImplMsgBundle_it, and a more specific Swiss Italian version would have the name UsersRowImplMsgBundle_it_ch. These classes typically extend the base message bundle class, and contain entries for the message keys that need to be localized, together with their localized translation. For example, assuming you didn't want to translate the number format mask for the Italian locale, the Italian version of the UserList view object message bundle would look like what you see in Example 5-4. Notice the overridden getContents() method. It returns an array of messages with the more specific translated strings merged together with those that are not overridden from the superclass bundle. At runtime, the appropriate message bundles are used automatically, based on the current user's locale settings.
Example 5-4 Localized View Object Component Message Bundle for Italian
package devguide.examples.common;
import oracle.jbo.common.JboResourceBundle;
public class UsersRowImplMsgBundle_it extends UsersRowImplMsgBundle {
static final Object[][] sMessageStrings =
{
{ "UserId_LABEL", "Codice Utente" },
{ "Email_LABEL", "Indirizzo Email" },
{ "FirstName_LABEL", "Nome" },
{ "LastName_LABEL", "Cognome" }
};
// merge this message bundles messages with those in superclass bundle
public Object[][] getContents() {
return super.getMergedArray(sMessageStrings, super.getContents());
}
}
JDeveloper includes an interactive application module testing tool that enables you to to test all aspects of its data model without having to use your application user interface or write a test client program. It can often be the quickest way of exercising the data functionality of your business service during development.
To test an application module, select it in the Application Navigator and choose Test from the context menu. The Business Component Browser Connect dialog appears as shown in Figure 5-14. In the upper right corner of the dialog, the Configuration Name list allows you to choose any of your application module's configurations for the current run of the tester tool. Click Connect to start the application module using the selected configuration.
When you launch the Business Components Browser, JDeveloper starts the tester tool in a separate process and the Business Components Browser appears. The tree at the left of the dialog displays all of the view object instances in your application module's data model. Figure 5-15 has only one instance called UserList. Double-clicking the UserList view object instance in the tree executes the view object — if it has not been executed so far in the testing session — and displays a panel to inspect the query results as shown in Figure 5-15. Additional context menu items on the view object node allow you to re-execute the query if needed, to remove the tester panel, and to perform other tasks.
You can see that the fields in the test panel for the view object are disabled for the UserList view object instance, because the view object is read only. In Section 7.5, "Testing Entity-Based View Objects Interactively", you'll see that the tester tool becomes even more valuable by allowing you to experiment with inserting, updating, and deleting rows in view objects, too. But even for a read-only view object, the tool affords some useful features. Firstly, you can validate that the UI hints and format masks are defined correctly. The attributes display with their defined Label Text control hints as the prompt, and the UserId field is displayed as 00303 due to the 00000 format mask defined in Section 5.4.1, "How to Add Attribute Control Hints". Secondly, you can scroll through the data using the toolbar buttons.
Thirdly, you can enter query-by-example criteria to find a particular row whose data you want to inspect. By clicking the Specify View Criteria button in the toolbar, the View Criteria dialog displays as shown in Figure 5-16. You can enter a query criteria like "H%" in the LastName attribute and click Find to narrow the search to only those users with a last name that begins with the letter H (Hunold, Himuro, Hartstein, and Higgins).
When using the Business Components Browser you can customize configuration options for the current run. You can also enable ADF Business Component debug diagnostics to output messages to the console. Both of these features can help you test various portions of your application or find problems.
As described in Figure 5-14, on the Connect dialog of the Business Component Browser you can select a predefined configuration to run the tool using that named set of runtime configuration properties. The Connect dialog also features a Properties tab that allows you to see the selected configurations settings and to override any of the configuration's settings for the current run of the browser. For example, you could test the Italian language translations of the UI control hints for a single Business Components Browser run by opening the Properties tab and setting the following two properties:
jbo.default.country = IT
jbo.default.language = it
If you wanted to make the changes permanent, you could use the Configuration Manager to copy the current UserServiceLocal configuration and create a new UserServiceLocalItalian which had these two additional properties set. This way, anytime you wanted to test in Italian you could simply choose to use the UserServiceLocalItalian configuration instead of the default UserServiceLocal one.
When launching the Business Components Browser, if your project's current run configuration is set to include the Java System parameter jbo.debugoutput=console, you can enable ADF Business Components debug diagnostics with messages directed to the console. These will display in the JDeveloper Log window.
Note:
Despite the similar name, the JDeveloper project's run configurations are different from the ADF application module's configurations. The former are part of the project properties, the latter are defined along with your application module component in its
bc4j.xcfg file and edited using the configuration editor.To set the system property described above, open the Run/Debug page in the Project Properties dialog for your model project. Click Edit to edit the chosen run configuration, and add the string:
-Djbo.debugoutput=console
to the Java Options field in the panel. The next time you run the Business Component Browser and double-click on the UserList view object, you'll see detailed diagnostic output in the console.
Example 5-5 Diagnostic Output of Business Component Browser
: [234] Created root application module: 'devguide.examples.UserService' [235] Stringmanager using default locale: 'en_US' [236] Locale is: 'en_US' [237] ApplicationPoolImpl.resourceStateChanged wasn't release related. [238] Oracle SQLBuilder: Registered driver: oracle.jdbc.driver.OracleDriver [239] Creating a new pool resource [240] Trying connection/2: url='jdbc:oracle:thin:@localhost:1521:XE' ... [241] Successfully logged in [242] JDBCDriverVersion: 10.1.0.5.0 [243] DatabaseProductName: Oracle [244] DatabaseProductVersion: Oracle Database 10g Release 10.2.0.1.0 [245] Column count: 4 [246] ViewObject: UserList Created new QUERY statement [247] UserList>#q computed SQLStmtBufLen: 110, actual=70, storing=100 [248] select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS order by EMAIL :
Using the diagnostics, you can see everything the framework components are doing for your application.
Other legal values for this property are silent (the default, if not specified) and file. If you choose the file option, diagnostics are written to the system temp directory. One best practice is to create multiple JDeveloper run configurations, one with the ADF Business Components debug diagnostics set on, and another without it, so you can easily flip between seeing and not seeing debug diagnostics by choosing the appropriate project run configuration.
Now that you have a working application module containing an instance named UserList, you can build a simple test client program to illustrate the basics of working programmatically with the data in the UserList view object instance.
The ViewObject interface in the oracle.jbo package provides the methods to make quick work of any data-retrieval task. Some of these methods used in the example include:
executeQuery(), to execute the view object's query and populate its row set of results
setWhereClause(), to add a dynamic predicate at runtime to narrow a search
setNamedWhereClauseParam(), to set the value of a named bind variable
hasNext(), to test whether the row set iterator has reached the last row of results
next(), to advance the row set iterator to the next row in the row set
getEstimatedRowCount(), to count the number of rows a view object's query would return
Chapter 27, "Advanced View Object Techniques" presents situations when you might want a single view object to produce multiple distinct row sets of results; however, most of the time you'll work only with a single row set of results at a time for a view object. That same later chapter, also describes scenarios when you might want to create multiple distinct row set iterators for a row set, however again most of the time you'll need only a single iterator. To simplify this overwhelmingly common use case, as shown in Figure 5-17, the view object contains a default RowSet, which, in turn, contains a default RowSetIterator. As you'll see in the examples below, the default RowSetIterator allows you to call all of the methods above directly on the ViewObject component itself, knowing that they will apply automatically to its default row set.
Note:
Throughout this guide, whenever you encounter the phrase "working with the rows in a view object," what this means more precisely is working with the rows in the view object's default row set. Similarly, when you read "iterate over the rows in a view object," what this means more precisely is that you'll use the default row set iterator of the view object's default row set to loop over its rows.With the concepts in place, you can create a test client program to put them into practice.
The getEstimatedRowCount() method is used on a RowSet to determine how many rows it contains:
long numReqs = reqs.getEstimatedRowCount();
The implementation of the getEstimatedRowCount() initially issues a SELECT COUNT(*) query to calculate the number of rows that the query will return. The query is formulated by "wrapping" your view object's entire query in a statement like:
SELECT COUNT(*) FROM ( ... your view object's SQL query here ... )
This approach allows you to access the count of rows for a view object without necessarily retrieving all the rows themselves which is an important optimization for working with queries that return a large number of rows, or proactively testing how many rows a query would return before proceeding to work with the results of the query.
Once the estimated row count is calculated, subsequent calls to the method do not re-execute the COUNT(*) query. The value is cached until the next time the view object's query is executed, since the fresh query result set returned from the database could potentially contain more, fewer, or different rows compared with the last time the query was run. The estimated row count is automatically adjusted to account for pending changes in the current transaction, adding the number of relevant new rows and subtracting the number of removed rows from the count returned.
To create a test client program, create a new Java class using the Create Java Class wizard. This is available in the New Gallery under the General category. Enter a class name like TestClient, a package name like devguide.examples.client, and ensure the Extends field says java.lang.Object. In the Optional Attributes, deselect the Generate Default Constructor and select the Generate Main Method checkbox. Then click OK to create the TestClient.java file. The file opens in the source editor to show you the skeleton code:
Example 5-6 Skeleton Code for TestClient.java
package devguide.examples.client;
public class TestClient {
public static void main(String[] args) {
}
}
Place the cursor on a blank line inside the body of the main() method and use the bc4jclient code template to create the few lines of necessary code. To use this predefined code template, type the characters bc4jclient followed by a [Ctrl]+[Enter] to expands the code template so that the class now should look like this:
Example 5-7 Expanded Skeleton Code for TestClient.java
package devguide.examples.client;
import oracle.jbo.client.Configuration;
import oracle.jbo.*;
import oracle.jbo.domain.Number;
import oracle.jbo.domain.*;
public class TestClient {
public static void main(String[] args) {
String amDef = "test.TestModule";
String config = "TestModuleLocal";
ApplicationModule am =
Configuration.createRootApplicationModule(amDef,config);
ViewObject vo = am.findViewObject("TestView");
// Work with your appmodule and view object here
Configuration.releaseRootApplicationModule(am,true);
}
}
Adjust the values of the amDef andconfig variables to reflect the names of the application module definition and the configuration that you want to use, respectively. For the Example 5-7, you would change these two lines to read:
String amDef = "devguide.examples.UserService"; String config = "UserServiceLocal";
Finally, change view object instance name in the call to findViewObject() to be the one you want to work with. Specify the name exactly as it appears in the Data Model tree on the Data Model panel of the Application Module editor. Here, the view object instance is named UserList, so you need to change the line to:
ViewObject vo = am.findViewObject("UserList");
At this point, you have a working skeleton test client for the UserService application module whose source code looks like what you see in Example 5-8.
Note:
Section 8.5, "Working Programmatically with an Application Module's Client Interface" expands this test client sample code to illustrate calling custom application module service methods, too.Example 5-8 Working Skeleton Code for an Application Module Test Client Program
package devguide.examples.client;
import oracle.jbo.client.Configuration;
import oracle.jbo.*;
import oracle.jbo.domain.Number;
import oracle.jbo.domain.*;
public class TestClient {
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");
// Work with your appmodule and view object here
Configuration.releaseRootApplicationModule(am,true);
}
}
To execute the view object's query, display the number of rows it will return, and loop over the result to fetch the data and print it out to the console, replace // Work with your appmodule and view object here , with the code in Example 5-9
Example 5-9 Looping Over a View Object and Printing the Results to the Console
System.out.println("Query will return "+
vo.getEstimatedRowCount()+" rows...");
vo.executeQuery();
while (vo.hasNext()) {
Row curUser = vo.next();
System.out.println(vo.getCurrentRowIndex()+". "+
curUser.getAttribute("UserId")+" "+
curUser.getAttribute("Email"));
}
The first line calls getEstimatedRowCount() to show how many rows the query will retrieve. The next line calls the executeQuery() method to execute the view object's query. This produces a row set of zero or more rows that you can loop over using a while statement that iterates until the view object's hasNext() method returns false. Inside the loop, the code puts the current Row in a variable named curUser, then invokes the getAttribute() method twice on that current Row object to get the value of the UserId and Email attributes to print them to the console.
When you run the TestClient class by choosing Run from the context menu of the source editor, you'll see the results of the test in the log window. Notice that the getCurrentRowIndex() used in Example 5-10 shows that the row index in a row set is a zero-based count of the rows:
Example 5-10 Log Output from Running a Test Client
Query will return 27 rows... 0. 303 ahunold 1. 315 akhoo : 25. 306 vpatabal 26. 326 wgietz
The call to createRootApplicationModule() on the Configuration object returns an instance of the UserService application module to work with. As you might have noticed in the debug diagnostic output, the ADF Business Components runtime classes load XML component definitions as necessary to instantiate the application module and the instance of the view object component that you've defined in its data model at design time. The findViewObject() method on the application module finds a view object instance by name from the application module's data model. After the loop described in Example 5-9, the call to releaseRootApplicationModule() on the Configuration object signals that you're done using the application module and allows the framework to clean up resources, like the database connection that was used by the application module.
The createRootApplicationModule() and releaseRootApplicationModule() methods are very useful for command-line access to application module components, however you won't typically ever need to write these two lines of code in the context of an ADF-based web or Swing application. The ADF Model data binding layer cooperates automatically with the ADF Business Components layer to acquire and release application module components for you in those scenarios.
When you need to filter the query results that a view object produces based on search criteria provided at runtime by the end user, you can apply a ViewCriteria to the view object. The view criteria is a row set of one or more view criteria rows, whose attributes mirror those in the view object. The key difference between a view row of query results and a view criteria row is that the data type of each attribute in the view criteria row is String to allow query-by-example operators to be entered like "> 304", for example.
To use a view criteria, follow the steps illustrated in the TestClientViewCriteria class in Example 5-11 to call:
createViewCriteria() on the view object, to be filtered to create an empty view criteria row set
createViewCriteriaRow() on the view criteria, to create one or more empty view criteria rows
setAttribute() as appropriate on the view criteria rows, to set attribute values to filter on
add() on the view criteria, to add the view criteria rows to the view criteria row set
applyViewCriteria(), to apply the view criteria to the view object
executeQuery() on the view criteria, to execute the query with the applied filter criteria
The last step to execute the query is important since a newly applied view criteria is only applied to the view object's SQL query at its next execution.
Example 5-11 Creating and Applying a View Criteria
package devguide.examples.client;
import oracle.jbo.ApplicationModule;
import oracle.jbo.Row;
import oracle.jbo.ViewCriteria;
import oracle.jbo.ViewCriteriaRow;
import oracle.jbo.ViewObject;
import oracle.jbo.client.Configuration;
public class TestClientViewCriteria {
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");
// 1. Create a view criteria rowset for this view object
ViewCriteria vc = vo.createViewCriteria();
// 2. Use the view criteria to create one or more view criteria rows
ViewCriteriaRow vcr1 = vc.createViewCriteriaRow();
ViewCriteriaRow vcr2 = vc.createViewCriteriaRow();
// 3. Set attribute values to filter on in appropriate view criteria rows
vcr1.setAttribute("UserId","> 304");
vcr1.setAttribute("Email","d%");
vcr1.setAttribute("UserRole","technician");
vcr2.setAttribute("UserId","IN (324,326)");
vcr2.setAttribute("LastName","Baer");
// 4. Add the view criteria rows to the view critera rowset
vc.add(vcr1);
vc.add(vcr2);
// 5. Apply the view criteria to the view object
vo.applyViewCriteria(vc);
// 6. Execute the query
vo.executeQuery();
while (vo.hasNext()) {
Row curUser = vo.next();
System.out.println(curUser.getAttribute("UserId") + " " +
curUser.getAttribute("Email"));
}
Configuration.releaseRootApplicationModule(am, true);
}
}
Running the TestClientViewCriteria example in Example 5-11 produces the output:
305 daustin 307 dlorentz 324 hbaer
When you apply a view criteria containing one or more view criteria rows to a view object, the next time it is executed it augments its SQL query with an additional WHERE clause predicate corresponding the query-by-example criteria that you've populated in the view criteria rows. As shown in Figure 5-18, when you apply a view criteria containing multiple view criteria rows, the view object augments its design time WHERE clause by adding an additional runtime WHERE clause based on the non-null example criteria attributes in each view criteria row.
There are several things you may need to know about query-by-example criteria, including how to test view criteria in the Business Components Browser, altering compound search conditions using multiple view criteria rows, searching for a row whose attribute value is NULL, searching case insensitively, clearing view criteria in effect, and how applying view criteria causes a query to be re-parsed.
In Section 5.6.1, "Common Methods for Working with the View Object's Default RowSet", you saw that the setWhereClause() method allows you to add a dynamic WHERE clause to a view object. As you'll see in later examples in this chapter, when you use setWhereClause() you pass a string that contains literal database column names like this:
vo.setWhereClause("LAST_NAME LIKE UPPER(:NameToFind)");
In contrast, when you use the view criteria mechanism, you saw in Example 5-11 above that you reference the view object attribute name instead like this:
criteriaRow.setAttribute("LastName","B%");
As explained above, the view criteria rows are then translated by the view object into corresponding WHERE clause predicates that reference the corresponding column names.
As shown in Figure 5-19, for any view object instance that you browse, clicking the Specify View Criteria toolbar icon brings up the Business Component View Criteria dialog. The dialog allows you to create a view criteria comprising one or more view criteria rows. To apply criteria attributes from a single view criteria row, enter query-by-example criteria in the desired fields and click Find. To add additional view criteria rows, click OR and use the additional tabs that appear to switch between pages, each representing a distinct view criteria row. When you click Find the Business Components Browser uses the same APIs described above to create and apply the view criteria to filter the result.
When you add multiple view criteria rows, you can call the setConjunction() method on a view criteria row to alter the conjunction used between the predicate corresponding to that row and the one for the previous view criteria row. The legal constants to pass as an argument are:
ViewCriteriaRow.VCROW_CONJ_AND
ViewCriteriaRow.VCROW_CONJ_NOT
ViewCriteriaRow.VCROW_CONJ_OR (default)
The NOT value can be combined with AND or OR to create filter criteria like:
( PredicateForViewCriteriaRow1) AND ( NOT( PredicateForViewCriteriaRow2 ) )
or
( PredicateForViewCriteriaRow1) OR (NOT( PredicateForViewCriteriaRow2 ) )
The syntax to achieve these requires using Java's bitwise OR operator like this:
vcr2.setConjunction(ViewCriteriaRow.VCROW_CONJ_AND | ViewCriteriaRow.VCROW_CONJ_NOT);
To search for a row containing a NULL value in a column, populate a corresponding view criteria row attribute with the value "IS NULL".
To search case-insensitively, call setUpperColumns(true)on the view criteria row to which you want the case-insensitivity to apply. This affects the WHERE clause predicate generated for String-valued attributes in the view object to use UPPER(COLUMN_NAME) instead of COLUMN_NAME in the predicate. Note that the value of the supplied view criteria row attributes for these String-valued attributes must be uppercase or the predicate won't match.
To clear any view criteria in effect, you can call getViewCriteria() on a view object and then delete all the view criteria rows from it using the remove() method, passing the zero-based index of the criteria row you want to remove. If you don't plan to add back other view criteria rows, you can also clear all the view criteria in effect by simply calling applyViewCriteria(null) on the view object.
A corollary of the view criteria feature described above is that each time you apply a new view criteria (or remove an existing one), the text of the view object's SQL query is effectively changed. Changing the SQL query causes the database to re-parse the statement again the next time it is executed. If you plan to use the view criteria filtering feature to apply different criteria values for fundamentally the same criteria attributes each time, you will get better performance by using a view object whose WHERE clause contains named bind variables as described in Section 5.9, "Using Named Bind Variables". In contrast to the view criteria filtering feature, using named bind variables you can change the values of the search criteria without changing the text of the view object's SQL statement each time those values change.
Whenever the WHERE clause of your query includes values that might change from execution to execution, you can use named bind variables. These are place holders in the SQL string whose value you can easily change at runtime without altering the text of the SQL string itself. Since the query doesn't change, the database can efficiently reuse the same parsed representation of the query across multiple executions which leads to higher runtime performance of your application.
To add a named bind variable to a view object, use the Bind Variables tab of the Create View Object wizard or the View Object Editor. You can add as many named bind variables as you need. As shown in Figure 5-20, for each bind variable you specify its name, data type, and default value. You can name the variables as you like, but since they share the same namespace as view object attributes you need to choose names that don't conflict with existing view object attribute names. As with view objects attributes, by convention bind variable names are created with an initial capital letter.
On the Control Hints tab, you can also specify UI hints like Label Text, Format Type, Format mask and others, just as you did above with the view object attributes. These bind variable control hints are used automatically by the view layer when you build user interfaces like search pages that allow the user to enter values for the named bind variables. The Updatable checkbox controls whether the end user will be allowed to change the bind variable value through the user interface. If a bind variable is not updatable, then its value can only be changed programmatically by the developer.
After defining the bind variables, the next step is to reference them in the SQL statement. While SQL syntax allows bind variables to appear both in the SELECT list and in the WHERE clause, you'll typically use them in the latter context, as part of your WHERE clause. You could edit the UserList view object created above, and open the SQL Statement page to introduce your named bind variables like this:
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL
Notice that you reference the bind variables in the SQL statement by prefixing their name with a colon like :TheName or :LowUserId. You can reference the bind variables in any order and repeat them as many times as needed within the SQL statement.
Once you've added one or more named bind variables to a view object, you gain the ability to easily see and set the values of these variables at runtime. Information about the name, type, and default value of each bind variable is saved in the view object's XML component definition file. If you have defined UI control hints for the bind variables, this information is saved in the view object's component message bundle file along with other control hints for the view object.
The Business Components Browser allows you to interactively inspect and change the values of the named bind variables for any view object, which can really simplify experimenting with your application module's data model when named bind parameters are involved. The first time you execute a view object in the tester, a Bind Variables dialog will appear, as shown in Figure 5-21. By selecting a particular bind variable in the list, you can see its name as well as both the default and current values. To change the value of any bind variable, just update its corresponding Value field before clicking OK to set the bind variable values and execute the query. Using the Edit Bind Parameters button in the toolbar — whose icon looks like ":id" — you can inspect and set the bind variables for the view object in the current panel.
If you've defined the Label Text, Format Type, or Format control hints, the Bind Variables dialog helps you verify they are correctly setup by showing the label text hint in the Bind Variables list and formatting the Value attribute using the respective format mask. You can see in Figure 5-21 that the label text hints are showing for the three bind variables in the list.
There are several things you may need to know about named bind variables, including the runtime errors that are displayed when bind variables have mismatched names, the default value for bind variables, how to set existing bind variable values at runtime, and how to add a new named bind variable at runtime.
You need to ensure that the list of named bind variables that you reference in your SQL statement matches the list of named bind variables that you've defined on the Bind Variables page of the View Object Editor. Failure to have these two agree correctly can result in one of the following two errors at runtime.
If you use a named bind variable in your SQL statement but have not defined it, you'll receive an error like this:
(oracle.jbo.SQLStmtException) JBO-27122: SQL error during statement preparation. ## Detail 0 ## (java.sql.SQLException) Missing IN or OUT parameter at index:: 1
On the other hand, if you have defined a named bind variable, but then forgotten to reference it or mistyped its name in the SQL, then you will see an error like this:
oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. ## Detail 0 ## java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: LowUserId
The resolution in both cases is to double-check that the list of named bind variables in the SQL matches the list of named bind variables on the Bind Variables page.
If you do not supply a default value for your named bind variable, it defaults to the NULL value at runtime. This means that if you have a WHERE clause like:
USER_ID = :TheUserId
and you do not provide a default value for the TheUserId bind variable, it will default to having a NULL value and cause the query to return no rows. Where it makes sense for your application, you can leverage SQL functions like NVL(), CASE, DECODE(), or others to handle the situation as you require. In fact, the UserList view object uses a WHERE clause fragment like:
upper(FIRST_NAME) like upper(:TheName)||'%'
so that the query will match any name if the value of :TheName is null.
To set named bind variables at runtime, use the setNamedWhereClauseParam() method on the ViewObject interface. You can use JDeveloper's Refactor > Duplicate... feature to create a new TestClientBindVars class based on the existing TestClient.java class from Section 5.7, "How to Create a Command-Line Java Test Client". In this new test client class, you can set the values of the HighUserId and TheName bind variables using the few additional lines of code shown in Example 5-12.
Example 5-12 Setting the Value of Named Bind Variables Programmatically
// changed lines in TestClient class
ViewObject vo = am.findViewObject("UserList");
vo.setNamedWhereClauseParam("TheName","alex%");
vo.setNamedWhereClauseParam("HighUserId", new Number(315));
vo.executeQuery();
// etc.
Running the TestClientBindVars class shows that your bind variables are filtering the data, and the resulting rows are only the two matching ones for Alexander Hunold and Alexander Khoo:
303 ahunold 315 akhoo
Whenever a view object's query is executed, the runtime debug diagnostics show you the actual bind variable values that get used like this:
[256] Bind params for ViewObject: UserList [257] Binding param "LowUserId": 0 [258] Binding param "HighUserId": 315 [259] Binding param "TheName": alex%
This information that can be invaluable in isolating problems in your applications. Notice that since the code did not set the value of the LowUserId bind variable, it took on the design-time specified default value of 0 (zero). Also notice that the use of the UPPER() function in the WHERE clause and around the bind variable ensured that the match using the bind variable value for TheName was performed case-insensitively. The example code set the bind variable value to "alex%" with a lowercase "a", and the results show that it matched Alexander.
Using the view object's setWhereClause() method, you can add an additional filtering clause at runtime. This runtime-added WHERE clause predicate does not replace the design-time one, but rather further narrows the query result by getting applied in addition to any existing design-time WHERE clause. Whenever the dynamically added clause refers to a value that might change during the life of the application, you should use a named bind variable instead of concatenating the literal value into the WHERE clause predicate.
For example, assume you want to further filter the UserList view object at runtime based on the value of the USER_ROLE column in the table. Also assume that you plan to search sometimes for rows where USER_ROLE = 'technician' and other times where USER_ROLE = 'User'. While slightly fewer lines of code, it would be bad practice to do the following because it changes the where clause twice just to query two different values of the same USER_ROLE column:
// Don't use literal strings if you plan to change the value!
vo.setWhereClause("user_role = 'technician'");
// execute the query and process the results, and then later...
vo.setWhereClause("user_role = 'user'");
Instead, add a WHERE clause predicate that references named bind variables that you define at runtime like this:
vo.setWhereClause("user_role = :TheUserRole");
vo.defineNamedWhereClauseParam("TheUserRole", null, null);
vo.setNamedWhereClauseParam("TheUserRole","technician");
// execute the query and process the results, and then later...
vo.setNamedWhereClauseParam("TheUserRole","user");
This allows the text of the SQL statement to stay the same, regardless of the value of USER_ROLE you need to query on. When the query text stays the same across multiple executions, the database give you the results without having to reparse the query.
If you later need to remove the dynamically added WHERE clause and bind variable, you can use code like this:
vo.setWhereClause(null);
vo.removeNamedWhereClauseParam("TheUserRole");
An updated TestClientBindVars class illustrating these techniques would look like what you see in Example 5-13. In this case, the functionality that loops over the results several times has been refactored into a separate executeAndShowResults() method. The program first adds an additional WHERE clause of user_id = :TheUserId and then later replaces it with a second clause of user_role = :TheUserRole.
Example 5-13 TestClient Program Exercising Named Bind Variable Techniques
package devguide.examples.client;
import oracle.jbo.ApplicationModule;
import oracle.jbo.Row;
import oracle.jbo.ViewObject;
import oracle.jbo.client.Configuration;
import oracle.jbo.domain.Number;
public class TestClient {
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");
// Set the two design time named bind variables
vo.setNamedWhereClauseParam("TheName","alex%");
vo.setNamedWhereClauseParam("HighUserId", new Number(315));
executeAndShowResults(vo);
// Add an extra where clause with a new named bind variable
vo.setWhereClause("user_id = :TheUserId");
vo.defineNamedWhereClauseParam("TheUserId", null, null);
vo.setNamedWhereClauseParam("TheUserId",new Number(303));
executeAndShowResults(vo);
vo.removeNamedWhereClauseParam("TheUserId");
// Add an extra where clause with a new named bind variable
vo.setWhereClause("user_role = :TheUserRole");
vo.defineNamedWhereClauseParam("TheUserRole", null, null);
vo.setNamedWhereClauseParam("TheUserRole","user");
// Show results when :TheUserRole = 'user'
executeAndShowResults(vo);
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();
System.out.println(curUser.getAttribute("UserId")+" "+
curUser.getAttribute("Email"));
}
}
}
However, if you run this test program, you actually get a runtime error like this:
oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL) QRSLT WHERE (user_role = :TheUserRole) ## Detail 0 ## java.sql.SQLException: ORA-00904: "USER_ROLE": invalid identifier
The root cause, which appears after the ## Detail 0 ## in the stack trace, is a SQL parsing error from the database reporting that USER_ROLE column does not exist. That's odd, since the USERS table definitely has a USER_ROLE column. The problem occurs due to the mechanism that ADF view objects use by default to apply additional runtime WHERE clauses on top of read-only queries. Section 5.9.3.5, "Understanding the Default Use of Inline Views for Read-Only Queries", explains a resolution for this issue.
If you dynamically add an additional WHERE clause at runtime to a read-only view object, its query gets nested into an inline view before applying the additional WHERE clause. For example, suppose your query was defined as:
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL
At runtime, when you set an additional WHERE clause like user_role = :TheUserRole as the test program did in Example 5-13, the framework nests the original query into an inline view like this:
SELECT * FROM( select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL) QRSLT
and then adds the dynamic WHERE clause predicate at the end, so that the final query the database sees is:
SELECT * FROM( select USER_ID, EMAIL, FIRST_NAME, LAST_NAME from USERS where (upper(FIRST_NAME) like upper(:TheName)||'%' or upper(LAST_NAME) like upper(:TheName)||'%') and USER_ID between :LowUserId and :HighUserId order by EMAIL) QRSLT WHERE user_role = :TheUserRole
This query "wrapping" is necessary in the general case since the original query could be arbitrarily complex, including SQL UNION, INTERSECT, MINUS, or other operators that combine multiple queries into a single result. In those cases, simply "gluing" the additional runtime onto the end of the query text could produce unexpected results since, for example, it might only apply to the last of several UNION'ed statements. By nesting the original query verbatim into an inline view, the view object guarantees that your additional WHERE clause is correctly used to filter the results of the original query, regardless of how complex it is. The downside that you're seeing here with the ORA-904 error is that the dynamically added WHERE clause can refer only to columns that have been selected in the original query.
Section 27.3.3.7, "Disabling the Use of Inline View Wrapping at Runtime" explains how to disable this query nesting when you don't require it, but for now the simplest solution is to edit the UserList view object and add the USER_ROLE column to the end of its query's SELECT list on the SQL Statement page. Just adding the new column name at the end of the existing SELECT list — of course, preceded by a comma — is enough to do the job: the View Object Editor will automatically keep your view object's attribute list in sync with the query statement.
The modified test program in Example 5-13 now produces the expected results:
--- 303 ahunold 315 akhoo --- 303 ahunold --- 315 akhoo --- 303 ahunold
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.
As you've seen, all of the basic querying functionality of a view object can be achieved without using custom Java code. Clients can retrieve and iterate through the data of any SQL query without resorting to any custom code on the view object developer's part. In short, for many read-only view objects, once you've defined the SQL statement, you're done. However, it's important to understand how to enable custom Java generation for a view object when your needs might require it. Appendix D, "Most Commonly Used ADF Business Components Methods" provides a quick reference to the most common code that you will typically write, use, and override in your custom view object and view row classes. Later chapters discuss specific examples of how the SRDemo application uses custom code in these classes as well.
To enable the generation of custom Java classes for a view object, use the Java page of the View Object Editor. As shown in Figure 5-30, there are three optional Java classes that can be related to a view object. The first two in the list are the most commonly used:
View object class, which represents the component that performs the query
View row class, which represents each row in the query result
When you enable the generation of a custom view object class, if you also select the Bind Variable Accessors checkbox, then JDeveloper generates getter and setter methods in your view object class. Since the Users view object had three named bind variables (TheName, LowUserId, and HighUserId), the custom UsersImpl.java view object class would have corresponding methods like this:
public Number getLowUserId() {...}
public void setLowUserId(Number value) {...}
public Number getHighUserId(){...}
public void setHighUserId(Number value) {...}
public String getTheName() {...}
public void setTheName(String value){...}
These methods allow you to set a bind variable with compile-time type-checking to ensure you are setting a value of the appropriate type. That is, instead of writing a line like this to set the value of the LowUserId:
vo.setNamedWhereClauseParam("LowUserId",new Number(150));
You can write the code like:
vo.setLowUserId(new Number(150));
You can see that with the latter approach, the Java compiler would catch a typographical error had you accidentally typed setLowUserName instead of setLowUserId:
// spelling name wrong gives compile error vo.setLowUserName(new Number(150));
Or if you were to incorrectly pass a value of the wrong data type, like "ABC" instead of Number value:
// passing String where number expected gives compile error
vo.setLowUserId("ABC");
Without the generated bind variable accessors, an incorrect line of code like the following cannot be caught by the compiler:
// Both variable name and value wrong, but compiler cannot catch it
vo.setNamedWhereClauseParam("LowUserName","ABC");
It contains both an incorrectly spelled bind variable name, as well as a bind variable value of the wrong datatype. If you use the generic APIs on the ViewObject interface, errors of this sort will raise exceptions at runtime instead of being caught at compile time.
When you enable the generation of a custom view row class, if you also select the Accessors checkbox, then JDeveloper generates getter and setter methods for each attribute in the view row. For the Users view object, the corresponding custom UsersRowImpl.java class would have methods like this generated in it:
public Number getUserId() {...}
public void setUserId(Number value) {...}
public String getEmail() {...}
public void setEmail(String value) {...}
public String getFirstName() {...}
public void setFirstName(String value) {...}
public String getLastName() {...}
public void setLastName(String value) {...}
public String getUserRole() {...}
public void setUserRole(String value) {...}
These methods allow you to work with the row data with compile-time checking of the correct datatype usage. That is, instead of writing a line like this to get the value of the UserId attribute:
Number userId = (Number)row.getAttribute("UserId");
you can write the code like:
Number userId = row.getUserId();
You can see that with the latter approach, the Java compiler would catch a typographical error had you accidentally typed UserIdentifier instead of UserId:
// spelling name wrong gives compile error Number userId = row.getUserIdentifier();
Without the generated view row accessor methods, an incorrect line of code like the following cannot be caught by the compiler:
// Both attribute name and type cast are wrong, but compiler cannot catch it
String userId = (String)row.getAttribute("UserIdentifier");
It contains both an incorrectly spelled attribute name, as well as an incorrectly-typed cast of the getAttribute() return value. Using the generic APIs on the Row interface, errors of this kind will raise exceptions at runtime instead of being caught at compile time.
When enabling the generation of a custom view row class, if you choose to generate the view row attribute accessor, you can also optionally select the Expose Accessor to the Client checkbox. This causes an additional custom row interface to be generated which application clients can use to access custom methods on the row without depending directly on the implementation class. As you learned in Chapter 4, "Overview of ADF Business Components", having client code work with business service tier interfaces instead of concrete classes is a best practice which ensures that client code does not need to change when your server-side implementation does.
In the case of the Users view object, exposing the accessors to the client will generate a custom row interface named UsersRow. This interface is created in the common subpackage of the package in which the view object resides. Having the row interface allows clients to write code that accesses the attributes of query results in a strongly typed manner. Example 5-16 shows a TestClient3 sample client program that casts the results of the next() method to the UsersRow interface so that it can call getUserId() and getEmail().
Example 5-16 Simple Example of Using Client Row Interface with Accessors
package devguide.examples.client;
import devguide.examples.common.UsersRow;
import oracle.jbo.ApplicationModule;
import oracle.jbo.ViewObject;
import oracle.jbo.client.Configuration;
import oracle.jbo.domain.Number;
public class TestClient3 {
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");
vo.executeQuery();
while (vo.hasNext()) {
// Cast next() to a strongly-typed UsersRow interface
UsersRow curUser = (UsersRow)vo.next();
Number userId = curUser.getUserId();
String email = curUser.getEmail();
System.out.println(userId+ " " + email);
}
Configuration.releaseRootApplicationModule(am, true);
}
}
You've seen how to generate custom Java classes for your view objects when you need to customize their runtime behavior, or if you simply prefer to have strongly typed access to bind variables or view row attributes.
To configure the default settings for ADF Business Components custom Java generation, choose Tools | Preferences and open the Business Components page to set your preferences to be used for business components created in the future. Oracle recommends that developers getting started with ADF Business Components set their preference to generate no custom Java classes by default. As you run into specific needs, you can enable just the bit of custom Java you need for that one component. Over time, you'll discover which set of defaults works best for you.
When you choose to generate one or more custom Java classes, JDeveloper creates the Java file(s) you've indicated. For a view object named devguide.examples.Users, the default names for its custom Java files will be UsersImpl.java for the view object class and UsersRowImpl.java for the view row class. Both files get created in the same ./devguide/examples directory as the component's XML component definition file.
The Java generation options for the view object are continue to be reflected on the Java page on subsequent visits to the View Object Editor. Just as with the XML definition file, JDeveloper keeps the generated code in your custom java classes up to date with any changes you make in the editor. If later you decide you didn't require a custom Java file for any reason, unchecking the relevant options in the Java page causes the custom Java files to be removed.
As with all ADF components, when you select a view object in the Application Navigator, the Structure window displays all of the implementation files that comprise it. The only required file is the XML component definition file. You saw above that when translatable UI control hints are defined for a component, it will have a component message bundle file as well. As shown in Figure 5-31, when you've enabled generation of custom Java classes, they also appear under the Sources folder for the view object. When you need to see or work with the source code for a custom Java file, there are two ways to open the file in the source editor:
Choose the relevant Go to option in the context menu as shown in Figure 5-31
Double-click on a file in the Sources folder in the Structure window
See the following sections for additional information to help you use custom Java classes.
When you use an "XML-only" view object, at runtime its functionality is provided by the default ADF Business Components implementation classes. Each custom Java class that gets generated will automatically extend the appropriate ADF Business Components base class so that your code inherits the default behavior and can easily add or customize it. A view object class will extend ViewObjectImpl, while the view row class will extend ViewRowImpl (both in the oracle.jbo.server package).
Based perhaps on previous negative experiences, some developers are hesitant to add their own code to generated Java source files. Each custom Java source code file that JDeveloper creates and maintains for you includes the following comment at the top of the file to clarify that it is safe to add your own custom code to this file:
// --------------------------------------------------------------------- // --- File generated by Oracle ADF Business Components Design Time. // --- Custom code may be added to this class. // --- Warning: Do not modify method signatures of generated methods. // ---------------------------------------------------------------------
JDeveloper does not blindly regenerate the file when you click the OK or Apply button in the component editor. Instead, it performs a smart update to the methods that it needs to maintain, leaving your own custom code intact.
As you've seen, the view object is designed to function either in an XML-only mode or using a combination of an XML component definition and a custom Java class. Since attribute values are not stored in private member fields of a view row class, such a class is not present in the XML-only situation. Instead, in addition to a name, attributes are also assigned a numerical index in the view object's XML component definition, on a zero-based, sequential order of the ViewAttribute and association-related ViewLinkAccessor tags in that file. At runtime, the attribute values in an view row are stored in a structure that is managed by the base ViewRowImpl class, indexed by the attribute's numerical position in the view object's attribute list.
For the most part this private implementation detail is unimportant. However, when you enable a custom Java class for your view row, this implementation detail is related to some of the generated code that JDeveloper automatically maintains in your view row class, and you may want to understand what that code is used for. For example, in the custom Java class for the Users view row, Example 5-17 shows that each attribute or view link accessor attribute has a corresponding generated integer constant. JDeveloper ensures that the values of these constants correctly reflect the ordering of the attributes in the XML component definition.
Example 5-17 Attribute Constants Are Automatically Maintained in the Custom View Row Java Class
public class UsersRowImpl extends ViewRowImpl implements UsersRow {
public static final int USERID = 0;
public static final int EMAIL = 1;
public static final int FIRSTNAME = 2;
public static final int LASTNAME = 3;
public static final int USERROLE = 4;
public static final int ASSIGNEDREQUESTS = 5;
// etc.
You'll also notice that the automatically maintained, strongly typed getter and setter methods in the view row class use these attribute constants like this:
// In devguide.examples.UsersRowImpl class
public String getEmail() {
return (String) getAttributeInternal(EMAIL); // <-- Attribute constant
}
public void setEmail(String value) {
setAttributeInternal(EMAIL, value);// <-- Attribute constant
}
The last two aspects of the automatically maintained code related to view row attribute constants are the getAttrInvokeAccessor() and setAttrInvokeAccessor() methods. These methods optimize the performance of attribute access by numerical index, which is how generic code in the ViewRowImpl base class typically accesses attribute values. An example of the getAttrInvokeAccessor() method looks like the following from the ServiceRequestImpl.java class. The companion setAttrInvokeAccessor() method looks similar.
// In devguide.examples.UsersRowImpl class
protected Object getAttrInvokeAccessor(int index,AttributeDefImpl attrDef)
throws Exception {
switch (index) {
case USERID: return getUserId();
case EMAIL: return getEmail();
case FIRSTNAME: return getFirstName();
case LASTNAME: return getLastName();
case USERROLE: return getUserRole();
case ASSIGNEDREQUESTS: return getAssignedRequests();
default:
return super.getAttrInvokeAccessor(index, attrDef);
}
}
The rules of thumb to remember about this generated attribute-index related code are the following.
Add custom code if needed inside the strongly typed attribute getter and setter methods
Use the View Object Editor to change the order or type of view object attributes
JDeveloper will change the Java signature of getter and setter methods, as well as the related XML component definition for you.
Don't modify the getAttrInvokeAccessor() and setAttrInvokeAccessor() methods
Don't change the values of the attribute index numbers by hand
Note:
If you need to manually edit the generated attribute constants, perhaps due to source control merge conflicts, you must ensure that the zero-based ordering reflects the sequential ordering of the ViewAttribute and ViewLinkAccessor tags in the corresponding view object XML component definition.