Invoke a WebService with the Inserted Rows data of Excel sheet in ADF-DI:

In ADF-DI you don’t have any direct option available to get the control over all the inserted rows to invoke WS (Web Service). So you must implement it with the help of programmatic approach.

First what you need to understand is, If you want to upload 100 records of data from Excel sheet to DB, first you must set a property [InsertBeforeRowActionID : ‘CreateInsert‘] which means before reading each row of data, a new empty row object is created and the data of that row is stored into that row object cache. The same thing happens for all the rows. Once all the rows of Excel sheet are read, the cached data has to be committed to DB. So set the property [CommitBatchActionID: Commit] which inserts the cached data to DB.

The above properties must be set to upload data to DB from Excel sheet. To achieve this your page definition file must contain the bindings [tree binding, Commit, CreateInsert bindings]

To get the Handle over the Excel sheet data, you must set below proprieties.
Property [InsertBeforeRowActionID : ‘eachRowInvokeAM‘] which is being used to Invoke a custom method where current row data can be read and stored to a List. This will repeat for all the rows.
Then set the property [SuccessActionID : afterAllRowsInvokeAM] for a ribbon command ‘Update‘. This triggers the custom method ‘afterAllRowsInvokeAM’ after successful Insert/Update to DB. In this custom method, you can work with the list which has the data of all rows inserted in the Update operation. Using this data you can Invoke a Web Service.

You must write your custom methods in AMImpl class. ADF12c supports only AMImpl class methods, where as ADF11g supports AMImpl & VOImpl class methods to invoke.

Eg: Let’s take an example, where we insert a record to Departments table. And after all the rows got inserted to DB from ADF-DI Excel sheet a webservice should be invoked with the inserted data as input. For this set the below properties.

InsertBeforeRowActionID : ‘CreateInsert’
InsertBeforeRowActionID : ‘eachRowInvokeAM’
InsertRowEnabled: True
CommitBatchActionID: Commit
RibbonCommand- Update -> SelectActionSet -> ActionOptions -> SuccessActionID : afterAllRowsInvokeAM

Custom methods given above are [‘eachRowInvokeAM’, ‘afterAllRowsInvokeAM’] are mehtods created in AMImpl class .
Another class also created ‘Dept’ which is a Pojo class with the column names as variables and having parameterized constructor.
In the AMImpl class, a list is created of type ‘Dept’ pojo class. And the parameterized constructor is being invoked to insert the department type object to the list. Two custom methods are created in it [‘eachRowInvokeAM’, ‘afterAllRowsInvokeAM’] and are exposed and then added to the page definition file as method bindings. During each row insert ‘eachRowInvokeAM’ will be invoked and that row data in inserted to the List. Once the commit operation is susccessful another method ‘afterAllRowsInvokeAM’ will be invoked where the list data is read and used as input to Invoke the webservice.

The page definition file should be:

ADF-DI_1.PNG

Excel properties should be:

ADF-DI_3

ADF-DI_2

 

AmImpl.java:


package model;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;

import java.net.HttpURLConnection;
import java.net.URL;

import java.util.ArrayList;
import java.util.List;

import model.common.AppModule;

import oracle.jbo.Row;
import oracle.jbo.server.ApplicationModuleImpl;
import oracle.jbo.server.ViewLinkImpl;
import oracle.jbo.server.ViewObjectImpl;

