A DATABASE-CENTRIC APPROACH TO J2EE

A Database-Centric Approach to J2EE Application Development

(Where should code go: Java or PL/SQL?)

Toon Koppelaars, Centraal Boekhuis

Introduction

Building a J2EE/Java application may seem a daunting task at first. Java, object oriented programming itself, is truly different, and cannot be compared with PL/SQL (which can be classified as an easy to learn programming language). There are many new technologies one has to explore and learn: for example, various frameworks to enable the Model-View-Control design pattern, HTTP, the stateless-protocol, HTML in the browser, including JavaScript, JDeveloper, Oracle's strategic IDE. Will you ever be able to grasp all this new technology? There are ways to ease this daunting task: in particular for traditional Oracle client/server shops. The most successful way is to adopt a database-centric development approach. The Oracle server has come a long way and various features such as Virtual Private Database (VPD), Instead Of Triggers (IOT), (updateable) views, Ref Cursors 121g63b and packaged procedures today enable you to implement the majority of all involved application logic into the well known database server. Strongly preferring stored PL/SQL to Java when deciding on where code should go, will enable a very thin J2EE/Java layer. By never writing a line of Java code that could have been a line of PL/SQL code, you will considerably reduce the risk in your J2EE project.

In order to deal in a structured way with the question in the title above, we will first present a model of the J2EE technology stack from the viewpoint of 'code execution environments'. This will enable us to talk about the where of code execution. Next we will develop a generic classification scheme for code (irrespective of where the code ends up running). This will enable us to talk about what code. We can then map code onto the technology stack. This paper will discuss how traditional Oracle client/server shops can approach the J2EE world using a database-centric mapping. We then explain in more detail how we successfully apply this strategy in all of our J2EE application development efforts.

Recap Of The J2EE Architecture

It is not our intention here, to give an in-depth description of the complete J2EE specification. Rather we will explain some main architecture components that enable you, the Oracle Client/Server guru, to start understanding the J2EE arena in general, and specifically its execution environments.

J2EE defines a multi-tier, distributed object architecture. The multi-tier environment breaks down into a Client Tier, a Middle Tier (the application server) and a Data Tier (the database server). Inside this multi-tier environment, J2EE supports the execution of five different software-component types.

Java inside client OS (fat clients)

Applets

Servlets

JavaServer Pages (JSP)

Enterprise JavaBeans (EJB)

Java Inside Client OS

This is J2EE's way of creating a traditional fat-client. Using Java in conjunction with libraries such as Swing or AWT, you write a front-end application that presents a rich user interface to the user. This Java application communicates with the Middle Tier to invoke business services and/or Data Tier to retrieve/manipulate persistent data. The application runs inside the Java Virtual Machine (JVM) of the client Operating System (typically windows).

Applets

These are Java programs that execute inside an Applet Container in your web browser . Usually this container will be the JVM of your browser. Applet-execution is initiated by your browser whenever it detects the Applet-tag in the html-source. Part of the Applet-tag is the Applet's Java classfile that needs to be fetched from the web server. Once the browser has downloaded this file from the webserver, it invokes a start-method which starts Applet execution. Applets too can use the popular Swing and AWT libraries to provide a rich (i.e. not HTML or XML based) User-Interface to the user. As such they too can be seen as 'fat' client programs, only these run inside your browser.

Servlets

These are Java programs that execute inside a Servlet Container on your web server. This container is provided by your vendor: Oracle provides OC4J (Oracle Containers for J2EE) as the J2EE Servlet Container. Note that a Servlet Container is itself a Java program, so it in turn runs inside the JVM provided by the operating system of your web server. Execution of Servlets is typically started by an incoming HTTP-request (post or get): when handed the request, OC4J instantiates the Servlet-object by loading the Java class file that holds the Servlet's byte-code from the filesystem and then invokes the doPost or doGet method on the Servlet object. Servlets handle the HTTP-request and eventually create the next (html or xml) page to be sent back to the browser that initiated the request. By doing so, Servlets can be viewed as Java's answer to existing solutions such as: CGI, PHP and ASP.

JavaServer Pages (JSP)

These are html pages mixed with special tags that hold embedded Java. This Java will be executed prior to sending the html to the browser and is able to generate dynamic content. JSP's eventually execute in the same Container as Servlets do. Whenever OC4J detects that a JSP needs to be executed, it reads the JSP-file from the filesystem and converts the JSP into a Servlet by generating a class file for it. It then instantiates this Servlet class and invokes a method to start execution of this 'servletised' JSP. It's often said that 'a JSP is a Servlet inside out' (HTML that executes embedded Java), and vice-versa 'a Servlet is a JSP inside out' (Java that outputs html).

Enterprise JavaBeans (EJB)

These are Java programs that execute inside an EJB-Container on your application server (not necessarily the same as your web server). With Oracle, OC4J also acts as the EJB Container. EJB's are used to implement reusable business functions. As such, EJB's typically act as 'back-end' application services for the other component types. They are considered to be 'fat', meaning that they hold the business and data logic code for the application. EJB's sit between Servlets/JSP's and the Data Tier: Servlets/JSP's can call EJB's. EJB's can also call other EJB's, potentially on other application servers. EJB's will communicate with the persistent data store (database) on the Data Tier.

Servlets, JSP's, and EJB's are enabled to access the relational database running at the Data Tier by means of JDBC (Java DataBase Connectivity). JDBC is J2EE's standard database access API, that replaces the well known SQL*Net layer in Client/Server applications. Figure 1 summarizes the multi tier, distributed object architecture.

Figure 1: The three tiers and five J2EE component types

J2EE Says Nothing About Data Tier (code) Objects

