How to get multiple columns from table using JPA?

Question

For example I have a table as Student it's contain columns like id, name, age I am reverting particular column values by using NativeQuery like below.

Query query = entityManager.createNativeQuery("SELECT age FROM Student");
List list=query.getResultList(); 

By using above query We will get list of ages in Student table Now I want to get age and name from table.

Query query = entityManager.createNativeQuery("SELECT age,name FROM Student");
List list=query.getResultList();

If I do like this My code is execute well, but How can I get name in one list and age in another list.So how can I do this. Thank you very much

Note I don't have any Entity class or POJO classes in my project I am getting table from Database using Native Query.

Answer 1

You could approach it two ways.

1. Parse the values from your result set into separate Lists. Since you don't have an entity defined for it, the query will return a

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

   For each row returned, you can access age and name by the index in which they appear. i.e. Age is selected first in your above example so you would it would be index 0 and Name is index 1.

   for (Object[] obj : list) {
        age = obj[0];
        name = obj[1];
        // ..put them into whatever collection you desire
   }
   

2. Use two queries. One that selects AGE and the other that selects NAME. Each one will return the corresponding values to it's own list.

   Query query = entityManager.createNativeQuery("SELECT age FROM Student");
   List ages=query.getResultList(); 
   
   query = entityManager.createNativeQuery("SELECT name FROM Student");
   List names=query.getResultList();
   

   Only caveat to this method is that there is no true relationship between the two lists. In other words, ages[3] may not logically match names[3]. But... as it is, the original question does not have any definition as to how the two lists should be created.

Answer 2

I based on the answer of @dispake, he suggested two ways for fix this issue but he forgot tu put the casting for the first ways. In my opinion it is the optimized one (you just need to do one query instead of two) do it like this :

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

for (Object[] obj : list) {
     int age = (int) obj[0];
     String name = (String) obj[1];
}

I hope this will help you