JPA Native Query select and cast object
Solution 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.
Solution 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.
Solution 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
}
Solution 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);
Menno
Updated on December 01, 2020Comments
-
Menno over 3 years
I have got an Object
Admin
which extendsUser
. By default both Objects are in the tableUser_
of my Derby Database (included fields fromAdmin
). Normally I'd select anUser
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)? -
Pallav Jha about 8 years@PaulVargas What if the result is an Integer and not an entity
-
Joe almost 8 years@PaulVargas, I would recommend doing something like this: Integer val = (Integer) query.getSingleResult(); However, I usually recommend against query.getSingleResult() as it throws to many checked exceptions. If you get your results as a list, you can then handle no results and more than one accordingly.
-
Paul Vargas almost 8 yearsHey, @Joe. Thanks! -- I continued with
query.getSingleResult()
because the OP. :) -
Georgios Syngouroglou over 6 yearsIt is not a good practice to write your data access code inside the rest controller. Store your data access code in @ Repository classes. Call your repositories from @ Service classes. Access the @ Service classes from the @ RestController.
-
bhavya about 3 yearswhich way is good for pass native query? 1) Using EntityManager 2) Using JpaRepository Interface