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.

Perkoff

what exception it throws. It should works and return a list of object[] – Acetic

@Acetic I am sorry it's executing but how can I get age in another list and name in another list. thank you – Perkoff

using cast, like for(Ojbect o:list){Object[] values = (Object[])o;int age = (int)values[0];String name = (String)values[1];} – Acetic

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.

Alisealisen

I did in Second way. I don't know first way, now it's fine.Thank you – Perkoff

@Perkoff I had the same problem, I fixed by adding the cating like : int age = (int) obj[0]; String name = (String) obj[1]; – Mannes

I suggested for first one because when you're working with big application costing is so high on database operations if you use second one . You also use some dummyDTO for setup values if it's using lot many places. – Cavour

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

Mannes

Answer 3

I believe this question has been answered here.

However I strongly suggest to create standard entity called Student with column name and age. This approach will be much easier to implement and to maintain it in the future.

Dhyana

Thank you, here I don't have POJO classes – Perkoff

I guess the easiest way is to create them. However if you don't want to do it - you might consider using standard java.sql.Connection instance? This way you'll manage the query and results by yourself. with 100% control. – Justinajustine

Answer 4

This can be done by defining an Entity with NamedNativeQuery and SQLResultSetMappping as below.

@Entity
@Data
@AllArgsConstructor
@NamedNativeQuery(
        name="fetchPersonNameAge",
        query = "SELECT name, age FROM PERSON",
                resultSetMapping = "person"
        )
@SqlResultSetMapping(
        name="person",
        classes =  @ConstructorResult(
                targetClass = Person.class,
                columns = {
                    @ColumnResult(name = "NAME", type = CharType.class),
                    @ColumnResult(name = "AGE", type = Integer.class)
                }
            )
        )
public class Person {
    
    @Id
    private String name;
    
    @NotBlank
    @Column(name = "AGE")
    private Integer age;
}

Be sure to also define the DTO (used as targetClass = PersonDto.class) similar to this entity, but without all the JPA annotations, if you need it to map to. Else just use targetClass = Person.class there.

Repository:

@Repository
public interface PersonRepository extends JpaRepository<Person, String> {

    @Query(name = "fetchPersonNameAge", nativeQuery = true)
    List<Person> findPersonNameAge();

}

Garretson

for each custom query, this is little overhead to create class, but looks spring way of doing it – Scad

Answer 5

Using JPA query 

//Repository
@Query("select pm.name, pm.age FROM PERSON pm")
List<Object[]> findAllPersonNameAndAge();


List<Object[]> obj = personRepository.findAllPersonNameAndAge();
    
    for (Object[] ob : obj) {
        name=(String) ob[0]; 
        age=(String) ob[1]);
    }

Spae

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Rudman