Using MySQL autoincrement PK column in ADF Entity PK attribute

Hello all. Continuing with the ADF + MySQL workaround series, today we are going to see what we need to do in order to use MySQL PK autoincrement columns with our ADF Entities PK attributes. If we were using Oracle database, we would use the oracle.jbo.domain.DBSequence along with a sequence and a trigger in order to do that out-of-the-box.

For simplicity, we are going to modify the Java file associated to the Entity, but as a good practice, you should have your own implementation of the oracle.jbo.server.EntityImpl class and configure JDeveloper so it uses your own implementation for every new Entity in your application. Check this post for more information about that.

This is not a step by step tutorial about how to create ADF BC from tables, we'll assume that you already have your Business Components definitions.

What you need

JDeveloper 12c

MySQL 5.5+

Confiure MySQL for ADF development

This is the database table that we are going to use in this post:

CREATE  TABLE `test`.`Actor` (
  `id_actor` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`id_actor`) );

Once you have created your entities from tables, JDeveloper will map your Entity attributes against the table columns and will set some properties based on the type of column. For example, for our PK column (id_actor), JDeveloper will set the attribute as a mandatory Integer and always updatable. We need to change that because we want our PK to be updatable while new (while creating a new instance) and not mandatory (because we are reading the autoincrement value after posting to the database):

You may have several tables in your database and hence several Entities in your ADF application but not all tables may have autoincrement PK columns, so we need a way to mark or identify when the Entity's PK attribute is coming from an autoincrement column in the database. We are going to do that using Property Set, which allows us to define a collection of key/values properties and then we can associate those properties to an attribute and access them during runtime:

1. In JDeveloper select: File-->New-->From Gallery...
   
   
2. In the ADF Business Components select Property Set
   
   
   
   
   
3. Set the name and package of the new property set:
   
   
   
   
   
4. Once created, we can define the set of key/value properties and if the properties are going to be shown to the users, we can even define them in translatable mode. This is not our case, so we are going to define a non-translatable property:
   
   
   
   
   
5. Set the property name as AI (for AutoIncrement) and its value as true (because attributes who use this Property Set are coming from an autoincrement column):
   
   
   
   
   
6. Now that we have our Property Set ready, we can use it in our Entity PK attribute:
   
   
   
   
   

In order to retrieve the autoincrement value for our PK attribute, we need to override the default implementation of our Entity class (remember we do this for simplicity, but you can do better as described at the beginning of this post). We can do that by implementing a Java class for our Entity and then by overriding the EntityImpl.doDML(int, TransactionEvent) method, which is where changes are posted to database:

1. Go to the Java section of the Entity and click on the pencil icon:
   
   
   
   
   
2. In the pop up window, select Generate Entity Object Class:... and click OK:
   
   
   
   
   
3. The Java section of the Entity class now shows the Java file, click on the link to the Java File name:
   
   
   
   
   
4. JDeveloper will open a new window with the Java code. Copy and paste the following methods:
   
   

   ...
       @Override
       protected void doDML(int i, TransactionEvent transactionEvent) {
           //got to call first to super, so the record is posted 
           //and we can then ask for the last insert id
           super.doDML(i, transactionEvent);
           
           //after the record is inserted, we can ask for the last insert id
           if (i == DML_INSERT) {
               populateAutoincrementAtt();
           }
       }
       
       /*
       * Determines if the Entity PK is marked as an autoincrement col
       * and executes a MySQL function to retrieve the last insert id
       */
       private void populateAutoincrementAtt() {
           EntityDefImpl entdef = this.getEntityDef();
           AttributeDef pk = null;
           //look for primary key with Autoincrement property set
           for (AttributeDef att : entdef.getAttributeDefs()) {
               if (att.isPrimaryKey() && (att.getProperty("AI") != null 
                   && new Boolean(att.getProperty("AI").toString()))) {
                   pk = att;
                   break;
               }
           }
           if (pk != null) {
               try (PreparedStatement stmt = 
                    this.getDBTransaction()
                        .createPreparedStatement("SELECT last_insert_id()", 1)) {
                   stmt.execute();
                   try (ResultSet rs = stmt.getResultSet()) {
                       if (rs.next()) {
                           setAttribute(pk.getName(), rs.getInt(1));
                       }
                   }
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }
   ...
   

   
   The above code is posting changes to database and then asks whether we were inserting values. If that's the case, we need to retrieve the autoincrement value and set it in our PK attribute. Refer to the MySQL function LAST_INSERT_ID() for more information about retrieving autoincrement values.
   
   

OK, let's try out our solution. First, run your application module:

Once the Oracle ADF Model Tester tool appears, select (double click) the ActorView1 View Object, on the left panel, and click on the green plus sign icon, on the right panel,  in order to add a new Actor:

Enter a name for the Actor and press the "Save changes to the database" button (little database with the checklist icon on the toolbar):

You will notice that the autoincrement value is now set to the idActor attribute!

No need to be worried about concurrent inserts, MySQL documentation states that it is OK (check this documentation):

Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

Download the ADF project used in this post.


See ya!


References:

Vesterli E., Sten (2013). Developing Web Applications with Oracle ADF Essentials. United Kingdom: Packt Publishing Ltd.

MySQL 5.5 Reference Manual :: 12.14 Information Functions. MySQL [online].
Available on Internet: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id
[accessed on November 14 2013].

MySQL 5.5 Reference Manual :: 22.8.15.3 How to Get the Unique ID for the Last Inserted Row. MySQL [online].
Available on Internet: http://dev.mysql.com/doc/refman/5.5/en/getting-unique-id.html
[accessed on November 17 2013].