IT Innovation

Working with Data


Using Oracle ADF to query, update, and publish data

By Steve Muench

May/June 2005


My last column ("Starting with Oracle ADF," in the March/April 2005 issue) explored the basic organization of a J2EE application and considered some simple examples of validation rules you might write for your Oracle ADF entity objects. This column explores how to query and manipulate data by using Oracle ADF view objects and how to expose that data as a Web service to other clients in a service-oriented architecture, using an Oracle ADF application module. (Note that the steps in this article use the new Oracle JDeveloper 10.1.2 production release, available on OTN.)

Creating a View Object for a SQL Query

A fundamental job in any business application is querying and manipulating database data, so Oracle ADF provides the view object component to simplify the task. A view object encapsulates a SQL query and lets you easily work with the rows in its result set. The view object gives you a simple API and internally handles interacting with the lower-level JDBC API, saving you a lot of coding and avoiding common JDBC pitfalls. When you define a view object in Oracle JDeveloper 10g, you start by indicating whether it will be read-only or whether you need the data to be updatable. Let's start with the simpler of the two: a read-only query.

If you already have a query in mind, you can create an Oracle ADF view object to work with its results in Java. If, instead, you need to experiment a little to determine your query, start by using the Oracle JDeveloper 10g SQL Worksheet. To access the worksheet, right-click on the desired database connection name under the Database folder in the Connection Navigator and select SQL Worksheet from the context menu. There you can experiment with different SELECT statements until you're satisfied with the results.

Select File->New from the Oracle JDeveloper 10g main menu, and choose the Business Components category in New Gallery to launch the View Object wizard. After specifying a meaningful package and component name for your view object in the first panel of the wizard, indicate that you want it to be read-only and click on Next . Then just paste your SELECT statement into the Query panel. For example, suppose you enter oramag.adf for the package name, enter EmployeeSeniority for the view object name, and then paste in a query like this:


After clicking on Next , you can see how the names of the view object attributes correspond to the columns in your SELECT list on the Attribute Mappings panel. Note that Oracle ADF gives names, such as YearsOfService , that are more Java-friendly than SQL column names such as YEARS_OF_SERVICE . You'll use these more Java-friendly attribute names to work with the data in each row in the view object's query results.

Click on Next , and in the Attribute Settings panel, confirm that all of the attributes are marked as Selected in Query . Click on Finish ; you're done.

Working with View Object Query Results

SQL queries need to run in the context of a database connection, and Oracle ADF supplies the application module component that provides it. Application modules simplify implementing data-centric business services that use instances of view objects to perform their data querying and manipulation. Accordingly, to put your new view object component to work, you'll use it in an application module. The term data model describes the set of named view object instances an application module contains.

In the New Gallery, create a new application module named EmpService in the oramag.adf package and include an instance of the EmployeeSeniority view object in its data model. Each view object instance in the data model needs a unique instance name. The default name of the instance you just added will be EmployeeSeniority1 , which is fine for now.

In future columns, I'll write more about Oracle ADF's automatic support for binding data from an application module's data model to user interface pages, but here you'll see that even working programmatically with data from your view object is easy. Each application module has an associated set of runtime configuration settings that includes details about its database connection, among many other customizable properties. The default configuration for an EmpService application module is named EmpServiceLocal , and in practice it's the one you use most often.

Assuming that the variables named AM and CF contain strings with the fully-qualified application module name ( oramag.adf.EmpService ) and its configuration name, respectively, Listing 1 shows the basic code you can use to create an instance of the application module, find a view object instance by name, execute its query, loop through its result rows, and finally release the application module when you're done.

Code Listing 1: Working progammatically with view-object data

