Database controls extend the BEA WebLogic controls architecture to enable developers to easily interact with relational databases with maximum productivity. Utilizing a database control, a developer can quickly create methods to issue commands and retrieve data as required. Database controls can often be a more productive choice over other technology alternatives such as Enterprise JavaBeans (EJB).

While database controls were introduced in BEA WebLogic Workshop 7.0, they were not overly popular until the introduction of the 8.1 version of the WebLogic Platform and the dramatic increase in WebLogic Workshop functionality in the 8.1 release. Developers now approaching these controls for the first time can be forgiven for thinking that the controls are so simple that no best practices are required. In fact, the very simplicity of the control is what can often cause maintenance problems down the road as the database control is utilized in less than optimum ways.

This article will discuss a set of best practices pertaining to the use of the database controls. I assume that readers are already somewhat familiar with database controls and their usage; developers new to these controls are encouraged to review the help section in WebLogic Workshop, which contains an excellent overview.

Rather than complete a laundry list of do's and don'ts, we have opted to examine best practices in the context of a specific use case. In this hypothetical scenario we are a developer for a company called "Music Search" that runs a WebLogic portal that connects music industry employees with up-and-coming musical acts. As a developer for this site, we have been tasked to create a portlet that enables the site administrators to poll the site's users as a mechanism to gather information. The functionality consists of displaying a single question with multiple answers in the portlet. The user's response to the question is recorded; once answered, the percentage of responses for each answer is displayed to the user.

The database schema for the functionality required by the portlet will consist of three tables (see Figure 1).

The three tables are Question, Answer, and Response. The Question table is used to store an individual question while the Answer table is used to store the available choices for a given question. Finally, the Response table is used to store a user's answer to the displayed question. While linking the response table to the question table through the QUESTION_ID foreign key is not strictly necessary, it has been done here as a convenience for reporting.

Our first order of business is to create the logic required to add, update, and delete questions, answers, and responses. To accomplish this, we will create three classes to represent the domain model. The business logic will manage the relationship between the Question and Answer classes; however, the Response class will only be used for inserting new responses, as the users will utilize a reporting tool for analyzing polls.

A simplified UML diagram of our three domain classes is shown in Figure 2. Note that the Answer class is aggregated by the Question class and is only accessible through an instance of the Question class.

When To Use
One of the most important considerations of database controls is knowing when to use them within a given application. Database controls provide many of the same services that developers might alternatively use session and entity EJBs to encapsulate. Thus a common question would be, "when is it appropriate to use a database control versus other solutions?"

Database controls in their current incarnation are intended to enable developers to write code that interacts with the database in a highly productive manner. While the increased productivity of database controls is a boon, developers should understand that they provide considerably less flexibility than an equivalent EJB. They are particularly well suited for supporting smaller applications and discrete pieces of functionality where the large feature set and complexity of EJBs is not required. In these cases the increased productivity of the database control is the compelling argument for their usage.

However, there are certainly cases where the power and flexibility of EJB is needed and database controls should not be used. Specifically, database controls may be less than optimal in applications whose components require complex transaction management or declarative transactions. These applications should strongly consider using an EJB architecture instead of database controls as database controls provide limited flexibility with respect to transaction management.

Second, with respect to object persistence, larger applications that feature a highly complex domain model with many relationships between data elements and are evaluating database controls for CRUD operations should consider choosing a more robust and flexible persistence mechanism such as entity beans, Hibernate, or Toplink. This is because the database control provides no mechanism for transparently managing relationships between objects and developers can quickly find themselves overwhelmed in writing this glue code in complex situations.

Best Practice: Carefully consider the requirements for your particular needs prior to choosing a technological architecture; database controls are not a panacea for every situation.

Database Control Encapsulation
Our first task is to decide on the organization of code for our polling portlet. It is here that many developers using the database control make their first mistake - a mistake they sometimes do not realize until much later in the development cycle.

The problem arises from the fact that unlike many of the controls in WebLogic Workshop, a database control is a Java interface, not a class. When the control is compiled, the WebLogic Workshop framework creates an implementing class based on the attributes and methods defined by the developer in the database control interface. The sample method below retrieves an instance of the Question class based on an identifier.

    /**
     * @jc:sql statement::
     *     SELECT QUESTION_ID ID,TEXT
     *     FROM QUESTION
     *     WHERE QUESTION_ID={questionId}
     * ::
     */
    Question getQuestion(long questionId) throws SQLException;

Often a developer who is new to database controls will initially start with a simple control that performs basic CRUD operations. The developer will then start using this database control in page flows and processes as needed. At some point though, the developer will realize that he needs to perform a complex operation that cannot be encapsulated in a single method call. This is a problem we will also face in our polling portlet in many instances, for example deleting a question also requires us to delete all of the answers and responses.

Since the database control is defined as an interface, there is no opportunity for the developer to add custom code to the database control itself. Now the developer is forced to add two or more methods and rely on the user of the database control to call those methods in the correct order. For example, to delete a question we would need to have three database control methods, one to delete the question, one to delete the question's answers, and one to delete the question's responses.

When the presentation layer has to delete a question, it is clearly undesirable to force the presentation to call these three methods individually in the correct order. Further complicating the issue is that the presentation layer would have to call these methods within the context of a transaction in order to ensure the deletion is rolled back should the second or third method calls on the database control fail.

A far better approach is to encapsulate the database control within a Java custom control. In this way we never expose the database control directly to consumers such as page flows and processes; they will use the Java custom control instead. Thus these consumers are completely oblivious to the fact that database controls are being used as the implementation mechanism; all they perceive is the encapsulating Java custom control.

This approach has two primary benefits:

    /**
     * @jc:sql statement::
     *     SELECT ANSWER_ID ID, QUESTION_ID QUESTIONID, TEXT
     *     FROM ANSWER
     *     WHERE ANSWER_ID={questionId}
     * ::
     */
    Answer[] getAnswers(long questionId) throws SQLException;

public class Answer
{
    public long id = -1;
    public String text;
}

public class Answer
{
    private long id = -1;
    private String text;

    public long getId()
    {
        return id;
    }

    public void setId(long value)
    {
        this.id = value;
    }

    public String getText()
    {
        return text;
    }

    public void setText(String value)
    {
        text = value;
    }
}

    /**
     * @jc:sql statement::
     *     SELECT QUESTION_ID ID,TEXT
     *     FROM QUESTION
     *     WHERE QUESTION_ID={questionId}
     * ::
     */
    Question getQuestion(long questionId) throws SQLException;