How to Develop with ADF Business Components for Multiple Databases

How to Develop with ADF Business Components for Multiple Databases

Blaise Ribet, ADF Product Management
January 2010

This document describes best practices for developing a fusion web application in JDeveloper 11g that can run on multiple databases. Applications built with the fusion technology stack (ADF business components, ADF controller, ADF Faces)  are supported with Oracle, SQL Server, DB2 and a number of other database; the document JDeveloper certification information contains the full list

Unsupported Features

When you're developing an application to run on multiple databases, some features are not supported and should not be used.

Auto-Generated Primary Keys

Your database cannot contain any automatically-generated primary key columns (IDENTITY column in SQL Server). Instead, use ADF BC-generated unique ids for primary keys of this type (see the section, Set Up Primary Key Generation).

Batch Update and Insert of Entity Objects

The Update Batching feature for entity objects is not supported (this option is set in the Tuning section of the entity object overview editor).

Large Object and Image Types

Large object and image types are unsupported. This includes the SQL Server types nvarchar(max), varchar(max), and varbinary(max); and the DB2 LOB types.

User-Defined Function Calls in View Object Queries

Because calls to user-defined database functions are different for different database types (for example, when calling a SQL Server user-defined function in a select statement the function name must be prefixed with a schema name), the query defined for a view object in design-time should not include any calls to user-defined database functions. The recommended approach is to implement your business logic in the middle tier. You may also call your user-defined function from a database trigger; or define the view object's query in runtime code, where you can define different queries for each database type.

View Object Auto Refresh

The view object auto refresh property (set in the tuning section of the property inspector) is not supported.

View Object Prefetch Size

The Tuning section of the view object overview editor has options to set the number of rows retrieved from the database. These settings are not supported for non-Oracle databases.

View Object Range Paging

A view object's access mode cannot be set to Range Paging, Range Paging Auto Post, or Range Paging Incremental.

Create a Database Connection and Register the JDBC Driver with JDeveloper

Before building a fusion web application you need to create a database connection. If you are developing against a non-Oracle database, you also need to register the appropriate JDBC driver with JDeveloper. You can do these two things together as follows.

1. Before you start, make sure the JAR file for the JDBC driver is on your local machine.
2. In JDeveloper, open the database navigator (View -> Database -> Database Navigator). Right-click the IDE Connections node and select New Connection.
3. In the Create Database Connection dialog, enter a name for your connection and select the connection type for the database you are developing against..
4. Enter the username and password. Fill in the remaining settings, or select Enter Custom JDBC URL and enter a JDBC URL.
5. To register the JDBC library, click Browse..., then in the Select Library dialog click New to create a new library for your driver.
6. In the Create Library dialog, create a User library and add an entry for the driver's jar file.
7. Click Test Connection to test your new connection, then click OK. If the connection has succeeded you can browse your database objects in the Database Navigator.

The JDBC driver is now registered with JDeveloper; if you create other connections to the same type of database you do not need to create a new JDeveloper library; you can select the JDeveloper library that you have just created.

Set the Appropriate Default Settings

There are some default settings you can select to help enforce best practices.

• Use ANSI-style outer-join syntax in view objects with multiple entity objects. Select Tools -> Preferences, then Business Components -> View Objects. Select Use Ansi style outer-join syntax while generating SQL for join view objects.
• Create Declarative SQL view objects by default. The SQL for declarative SQL view objects is generated at run time. Select Tools -> Preferences, then Business Components -> View Objects. Select the following options:
• • Enable Declarative SQL mode for new objects
  • Include all attributes in runtime-generated query
  •  

Create and Initialize your Business Components Project

1. From the New Gallery (File -> New), create a new Fusion Web Application. Alternatively, in an existing application create a new ADF Model Project.
2. From the New Gallery, create Business Components from Tables.
3. In the Initialize Business Components Project dialog, select the database connection you created earlier. If your database connection isn't displayed in the dropdown list of connections, click Browse to select your connection, or Add to create a new one.
4. Select SQL92 for the SQL Flavor.
5. Select the Java type map.
6.  

Set Up Primary Key Generation

Follow these steps if you need to generate unique identifiers for primary keys.

1. Create a Unique Id Table
ADF BC's unique id generation feature uses a database table to specify the range of ids that may be used, as well as the latest id in the sequence.  Create a database table named S_ROW_ID and add one row of data to the table, using the following format:

Column Name	Data Type	Comments
START_ID	NUMERIC(38,0)	Starting id in the range. To avoid duplicate key errors, you must make sure this value is higher than any existing primary key values in your database.
NEXT_ID	NUMERIC(38,0)	Next available id within current range (optional).
MAX_ID	NUMERIC(38,0)	Maximum id in the range
AUX_START_ID	NUMERIC(38,0)	Starting id in auxiliary block range (use 0 if no auxiliary block is available).
AUX_MAX_ID	NUMERIC(38,0)	Maximum id in the auxiliary block range (use 0 if no auxiliary block is available).

2. Create a Connection to the Table
In your application, create a database connection named ROWIDAM_DB that points to the database containing your S_ROW_ID table. Alternatively, edit your BC project's properties and add the following Java option to the project's run configuration:
 -Djbo.rowid_am_conn_name= appconnection
where appconnection is the name of a database connection that points to the S_ROW_ID table.

3. Configure the Primary Key Attributes
To generate a unique id for a primary key attribute, edit the entity object attribute and enter the following expression for the attribute's value:
oracle.jbo.server.uniqueid.UniqueIdHelper.getNextId()

