Monthly Archives: June 2011

How to use discriminator columns

Environment (JDeveloper 11.1.1.5.0, ADF BC, hr schema)

Sometimes you may have a table with more than one type of rows. For example, a company may have a distinction between  salaried employees, and commissioned employees, but we store all our employees in the employees table rather than create new table for each type of employees. In this post I will explain how to use discriminator columns and extended-entities (sub-entities) to represent a table with two different kinds of rows, each with its own business logic.

This example based on EMPLOYEES table in hr schema with a small modification, one extra column (em_type) is added to the EMPLOYEES table to distinguish between different kinds of employees. You can use this SQL statement to add the column.

ALTER TABLE EMPLOYEES ADD emp_type varchar2(1);

A discriminator column is used to indicate which attribute’s value distinguishes the type of row, You can create different entity objects for each of these types, each one implements its own business logic.  To represents a table with a discriminator column, use the following steps:

  1. Create an entity object based on EMPLOYEES table in hr schema.
  2. Double click on youe entity object to open it in the main window, click on the Attributes node, select the EmpType attribute and click edit.
  3. Check the discriminator check box as shown below, then click OK.

    Discriminator Attribute

  4. In your Application Navigator area, right click on Employees entity, and select New Extended Object from the menu as shown below.

    Create sub-entity

  5. Select your package and enter SalariedEmployee in the Name field, and click OK button.The new Entity Object will be open in the main window.
  6. Select the Attributes node, then select the EmpType attribute and any other attribute you want to override it and click the Override button as shown below.

    Override EmpType attribute

  7. After you click on the button, the Edit (pencil symbol) becomes enabled for the overridden attribute. keep EmpType attribute selected and click the edit symbol.
  8. In the Default value field, enter the value of the discriminator column corresponding to this sub-entity. For a sub-entity handling the salaried employees enter “S” as shown below.

    Add the value of the discriminator column

  9. Click Ok, and repeat steps 4-8 for Commissioned employee entity.
  10. For each sub-entity, you can add the business logic you want.
  11. For common business logic, you can add it in the main entity object.

2 Comments

Filed under ADF

What is Change Indicator property

Recently I was wondering, what is the Change Indicator property that we see in each entity’s attribute?

Change Indicator

In this post I will answer myself. ADF framework provides automatic “lost update” detection for entity objects to ensure that a user cannot unknowingly modify data that another user has updated and committed in the meantime. Typically, this check is performed by comparing the original values of each persistent entity attribute against the corresponding current column values in the database at the time the underlying row is locked. ADF framework checks for row changes as follow:

  1. When the entity cache is first populated, or whenever commit() is called, the framework saves a copy of the data as it is exist in the database. This copy is maintained even if the value in the cache is later changed.
  2. When the transaction attempts to acquire a lock, every attribute in the copy is compared with the corresponding table column. If any of these comparison fail, the frame work throws oracle.jbo.RowInconsistentException.

If you are attempting to acquire locks for many rows, and each row has a large number of column, this can be expensive operation. You can make the lost update detection more efficient by identifying any attributes of your entity whose values you know will be updated whenever the entity is modified. Typical candidates include a version number column or a set of attributes that you know will change every time any value in the row is changed. The change-indicator attribute’s value might be assigned by a database trigger you’ve written and refreshed in the entity object using the Refresh After Insert and Refresh After Update options (in the Edit Attribute dialog).When you use a change indicator, the framework only performs the comparison for attribute in the change indicator instead of for every entity attribute.

3 Comments

Filed under ADF

Differences between Pessimistic and Optimistic Locking

Locking is an RDBMS feature that prevents users from different transactions from causing data conflicts. When locking is acquired on a row, it prevents other transactions from changing that row until the transaction ends. In this post I will explain the differences between pessimistic and optimistic locking in the context of ADF framework.

pessimistic locking:

