Skip to Content

One question I here a lot is how to handle the Join Operation.

For example: we have the data of Students, and Students Courses:

rt_no_join.PNG

Then we want to Join it like this:

rt_join.PNG

In the general case, I would say to do it on the database:Handling the Join Operation. After all, in other programming languages we don’t do it in the application level either. The customer case, however, might be more complex, as I recently learned:

The two lists we want to join might be coming from different databases (or even different database types).

We might not have permission to create this join query on this database.

All this brings us to this point, where we want the application to handle this.

model_temp.PNG

We need to extend the solution to include a piece of code, performing the join operation for us. This is done using a Web Service. Fortunately, there is a built in option to generate the Web Service, in the NetWeaver Developer Studio.

The steps in high level

Create a mockup Service Component in Visual Composer

Create the Web Service

Consume the Web Service

For the detailed flow of creating the web service, follow the step in: Using Web Services in Visual Composer. I added details for the sake of this specific example, and some screenshots to make it a bit more clear.

Create Service Component skeleton for the Web Service

The relevant part in the documentation: Defining the Interface of the Service Component.

1. Open NetWeaver Developer Studio

2. Open the Visual Composer perspective

3. Create a new model

4. Add a Service Component to the model

serv_comp.PNG

5. Right Click => Drill Down

6. Add a Data In element

7. Open Define Data and press View => Hierarchy Tree

8. Add the students Node under the root.

9. Add the fields student_id, student_name in the right panel.

10. Add the students_courses Node under the root

11. Add the fields student_id, course_name

data_in_2.PNG

12. Add a Data Out element

13. Open Define Data, add the fields student_id, student_name, course_name

14. Save the model.

15. Go back to the main model, press Redefine Ports for the Service Component. Check all the ports and press OK.

redefine_ports_2.PNG

We need to pass the service two lists, with a different set of fields: students and students_courses. To achieve this we defined a cluster, with two sub-nodes, one for each list.

Create the Web Service

The relevant part in the documentation: Implementing the Web Service.

I only added screenshots for some of the steps to make it a bit more clear.

We start the process with this the Generate WSDL option:

/wp-content/uploads/2014/11/gen_wsdl_586221.png

Here is the example Java Bin Skeleton created for us:

java_files.PNG

We only need to implement one method of one class in this case. See the following java coding I added:

public class JoinSEIImplBean {

    public java.util.List<com.sap.vc.sad.Out1> join(com.sap.vc.sad.In1 in1) {

        ArrayList<Out1> res = new ArrayList<Out1>();

        List<InDirin1Students> students = in1.getStudents();

        List<InDirin1StudentCourses> studentCourses = in1.getStudentCourses();

        Map<String, InDirin1Students> studentsMap =

          new HashMap<String, InDirin1Students>();

        // convert students table to map

        for (InDirin1Students s : students)

            studentsMap.put(s.getStudentId(), s);

        // go over students courses and find matches

        for (ListIterator<InDirin1StudentCourses> it = studentCourses.listIterator();

            it.hasNext();   ) {

            InDirin1StudentCourses sc = it.next();

            InDirin1Students s = studentsMap.get(sc.getStudentId());

            if (s == null) continue;

            Out1 out = new Out1();

            out.setStudentId(sc.getStudentId());

            out.setStudentName(s.getStudentName());

            out.setCourseName(sc.getCourseName());

            res.add(out);

        }

        return res;

     }

}

Consume the new Web Service in our model

The relevant part in the documentation: Consuming the Web Service.

I only added screenshots for some of the steps to make it a bit more clear.


Adding destination for the Web Service looks like this in NWDS:

/wp-content/uploads/2014/11/dest_nwds_586981.png

And in NWA:

/wp-content/uploads/2014/11/dest_nwa_2_587550.png


Modeling steps:

1. Open the Search Panel and search for the new Web Service

2. Add the service to the model

3. Connect a Grid View to the output port of the service

4. Connect the existing data services to the input port of the Web Service

model_final_mark_2.PNG

5. Define the mapping from the Get Students service to the Web Service:

map_from_students.PNG

6. Define the mapping from the Get Students Courses service to the Web Service:

map_from_students_courses.PNG

That’s it

We can now run our new application with the joined data.

Some notes:

What if we don’t want to handle the join in a the application? And can’t do it on the database where the data is originated? In that case we could have an external process copy the data of both sources to one database we can manage. Then we write the join operation on this database. Finally, consume the join result in Visual Composer (e.g. as a JDBC Stored Procedure).

When we need to pass several different lists to a Service in one input port: This port should have a clustered structure. each list could be represented as a different Sub-Node, thus allowing for its own structure.

Performance: Join operations might be costly. It is important we are aware of the Memory / CPU consumption of the specific Join we are implementing, lest we suffer a performance hit.

Related documents:

Handling the Join Operation

Paging records in the UI – Coding our own Web Service to get Row Numbers

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply