Show TOC

Background documentationJPQL Queries on a Native SQL Execution Layer Locate this document in the navigation structure

 

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

Example Example

  1. @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;
	// ...
}
End of the code.

Example Example

  1. EntityManager em;
Query query = em.createNamedQuery("Employee.getAllEmployeesUsingNativeSQLExecutionLayer");
query.getResultList();
End of the code.
Dynamic JPQL Query

Example Example

  1. EntityManager em;
Query query = em.createQuery("select e from Employee e");
query.setHint("com.sap.jpa.query.execution-layer", "native");
query.getResultList();
// ...
}
End of the code.
Named Native Query

Example Example

  1. @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;
	// ...
}
End of the code.

Example Example

  1. EntityManager em;
Query query = em.createNamedQuery("Employee.getAllEmployeesUsingNativeSQLExecutionLayer");
query.GetResultList();
End of the code.
Dynamic Native Query

Example Example

  1. EntityManager em;
Query query = em.createNativeQuery("select e from Employee e", Employee.class);
query.setHint("com.sap.jpa.query.execution-layer", "native");
query.getResultList();
// ...
End of the code.
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.

Example Example

  1. 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;
	// ...
}
End of the code.

Example Example

  1. EntityManager em;
Query query = em.createNamedQuery("Employee.getAllEmployeesUsingNativeSQLExecutionLayer");
query.getResultList();
End of the code.