Running JPA Applications on the System DataSource 

Use

You can use the System DataSource that comes with the AS Java to connect your JPA-based applications to the underlying system database. By using the System DataSource, your applications are able to access the underlying database using the Open SQL framework.

The Open SQL is an SAP-proprietary database abstraction layer, which is designed to provide portability across various database platforms and performance optimizations through client-side data buffering. The semantic checks performed by the Open SQL engine are based on catalog information, which is provided by a database-independent API (the Catalog API) for all tables that have been defined and deployed using the Java Dictionary in the SAP NetWeaver Developer Studio. Open SQL engine evaluates exactly the meta-information provided by the Java Dictionary. Therefore, this layer relies on an exact structural correspondence between a table definition in the dictionary and the real structure of the table in the database. This correspondence is guaranteed if structural changes on database tables are exclusively performed through the deployment tool of the SAP NetWeaver Developer Studio. The structure of a database table must not be changed by native database means; otherwise Open SQL engine may use invalid metadata when accessing a table.

More information about Java Dictionary: Providing Java Dictionary Tables and Data Types

If you want to connect your application to existing tables in an external database, then you should consider using a custom DataSource with Native SQL or Vendor SQL access type. Open SQL is not an option in this case, since it only works using the System DataSource and connecting to the system database schema.

More information about defining custom DataSources: Working with Data Sources

With respect to the Open SQL grammar, the following list of restrictions applies to using the Java Persistence Query Language (JPQL) when your application runs on the System DataSource:

●        The CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE built-in functions are not supported;

●        The CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP date/time functions are not supported;

●        The ABS, SQRT, MOD arithmetic functions are not supported;

●        Input parameters within arithmetic expressions are not supported (for example, WHERE e.count = ?1 + ?2);

●        Input parameters within a NULL expression (for example, WHERE ?1 is NULL) are not supported;

●        The aggregate function COUNT can only be used in conjunction with DISTINCT;

●        The result of a sub-query, if used with IN, ANY, SOME, ALL, may consist only of a single persistent-field.

●        Right-deep outer joins are not admissible. In particular, outer joins over relationships that involve join tables are mapped to right-deep outer joins.

More information: Open SQL Grammar

The list is not exhaustive.

Prerequisites

You have defined:

●      Your application tables, using the Java Dictionary

More information: Providing Java Dictionary Tables and Data Types

●      A DataSource alias, which points to the System DataSource

More information: Deploying Data Source Aliases

Procedure

...

       1.      To make your JPA application aware of the DataSource alias to be used to connect through the System DataSource, enter the name of the alias as value of the <jta-data-source> tag in the persistence.xml.

Make sure that the value of the <jta-data-source> tag is the same as the value of the <alias> tag in the data-source-aliases.xml that you used to define the DataSource alias.

       2.      Define the version generator table to be used by adding the com.sap.engine.services.orpersistence.generator.versiontablename property to the persistence.xml.

More information: Versioning with read_uncommitted Isolation Level