JPA Native query get a Single Object

Question

How can i get a single object by using JPA Native query. I did some researches but all give an answer is use "getSingleResult", however it didn't return what i want to get. For example, what should i do if i want to get a count of table in my database and fetch it into a Integer.

This code below shows how i get this by using Sessison hibernate:

int check = Integer.parseInt((String) sessionF.createSQLQuery("select to_char(count(*)) as count from user_tables where table_name upper('TEST')").uniqueResult()); 

And what i hope to be fine in JPA:

int check = Integer.parseInt((String) getEntityManager().createNativeQuery("select to_char(count(*)) as count from user_tables where table_name upper('TEST')").getSingleResult()); 

Obviously, the code doesn't return what i want. Therefore, please help me to cope with this problem. Thank you !

Answer 1

With JPA you need to do like following

int num = ((Number)this.entityManager.createNativeQuery("select count(*) from your_table_name")
                .getSingleResult()).intValue();

edited :

 String name = this.entityManager.createNativeQuery("select t.name from your_table_name t limit 1").getSingleResult().toString();

you will got count with num object

Hope its will help to you.

Answer 2

In case of native query with multiple select expressions returns an Object[] (or List<Object[]>).

you can use below example code as per your requirement .

Query q = em.createNativeQuery("SELECT id,name FROM user WHERE id = ?1");
q.setParameter(1, userId);
Object[] result = (Object[])q.getSingleResult();
String id= result[0];
int name = result[1];

You might need to typecast the result Array values.

Answer 3

This may help someone. To get a single object you can choose either of the below:

Method 1:

Query theQuery = entityManager.createNativeQuery("SELECT * from yourTable where yourTableId = (Select MAX(yourTableId) from yourTable where empId = ?1 and instId = ?2 GROUP BY empId,instId)",YourTableEntity.class);
theQuery.setParameter(1, empId);
theQuery.setParameter(2, instId);
System.out.println(theQuery.getSingleResult()); 

Method 2:

Query theQuery = entityManager.createNativeQuery("SELECT * from yourTable where empId = ?1 and instId = ?2 order by yourTableId DESC");   
theQuery.setParameter(1, empId);
theQuery.setParameter(2, instId);
theQuery.setMaxResults(1); //Use if it makes good impact in complexity
System.out.println(theQuery.getResultList().get(0));

NOTE: For simplicity sake I just printed it. You may need to typecast. Check if the time taken by method 1 or method 2 works better for you.