Most Oracle developers are already familiar with pessimistic locking, which was the default locking in BC4J (now optimistic is the  default in 11.1.2.x)). This means that the row is locked in advance once one of  its attribute is changed through a call to setAttribute() method. If anyone else attempts to acquire a lock of the same row during this process, he will be forced to wait until the first transaction has completed. This is achieved by using the familiar SELECT…FOR UPDATE syntax. This is the safest locking mode because two transactions will never make inconsistent change to the same row. However, this locking mode has disadvantages such that:

  1. If a user selects a record for update, and then leaves for lunch without
    finishing or aborting the transaction. All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction and releases the lock.
  2. The Deadlock – Users A and B are both updating the database at the same time. User A locks a record and then attempt to acquire a lock held by user B – who is waiting to obtain a lock held by user A.

Pessimistic locking, which is the default, should not be used for web applications as it creates pending transactional state in the database in the form of row-level locks. If pessimistic locking is set, state management will work, but the locking mode will not perform as expected. Behind the scenes, every time an application module is recycled, a rollback is issued in the JDBC connection. This releases all the locks that pessimistic locking had created.

An example about pessimistic locking based on well known hr schema, suppose user1 and user2 are two different users (two distinct transactions) using pessimistic locking, both of them try to change the same row of data as follows:

  1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, so user1 immediately acquire a lock on that row.
  2. Now user2 calls EmployeesImpl.setSalary(2000) on the same row, user2 tries to acquire a lock on the row and receives oracle.jbo.AlreadyLockedException.

Optimistic Locking:

Optimistic locking assumes that multiple transactions can complete without affecting each other. Oracle recommends using optimistic locking for web applications. instead of locking a row as soon as it is changed, under optimistic locking, BC4J waits until changed row is posted before attempting to acquire a lock. An exception is not thrown until the conflicting transactions attempt to post their changes to the database.

An example about optimistic locking, suppose user1 and user2 are two different users (two distinct transactions) using optimistic locking, both of them try to change the same row of data as follows:

  1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, user1 does not immediately acquire a lock on that row.
  2. User2 calls EmployeesImpl.setSalary(2000) on  the same row. User1 and User2 now have different entity cache for the same row.
  3. User2 calls commit() action, as part of the commit cycle the changed row is posted to the database. before the update can be executed, user2 acquires a lock on that row. The lock expires immediately, when the commit command is sent to the database.
  4. User1 now calls commit() action, BC4J tries to post the changed row to the database, right before posting it, it attempts to acquire a lock on that row. BC4J recognizes that the row has been changed by another user and that updating the row would overwrite another transaction’s changes, so it throws an oracle.jbo.RowInconsistentException.

Finally, Whatever you use, you can lock a row at any time by calling EntityImpl.lock() on the corresponding entity object instance, even if the locking mode is optimistic.

References:

[1] Oracle 9i Jdeveloper handbook, Peter Koletzeke, Dr. Paul Dorsey, Dr. Avrom Faderman.

[2] Fusion Developer’s Guide for Oracle Application Development Framework
11g Release 1 (11.1.1).

[3] http://www.orafaq.com/papers/locking.pdf

6 Comments

Filed under ADF

Display row number in af:table

Environment (JDeveloper 11.1.1.5.0, ADF Faces)

In this post, I will explain how to add an extra leading column to display the row’s number. This example assumed that we have oracle.adf.RichTable in our page and we need to add extra column to hold the row’s number for each row. The steps to do this are:

  1. From the structure window, right click on your first column and choose Insert before af:columncolumn from the menu.
  2. Still in the structure window, right click the newly created column and choose Insert inside af:column Output Text from the menu.
  3. The oracle.adf.RichTable component has an attribute called varStatus.  The VarStatus provides contextual information about the state of the component to EL expressions. For components that iterate, varStatus also provides loop counter information. The common properties on varStatus include:
    • “model” – returns the CollectionModel for this component.
    • “index” – returns the zero based row index. we will use this property later.
  4. Still in the structure window, click on the table, and from the property inspector expand the column group and check the name of the EL variable used to reference the varStatus information(default value is vs) as shown below.

    Table's varStatus attribute

  5. Still in the structure window, click on the output text which was created on step 2, and from the property inspector set the value attribute to #{vs.index+1}.
  6. Your column source should be like this:
    <af:column id=”c5″>
    <af:outputText value=”#{vs.index+1}” id=”ot1″/>
    </af:column>
  7. Run your page, and you will see the row number for each row.

6 Comments

Filed under ADF