Dynamically Calculating Primary Keys

Oracles’ Application Development Framework business Components (ADF BC ) allows for the implementation of business rules for adding default values to entity attributes, but what type of rules can we implement for the case of a sequence generated primary key?
It is standard practice within current industry practice to define a primary key column on all object relational database tables, which in most cases is a unique number (as opposed to a unique string of characters). This unique number can be generated by a database sequence number generator or by calculating the next available value based on what is currently realized on that column. The latter presents obvious transactional problems whereas the previous is the standard way of handling unique number-based primary keys. This presents an interesting challenge when implementing business logic, which involves inserting new record/records into a database. If we create a new Row, populate the various attributes (except for the primary key) and attempt to save the transaction we will most assuredly receive a database error, as a primary key is always unique and non- null. To prevent this type of error we can implement this logic in one of two ways:
• java code (dynamically calculated default values),
• or we can use ADF BC provided class, oracle.job.Domain.DBSequence type in tandem with a database trigger
There are many approaches to dynamically calculating the sequence value with java code. I will describe two approaches, one is how I implemented the solution on one project and the second is a solution recommended by Oracle and is which I currently practice.
The java based solution I implemented, involved creating various private methods on the ApplicationModuleImpl: getNextSequence() – which called a pl/sql stored procedure to return the next available sequence, and createRecord() – which creates a new row, populates the various attributes (including a call to getNextSequence() for the primary key) and inserts the row back into the ViewObject, which ultimately inserts into the database (for the current transaction upon post changes) and in the database once commit is called.
The Oracle solution is far more eloquent and utilizes the existing ADF BC library.
The EntityImpl class contains a protected method, create(), which can be overridden to set attribute defaults or in this case, dynamically assign a database sequence number to the primary key. create() is called whenever the entity object instance is created, i.e. a new record is created from the ViewObject. In the create() method, just after the call to super.create() we use the oracle.jbo.server.SequenceImpl class which wraps Oracle database sequences. To instantiate a SequenceImpl, we need the current DBTransaction, which can be obtained through a call to the getDBTransaction() method (conveniently found in the EntityImpl class) and the db sequence name. Once we instantiate an instance of SequenceImpl we can get the next sequence and ‘automatically’ set the attribute representing the primary key immediately upon Row creation. Two obvious observations and potential issues from both ‘java code’ solutions can be asserted here: we had to add extra java code and the possibility of using up sequence numbers when the current transaction must be rolled back. I’m not saying that adding java code is necessarily ‘bad’, as certain requirements warrant this type of solution, but we certainly don’t want to use up our sequence numbers as it helps to keep our sequence consecutive.

The second way (and certainly not final) to generate a Primary Key using DB sequences is to create a trigger in the database to update the value of the database column on which the attribute is based. If a trigger is indeed created to handle the creation of the primary key, than the data-type for the entity attribute representing the primary key should be set to DBSequence. DBSequence maintains a temporary unique value in the entity cache until the data is posted at which time a new sequence value will be generated. In this way whenever a new record is created we do not have to worry about populating the primary key attribute via java code inside the service methods and/or use up our sequences. Of note, any field that uses DBSequence, except on rows that have been posted, should not be made visible to the user as the temporary value inserted into the cache has no relationship to the value that the database column will contain.
So there you have it, two options of an ADF BC general business rule for implementing the insertion of a sequence generated primary key value into newly created rows: dynamically calculating a value using java code inside ADF BC libraries or by using a DML enabled database trigger and a DBSequence as the attribute type.
Next month, we’ll talk about adding default values to entity attributes other than primary keys.