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 lt User gt  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

User · Answer

When your native query is based on joins, in that case you can get the result as list of objects and process it.

one simple example.

@Autowired
EntityManager em;

    String nativeQuery = "select name,age from users where id=?";   
    Query query = em.createNativeQuery(nativeQuery);
    query.setParameter(1,id);

    List<Object[]> list = query.getResultList();

    for(Object[] q1 : list){

        String name = q1[0].toString();
        //..
        //do something more on 
     }

User · Answer

Please refer JPA   How to convert a native query result set to POJO class collection  For Postgres 9 4   List lt String gt  list   em createNativeQuery  select cast row to json u  as text  from myschema USER  u WHERE ID                           setParameter 1  id  getResultList     User map   new ObjectMapper   readValue list get 0   User class

User · Answer

First of all create a model POJO   import javax persistence     Entity  Table name    sys std user   public class StdUser        Id      GeneratedValue strategy   GenerationType AUTO       Column name    class id       public int classId       Column name    user name       public String userName        getter setter        Controller   import com example demo models    import org springframework beans factory annotation Autowired  import org springframework web bind annotation GetMapping  import org springframework web bind annotation RestController  import javax persistence EntityManager  import javax persistence EntityManagerFactory  import javax persistence PersistenceUnit  import java util List    RestController public class HomeController        PersistenceUnit     private EntityManagerFactory emf        GetMapping          public List lt StdUser gt  actionIndex             EntityManager em   emf createEntityManager       Without parameter         List lt StdUser gt  arr cust    List lt StdUser gt  em                  createQuery  SELECT c FROM StdUser c                    getResultList            return arr cust              GetMapping   paramter       public List actionJoin             int id   3          String userName    Suresh Shrestha           EntityManager em   emf createEntityManager       With parameter         List arr cust   em                  createQuery  SELECT c FROM StdUser c WHERE c classId    Id ANd c userName    UserName                    setParameter  Id  id                   setParameter  UserName  userName                   getResultList            return arr cust

User · Answer

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 lt MyEntity gt   an unchecked assignment warning is produced   Whereas  createQuery will return a TypedQuery   public  lt T gt  TypedQuery lt T gt  createQuery String qlString  Class lt T gt  resultClass     Calling getResultList on a TypedQuery returns List lt X gt    List lt X gt  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

User · Answer

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  lt named-native-query gt  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

[java] JPA Native Query select and cast object

Examples related to java

Examples related to sql

Examples related to jpa

Examples related to jpql