Writing JPA query from a Native Query

Question

I want to write a JPA query from a native query. The Native query is as follows:

select alias.fname 
from (select name, fname, lname from student) alias 
where student_id='1';

I am unable to create a JPA query from this, any help is appreciated. Thanks.

Answer 1

Looking at the JPQL docs and Hibernate docs, subquery in the WHERE clause is not supported. You are left with the options of either:

1. Simplifying your query to just select fname from student where student_id='1' (which I assume isn't your real query).
2. Converting your query to use JOINs if possible, or
3. Executing the native query using the JPA EntityManager.createNativeQuery() method. (Least preferred unless you really can't simplify your query.)