JPA Native Query select and cast object

Question

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)?

Answer 1

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.}

Answer 2

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.