Using JPA in SAP Java development

Java offers many technologies and mechanisms that help developers realize their ideas. One is to save data in a database without dealing with object relative mapping. From the several technologies available to save a Java object into a DB NetWeaver AS Java comes with SAP’s implementation of JPA. For the following blog I’m only looking into NetWeaver >= 7.1 as the code will be using annotations, available since Java 5.

The first step to save a Java object is to create the database table. The data stored will be the name and price of a product, as well as a unique id. For this, the Data Dictionary perspective in NWDS is used.

A table should have a primary key value and the columns defining the values that can be stored inside the Java object (this can also be done in the reverse way: create first the Java object and then the DB based on the object).

Second step is to create the Java object. The values that are going to be stored are mapped to the DB columns. This can be done automatically or by using the @Column annotation. The primary key column is special as the value of this column is normally determined by the application or database without any interference by the user. Here the additional constraint of a table generated Id is used.

@Entity
@Table(name="TMP_TEST")
@NamedQuery(name="findTest", query="SELECT p FROM Test p")
public class TestJPA implements Serializable {
    @Id
    @TableGenerator(name="TABLE_GEN_TEST", table="TMP_TEST_SEQ", pkColumnName="GEN_SEQ",
            valueColumnName="GEN_COUNT", pkColumnValue = "TEST")
    @GeneratedValue(strategy=TABLE, generator="TABLE_GEN_TEST")
    private long id;       
    private String name;
    private int price;\ 

• @Table defines the table where the object values will be stored.
• @NamedQuery is a select string to get all the object stored in the DB. You can have more than one named query.
• @Id before the variable id define it as the unique Id for JPA, how to store the id value (@GeneratedValue) and the actual value that will be used (@TableGenerator).

To automatically create the PK value for the ID column, SAP offers 3 alternatives:

• TABLE
• SEQUENCE
• IDENTITY
• [AUTO]

For table Id generation SAP Help states: “The TABLE ID generation strategy relies on the existence of a database table that manages ID values”

=> The database table needs to exist!

In case the table isn’t specified, the default table TMP_SEQUENCE will be used. You have to ensure that either the default table or your custom table exists in the DB. Besides that, the table needs to contain specific elements.

• AUTO defaults to TABLE sequence. The other 2 alternatives (IDENTITY and SEQUENCE) come with certain restrictions:
• SEQUENCE needs a database sequence objects that has to be created manually at the DB
• IDENTITY won’t work with OpenSQL and MS SQL server and IBM DB2.

TABLE sequence is the only way to create the ID by using the data dictionary and for every database. The table generator option for the ID value of your JPA tables requires a specific format for the sequence table:

• primary key column needs to be of type varchar (Java: String)
• the value column needs to be a number value

Creating a sequence table in NWDS for CE 7.1:

The table generator definition in the Java class works like this:

• Name: defines the name of this table generator. Used by the @GeneratedValue part.
• Table: defines the table where the sequence values are stored
• valueColumnName: the name of the column where to look up the value. When the value found there is 56721 the value will be incremented by 1, thus the id will be 56722.
• pkColumnName: name of the primary column of the table defined by table (TMP_TEST_SEQ). That value can be the name of the Java class or something else.

The JPA Details view shows all the information needed:

The object can be used as any other Java object. To store the data into the DB an entity manager is used. In the SAP scenario, this means that a data source and alias as well as a persistence unit need to be defined. The data source alias gets defined in the EAR project:

And in the EJB project:

The entity manager is using the persistence unit name to find out the data source. The data source is defined in the NWA of the AS Java and contains the information how to connect to the DB. The persistence unit gets defined in the EJB project. Java objects that are persisted normally are accessed via a bean.

TestBean is the Bean, while TestLocal is the local interface for lookups. The actual business logic has to be implemented in the Bean, while adding the method to the local interface will make it public.

Bean:

@Stateless
public class TestBean implements TestLocal {
@PersistenceContext(unitName = "PERM_UNIT", type =    PersistenceContextType.TRANSACTION)
    private EntityManager em;
    public void createTest(TestJPA test) {
        em.persist(test);
    }
    public List<TestJPA> getAllTest() {       
        List<TestJPA> test = em.createNamedQuery("findAllTest").getResultList();
        return test;
    }
} 

Local Interface:

@Local
public interface TestLocal {
    public int createTest (TestJPA test);
    public List<TestJPA> getAllTest ();
}

Now the bean can be used in your Java code. In your SAP Portal application (PAR/WAR, of course, in WDJ too). Use the context and JNDI lookup to find the bean and start using it:

Context ctx = new InitialContext(); 
TestLocal testLocal = (TestLocal) ctx.lookup("com.tobias.<project>~ear/LOCAL/TestBean/com.tobias<path.to>TestLocal”); 
// get all Test objects 
List<TestJPA> test = testLocal.getAllTest(); 
// Create a new Test object \
TestJPA newTest = new TestJPA(); 
newTest.setName("ProductA"); 
newTest.setPrice(125.13); 
testLocal.create(newTest);