ApplicationModule empSvc = Configuration.createRootApplicationModule(AM, CF);
ViewObject emps = empSvc.findViewObject("EmployeeSeniority1");
while (emps.hasNext()) {
  Row emp =;
Configuration.releaseRootApplicationModule(empSvc, true);

Creating Updatable View Objects

When your application needs to INSERT, UPDATE , or DELETE data, your view object can automatically collaborate with related Oracle ADF entity objects to get the job done. Suppose that you've already created an Emp entity object (related to the EMP table) in the oramag.adf package. To create an updatable view object, select the Updateable Access through Entity Objects option on the first panel of the View Object wizard. Clicking on Next takes you to the Entity Objects panel, where you can select the entity objects to include in the view. On the following panel, select the entity attributes you want to include. If you choose multiple related entities to include in the view object, you'll get an automatic join query and management of referenced data when you modify foreign key values as well.

Although the wizard automatically determines the query you need, you can still modify it on the Query panel. For example, you can adjust a join clause to be an outer join instead, if necessary, as well as add more WHERE or ORDER BY clauses. If you need a really advanced SQL query, you can also check the Expert Mode check box to have complete control over the entire SELECT statement. As you are using the various wizard panels to configure your view object, the wizard is saving information in a related XML file to record how your query's SELECT list columns relate to underlying entity object attributes. This metadata is used by the framework at runtime to enable updatability of your data and enforce business rules without requiring you to write any code to achieve this result. Note that to create updatable Expert Mode view objects, you need to ensure that the information on the Attribute Mappings panel is correct. Generally, if you've kept the number, names, and datatypes of the SELECT list expressions the same as when you introduced your more-complex query statement, you won't need to perform any manual adjustments.

Assume you've created an updatable view object named EmpInfo in the oramag.adf package as described here. To use it at runtime, you just need to add it to the data model of some application module. So right-click on the application module you created previously in the Application Navigator and choose Edit from the context menu. On the Data Model panel, add an instance, named EmpInfo1, of the updatable view object to the data model, and you're ready to query and update data. Listing 2 shows code for finding an instance of your updatable view object, creating a new row, setting some of the row's attributes, and committing the row to the database. Updating data in existing rows is equally straightforward. Because the view object delegates data modification to underlying entity objects, the encapsulated business validation rules are consistently enforced without your having to write additional code.

Code Listing 2: Creating and committing a new employee

ViewObject emps = empSvc.findViewObject("EmpInfo1");
Row newEmp = emps.createRow();
newEmp.setAttribute("Empno", new Number(1234));

Publishing an Application Module as a Web Service

In a service-oriented architecture, the focal point for developers is building business services for clients of various types. Luckily, the Oracle ADF application module makes implementing any kind of data-centric service a snap. You can write your service component once and easily deploy it as a simple JavaBean, an Enterprise JavaBean, or a Web service. In addition to providing a convenient transaction context for view objects and their related entity objects, an application module is also a class in which you can write service methods to expose business functionality to clients.

Say you want to provide a service method that allows a client to look up an employee's years of service. You can add a lookupYearsOfService method to your application module class that uses the view object to look up the years of service for the employee ID passed in. The simple code in Listing 3 finds the view object to use, sets an additional WHERE clause, binds a value of the first positional bind parameter to the ID number of the employee passed in, executes the query, and returns the value of the YearsOfService attribute if the row in question is found.

In Listing 3, note the WHERE clause containing a bind variable that we set: WHERE EMPNO = :0 . Should they be needed in view objects you create on your own, subsequent bind variable placeholders would be named : 1, :2 , and so on, reflecting their position in the query string.

You can publish this application module as a Web service with just two additional steps. First, go to the Client Interface panel of the application module editor and select the lookupYearsOfService method to be included on the component's client interface. Then go to the Remote panel in the same editor and select J2EE Web Service as your deployment style. That's it! Just point Oracle JDeveloper 10g at a target application server and deploy the Oracle ADF-powered Web service.

Code Listing 3: Simple service method in an application module

public long lookupYearsOfService(long empId) {
  ViewObject emps = findViewObject("EmployeeSeniority1");
  emps.setWhereClause("empno = :0");
  emps.setWhereClauseParam(0, new Number(empId));
  Row emp = emps.first();
  return emp == null ? 0 : ((Number)emp.getAttribute("YearsOfService")).longValue();


This column provides some simple examples of how the Oracle ADF application module, view object, and entity object components work together to simplify implementation of data-centric business services. They make querying, modifying, and validating business information much easier than trying to implement these common requirements by hand. For a complete list of J2EE design patterns that Oracle ADF implements for you, see

Next Steps

READ more about Oracle ADF

Oracle JDeveloper 10g
 workspace containing this article's sample code


Photography byNoah Näf,Unsplash