Don't constrain yourself: displaying alternative database constraint error messages with ADF BC

An existing SQL database such as Oracle will have a full range of table constraints, including primary keys, unique keys, foreign keys, not nulls and check constraints. JDeveloper's ADF Business Components (ADF BC) provides a number of different mechanisms for validating data before it hits the database. However does ADF BC provide any mechanisms for overriding the database constraint error messages if they do occur, supplying an alternative "user friendly" error message?

The following post was raised on request for a client to explain the ADF BC facility provided. The discussion assumes you are familiar with Oracle's default schema "HR" and its associated table employees, and the constraints implemented in the employees table:

CREATE TABLE employees
(employee_id NUMBER(6,0)
,first_name VARCHAR2(20)
,last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL
,email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL
,phone_number VARCHAR2(20)
,hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
,job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL
,salary NUMBER(8,2)
,commission_pct NUMBER(2,2)
,manager_id NUMBER(6,0)
,department_id NUMBER(4,0)
,CONSTRAINT emp_salary_min CHECK (salary > 0)
,CONSTRAINT emp_email_uk UNIQUE (email)
,CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)
,CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
,CONSTRAINT emp_job_fk FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
,CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
REFERENCES employees (employee_id));

Overriding the database constraint error messages

The Oracle database isn't known for displaying particularly useful or friendly error messages when a table constraint is violated. For example if we create a new HR.employees record with an unknown DepartmentId value, the database throws the following error:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

This message is not particular useful to the average user as they wont know what EMP_DEPT_FK is, what an integrity constraint is, or why the parent key is not found.

Via ADF BC can we provide a more useful message?

Section "25.8.3 How to Customize Error Messages for Database Constraint Violations" of the "Application Development Framework Developer's Guide for Forms/4GL Developers" manual outlines that a standard ADF BC project can be extended with a custom message bundle to customise the database's constraint error messages when caught by the midtier. Note to implement this, as noted in section 25.8.3, you must follow steps 1 to 5 of section 25.8.1. Take care to follow the special Note in step 3 if you're using JDev 10.1.3+, though it appears unnecessary in JDev 11g.

Section 25.8.3 shows us how to create a custom message bundle class for our ADF BC project, which is comprised of key-value message pairs, where we can list our constraint names and the custom message we wish to show respectively.

If we consider the HR.employees tables named constraints, of which there are 4 not null constraints, 1 uk, 1 pk, and 3 fk constraints, we could create the following custom message bundle class to cover off all the constraints:

public class MessageBundle1 extends ListResourceBundle {

private static final Object[][] sMessageStrings = new String[][] {
{ "EMP_LAST_NAME_NN" ,"A not null error message0" }
,{ "EMP_EMAIL_NN" ,"A not null error message1" }
,{ "EMP_HIRE_DATE_NN" ,"A not null error message2" }
,{ "EMP_JOB_NN" ,"A not null error message3" }
,{ "EMP_SALARY_MIN" ,"A check error message4" }
,{ "EMP_EMAIL_UK" ,"A unique key error message5" }
,{ "EMP_EMP_ID_PK" ,"A primary key error message6" }
,{ "EMP_DEPT_FK" ,"A foreign key error message7" }
,{ "EMP_JOB_FK" ,"A foreign key error message8" }
,{ "EMP_MANAGER_FK" ,"A foreign key error message9" }};

protected Object[][] getContents() {
return sMessageStrings;
}
}

Some caveats to be aware of:

The constraint names in the message bundle class must be in uppercase to match the case-specific constraint name raised by the database.

The HR schema has named the not null constraints within its tables explicitly. In your schema if you choose not to name the not null constraints, Oracle will generate a constraint name of the format SYS_C00000n where n is a counter incremented for each auto named constraint created by the database. It's fine to refer to these constraint names instead in your custom message bundle. However be aware if you recreate your schema and Oracle chooses to name the constraints starting at a different n value, obviously the ADF BC custom message bundle approach above will not work.

If you've setup your ADF BC Entity Object (EO) to have the same primary key as the table, the EO includes a mechanism for validating primary key values within the ADF BC mid-tier before it gets to the database. Because of this the alternative error message above may or may not be shown.

The EO pk routine checks any created/updated pk value against all pk values for the existing EO that have been cached in the midtier at submit time. If it finds a matching value (or in other words that the values aren't unique) it throws the following JBO (JDeveloper) exception where X is the primary key value(s):

JBO-25013: Too many objects match the primary key oracle.jbo.Key[X ].

In this case you will not see your message, but the JBO error above.

EOs and their associated View Objects (VOs) are by default configured to lazy fetch records from the database into the midtier in ranges (record sets of say 10 records) based on user requests. This lazy loading feature is designed to make it unnecessary to load all the records from a table into the midtier for speed and memory efficiencies while taking advantage of the fact the user probably doesn't want to view all 1000 employee records at once, but will be happy viewing a sub set at a time.

As noted above, if the record has been fetched into the midtier's ADF BC memory structures, the JBO-25013 error will display (without our custom message) when a new record's pk violates the pk constraint at submit time. However if the record with the matching pk value hasn't yet been fetched into the midtier, the check will be left to the database and is done at commit time. In this case the database will raise the error message, and our custom message bundle message will also be shown.

To my knowledge JDeveloper 10.1.3+ has no facility with the EOs to validate uk values similar to the pk check above. The JDeveloper 11g Technical Preview release introduces a new uk mechanism as separate to the pk facility and should be considered.

Beating the database constraints at their own game in ADF BC

In my opinion however, good user interface (UI) design dictates that it would be better to stop the database errors occurring in the first place to provide a relatively more satisfying experience for the user. How do you do this? Well you stop the user from ever entering data that violates the constraints in the first place.

The first set of suggestions here are obvious but need to be stated:

Using and generating surrogate read-only primary keys - if you're allowing the user to create or edit records, and the table has a surrogate primary key generated via a sequence in the database that guarantees uniqueness, make the field read-only (or hidden). Then within the ADF BC EO for the associated table, populate the pk programmatically on creating a new record via the sequence. In this fashion the user can never enter a non unique pk for the primary key value.

Use poplists and list of values (LOVs) for foreign keys - if the user in creating or editing a record needs to enter a foreign key value, don't allow them to manually type potentially invalid foreign key values. Instead provide poplists and LOVs for the user to select a valid value from.

Make mandatory database fields mandatory in ADF BC objects and the UI components - the ADF BC EO and VO attributes have a property Mandatory that will enforce the user entering a value before it hits the database. In turn the UI technologies usually provide facilities to check this too. For instance the 10.1.3 ADF Faces component has a required boolean attribute, that shows a star next to the item at runtime indicating it is mandatory, and fires Javascript to check for a value and raises an error otherwise at runtime. (Admittedly this is done by default, but some users may miss this point)

Beyond the obvious ideas, JDeveloper also provides a number of validators at the EO level that enforce data integrity checks and supply custom error messages if a problem is found before they hit the database. For the brevity of this post they will not be considered here. I note that Jan Kettenis under the recent post UML Rules is currently writing a whitepaper on implementing business rules in ADF BC which hopefully will cover the majority of the validators and a useful source of information beyond Section "6.7 Using Declarative Validation Rules" in Oracle's developer's guide.