There are no software component types in the J2EE architecture that run on the Data Tier. J2EE does not regard the Data Tier as a tier that could (or even should) run code. On the contrary the Data Tier is supposed to only house persistent data, and surely no code. The J2EE standard prides itself of being portable across platforms. This is enabled by Java's "Write Once Run Anywhere" promise. As such J2EE will never introduce Data Tier (RDBMS) specific specifications (nor any other vendor specific service). The API between the Middle Tier and the Data Tier typically does not have procedure calls in it, but is fully based on SQL-statements (select, insert, update, delete) in conjunction with transactional and session control commands (commit, rollback, connect, disconnect). Many of us Oracle adepts must find this very sad. The Oracle database server is capable of doing so much more than just service SQL-statements.

J2EE Architecture Or Architectures?

As may have become clear from above sections, there is actually no such thing as one J2EE architecture. You will have to pick your own architecture that will fit your J2EE application. Not all J2EE applications will use/require all software component types mentioned above. Looking at the Middle Tier, the majority of the applications out there do not use EJB's (considered a most difficult to use component type). Instead they employ some framework to implement direct access to tables/views in the database. Typically a framework will offer an execution environment within which code can be added around the retrieval and manipulation of data. Oracle's BC4J framework is a prime example of such a framework. In a very similar way as Oracle*Forms does, BC4J too allows you to add block/row/item level triggers that implement additional business logic. Looking at the Client Tier, most J2EE applications make use of the thin HTML front-end to implement the user interface. Fewer employ Applets or fat Java clients to implement the user interface.

A common denominator in all alternative architectures is the use of the MVC design pattern. The next section will shortly introduce this design pattern. Thereafter we will present a few alternative architectural approaches.

MVC

The Model-View-Control design pattern sounds new, but in fact is something Oracle Client/Server developers (perhaps not knowingly) do everyday when building Forms applications. This design pattern dictates that application code should be separated into three parts: the Business Logic part, the User Interface part and the Controller part.

Handling Business Logic: Model

This is the application code that is responsible for the 'Business Logic', including the data retrieval and manipulation. Client/Server developers that have adopted the fat database server model separate this code by creating database stored PL/SQL modules that are called by client-side code, and by creating database triggers that are fired by DML-statements for validation of the transaction. J2EE developers will use a specific Model framework like BC4J to develop this code. The use of Toplink or EJB's can also be positioned within the M of MVC.

Creating User Interface: View

