JPA Native Query select and cast object

Menno picture Menno · Jul 17, 2013 · Viewed 145.5k times · Source

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

Paul Vargas picture Paul Vargas · Jul 17, 2013

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.