Saturday, October 23, 2010

ADF Model: Generating unique autogenerated number for primary key EO attribute

When we create a table and want to insert data, we must make sure that the value we give for the primary key should be unique. So, checking the existing data in table and using unique number would be obviously painful. To generate unique id for the primary keys, we have different methods for different DBs. For example, in MYSQL we use 'auto increment' fields in SQL statement while creating a table.

E.g.,
CREATE TABLE `classes` ( `class_id` INT( 3 ) NOT NULL AUTO_INCREMENT, `class_name` VARCHAR( 25 ) NOT NULL , UNIQUE ( `class_id` ) );


Coming to Oracle SQL, we write triggers and sequences to generate auto-increment number for primary keys.

E.g.,
CREATE SEQUENCE class_seq START WITH 1 INCREMENT BY 1; CREATE OR REPLACE TRIGGER class_id_insert_trigger BEFORE INSERT ON classes REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT class_seq.nextval INTO :NEW.CLASS_ID FROM dual; END; /


But, Oracle ADF has a easy way to generate auto-increment numbers for the primary key fields in EO. To make an EO attribute as an auto-increment field:

1. Create a new DB connection with name ROWIDAM_DB with the same database connection parameters of your original application DB. This DB connection will be used to generate the unique id for the entity attribute.

2. The SQL type of the EO attribute should be numeric data type. Specify default value for the EO attribute as the expression "oracle.jbo.server.uniqueid.UniqueIdHelper.getUniqueId();"

That's it! Now, you don't need to worry about setting unique id/number for the primary key attributes. ADF will automatically generate a unique-id for these fields.

3 comments:

  1. hello,
    Nice steps for autoincrement.
    We are following above steps in our ADF 11g application.

    We are getting Exception : Table of View does not exist.!!
    We are using Oracle Database.
    1) should we have to create any sequence or trigger for that table in Database?
    2) how can we generate autoincrement on multiple tables..? is it maintained automatically by ADF?

    Please reply....

    ReplyDelete
    Replies
    1. I am also getting same error, please let me know if someone knows solution..

      Thanks in advance
      Anshul Chandak

      Delete
  2. Cannot create an object of type:oracle.jbo.domain.Number from type:java.lang.String with value:oracle.jbo.server.uniqueid.UniqueIdHelper.getUniqueId();

    ReplyDelete

Related Posts with Thumbnails