This is the code that is responsible for creating the 'Look' of the application. It creates the very front end of the application: screens/pages that the user is presented with to interact with the application. Client/Server developers use the Forms*Builder screen-painter to define screens (Canvases, Blocks, LOV's, menus etc.) and then delegate 'creating the Look' to the runform component of Forms. J2EE developers will use a specific View framework like UIX to develop this code. Homegrown tag libraries for JSP's can also be positioned within the V of MVC.

Dealing with User Interface events (triggered by user): Control

This is the code that is responsible for creating the 'Feel' of the application. Every event that the user can trigger by using the View-layer of the application, is dealt with by this code. Client/Server developers, again, delegate the majority of this task to the state-machine called runform. Runform presents us with default behavior that deals with every possible User Interface event. It even offers us the ability to adapt this behavior by writing triggers (in the Client side) that override/enhance the default behavior. J2EE developers will use a specific Control framework like Struts to develop this code. Oracle's own ADF-controller can also be positioned within the C of MVC.

We will now continue by examining different MVC approaches.

Alternative MVC Approaches In J2EE

We will study alternative MVC approaches by looking at the amount of application code that is deployed in each tier. This is a suitable way to explore these alternatives, given the context of this paper. A tier is considered Fat if it houses lots of code, and Thin if it houses little code. In this way we can generate eight alternatives:

Table 1: Alternative MVC approaches

Thin/Fat/Thin This is the typical J2EE way of creating an HTML-based application. Control is implemented for instance with Struts. Model is implemented with EJB or some model framework (e.g. BC4J). View is implemented with JSP or some view framework (e.g. UIX). All the browser does is display a (poor) HTML GUI[3] (thin), and send HTTP requests to the Struts controller. The controller then coordinates the execution of EJB code or model framework code. Inside these all business logic and data logic processing has been implemented (fat). Eventually simple queries (single table), or simple DML statements (single row, primary-key based) are executed towards the database (thin). Struts then determines the next page to be sent back and initiates the View framework to do so. Both Control and View will not offer the possibility to house business or data logic, since that would violate the MVC design pattern.

Fat/Fat/Thin This is the typical J2EE way of creating a rich GUI based application (either Applet based, or Java-in-OS based). Control and View are implemented within the fat client-side GUI application (compare to Cient/Server- Oracle*Runform). Model is implemented with EJB or some model framework. The client tier is in charge in this alternative (fat): it deals with creating the rich GUI and handling all UI-events (control) on behalf of the user. In this alternative the client delegates the execution of all business logic and data logic processing to EJB code or Model framework code, which are located centrally in the middle tier (fat). The database again only needs to serve simple queries and simple DML-statements (thin) initiated by the fat Model layer. In short, processing for V and C takes place in the Client tier and for M takes place in the Middle tier.

Fat²/Thin/Thin This is basically a Client/Server architecture moulded into the J2EE architecture. The application is again either Applet based or Java-in-OS based. The difference with the previous alternative is that all business and data logic is now also implemented within the rich GUI application running on the Client Tier (fat). So not only is this tier fat due to the rich GUI it offers, but also due to all the logic code it hosts: hence the "Fat-square" above[4]. The fat client application will either communicate directly with the Back Tier via JDBC, or go through the Middle Tier via a thinly configured Model framework, i.e. it only offers database connectivity and does no additional logic processing in this case (thin). The database again needs to serve simple queries and simple DML-statements (thin) initiated by the fat Client tier. In short all dimensions M, V and C are located in the Client tier.

Fat/Thin/Fat
This is alternative two where the M has moved from the Middle tier to the Data tier.
The GUI is rich (Applet or Java-in-OS). This can be considered a Client/Server architecture where all business and data logic is now taking place in the Data tier: the Middle tier (thin) only supports database connectivity from the Client to the Data tier. The database is now fully employed (fat) through the use of stored PL/SQL (functions, procedures, packages), triggers, complex views (updateable, possibly with Instead-of triggers). Also the complexity of data retrieval and manipulation is now dealt with by programming complex SQL queries and DML statements. Is short View and Control sit in the Client tier, and Model in the Data tier.

Thin/Fat/Fat This is again an html-based application (thin Client tier). All dimensions of MVC are available in the Middle tier (fat). Business and data logic processing takes place not only within the Model framework, but also within the Data tier (fat). The big challenge in this alternative is: how do we divide this logic processing? What part do we implement within the Model framework, and what part within the rich PL/SQL environment of the database? For this alternative to be manageable, we first need to establish a clear separation of concerns in this area.

Thin/Thin/Fat This is alternative one where the M has moved from the Middle tier to the Data tier. All business and data logic processing takes place inside the database (fat). The Model framework is deployed only for database connectivity: no additional logic code runs within this framework (thin). Compared to the previous alternative (five), the division of the business and data logic processing has been made 100% in favour of the Data tier. The database is now fully employed (fat) through the use of stored PL/SQL (functions, procedures, packages), triggers, complex views (updateable, possibly with Instead-of triggers). Also the complexity of data retrieval and manipulation is now dealt with by programming complex SQL queries and DML statements. Is short View and Control sit in the Middle tier, and Model in the Data tier. This alternative is actually the proposition of this paper (we will come back on this later).

Fat/Fat/Fat Business and data logic is distributed among all tiers: within the fat client, within the Model framework and/or EJB layer, and also within the database. The big challenge introduced in alternative five, is even bigger here. Managing application development within this architecture must be a complete nightmare.

Thin/Thin/Thin
For completeness this alternative is mentioned, but we do not consider this an architecture that is appropriate for application development, since there is no tier to hold any business and data logic code.

Code Execution Environments

As mentioned in the introduction of this paper we will establish code execution environments enabling us to talk about the where of code execution in a structured way. Within the context of the J2EE architecture alternatives introduced in above section, and looking from a viewpoint of code execution environments we can now distinguish the following:

SQL and PL/SQL execution within the Oracle RDBMS (Data tier)

Java code execution (Middle tier, in the web-container or the application-container)

JavaScript code execution (Client tier, inside browser)

Java code execution (Client tier, inside browser)

Java code execution (Client tier, inside OS)

The execution environments that are available to you depend of course on the J2EE alternative that you have chosen for your application. Since the most often chosen J2EE architectures involve a browser client displaying HTML, we will not consider the latter two environments in the remainder of this paper (from now on we assume a browser client displaying HTML).

Classifying Application Code

Before we can talk about where (what) code should go, in terms of the execution environments, we must also establish some classification of application code. Up until now we have used somewhat intuitive terms such as:

Data retrieval and manipulation code

User Interface code

Business logic code

Data logic code

In this section we will more precisely define what the meaning of above terminology is.

Data Retrieval And Manipulation Code

This is code that queries (retrieval) the persistent data store, or changes (manipulation) the persistent data store. All, and only, SQL Select- and DML-statements fall in this class.

User Interface Code (UI-Code)

This is code that creates User Interface (look), including code that responds to User Interface events and then modifies the User Interface (feel). Part of the creation and/or modification of such User Interface will require calls to business logic code (defined hereafter). Dependent upon the results of these calls the User Interface will be modified differently.

Examples:

In a Forms application: user presses Enter-query, enters query criteria, then presses Execute-query. UI-code (that is part of the Runform executable) will submit the query towards the RDBMS, display the rows returned inside the Forms block. Note the query itself is not part of the UI-code.

In a browser HTML application: user presses a tab-bar that is not yet current. UI-code will determine how the User Interface should be changed, and executes code that generates the new html to be displayed by the browser.

Business Logic Code (BL-Code)

This code is subdivided into two classes. Code that composes/executes transactions and code that composes/executes queries.

Query composing/executing code
This is procedural code surrounding data retrieval code. This code is responsible for composing the actual Select statements, or conditionally determining which Select statements should be executed. This code also initiates the execution of these statements and handles the rows returned by the RDBMS. We will refer to this class as read-BL-code (rBL-Code).
Examples:
- In a Forms application: user enters a customer-id in an item, post-validate-item trigger fires and executes a query to retrieve the customer name, customer address and debit amount.
- In a browser html application: user enters query criteria and presses Search button. rBL-Code communicates the criteria to the RDBMS and submits a query, using the criteria the RDBMS dynamically adds an appropriate where-clause to the query (Virtual Private Database feature) and then executes it.
- The definition of a database view can also be considered part of this code class.

Transaction composing/executing code
This is procedural code surrounding data manipulation code. This code is responsible for composing a (series of) DML statement(s), or conditionally determining which DML statements should be executed. This code also initiates the execution of these statements. Depending upon the return code(s) given by the RDBMS this code will also execute the commit or rollback processing for the transaction. Note that data retrieval will often be part of the transaction composition. We will refer to this class as write-BL-code (wBL-Code).
Examples:
- In a Forms application: user presses Commit after having changed various queried records. wBL-Code will generate and execute the required DML-statements to change the corresponding records in the database (this is performed by the Runform executable).
Or, the user presses some button inside a Form, which then fires a stored procedure inside the database that starts execution of a large (batch) transaction.
- In a browser html application: user has entered a new record, and presses a Save button. wBL-Code will create and execute the required DML-statement to insert the new record into a database table. In this case this will be performed by the model Framework (or EJB).

All code that is considered data logic code (see below), is not considered part of BL-Code.

Data Logic Code (DL-Code)

This is code that deals with the checking of data integrity rules (aka. business rules). It is responsible for the upholding of all assertions (rules) that are applicable to the database table contents. We consider this code to be in a distinct class by itself and not part of the above mentioned BL-code class. Whenever wBL-Code executes transactions, the data manipulation code that gets executed as part of such transaction can potentially violate data integrity rules. If this is the case, DL-Code will ensure that such a transaction fails and be forced to rollback. For the majority of the integrity rules, the DL-Code will need to execute data retrieval code to determine if a given data manipulation is allowed or not. [Kopp-2003] describes in much detail various issues and how-to's regarding the implementation of DL-Code. It also introduces the following sub classification of rules that can be applied one-on-one to the DL-Code that checks the rules:

Rules that are checked by inspecting a single column value (attribute rules).

Rules that are checked by inspecting more than one column value in a single record (tuple rules).

Rules that are checked by inspecting more than one record in a single table (table rules).

Rules that are checked by inspecting more than one table (database rules).

Rules than are checked by inspecting values prior to TX-start and values prior to TX-end (transaction rules).

Examples:

In a Forms application: user changes status of a record and presses commit, pre-update block trigger fires and executes DL-Code to check a Business Rule that involves this record status.

In a browser HTML application: user enters a record, presses Save, insert-statement is executed by wBL-Code, RDBMS fires pre-insert table trigger that executes DL-Code to check an involved Business Rule.

Of the four code classes introduced above only the last three are fully distinct from each other and together cover the full spectrum of application code. Data retrieval and manipulation code is embedded in (and thereby part of) either BL-Code or DL-Code.

Figure 2: Positioning the Different Code Classes

Figure 2 illustrates the way the different code classes interact with each other. Note that actual code of an application is never fully separated as visualized in this figure. For instance the UI-Code will hold embedded BL-Code or DL-Code . And wBL-Code will typically hold embedded DL-Code . These embedded code modules of a certain class inside code of a different class, can be considered the API-objects of that class. As we will see later on it is sensible to make a clear distinction of all these API code modules in a class versus the non-API code modules in that class.

Mapping Code Classes Into The Execution Environments

Now that we have established a classification for application code and execution environments that are available to us, we will proceed with the mapping of code into these environments. Given the context of this paper we will only investigate database-centric mappings (or slight deviations from this). Also since we assumed a context of a browser client displaying HTML we will not focus on UI-Code mappings: these are given within that context. UI-Code will be executed within the Java execution environment of the web-container using a View-Framework (JSP, or UIX) and Controller-Framework. For DL-Code and BL-Code we do still have options available in terms of mappings. We will investigate these alternatives first for each class separately and then merge them.

Mapping DL-Code

There are basically three options for mapping DL-Code into the execution environments: all goes into the Middle tier, all goes into the Data tier (ie. the database-centric approach), or using certain criteria all DL-Code is spread across both tiers.

Table 2: Alternative DL-Code Mappings

Model frameworks such as BC4J offer a rich set of possibilities to implement DL-Code for all business rules. As mentioned earlier the way you do this looks a lot like this was done in Forms using pre and post DML-triggers on base-table blocks. There is one big difference though: it's all Java code now, instead of PL/SQL. If you are familiar with PL/SQL why not opt to implement all DL-Code inside the RDBMS? You have probably been doing this for quite some time. Nowadays there are solid frameworks, such as RuleFrame or RuleGen , that aid in implementing DL-Code for all business rules inside the RDBMS. Either way, using a Java framework or a PL/SQL framework to implement DL-Code, you will end up with DL-Code that is clearly separated from any other code (BL and UI). During the lifecycle of the application this will turn out to be a big advantage in the area of cost for application code management and maintenance.

One could also opt to spread DL-Code across two frameworks. Again, if you choose to do so make sure you first set clear guidelines that tell your developers when to implement DL-Code in the Java framework, and when to do so in the PL/SQL framework. Examples of these guidelines could be: all DL-Code that can be dealt with in a declarative way using the RDBMS, is done in the RDBMS (e.g. primary/unique keys, foreign keys, check-clauses), all DL-Code that requires procedural logic and needs to be hand-coded is implemented in the Java Framework. Another strategy could be to implement all DL-Code that requires data retrieval (i.e. must submit Select statements to check the rule) with the PL/SQL framework, and to implement all other DL-Code (that does not require data retrieval, i.e. attribute and tuple rules) with the Java framework.

Like many subjects concerning J2EE, the subject of where to implement DL-Code tends to divert into a 'religious' discussion. The Object-Oriented/Java tribe won't consider the RDBMS as an option. The Relational/Plsql tribe can't see the benefits of taking DL-Code out of the RDBMS (authors' strong opinion too). The RDBMS is the 'final frontier' when it comes to data integrity. Once DL-Code has been implemented in the RDBMS the integrity of the data inside the tables has been secured no matter what future applications are developed against those tables.

Mapping BL-Code

This one is even more religious, since the methodology used for systems development plays a big role here. When it comes to BL-Code, there is a whole new wave of belief out there: "thou shall model thou business (logic) in an OO-way and UML is thou vehicle to do this". And by the way, don't bother about your data, we (UML) will derive top-down what needs to be stored persistently. It's beyond the goal of this paper to fully explore this new belief. The author has had encounters with a few of these new fundamentalists and so much has become clear: they refuse to spend time investigating any other approach. In short, you are either on their side of the hill or you are not. Their side seems to have momentum in the industry and therefore is appealing. However think twice before you decide to depart to that other 'greener' side of the hill. If you have been happy designing your applications using traditional ER-Diagrams, Process Flow Diagrams, Business Functions, and then down to tables and modules, by all means keep on doing so. Why change? From the looks of it, if you are going down (full-fledged) UML-lane, two things seem to be certain: 1) your BL-Code is the 'centre of the universe' instead of your persistent data, and 2) your BL-Code will not end up in PL/SQL. Your choice should be really simple now. If your application is a 'window-on-data' type of application (and frankly, which one is not?), a bottom-up traditional relational design approach is your way to go.

Table 3: Alternative BL-Code Mappings

Under the assumption that we are still on this side of the hill, we have the same three options for BL-Code mappings as we did have for DL-Code. Model frameworks such as BC4J, will aid in transaction and query composition as far as it concerns the composition of the data retrieval and manipulation code. All other procedural (hand coded) BL-Code will end up in Java classes outside (in front of) such framework. Triggered by events in the UI initiated by users, UI-Code will invoke these classes. No BL-Code sits in the RDBMS: only straightforward SQL is submitted in this case. Again here, if your background is Oracle why not stick to implementing BL-Code in PL/SQL? Two key technologies inside Oracle will greatly aid in moving all BL-Code into the RDBMS: for rBL-Code complex views in conjunction with Virtual Private Database feature is the magic bullet and for wBL-Code complex views with Instead-Of Triggers is.

Our Database Centric Approach

Our J2EE software factory has adopted the BC4J and UIX frameworks. Currently we deploy all J2EE applications with a custom built Controller framework. In the future however it is likely that we will adopt the Struts or ADF-Controller framework instead of this. All of our J2EE applications are deployed as HTML applications running in the browser. This section presents the database-centric approach in our J2EE software factory. The section hereafter will further detail that approach.

DL-Code

All business rules (data integrity rules) are implemented inside the RDBMS. The DL-Code that checks them sits in stored procedures, functions and packages that are part of wBL-Code. Only two types of rules are allowed to (also) be checked outside the RDBMS: attribute and tuple rules. Since these can be checked without having to execute additional data retrieval code, they are allowed to be checked by JavaScript (in the browser) too. Note that no DL-Code whatsoever is executed in the middle tier. Apart from the 'final frontier' argument mentioned earlier, the rationale behind this is that for DL-Code to validate a business rule (given a data manipulation statement), it either has to execute additional retrieval code, or it does not have to. In the former case the most efficient way to do this is from within the RDBMS. In the latter case the most user-friendly (responsive) way to do this is in the front end (JavaScript). Ergo nothing is left for the middle tier to execute when it comes to DL-Code.

