How to use native Queries in JPA ?

Native Queries are typically used to leverage some optimizations/features of your database which are not available through HQL/JPQL. Consider the following example:

@Stateless
public class ServiceBean  {
  private static final String QUERY =
    "SELECT emp_id, name, dept_id, address_id from Employee " +
    "START WITH manager_id = ? " +
    "CONNECT BY PRIOR emp_id = manager_id";
  @PersistenceContext(unitName = "EmployeeService")
  EntityManager em;
}
public List findEmployeesReportingTo(int managerId) {
    return em.createNativeQuery(QUERY, Employee.class)
      .setParameter(1, managerId)
      .getResultList();

The above SQL statement leverages a feature of Oracle DB (Hierarchical queries) using a clause:

... PRIOR expr = expr

To use the same feature in JPA, we can use the createNativeQuery method of the EntityManager interface.

The query engine uses the object-relational mapping of the entity to figure out which result column aliases map to which entity properties. As each row is processed, the query engine instantiates a new entity instance and sets the available data into it.

You can also define a Native Query as @NamedNativeQuery annotation. This annotation may be
placed on any entity and defines the name of the query as well as the query text. Like JP QL named
queries, the name of the query must be unique within the persistence unit. If the result type is an entity,
the resultClass element may be used to indicate the entity class. If the result requires a SQL mapping,
the resultSetMapping element may be used to specify the mapping name.

@NamedNativeQuery(
name="orgStructureReportingTo",
query="SELECT emp_id, name, salary, manager_id FROM employee " +
"START WITH manager_id = ? " +
"CONNECT BY PRIOR emp_id = manager_id",
resultClass=Employee.class
)

Also please note that the createNamedQuery() can return a TypedQuery whereas the createNativeQuery() method returns an untyped Query.

What about if your query returns more than one Entity ? You have to use the @SqlResultSetMapping
which will be passed to the method instead of the Entity:

At first we define the mapping:

@SqlResultSetMapping(name = "MyMapping",
entities = @EntityResult(entityClass = sample.Person.User.class))

then we can specify the mapping in the Query as follows:

Query q = em.createNativeQuery("SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1 ON p1.title_1 = t1.title_abbrev,
     person p2 INNER JOIN title t2 ON p2.title_2 = t2.title_abbrev",
sample.Person.User.class);
return q.getResultList();