Tuesday, 10 March 2009

ADF BC: Using Groovy to fetch sequence numbers for EO/VO attribute default values

I've recently been fooling around with the new ADF Business Component support for Groovy expressions in JDeveloper 11g, and thought I'd share the following discovery.

(Usual caveat this hasn't yet been tested in a production system, so your mileage may vary)

JDeveloper's ADF BC supports a number of different mechanisms to fetch database sequence numbers for Entity Object & View Object primary key attributes when a new record is created.

One such method is to override the create(AttributeList) method in the Entity Object and include the following Java code:

public void create(AttributeList attributeList) {
  SequenceImpl seq = new SequenceImpl("LOG_SEQ", getDBTransaction());
  Number seqNextval = seq.getSequenceNumber();

This example retrieves the next value from the database's LOG_SEQ sequence and writes it to the local Entity Object's ID attribute via a call to setId().

This minor piece of code can now be replaced with a Groovy expression in JDeveloper 11g's ADF Business Components.

If we open the Entity Object editor, then double click the primary key attribute we're interested in populating with the sequence number, we can now enter the following Groovy expression for the Value field with the Expression radio button selected:

(new oracle.jbo.server.SequenceImpl("LOG_SEQ", (cont)
object.getDBTransaction())). (cont)

The following picture shows the entry in context of the Edit Attribute dialog:

Note in the expression how we must explicitely name the package + Java class for the SequenceImpl as we don't have a mechanism for importing classes in the simple Groovy expression.  In addition for the getDBTransaction() call we need to make reference to the Groovy "object" expression (or is that class? Whatever).  "object" passes a copy of the Entity Object class to the Groovy expression evaluator allowing it to call methods of the Entity Object, in this example getDBTransaction().  More information on Groovy expressions can be found in Steve Muench's following blog post.

The advantage of this Groovy approach is one less reason to code in Java in JDeveloper if you feel the desire (though the Java code example from above isn't exactly difficult).  The disadvantage of this approach, I can imagine it would be difficult to debug if something went wrong.

In conclusion we can see the power of the new Groovy expressions supported through JDeveloper 11g and ADF Business Components.


Ayhan Gungor said...

hi Chris, this is a great tip, thanks.

Vik said...

Chris... i am using the groovy apporach. But i get an error

Too many objects match the primary key oracle.jbo.Key[3 ].

when i have an ADF form in my ui based on this EO/VO. so, is there any way to bump the seq to next until it find it unique?

in my case the data can be entered through backend as well and the data seeder is free to use any available no. for pk column. He may not use sequence defined for the table while populating data.

so what could be done in this case?

Chris Muir said...

The issue isn't really an ADF one but an issue around your application users not consistently using sequences. Do some research on the mess people get into when they don't use sequences consistently.

Your suggested example wont work in all situations. If there was a way to get the next max number, by the time you've derived it, and used it, the data seeder may have consumed that number too.

Maybe a solution in your case is to set the sequence starting number way in excess of what the data seeder will ever use. Obviously if you can't avoid the collision you're stuck.

Another is to allow your app to generate the ID value in the same way the data seeder does. Hopefully that algorithm has a mechanism for avoiding collisions.

Good luck.


Vik said...

Ok so it is true that ultimately avoidance is the only way?

Lets me give you another usecase... say data seeder just for one time randomly choose some numbers and used and done with his job forever. Say he choose between 1 to 999999.

And for me as well this is min and max restriction and i set my seq start at 1. So, is there any way my seq generator can skip those numbers instead throwing exception for that?

Chris Muir said...

Refer to the Oracle documentation for sequences including: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema.htm#i5667 and http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6015.htm#i2067093.