How to persist custom data in SAP PI java table – Part 1

Introduction:

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.

Scenario:

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