BL-Code

All BL-Code is implemented inside the RDBMS. In the application code classification section we have discussed that BL-Code is called by the UI-Code layer. There are two high level strategies to accomplish this.

API from UI-Code to BL-Code is PL/SQL procedure calls.
In this case all BL-Code can sit inside the procedures that are being called by the UI-Code. The PL/SQL code inside these procedures then composes the transaction or query.

API from UI-Code to BL-Code is SQL-statements.
In this case we must create a layer of views to enable us to push down as much as possible BL-Code into the RDBMS. Data retrieval and manipulation takes place through these views only. wBL-Code can then sit inside Instead-Of insert/update/delete triggers attached to the views and rBL-Code is implemented by the view definitions themselves.

We chose SQL-statements to be the standard API from UI-Code to BL-Code. Main reason for this is that model frameworks (such as BC4J) use SQL-statements as the de-facto way to communicate with the Data tier. Procedure calls are supported too, but they are not the standard way to communicate with the Data tier.

The BL-Code calls that are embedded in the UI-Code is the only Business Logic that is left outside the RDBMS. In our case these are the actual SQL-statement texts, which represent the API-call to the BL-Code. It is prudent to also think about what type of SQL-statements are actually allowed to be embedded in the UI-Code. For instance do we allow queries that join two objects (table or view)? Or, do we allow function-calls inside the SELECT-list of the query? In our J2EE software factory we have set the following standards for this API:

Queries
- Are always based on a single view (FROM-clause): all additional logic, join, sub-query, etc., sits in the view definition
- Only hold bare view columns in the SELECT-clause: all additional logic, built-in/stored function call to format values, sits in the view definition
- Never have a WHERE-clause: Virtual Private Database (VPD) adds this dynamically
This gives us the following template for a query that is embedded in UI-Code:
SELECT <bare column>, <bare column>, .
FROM <view>;

DML-statements
- Are always based on a view
- Always manipulate one-row
This gives us the following templates for DML-statements that are embedded in UI-Code:
UPDATE <view> SET <column>=<value>, <column>=<value>, .
WHERE <primary key>=<value>;

DELETE FROM <view>
WHERE <primary key>=<value>;

INSERT INTO <view> VALUES(<value>, <value>, .);

Other than the calls to these API BL-Code objects, we do not allow BL-Code in the Java container or inside the Browser (Javascript).

UI-Code

As mentioned earlier UI-Code will be executed within the Java execution environment of the web-container using a View-Framework (for UI generation) and Controller-Framework (for UI event handling). All this code does is generate HTML for the browser (embedded with data retrieved from the API views). This code is kept as thin as possible: we even do not allow field level formatting within this UI-Code. This should be done within the API view that services the particular HTML page. In short, since no DL-Code nor BL-Code runs in the Java container, all that is left for the Java container to execute is this (thin) UI-Code.

Our Approach In More Detail

Generating Where-clauses at runtime has become possible in the database server with the introduction of the 'Virtual Private Database' feature . Instead of coding the Where+Order-by clauses in the BL-Code API statements embedded in the UI-Code, the database can generate these at runtime using the VPD-accompanying feature called Application Context. This context tells VPD in what context the application is and enables VPD to generate the necessary WHERE-clause. Both features are further discussed in the next section. UI-Code would have to transfer the application context to the database (using a stored procedure call that is part of the BL-Code API too), before performing the simple 'select * from <view>' (without any Where + Order-by clauses). For those HTML pages where users are allowed to change queried data (typically the smaller part of a web application), we must ensure that the API view can accept single-row updates: these are update statements with Where-clauses generated by BC4J when posting changes of cached rows to the database. They are always of the form "Where <pkcol(s)> = <pkval(s)>". This poses a problem when using database views that, due to their complexity, are not 'updateable'. Updates to complex views cannot be computed by the Oracle server and will generate a "ORA-01732: data manipulation operation not legal on this view". The solution for this problem is to create instead-of-update (database) triggers on these views: they fire instead of the update-statement. These triggers should perform all necessary Business Rules checking (DL-Code) and, when successful, execute the applicable DML on the views' underlying tables (wBL-Code). Figure 13 shows a page network. Application context is transferred from page to page as the user navigates through the application. Each page is based on an API view with aVPD policy attached to it and optionally an Instead-Of trigger.

Figure 3: Application Page Network With API Views

Virtual Private Database (VPD) / Application Context

The VPD feature, available since Oracle8i, enables us to have the server dynamically add a Where-clause 'predicate' to a SQL-statement. It is often referred to as the preferred mechanism to be used for implementing 'Row Level Security'. Row Level Security policies may dictate that certain users may only see certain rows in a table (or view). Instead of programming these where-clauses everywhere in application code, it is now possible to define such policy once centrally on the database table/view. The way this is done is by a) writing a Stored PL/SQL Function whose job it is to produce a (piece of) where-clause predicate, and b) telling Oracle that this (policy) function is to be attached to a specific table/view. Oracle will then automatically execute the function every time it is about to execute a SQL-statement that accesses the table/view. It will first add the return-value of the function to the where-clause of the SQL-statement, and then execute it. For instance, consider a row-level security policy that dictates employees may only access employee records within the same department, except for managers who are allowed to access all employee records. This would require the following policy function:

function Emp_Policy (p_schema in varchar2, p_object in varchar2) return varchar2 as
l_deptno number;
l_job varchar2(10);
begin select e.deptno, e.job into l_deptno, l_job
from emp e
where e.login_name = USER; -- Login_name-column holds database username
if job <> 'MANAGER'
then return 'DEPTNO = '||to_char(l_deptno);
else return '';
end if;
end;

Once this policy function is created in the DEMO schema, it can be attached (added) to the DEMO.EMP-table using the dbms_rls (row-level-security) supplied package, as follows:

exec dbms_rls.add_policy('DEMO', 'EMP', -- Object to add policy too
'EMP_POL', -- Name for this policy
'DEMO', 'EMP_POLICY', -- Policy function to add
'SELECT'); -- When to be enforced



The dbms_rls package is also used to remove a policy:

exec dbms_rls.drop_policy('DEMO', 'EMP', 'EMP_POL');



Policy functions can access system-variables such as USER, SYSDATE, etc, or functions like USERENV(), to dynamically determine what the predicate to be returned should be. To further enable a policy function to dynamically determine this return-value, one can use another feature called 'Application Context'. Application Context offers a generic way to register within the database session, knowledge on what the context within the (client side) application currently is. This is done by setting <attribute, value> pairs within a named context of the current database session. The <attribute, value> pairs are stored inside v$context, of which each database session has its own copy. Policy functions can read these attribute-values and use them to compute the correct predicate. A (named) context must first be created using the 'create context' command. Oracle offers 'secure' contexts by making sure <attribute, value> pairs can only be set within a context by a given stored package (which is supplied at 'create context'-time). Note that contexts exist at the database-level and not within a specific schema. To create a context the 'create context' system privilege is required. Here is the code that creates a context (specifies the package that can write the context), and the package source itself.

create context EMP_ APPLICATION using EMP_SEC;
package EMP_SEC is
procedure set_empno(p_empno in number);
end;
package body EMP_SEC is
procedure set_empno(p_empno in number) is
begin dbms_session.set_context('EMP_ APPLICATION', 'CURRENT_EMPNO', to_char(p_empno));
end;
end;

A client application can now register a 'current employee' in the context by calling the EMP_SEC.set_empno packaged procedure. Policy functions can read context contents in two ways: by directly selecting from v$context, or by calling the sys_context PL/SQL built-in function.

select value into l_current_empno
from v$context
where namespace = 'EMP_APPLICATION' and attribute = 'CURRENT_EMPNO';

Or,

l_current_empno := sys_context ('EMP_APPLICATION', 'CURRENT_EMPNO');

VPD and Application Context made it very simple for our Java programmers to retrieve the data that had to be shown on a certain UIX page: first they call a 'set-context' procedure telling the database what the current application context is, and next, they perform a full access of a BC4J View object (which would translate into a 'select * from <view>' statement). The policy function then makes sure only the necessary rows are returned. See figure 14 below depicting these steps taking place after one another.

Figure 4: VPD and Application Context at work

A separate (PL/SQL) team would develop the (page) API views, context procedures and policy functions, required by our 'Java-boys'. Some of the coding standards we issued were:

Page views should return no rows when the policy function detects that either the context is not available (empty), or holds incorrect values. In these cases a policy function should always return a false predicate (e.g. WHERE 0=1).

All columns of page views should be of type varchar2, and every value should already be formatted in the way it should be displayed: no additional formatting was allowed on the Middle Tier. UIX-page bind variables would always be of type String.

When pages would feature bubble-text on certain view column values (e.g. explaining a coded value), then these too were to be supplied by additional columns in the view.

Page views only return columns that are to be displayed, with two exceptions: the case of a multi-row page where a row can be selected by the user and a (hidden) primary key must be communicated to the context of the next page. And the case of a page view that is to support updates and/or deletes: these views need an explicit primary key defined within the BC4J layer and available at runtime. In both cases such primary key columns are then part of the page view, but possibly not shown on the UIX page (similar to hidden items in Oracle*Forms).

