JPQL is one of the most standard feature of JPA(Java Persistence API) for querying the database. But JPA queries doe not support some of the database specific features. It is also not recommended to use JPQL in case of complex SQL queries.
So how do we work with JPA in case of Complex SQL Queries???
No worries.. JPA is such a powerful framework that it also provides support for the SQL queries with the native queries feature in JPA.
Lets look at the different ways of querying using native SQL queries using JPA.
Query q = em.createNativeQuery("SELECT e.firstName, e.lastName, e.id FROM EMPLOYEE e");
List<Object[]> employees = q.getResultList();
Above piece of code is one of the simplest way of using a native query in JPA.
Its looks fine but how do we map the result-set to an Entity???
Well, there is a way for that too. Following lines of code we can see how we can map a result-set in JPA native queries.
Query q = em.createNativeQuery("SELECT e.firstName, e.lastName,e.id FROM EMPLOYEE e",Employee.class);
List<Employee> employees = q.getResultList();
Now lets look how we can query in case of dynamic parameters.
We can do parameter binding in two ways in JPA.
1) using positional Parameters
2) using named parameters.
Parameter Binding with Positional Parameters:
Query q = em.createNativeQuery("SELECT e.firstName, e.lastName, e.id FROM EMPLOYEE e where gender=?1 and id=?2",Employee.class);
q.setParameter(1,”male”);
q.setParameter(2,123);
List<Employee> employees = q.getResultList();
from the above code snippet we can see how the positional parameters (?1 and ?2)are specified and how they have been set using the setParameter of the query.
Parameter Binding with named Parameters:
Query q = em.createNativeQuery("SELECT e.firstName, e.lastName, e.id FROM EMPLOYEE e where gender=:gender and id= :id",Employee.class);
q.setParameter(“gender”,”male”);
q.setParameter(“id”,123);
List<Employee> employees = q.getResultList();
from the above piece of code we can see how the named parameters ( :gender and :id)are specified and how they have been set using the setParameter of the query.
Now this looks fine when we are mapping it to an entity.But what if our query result set contains attributes from multiple different tables ?? what if we want to map it to a custom DTO?? there is a way for that too..
This can be achieved using @SqlResultSetMapping annotation.
following code illustrates how it can be achieved using this annotation.
@SqlResultSetMapping(
name = "employeeResultSetMapping",
classes = @ConstructorResult(
targetClass = EmployeeDTO.class,
columns = {
@ColumnResult(name = "id”),
@ColumnResult(name = "firstName"),
@ColumnResult(name = "lastName"),
@ColumnResult(name = "city"),
@ColumnResult(name = "country")}))
the above code snippet can be added in the DTO class and the mapping name can be used in the query in the following way.
Query q = em.createNativeQuery("SELECT e.firstName, e.lastName, e.id, a.city, a.country FROM EMPLOYEE e JOIN Address a on e.addressId=a.id where gender=:gender and id= :id",”employeeResultSetMapping”);
q.setParameter(“gender”,”male”);
q.setParameter(“id”,123);
List<EmployeeDTO> employees = q.getResultList();
These mappings can also be defined in xml files.
The easiest way to do this is to use the default mapping file named orm.xml that will be used by default, if it is added to the META-INF directory.
<sql-result-set-mapping name="employeeResultSetMapping">
<constructor-result target-class="com.example.EmployeeDTO.class ">
<column name="id" />
<column name="firstName"/>
<column name="lastName"/>
<column name="city"/>
<column name="country"/>
</constructor-result>
</sql-result-set-mapping>
Now if you don't want to use any of the result transformers provided JPA then you can still do it manually in the following way using "Tuple" class.
Query q = em.createNativeQuery("SELECT e.firstName, e.lastName,e.id, a.city, a.country FROM EMPLOYEE e JOIN Address a on e.addressId=a.id where gender=:gender and id= :id",”Tuple.class”);
q.setParameter(“gender”,”male”);
q.setParameter(“id”,123);
List<Tuple> employeeTuple = q.getResultList();
List<EmployeeDTO> list= new ArrayList<>();
For(Tuple tuple : employeeTuple)
{
EmployeeDTO dto = new EmployeeDTO();
dto.setFirstName(tuple.get(“firstName”));
dto.setLastName(tuple.get(“lastName”));
dto.setId(tuple.get(“id”));
dto.setCity(tuple.get(“city”));
dto.setCountry(tuple.get(“country”));
list.add(dto);
}