How to map native query result into custom class object?

Question

I have three classes: Lending, LendingReturn and ProductLending.

Lending

@Entity
public class Lending implements Serializable {

@Id
private Long id;

private BigDecimal amount;

private Instant createdDate;

private User lender;

private ProductLending productLending;

}

Lending Return

@Entity
public class LendingReturn implements Serializable {

@Id
private Long id;

private BigDecimal amount;

private Instant createdDate;
}

Product Lending

@Entity
public class ProductLending implements Serializable {

    @Id
    private Long id;

    private String title;
}

Below is my native query. I want to query then the result is mapped into good mapped list

Query query = em.createNativeQuery("select l.id, pl.title, sum(lr.amount)+l.amount as total_return, " +
                                            "(select amount as yesterday_return from lending_return where lending_id=l.id and date(created_date)=current_date-2), " +
                                            "l.period_in_day-(current_date-date(l.created_date)) as mature_in_day " +
                                            "from lending_return lr right join lending l on lr.lending_id=l.id " +
                                        "join product_lending pl on l.product_lending_id=pl.id " +
                                        "where l.lender_id=:lenderId " +
                                        "group by l.id, pl.title, l.amount");

    query.setParameter("lenderId", userService.getLoggedInUser().getId());
    List<Object[]> resultList = query.getResultList();

So the query returns columns: id, title, total_return, yesterday_return

Then the resultList contains:

[
  [
    2804,
    "Title 1",
    1001800,
    600,
    24
  ],
  [
    2809,
    "Title 2",
    null,
    null,
    28
  ]
]

How to map the resultList so the result will be like below?

[
  {
    "id": 2804,
    "title": "Title 1",
    "total_return": 1001800,
    "yesterday_return": 600,
    "mature_in_day": 24
  },
  {
    "id": 2809,
    "title": "Title 2",
    "total_return": null,
    "yesterday_return": null,
    "mature_in_day": 28
  }
]

you have to use projections https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections — Ryuzaki L, Dec 09 '19 at 16:50

score 0 · Answer 1 · answered Dec 09 '19 at 16:58

you need to map it manually :

public String map(ResultSet rs) throws Exception {
        Lending lend = new Lending ();
            lend.setId( rs.getLong("id"));
        // complete all columns 
        return lend;
    }

and call the method where do you need it.

How to map native query result into custom class object?

1 Answers1