In this blog, we will have a look on how to store and retrieve data in SAP PI on java database in a custom table. And perform CRUD (Create, Read,Update and Delete) of records in the custom table. Also, I will show how to expose this functionality as a web service of that other local applications can easily access the data stored in the custom table and perform operations on the table (after authentication of course).
Why store data:
There can be many use cases to store data and retrieve data from SAP PI database tables, for example, to store key attributes of set of interfaces for administration or store business error details of set of interfaces for tracking purpose.
Why on java stack:
In dual stack PI systems, data persistence can be achieved in ABAP stack or Java stack. However, in single stack PI systems (SAP Process Orchestration), data persistence can be achieved only in Java stack. Also, by persisting data on java stack in dual stack systems, interfaces can be PO ready.
Let’s assume that PI is interfacing ticketing tool, and we want to store all the tickets operations (like create, update, close etc.) made on all tickets through PI. Later we can check for any particular ticket what operations were made through PI.
Step 1: Create new table using Java dictionary.
Open NWDS and Select Window > Open Perspective > Other > Dictionary
Create new project. Select File > New > Other
Select Development Infrastructure > Development Component
Select Dictionary in Development Component Type and Click Next
Select MyComponents and Click Next
Provide development component name and click Finish
In Dictionary Explorer, Expand Dictionaries > Local Dictionary > Database Tables and right click and select Create Table
Give a prefix and suffix and select Finish
Add columns to the table and save
Right-click on project, Select Development Component > Build
Right-click on project, Select Development Component > Deploy
Verify the table created in SAP PI NWA > Troubleshooting > Database > Open SQL Data Browser
STEP 2: Create Entity Bean for the table created
Create new project in NWDS
Select Development Infrastructure > Development Component and click Next
Select J2EE > EJB Module and click Next
Select MyComponents and Click Next
Provide Development Component Name and click Next
Select Java EE Version 5.0 and click Finish
Switch to Java EE perspective
In project explorer, Expand to ejbModule and Right-click on it and select New Class
Provide package name and Class name. Implement Serializable interface and click Finish
Add @Entity, @Table annotations to entity bean class. Add @Id annotation to all primary key fields. The attributes of the class should be exactly same as columns of the database table. The table name should be same as the table created in java dictionary
Since our primary key is composite (more than 1 column) we have to create composite key class and implement Serializable interface. Right click on EJB project and select New > Class and provide package name and class name and Implement Serializable interface
Add all the primary key fields in this key class
Generate getters and setters for the primary key class attributes
Select all attributes and click OK
The primary key class should look like this
Generate getters and setters method for entity bean class
Add default default serial version ID and IdClass annotation to the entity bean class
Add a NamedQuery to the entity bean. Provide a name to this named query so that we can call this query later. This query will return all the rows in the table
Next steps are available in part-2