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:
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();
@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.
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
}
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;
}
}
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.
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);
Source: Stackoverflow.com