(goes without saying) Every where-clause returned should be checked for optimal query-execution.

Page views can be classified into two types: those returning a single row, and those returning multiple rows. The UIX templates we used for multi-row pages featured 'clickable' column headers. A user could click on a column header to indicate that the result set should be ordered on that column. Using VPD, the optimizer can be tricked such that the rows are returned in a certain (requested) order. Consider the Emp_Policy function again: it now retrieves a column-name from the EMP_APPLICATION context (attribute 'ORDERCOL') which has been set by client-code before issuing the query on EMP, indicating the column on which the rows should be sorted. The function now returns a sub-query predicate that already orders the empno-values (we need a double sub-query for this).

function Emp_Policy(p_schema in varchar2, p_object in varchar2)
return varchar2 as
l_ordercol varchar2(10) := sys_context ('EMP_APPLICATION', 'ORDERCOL');
l_empno number := to_number (sys_context ('EMP_APPLICATION', 'CURRENT_EMPNO'));
l_deptno number;
l_job varchar2(10);
begin select e.deptno, e.job into l_deptno, l_job
from emp e
where e.empno = l_empno;
if job <> 'MANAGER'
then return
'empno in (select te.empno'||
' from (select empno'||
' from emp'||
' where deptno = '||to_char(l_deptno)||
' order by '||l_ordercol||') te'||
' )';
else return '';
end if;
end;

A 'select * from emp' including the above generated where clause will be optimized as follows (the 'in <subquery>' translates to a nested-loop join):

>.SELECT STATEMENT
>...NESTED LOOPS -- does not disturb row order
>.....VIEW
>.......SORT order by -- will sort on requested column
>.........TABLE ACCESS by rowid emp -- retrieves order-by column value
>...........INDEX range scan emp_fk -- Uses l_deptno
>.....TABLE ACCESS by rowid emp
>.......INDEX unique scan emp_pk

The resulting rows from a nested-loop join will always be ordered by the row-order of its first row-source. In the case above this is the order generated by the sub-sub-query. All the second row-source does, is execute the IN-operator: it sequentially retrieves single EMP-rows, given the ordered empno 'stream' it receives from the sub-query.

Instead Of Triggers On Views

With the introduction of Oracle8i, it has become possible to also create 'instead of' insert/update/delete triggers in the database, next to the already available before/after, statement/row triggers. Instead-of triggers will fire instead of the DML statement issued against the table. They will fire 'for each row' affected. Contrary to the already available trigger types, instead-of triggers can also be created on views. This greatly enhances the possibilities to use views as the interface of choice for client-side application code communicating with the database server. These type of views, views that retrieve data ready-for-display in the front end, typically join data from various tables, maybe also aggregate some data, return virtual columns, etc., and very likely will have become non-updateable (ie. generate ORA-01732 error when updating) due to their query-complexity. Instead-of triggers solve this problem, and now make it possible to also use these views for transacting with the database (we already used them in Client/Server applications for retrieving data ready-for-display in Forms). When the view is DML'led the instead-of trigger fires and should first perform all necessary Business Rules (Data Logic) checking and only when successful, execute the applicable DML on the views' underlying tables. As mentioned earlier, BC4J will generate update-statements of the form:

Update <view> set <col>=<value>, .
Where <pkcol> = <pkval>

It is therefore essential that when a page view is developed for a UIX page that supports updates by the user, this view is explicitly checked to optimally execute its query given the where-clause BC4J adds (and not only given the where-clauses added by the VPD policy, as mentioned earlier). To prevent mixing of these (BC4J) where-clauses with our own policy where-clauses, we standardized that VPD only adds a where-clause when SELECT-ing from page-views. It was up-to the instead-of trigger code (before even starting with the execution of DL-Code or wBL-Code), to always first check the row-level-security: is, given the application context, updating this row at all allowed or not? Figure 15 depicts the steps taking place when BC4J updates a view with an instead-of trigger: 1) BC4J generates update-statement. 2) View will retrieve affected rows (perform a select, but not update). 3) Update responsibility is handed over to instead-of-trigger. Trigger is supplied with retrieved record(-variables). 4) Trigger will check row-level-security and/or other application-context dependant issues. 5) Trigger determines which tables to update, and does so.

Figure 5: Instead of Trigger at work

There is one issue that you need to be aware of when adopting Instead-Of triggers to update complex views. This concerns the row-locking strategy. Model frameworks will try to acquire a row-lock before performing the (PK-based) DML statement. Acquiring the row-lock is done via a 'select . for update' query. API-views are usually such complex that without an Instead-Of trigger they would not be updateable. This implies however that they cannot accept a 'select . for update' query either. Oracle will generate a "ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc." error when attempting so. The solution to this issue is to configure the Model framework such that it won't try to acquire the row-lock. BC4J supports three locking strategies: pessimistic (lock when row is touched in the cache, just like Oracle*Forms does), optimistic (lock just before posting the DML), or None (do not acquire row-locks). In our situation we use the 'none' strategy. This implies that the Instead-Of trigger now has the responsibility to acquire the row-locks to prevent waiter/blocker situations.

A nice enhancement request here would be for Oracle to implement a Instead-Of Select-for-Update trigger in the RDBMS too.

Lessons Learned

Here are some of the lessons we have learned during the first few J2EE projects that we have done, using the database-centric approach that we have described.

Do Not Trust Java Geeks

