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.

Answer 3

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 
     }

Answer 4

Please refer JPA : How to convert a native query result set to POJO class collection

For Postgres 9.4,

List<String> 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);

Answer 5

The best solution I found is using Interface projection .

At the beginning, I created a DTO class but it just didn't work, replacing the class with an interface like this works great:

@Query(value = "SELECT vat as vatRate, SUM(...) as amount from ...", nativeQuery = true)
List<VatReportLine> getSalesVats();


public interface VatReportLine {

    double getVatRate();

    long getAmount();
}

Answer 6

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<StdUser> actionIndex() {
        EntityManager em = emf.createEntityManager(); // Without parameter
        List<StdUser> arr_cust = (List<StdUser>)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;
    }
}