Limitations of Primary Key Generation
Since all primary keys are generated from a single row in the S_ROW_ID table, all entity objects in your application share the same sequence of keys. 

While Developing Business Components

Use Declarative SQL Mode view objects whenever possible
If your view objects use declarative SQL mode, the where clause SQL is generated at run time. This allows ADF to generate the correct SQL for your project's SQL flavor. If you selected Enable Declarative SQL Mode as a global preference before creating any business components, then this default is already selected. Otherwise you can change the SQL mode by editing the view object's query and selecting Declarative as the SQL mode.

Use JDBC Positional Binding Style for bind variables
If you are developing against a non-Oracle database, this style is selected automatically so you don't need to do anything. If you are developing against an Oracle database you must set this option before creating any bind variables in a view object. Edit the view object's query and select JDBC Positional as the binding style. If you are creating individual view objects using the View Object wizard, select the binding style in step 5, Query.

Make sure any hand-coded SQL is database-neutral
You may need to add hand-coded SQL in expert mode view objects; in the where clause of normal, entity-based view objects; or in calculated attributes derived from a SQL expression. If you do write any SQL, make sure it will work on other databases you may want to run this application against. The safest approach is to use SQL92 constructs.

Don't select refresh on insert or refresh on update for entity object attributes
These options generate Oracle-specific SQL and should not be used.

Configure Runtime SQL Generation

Before running your application, you must specify a system property to generate the correct flavor of SQL at runtime. You do this by specifyingjbo.SQLBuilder as a system property with the correct value for the database you are using, see the following table.

Database	Value of jbo.SQLBuilder
SQL Server	SQLServer
DB2	DB2
Other databases (MySQL, Sybase, etc)	SQL92

You can specify the system property as an operating system environment variable or Java command line option, but the recommended approach is to set up a web application filter, using the steps below.

1. Create a Servlet Context Listener
Create a new class in your application and add code to set the jbo.SQLBuilder property to the correct value for your database. This example is for SQL Server

package myproject.common; // Substitute your package name import java.security.AccessController; import java.security.PrivilegedAction; import javax.servlet.ServletContext; import javax.servlet.ServletContextEvent; import javax.servlet.ServletContextListener; import oracle.jbo.common.PropertyMetadata; public class InitializationListener implements ServletContextListener { private ServletContext context = null; public void contextInitialized(ServletContextEvent event) { context = event.getServletContext(); AccessController.doPrivileged( new PrivilegedAction() { public Object run() { // Set the SQL Builder to the correct value for your database, SQL Server in this example. System.setProperty(PropertyMetadata.PN_SQLBUILDERIMPL.getName(), "SQLServer"); return null; } }); } public void contextDestroyed(ServletContextEvent event) { context = event.getServletContext(); } }

2. Set up a Web Application Filter
Edit your application's web.xml file and add <listener> tags containing your servlet context listener class. This causes your listener to be executed every time the application starts up.

<listener> <listener-class>myproject.common.InitializationListener</listener-class> </listener>

 Note: The application module's configuration has a jbo.SQLBuilder property, but setting it has no effect. See therelated release note describing this problem.

Configure Passivation

During application module passivation, ADF Business Components runtime uses a persistence manager to write snapshots of your application module's state to the database. In order for application module passivation to work correctly you must make sure your application is using the correct persistence manager. Note that your application doesn't have to passivate to a database: file-based passivation is an option but is not generally recommended for production applications because it does not work in a cluster environment. If you're not familiar with ADF BC state management you may wish to read this paper: Understanding Application Module Pooling Concept and Configuration Parameters.

Database	How to Configure Passivation
SQL Server	Edit the application module's configuration and set the jbo.pcoll.mgr property to oracle.jbo.pcoll.pmgr.SQLServerPersistManager. See Note 1, below.
DB2	No action required: the correct persistence manager will be picked up automatically.
Other databases (MySQL, Sybase, etc)	Option 1, recommended: Implement a custom persistence manager and set the jbo.pcoll.mgr property to your persistence manager, see Note 2 below. Option 2: Do nothing, in which case file-based passivation will be used.

Note 1: How to Edit Your Application Module's Configuration and Change the jbo.pcoll.mgr Property

1. In the application navigator, right-click your application module and select Configurations...
2. In the Manage Configurations dialog, select the configuration you will be using at runtime and click Edit. If you are using multiple configurations, or if you're not sure which configuration you will be using, you can edit each configuration in the list.
3. In the Edit Business Components Configuration dialog, select the Properties tab.
4. Change the value of the jbo.pcoll.mgr property to the fully qualified class name of the correct persistence manager.

Note 2: How to Implement a Custom Persistence Manager
To create a custom persistence manager you need to implement the interface oracle.jbo.pcoll.PersistManager. As a short cut you can use SQLServerPersistManager.java as a starter class. Save this file and rename it (and the class name), then modify all SQL statements in the code to work for your database.
After creating your persistence manager, edit your application module's configuration and change the value of the jbo.pcoll.mgr property to the fully qualified class name of your custom persistence manager; see Note 1 above if you're not sure how to do this.

Further Information

•  
• JDeveloper certification information - Lists supported databases
• Understanding Application Module Pooling Concepts and Configuration Parameters
• Fusion Developer's Guide for Oracle ADF
• ADF home page on OTN

false ,,,,,,,,,,,,,,,,