Map result of a sql query to pojo using spring Data JPA

Question

I am a student learning Spring data jpa. I am trying to solve some of practice coding questions. I have a question which i could not find answer to. I have a database table by name MYDB which has fields:

(id, firstname, lastname, rollno, major, country)

And i have an sql query like this:

select Count(*) as counts, lastname as last_name, major as major_field from MYDB group by country

The above query returns three fields: counts(which is not a db column), last_name and major_field.

I have a POJO like this:

public class MyPojo {
    private int counts;
    private String lastName;
    private String majorField;

    // Getters and Setters of all data members here
    ...................
}

My question is how do i map the result that i got from sql query to my POJO? I need to assign:

counts = counts(from sql query), lastName = last_name(from sql query), majorField = major_field(from sql query).

I am stuck at this point and do not know how to implement further to map result of sql query to POJO:

public interface MyRepo extends JpaRepository<MyPojo, String> {
    @Query(value=MY_SQL_QUERY, nativeQuery = true)
    List<MyPojo> findAll();
}

Ultimately i need to convert MyPojo to a Json object, but i know how to do that part. I am only stuck without ideas about assigning result of sql query to pojo.

This solved the problem: baeldung.com/…

RockyB
– RockyB

2020-05-14 21:18:47 +00:00
Commented May 14, 2020 at 21:18 — RockyB
– RockyB, Commented May 14, 2020 at 21:18

RockyB · Accepted Answer · 2020-05-14 21:17:52Z

2

Problem solved using interface-based projections:

https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions#solution_interface_jpa

answered May 14, 2020 at 21:17

RockyB

591 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thomas Portwood · Accepted Answer · 2020-05-13 22:40:32Z

0

Use the javax.persistence @Column annotation to specify which values from the query are used to populate the fields of the Java object:

public class MyPojo {

    @Column(name = "counts")
    private int counts;

    @Column(name = "last_name")
    private String lastName;

    ... and so on
}

Here is a great tutorial on the annotations:

https://www.javaworld.com/article/3373652/java-persistence-with-jpa-and-hibernate-part-1-entities-and-relationships.html

answered May 13, 2020 at 22:40

Thomas Portwood

1,0918 silver badges13 bronze badges

Comments

Mohsen Msr · Accepted Answer · 2020-05-14 08:43:49Z

0

You have to use TypedQuery and give your class name for executing and mapping your query result into pojos

answered May 14, 2020 at 8:43

Mohsen Msr

92 bronze badges

Collectives™ on Stack Overflow

Map result of a sql query to pojo using spring Data JPA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related