Cmobilecom JPA 2.0.1 Developer Guide

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.
InheritanceStored ProcedureFrames / No Frames