13 Native Query
A native query is SQL query for underlying database and optionally with query result
set transformation, which can be named or created dynamically.
Named Native Query
A named native query can be configured with annotation or XML. For example,
@SqlResultSetMapping(name="employeeResultSetMapping", entities={
@EntityResult(entityClass = FullTimeEmployee.class, fields={
@FieldResult(name="id", column="employeeId"),
@FieldResult(name="name", column="employeeName")
})}
)
@NamedNativeQueries({
@NamedNativeQuery(name = "nativeQueryWithResultType",
query = "select e.salary from Employee e where e.salary between ? and ?",
resultClass=BigDecimal.class),
@NamedNativeQuery(name = "nativeQueryWithEntityResultMapping",
query = "select e.id employeeId, e.name employeeName " +
"from Employee e where e.salary between ? and ?",
resultSetMapping="employeeResultSetMapping")
})
@Entity
public class Employee {
}
Execute a named native query with result set type:
TypedQuery<BigDecimal> nativeQuery = em.createNamedQuery("nativeQueryWithResultType", BigDecimal.class);
nativeQuery.setParameter(1, 60000);
nativeQuery.setParameter(2, 150000);
List<BigDecimal> resultList = nativeQuery.getResultList();
Execute a named native query with result set mapping:
TypedQuery<FullTimeEmployee> nativeQuery = em.createNamedQuery("nativeQueryWithEntityResultMapping",
FullTimeEmployee.class);
nativeQuery.setParameter(1, 60000);
nativeQuery.setParameter(2, 150000);
List<FullTimeEmployee> resultList = nativeQuery.getResultList();
Dynamic Native Query
Create and execute native query specifying result type:
Query nativeQuery = em.createNativeQuery("select e.salary from Employee e "
+ "where e.salary between ? and ?", BigDecimal.class);
nativeQuery.setParameter(1, 60000);
nativeQuery.setParameter(2, 150000);
List resultList = nativeQuery.getResultList();
Create and execute native query with result set mapping:
Query nativeQuery = em.createNativeQuery("select e.id employeeId, e.name employeeName " +
"from Employee e where e.salary between ? and ?", "employeeResultSetMapping");
nativeQuery.setParameter(1, 60000);
nativeQuery.setParameter(2, 150000);
List resultList = nativeQuery.getResultList();
The result set mapping maps selection Object[] to FullTimeEmployee objects.