JPQL Queries on a Native SQL Execution Layer

Use

If you are running a JPA application on an Open SQL data source, all queries are executed through the Open SQL execution layer. The SQL statements, which are created by the SAP implementation of JPA, must comply with the Open SQL Grammar. Additionally, all values used by the application must comply with the Open SQL types system.

More information:

Overview of the Java Persistence Infrastructure

Open SQL Grammar

Open SQL Data Types

Consequently, some restrictions apply:

The Open SQL engine does not translate every JPQL clause. For example, upper .
The Open SQL engine does not accept every value. For example, an empty string.

If you need to overcome the restrictions that are imposed by the Open SQL execution layer, you can apply a special “hint” to your query. This hint causes the query to be executed by the Native SQL execution layer. If you use a JPQL query, it is also translated to the Native SQL dialect of the underlying database.

Using Execution Layer Hints

Named JPQL Query

Sample Code

                  @NamedQuery(name = "Employee.getAllEmployeesUsingNativeSQLExecutionLayer",
        query = "select e from Employee e",
        hints = { @QueryHint(name = "com.sap.jpa.query.execution-layer", value = "native") } )
@Entity
public class Employee {
        @Id int id;
        String name;
        // ...
}

Sample Code

                  EntityManager em;
Query query = em.createNamedQuery("Employee.getAllEmployeesUsingNativeSQLExecutionLayer");
query.getResultList();

Dynamic JPQL Query

Sample Code

                  EntityManager em;
Query query = em.createQuery("select e from Employee e");
query.setHint("com.sap.jpa.query.execution-layer", "native");
query.getResultList();
// ...
}

Named Native Query

Sample Code

                  @NamedNativeQuery(name = "Employee.getAllEmployeesUsingNativeSQLExecutionLayer",
        query = "select * from TMP_EMP",
        resultClass = Employee.class,
        hints = { @QueryHint(name = "com.sap.jpa.query.execution-layer", value = "native") } )
@Entity
@Table(name = "TMP_EMP")
public class Employee {
        @Id int id;
        String name;
        // ...
}

Sample Code

                  EntityManager em;
Query query = em.createNamedQuery("Employee.getAllEmployeesUsingNativeSQLExecutionLayer");
query.GetResultList();

Dynamic Native Query

Sample Code

                  EntityManager em;
Query query = em.createNativeQuery("select e from Employee e", Employee.class);
query.setHint("com.sap.jpa.query.execution-layer", "native");
query.getResultList();
// ...

Using Constants

For convenience, the name of the hint com.sap.jpa.query.execution-layer and the value native are exposed as constants using the com.sap.jpa.Constants class, which is available in the client library.

Sample Code

                  import static com.sap.jpa.Constants.QUERY_EXECUTION_LAYER;
import static com.sap.jpa.Constants.QUERY_EXECUTION_LAYER_NATIVE_SQL;

// ...

@NamedQuery(name = "Employee.getAllEmployeesUsingNativeSQLExecutionLayer",
        query = "select e from Employee e",
        hints = { @QueryHint(name=QUERY_EXECUTION_LAYER,
        value = QUERY_EXECUTION_LAYER_NATIVE_SQL) } )

@Entity
public class Employee {
        @Id int id;
        String name;
        // ...
}

Sample Code

                  EntityManager em;
Query query = em.createNamedQuery("Employee.getAllEmployeesUsingNativeSQLExecutionLayer");
query.getResultList();