JPA Native Query select and cast object
I have got an Object Admin
which extends User
. By default both Objects are in the table User_
of my Derby Database (included fields from Admin
). Normally I'd select an User
like this:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root user= query.from(User.class);
Predicate predicateId = cb.equal(category.get("id"), id);
query.select(user).where(predicateId);
return em.createQuery(query).getSingleResult();
However due to the complexity of my query I'm using a native query like this:
Query query = em.createNativeQuery("SELECT USER.* FROM USER_ AS USER WHERE ID = ?");
query.setParameter(1, id);
return (User) query.getSingleResult();
Though this throws a cast exception. I figure this is due to any fields from Admin
.
My question is, how can I select a User
using a native query with an equal result as the first example (including the same values for @LOB
and @ManyToOne
(et cetera) as the JPQL query would return)?
You might want to try one of the following ways:
-
Using the method
createNativeQuery(sqlString, resultClass)
Native queries can also be defined dynamically using the
EntityManager.createNativeQuery()
API.String sql = "SELECT USER.* FROM USER_ AS USER WHERE ID = ?"; Query query = em.createNativeQuery(sql, User.class); query.setParameter(1, id); User user = (User) query.getSingleResult();
-
Using the annotation
@NamedNativeQuery
Native queries are defined through the
@NamedNativeQuery
and@NamedNativeQueries
annotations, or<named-native-query>
XML element.@NamedNativeQuery( name="complexQuery", query="SELECT USER.* FROM USER_ AS USER WHERE ID = ?", resultClass=User.class ) public class User { ... } Query query = em.createNamedQuery("complexQuery", User.class); query.setParameter(1, id); User user = (User) query.getSingleResult();
You can read more in the excellent open book Java Persistence (available in PDF).
───────
NOTE: With regard to use of getSingleResult()
, see Why you should never use getSingleResult()
in JPA.
The accepted answer is incorrect.
createNativeQuery
will always return a Query
:
public Query createNativeQuery(String sqlString, Class resultClass);
Calling getResultList
on a Query
returns List
:
List getResultList()
When assigning (or casting) to List<MyEntity>
, an unchecked assignment warning is produced.
Whereas, createQuery
will return a TypedQuery
:
public <T> TypedQuery<T> createQuery(String qlString, Class<T> resultClass);
Calling getResultList
on a TypedQuery
returns List<X>
.
List<X> getResultList();
This is properly typed and will not give a warning.
With createNativeQuery
, using ObjectMapper
seems to be the only way to get rid of the warning. Personally, I choose to suppress the warning, as I see this as a deficiency in the library and not something I should have to worry about.