In our database-centric approach we explicitly create API views in conjunction with set-context stored procedures, that are to be used by the UI-Code for executing the data retrieval and manipulation (including BL+DL Code as described earlier). Once you have set these guidelines in terms of what BL-Code API objects are to be used from within the UI-Code (Java View+Control frameworks), make sure that the Java programmers cannot access any other database objects than these ones. For if you supply them with a database connection that gives them access to all other BL-Code objects, or all underlying tables of the API-views, you can be sure of one thing, they will start using those too. Part of our approach now is that we create a separate database schema that holds the BL-Code API objects only. The Java Model framework connects to this schema, and cannot see or access any other application objects inside the database (they sit in a different schema). We create separate development teams too: one team that develops the View+Control (UI-Code) for the application, and one team that develops the Model (BL+DL Code) for the application. The first team uses JDeveloper. The second team still uses the 'old' Designer product. For each page of the application these two teams are forced to sit together first and discuss the application context dimensions for that page, and the semantics of the database view that services the data retrieval and/or manipulation for that page. Once this has been agreed upon both teams can start development for that page independent from each other.

VPD: Hard Parses Versus Soft Parses

Consider a 'display-book-details' page of an Order-Book application. The application context of this page consists of only one attribute, say Book_Id. The API database view selects all columns of the Books-table. The VPD policy function is supposed to add a where clause such as 'WHERE Book_Id = <current book_id of the application context>. The following policy function would to this (assuming a BOOK_DETAILS context with a BOOK_ID attribute that has been set by the UI-Code):

function Book_Details_Policy(p_schema in varchar2, p_object in varchar2)
return varchar2 as
l_book_id number;
begin

l_book_id = sys_context('BOOK_DETAILS', 'BOOK_ID');

return 'Book_Id = '||to_char(l_book_id);
end;

This would however cause a hard parse for every different book_id submitted, since the book_id is embedded as a literal inside the where-clause that is generated. To prevent this the policy function should be rewritten as follows:

function Book_Details_Policy (p_schema in varchar2, p_object in varchar2)
return varchar2 as
begin

return 'Book_Id = sys_context (''BOOK_DETAILS'', ''BOOK_ID'')'; -- Double quotes.
end;

In this case the where-clause will be treated as if it were of the form 'where book_id = :b1'. The sys_context call will behave as if it were a bind-variable and is bound at execute time of the query. This will now cause soft parses for different subsequent book_ids that are submitted and therefore consume significantly less cpu-resources.

VPD: Policy Function Fires Twice

Some of our policy functions are quite complex, due to the nature of the API view and the required where-clause semantics. To give you an idea here is a pattern we often use inside a policy function:

Read all attribute values of the current application context namespace.

Truncate a global temporary table.

Execute lots of procedural PL/SQL code using above values to compute a set of primary-key values.

Store these primary key values inside the global temporary table.

Return the following where clause:
where <pk-columns of API-view> in
(select <columns>
from <temporary table>)

Filling the temporary table is CPU-intensive in this case. Once it is done, fetching the rows from the view (by UI-Code) requires little CPU. This worked fine for us in Oracle8i. However with the introduction of 9i Oracle has changed the execution model of VPD policy functions. A policy function now fires twice for every query that is submitted: once during the parse-phase (irrespective of whether this is a hard or a soft parse), and once during the execute-phase. Given the CPU-intensive policy function this has meant for us that the migration to 9i has caused a near doubling of the CPU-usage for these pages. We have opened a TAR for this on Metalink but to no avail: this is documented behavior (in fact Oracle states that the only once firing of the policy function in 8i was a bug). We are in the process of rewriting these CPU intensive policy functions so that they detect whether the contents of the temporary table are already OK, and if so do not execute the procedural PL/SQL code a second time.

Sorting Via VPD: A Bridge Too Far

The double sub-query trick we explained earlier to mandate a certain order in the rows retrieved by the API view, works fine as long as the view involved is simple. Getting this trick to work for more complex views, turned out to be rather difficult. Currently we do not use this trick anymore. Using BC4J the order-by clause is added to the 'select * from <API view>' query. This can easily be coded as part of the embedded BL-Code call inside the UI-Code as follows:

ViewObject vo = am.findViewObject("VBOOKHITS");

Vo.setorderbyclause('author');

RowSet rowSet = vo.getRowSet();

Row r = null;
while (rowSet.hasNext())

Instead of sending a 'select * from <view>' statement to the RDBMS, this code will send a 'select * from <view> order by author' query to the RDBMS. VPD will still insert the necessary where clause.

Conclusion

This paper assumes you are an Oracle PL/SQL shop. If you are, then coding all Data Logic and Business Logic Code in PL/SQL will enable you to considerably reduce the risk in your (first) J2EE project. We are not saying that it will be easy though. On the contrary, there is a whole new world for you to discover and adopt. Approaching this in a database-centric way will enable you to not make it more complex than it needs to be.

For us this approach has meant that only few developers needed to learn the new IDE including all the technology aspects surrounding it. Since the new Java IDE is only used to build UI-code, we were able to specify a finite number of UI-patterns (page types) that enable us to build almost any browser application. Each pattern is supported by a super-class that already implements the bulk of the job necessary for that pattern. In this way we even further simplified the work that had to be done in the new Java IDE. Right now the majority of time spent on a J2EE project within our company involves the good old PL/SQL and SQL languages. This has benefits in many areas. For instance scalability and tuning issues do not play at the OC4J level, but at the well-known RDBMS level: we do not know how to perform tuning of memory, CPU-usage and contention in an OC4J instance. In our database centric approach the container has hardly any work to do.

The database-centric approach presented in this paper will also work for application development that does not involve J2EE. Given the architecture of the DL+BL-Code including the API-layer that we have defined, we can fairly easy change to other client-tools (ask yourself: is J2EE here to stay?). All such client development tool has to support, is being able to call a stored procedure (to set context) prior to doing a select (from API view). And for the data manipulation part, all the tool has to support, is generate PK-based DML-statements.

Ask yourself: is PL/SQL here to stay?

Bibliography

[Kopp-2003] Business Rules: Theory and Implementation (What and How). Proceedings of the Business Rules Symposium ODTUG-2003, Miami (also available at https://web.inter.nl.net/users/T.Koppelaars).