Thursday, February 14, 2008

ADF Business Components: Complex Insert in Easy Way

In this post I continue series of my posts related to DML (Data Manipulation Language) in ADF Business Components, those posts are among of most popular on my blog. I'm writing this post and have developed sample application based on comment i received from one blog reader of Create, Edit and Delete operations in Master-Detail af:table components post. Reader was asking me about how during Insert operation to insert data into second table, table that is not referenced through binding in View layer. Yes, it's possible to put custom method in application module and do insert from Java code, however this will broke declarative approach for Create action in main table.

You can download sample application - ComplexInsert.zip, this application implements declarative approach to Insert new row into Locations table and triggers Insert into Departments table. To run this sample you need to have Oracle XE database installed with HR schema. Application is developed with JDeveloper 10.1.3.3 production release.

First let's look into Model part. Application is based on two Entity objects and one View object. LocationsView is exposed to View layer where new row is created in declarative way.


New Location is created with CreateInsert action defined in mainPageDef.xml Page Definition file:


And let's say we have requirement to create default Department for newly created Location. How to achieve this and not to broke declarative approach we have already implemented for Locations? My answer - with ADF BC you can do this in easy way, simply you can use doDML(...) method in Entity object.

In order to be able to use doDML(...) method, with Entity Object Editor generate Entity Object Class and select Data Manipulation Methods option as well:


Entity Object Class is generated and can be accessed from Structure window:


In generated doDML(...) method I have implemented custom code to insert new row into Departments entity with some default data. This code is executed only during Insert DML operation. This allow to insert data programmatically into separate table based on executed declarative Create action:


To make it even more clear, I will show how it works. User creates new Location from Web UI and press Save button - new row is inserted into LOCATIONS table in database:


Based on this insert, default department for newly created location should be generated. We can check this in database. Oracle SQLDeveloper shows that new row is inserted into LOCATIONS table:


That correct, but what about DEPARTMENTS table? We can check it, and here we have new row inserted for newly created Location:


When running sample application, don't forget to add adf-faces-impl.jar and jsf-impl.jar to application's WEB-INF\lib directory.

13 comments:

Anonymous said...

Hi Andrejus,

I try to run this application with a 11gP4 and when i run the application, i get an error:

1. Failed to post data to database during "Insert": SQL Statement "BEGIN INSERT INTO LOCATIONS(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,COUNTRY_ID) VALUES (:1,:2,:3,:4,:5) RETURNING LOCATION_ID, COUNTRY_ID INTO :6, :7; END;".
2. ORA-01400: impossible d'insérer NULL dans ("HR"."LOCATIONS"."LOCATION_ID") ORA-06512: à ligne 1

I try to understand the data creation with a multi-table selection, but i can not find any informations about it in the ADF BC 11g Development Guide.

I thank you in advance,
Steve

Andrejus Baranovskis said...

Steve,

Looks like something wrong with database. You have HR schema, right?

Regards,
Andrejus

Anonymous said...

Hi Andrejus,

Yes I have the HR Schema on my database, but it's the 11g. I have check the database with this table:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10771/scripts003.htm#Cihhhefj

I have the same schema. I want to reinstall it but i don't have the script in the folder $Oracle_Home/demo/schema/human_ressources

I have juste one file "hr_code.sql" with 2 procedures and 2 triggers.

Regards,
Steve

Andrejus Baranovskis said...

Hi,

You can download HR schema scripts from Steve Muench blog.

Regards,
Andrejus

Anonymous said...

Hi,

I've got the same error after rebuild my sample with the scripts. Probably a bug with the 11g or with TP4?

Regards,
Steve

Andrejus Baranovskis said...

Hi,

I dont think it is a bug. Just, you trying to run application developed for 10g, not 11g. You can implement your own app in 11g, based on described steps.

Regards,
Andrejus

Anonymous said...

Hi,

I've try on a other sample, i can insert an other line in an other table based on the line insert in the main table with doDML() method.

Thanks for the advices,
Steve.

Anonymous said...

I think the server uosis.mif.vu.lt is only working sometimes. Are the example downloads still available there?

Andrejus Baranovskis said...

Hi,

Its down only today, I think it will be running tomorrow.

If you can drop me email, i will forward you this sample.

Regards,
Andrejus

whackee said...

Hi Andrejus,

Can u plz help me out with how to select two rows from two read-only table and then display both the rows in a single row in the third table.
I m using ADF BC.

Mallikarjun said...

Hi how to insert multiple rows into details table before inserting into Master table.

Andrejus Baranovskis said...

Hi,

You need to use details table iterator.

Regards,
Andrejus

Anonymous said...

Hi,
I'm trying to do something very similar:
1. I have a 1:M relationship from entity A to entity B
2. UI can create or update rows in B associated to a particular row in A.
3. Whenever a row in entity B is created or updated, I want to create or update a row in a denormalization entity C. C is basically a pivoted version of entity B where up to 10 rows of B (for each instance of A) are pivoted as columns in C. Some logic is involved to determine which 10 rows get pivoted and which are ignored.
4. Would like to keep the pivot logic and selection of which rows to denormalize as abstracted and encapsulated as possible.

I found this article very helpful as a starting point on the approach. But instead of creating a second Entity row, I was seeking to abstract the denormalization behind an AM method.

I was considering:
1. Use Configuration.createRootApplicationModule and calling an AM method to perform the denormalization.
But it seems to me that this would get a separate AM from the AM Pool, with a separate DB Transaction and DB session. The commit cycle would be disconnected between the denorm rows in C from the rows in B.

2. Use DBTransaction.getRootApplicationModule and call a method on the AM. But this design would have some implied coupling between the EO code and the AM it's being used in, which is not good.

Is there better way to achieve this?

Thanks,
Steve (a different Steve)