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.
Hi Andrejus,
ReplyDeleteI 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
Steve,
ReplyDeleteLooks like something wrong with database. You have HR schema, right?
Regards,
Andrejus
Hi Andrejus,
ReplyDeleteYes 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
Hi,
ReplyDeleteYou can download HR schema scripts from Steve Muench blog.
Regards,
Andrejus
Hi,
ReplyDeleteI've got the same error after rebuild my sample with the scripts. Probably a bug with the 11g or with TP4?
Regards,
Steve
Hi,
ReplyDeleteI 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
Hi,
ReplyDeleteI'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.
I think the server uosis.mif.vu.lt is only working sometimes. Are the example downloads still available there?
ReplyDeleteHi,
ReplyDeleteIts down only today, I think it will be running tomorrow.
If you can drop me email, i will forward you this sample.
Regards,
Andrejus
Hi Andrejus,
ReplyDeleteCan 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.
Hi how to insert multiple rows into details table before inserting into Master table.
ReplyDeleteHi,
ReplyDeleteYou need to use details table iterator.
Regards,
Andrejus
Hi,
ReplyDeleteI'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)
Hi Andrejus,
ReplyDeleteThanks for this ,it helps me a lot. Btw, Instead of inserting using doDML. How to write doDML code only for update between these two table without inserting. I am sorry I'm new in adf, but still i need this idea. Thanks in advance.
Thanks,
Han
Hi Andrejus,
ReplyDeleteSimilarly when i tried with adf select one choice .its not refreshing the data .in input list of values you have used returnpopup listener and binded the excute with param operation where as in select one choice how do i bind execute with param operation.Please help me