navigation

Retiring Sequences and Database Triggers with ADF 12c

Retiring Sequences and Database Triggers with ADF 12c

What Is a UUID

 

Some features from JAVA can retire creation of thousands database sequences for populating the ID columns in each table. In the past, the best practice was to create a table with a unique key column, primary key, creating a sequence and then creating a trigger with PL/SQL code for populating these primary keys. And this is wonderful until you hear about the UUID. These four letters simply mean Universally Unique Identifier.

Here is a list of the UUID main benefits to confirm my point:

1) Use only one mechanism for all unique keys;

2) Third party guarantees for the uniqueness of each value;

3) Don’t need to create sequences;

4) Don’t need to write database triggers;

5) All the keys are with the same length (36 CHAR);

6) All create and populate PKs logic is in the Middleware;


How To Use It

Just put this expression in each PK field in Entity object when creating the model (Updatable must be Always).

Id==null?UUID.randomUUID().toString():Id)

 

2015-05-12 18_18_15-Средиземноморците - Боби, Вили, Ицо, Стефи, Теодор, Тошко - Google Docs


Using Archive Log Columns In ADF 12c

Some or all tables in our database schema may use system columns to collect when record is created or updated, which is the current version of the record (how many times somebody updated this record) and who updated or created each record in our table.

If we use database mechanism we must create before insert and before update trigger(s) and implement an algorithm to count system number of change for an each record. This may be done with update with increment:

<em>:new.sys_version := :old.sys_version + 1

For date columns is easy, just write:

sys_mod/cre_date := sysdate.

For username is easy too if we use database authentication.

JDeveloper delivers very simple and easy to use method for their columns.

We just need to set property Track Change History. It may have just five values:

1) created on – used for creation date columns;

2) modified on – used for modified date columns;

3) modified by – used for modified username columns;

4) created by – used for creation username columns;

5) version number – we can apply it only for Number columns;

How To Do It

We need just to select appropriate value for Track Change History property:

1111111111111

 

Use this method and share your experience in the comments below.

Teodor Ognianov

Qualified IT professional focused on software development having rich background with Oracle technologies. Deep knowledge in Business Intelligence, It project management. Familiar with broad set of Java EE frameworks, including Oracle ADF, database servers as Oracle Database (9i, 10g, 11g), Database Replications, Heterogeneous Service. Teodor is a team player with good coaching skills, providing assistance with patience and deep understanding of knowledge sharing. Responsible and highly motivated.

More Posts - Website

Follow Me:
LinkedInGoogle PlusReddit

Do you want more great blogs like this?

Subscribe for Dreamix Blog now!