Business Components Without the Business: Part I

Last week I talked about where business logic should go in an enterprise application, and voiced my firm opinion that sometimes–but not always–it belongs in the database. But if you’re using Oracle ADF, there’s a tricky point about this: ADF does not handle trigger-raised database errors especially well, so has no out-of-the-box way of nicely integrating with database validation. Even if you’re not using ADF (and, in particular, ADF BC), knowing what sort of thing is involved in getting database error messages into a user-friendly form may prove useful.

This is an independent issue of how to handle other database-implemented business rules, such as database-defaulted or database-calculated attributes; to deal with those, you need to understand the Refresh After settings in entity object definitions. I’m not going to cover those here, because they’re quite adequately covered in a number of places, including the ADF Developers’ Guide for Forms/4GL Developers.

Trigger validation, on the other hand, is a different story. The closest I’ve seen to a decent treatment of this is in AMIS Technology’s blog. But it requires JHeadstart, and even then, I do not think it satisfies all the desiderata for user-friendly handling of raised application errors. Here are those desiderata:

  • An error message should display only information that the user may find helpful.
  • In particular, an error message should never display information about the structure of the database, such as schema, table, or column names.
  • If an error message is related to a particular UI control, the error message should link to the control, and the control should be highlighted in a manner that makes it clear there is a problem.

An approach like AMIS’ handles the first two of these requirements very nicely–unlike ADF’s default behavior, which is to publish the entire database error message in all its “ERROR DURING INSERT…” glory. But that third is important too–people expect attribute-specific errors to be flagged on the control, so they don’t have to hunt through the entire form to fix them. We want an error-handling system that will satisfy all three requirements.

Flagging Error Messages with Column Names

The first step in this process is to make sure that, in the case of column-specific messages, ADF gets enough information back from the database to pick out an appropriate UI control. You can accomplish this by putting the problematic column (from which we can figure out the problematic control) into the error you raise. I suggest taking a single character that will never be used in your error messages propper (for my example code, I’ll go with ‘]’), and using it to set off column names where appropriate. E.g.:

HIRE_DATE]Employees cannot have a hire date earlier than Jan. 1, 1985

For error messages that don’t apply to a specific column (or apply to more than one), just leave out the column designation:

Non-sales staff cannot have a commission percent.

You could get very fancy and write code that would allow you to cite multiple columns (e.g., in a comma-separated list) and link them up nicely, but for simplicity here we’ll keep to the level of support ADF provides for middle-tier error messages.

Catching Database Errors

Individual entity object instances are added to the database by ADF’s calling their doDML() methods. If there is a problem (such as a trigger raising a database error), this method throws an instance of oracle.jbo.DMLException that wraps the SQLException that, in turn, wraps the entire message returned by the database. We want to catch this exception, extract the database error message, and throw an excpetion that will be more useful in terms of constructing a message to display to the user. That means we want to override oracle.jbo.server.EntityImpl.doDML(), preferably in a custom framework class. And ta-da:

protected void doDML(int operation, TransactionEvent event) {
    try {
        super.doDML(operation, event);
    } catch (DMLException e) {
        Throwable[] baseExceptions = e.getExceptions();
        if (baseExceptions.length > 0) {
            throw new BundledDBValException(baseExceptions, getEntityDef().getSource());
        } else {
            throw e;
        }
    }
}

So, we’re checking to make sure that this DMLException actually does wrap at least one other exception, and if it does, we’re throwing a BundledDBValException.

What’s a BundledDBValException?

A BundledDBValException is an exception we’re going to write. It’s going to process SQLException instances, convert them into exceptions we can use to construct nice error messages, and bundle them up into a neat package for throwing.

package...
import...
public class BundledDBValException extends JboException {
    private static final Pattern VAL_MESSAGE_PATTERN =
        Pattern.compile("ORA-20.{11}([^]]*)]?(.*)$",
                        Pattern.CASE_INSENSITIVE);
    private final String tableName;
    public BundledDBValException(Throwable[] baseExceptions, String tableName) {
        super("");
        this.tableName = tableName;
        for (Throwable baseException : baseExceptions) {
            if (baseException instanceof SQLException) {
                processSQLException((SQLException)baseException);
            } else {
                addToExceptions(baseException);
            }
        }
    }
    private void processSQLException(SQLException ex) {
        Matcher valMessageMatcher = VAL_MESSAGE_PATTERN.matcher(ex.getMessage());
        if (valMessageMatcher.find()) {
            String firstGroup = valMessageMatcher.group(1);
            String secondGroup = valMessageMatcher.group(2);
            if (secondGroup == null || secondGroup.length() == 0) {      // No ] found
                addToExceptions(new DBValException(null, tableName, firstGroup);
            } else {                                                   // ] found
                addToExceptions(new DBValException(firstGroup, tableName, secondGroup);
            }
        } else {                                                       // Not a trigger-raised error
            addToExceptions(ex);
        }
    }
}

So, for each exception in the DMLException‘s details, we do one of the following:

  • If the exception does not contain an ORA-20% error, then it’s not due to a custom error that was raised by a trigger. You could handle these any number of ways, such as by logging the exception details and instructing the user to contact support, but for now, we’re just going to add these errors to this exception’s details as-is.
  • If the exception contains an ORA-20% error with a ‘]’ character in it, then we separate out the column name that comes before the ‘]’ character and the message itself that comes after the ‘]’ character, and use them to build a custom exception–I’ll talk about that custom exception in the next section. Then, we add the custom exception to this exception’s details.
  • If the exception contains an ORA-20% error that doesn’t have a ‘]’ character in it, then it’s a non-column-specific exception. We create the same custom exception as above, using null for the column name, and add it to this exception’s details.

OK, and DBValException?

DBValException is a single exception that holds details of the table, column, and/or error message relevant to the trigger exception message. Here it is:

package ...
import ...
public class DBValException extends JboException {
    private final String tableName;
    private final String columnName;
    public DBValException(String columnName, String tableName, String errorMessage) {
        super(errorMessage);
        this.columnName = columnName;
        this.tableName = tableName;
    }
    public String getTableName() {
        return tableName;
    }
    public String getColumnName() {
        return columnName;
    }
}

Not much to that, eh?

That’s all the stuff that you need to do in your model layer. Now all you need is a custom error handler in your controller that can convert these exceptions to the FacesMessage and LabeledFacesMessage instances needed to display the errors helpfully. I’ll talk about that next week.