public class AppModuleImpl extends ApplicationModuleImpl implements AppModule {

/* Custom Code */
List dept = new ArrayList();
public void eachRowInvokeAM(){
System.out.println("Entered before Upload..");
System.out.println("row count:"+getDepartmentsView1().getRowCount());
Row r = getDepartmentsView1().getCurrentRow();
System.out.println(r.getAttribute("DepartmentId"));
System.out.println(r.getAttribute("DepartmentName"));
System.out.println(r.getAttribute("LocationId"));
System.out.println(r.getAttribute("ManagerId"));

int deptId = Integer.valueOf(r.getAttribute("DepartmentId").toString());
String deptName = r.getAttribute("DepartmentName").toString();
int locId = Integer.valueOf(r.getAttribute("LocationId").toString());
int mgrId = Integer.valueOf(r.getAttribute("ManagerId").toString());

//adding current row to list
dept.add(new Dept(deptId, deptName, locId, mgrId));
}

public void afterAllRowsInvokeAM() {
System.out.println("after Insert success..");
System.out.println("rows inserted:"+dept.size());
for(int i=0; i< dept.size(); i++){
System.out.println(dept.get(i).getDepartmentId());
System.out.println(dept.get(i).getDepartmentName());
System.out.println(dept.get(i).getLocationId());
System.out.println(dept.get(i).getManagerId());
}
System.out.println("Now Invoking WebService");
try {
http_client();  // Invoke Webserive method
} catch (Exception e) {
}
}

public static void http_client() throws Exception {
System.out.println("Invoke service using direct HTTP call with Basic Auth");
String payload =
"\n" +
" \n" +
" \n" +
" \n" +
" INR\n" +
" USD\n" +
" \n" +
" \n" +
"";

httpPost("http://www.webservicex.net/CurrencyConvertor.asmx" + "?invoke=", payload);
}
private static String httpPost(String destUrl, String postData) throws Exception {
URL url = new URL(destUrl);
HttpURLConnection conn = (HttpURLConnection)url.openConnection();
if (conn == null) {
return null;
}
conn.setRequestProperty("Content-Type", "text/xml; charset=UTF-8");
conn.setDoOutput(true);
conn.setDoInput(true);
conn.setUseCaches(false);
conn.setFollowRedirects(true);
conn.setAllowUserInteraction(false);
conn.setRequestMethod("POST");

//byte[] authBytes = authStr.getBytes("UTF-8");
//String auth = com.sun.org.apache.xml.internal.security.utils.Base64.encode(authBytes);
// conn.setRequestProperty("Authorization", "Basic " + auth);

System.out.println("post data size:" + postData.length());

OutputStream out = conn.getOutputStream();
OutputStreamWriter writer = new OutputStreamWriter(out, "UTF-8");
writer.write(postData);
writer.close();
out.close();

System.out.println("connection status: " + conn.getResponseCode() +
"; connection response: " +
conn.getResponseMessage());

InputStream in = conn.getInputStream();
InputStreamReader iReader = new InputStreamReader(in);
BufferedReader bReader = new BufferedReader(iReader);

String line;
String response = "";
System.out.println("==================Service response: ================ ");
while ((line = bReader.readLine()) != null) {
System.out.println(line);
response += line;
}
iReader.close();
bReader.close();
in.close();
conn.disconnect();

return response;
}

Dept.java:

</pre>
package model; public class Dept { int DepartmentId; String DepartmentName; int LocationId; int ManagerId; public Dept(int DeptId, String DeptName, int LocId, int MgrId) { //super(); this.DepartmentId = DeptId; this.DepartmentName = DeptName; this.LocationId = LocId; this.ManagerId = MgrId; } public void setDepartmentId(int DepartmentId) { this.DepartmentId = DepartmentId; } public int getDepartmentId() { return DepartmentId; } public void setDepartmentName(String DepartmentName) { this.DepartmentName = DepartmentName; } public String getDepartmentName() { return DepartmentName; } public void setLocationId(int LocationId) { this.LocationId = LocationId; } public int getLocationId() { return LocationId; } public void setManagerId(int ManagerId) { this.ManagerId = ManagerId; } public int getManagerId() { return ManagerId; } } 

Your Excel in Design Time:

ADF-DI_4.PNG

Your Excel at run time:

ADF-DI_5


After Click on Ribbon button ‘Upload’, all the rows inserted to DB and also a web service is invoked at back end.

ADF-DI_6

Hope the post is Informative!

Advertisements