How to map native query result to POJO?

Question

I have a complex sql query that joins 7 tables and returns 110 columns. I am trying to use native query created using shared entity manager. Not sure how to map the resultset to a POJO. Mapping each column to resultset object array works, but is very inefficient in terms of lines of code. Any suggestion how to map it or any other way I can perform this?

Here's some pseudo code I have implemented:

Query query = sharedEntityManager.createNativeQuery(
                 " select a.c1, a.c2,.., b.c1, b.c2,.., c.c1, c.c2,...
                   from atab a, btab b, ctab c, ...
                   where condition1, condition2,...");

query.setParameter("param1", param1);

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

List<CustomPojo> retList = new ArrayList<>();
for(Object[] obj : results){
  CustomPojo row = new CustomPojo();
  row.setF1(obj[0].toString());
  row.setF2(obj[1].toString());
  ...
  ...
  retList.add(row);
}
return retList;

take a look at this https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection — want2learn, Jul 15 '19 at 17:27

score 0 · Answer 1 · answered Jul 15 '19 at 21:22

If you use Spring Data JPA (and we can assume that because you added that tag), then simply add the query to one of your Repository interface:

public interface WhateverRepository extends JpaRepository<...,...>

  ...

  @Query(SELECT NEW com.yourcompany...YourPOJO a.c1, a.c2,.., b.c1, b.c2,.., c.c1, c.c2,...
               from atab a, btab b, ctab c, ...
               where condition1, condition2,...")
  YourPOJO callFancyQuery( arg1, arg2, ...);

Spring Data JPA will do the rest. (You can refer to the arguments in your query by their name: :arg1, :arg2, etc.)

score 0 · Answer 2 · answered Jul 16 '19 at 16:39

Instead of DTO, you can easily use Projections with native queries in Spring Data JPA, for example:

@Entity
@Table(name = "models")
public class Model {

    @Id
    @GeneratedValue
    private Integer id;

    @Column(length = 32)
    private String name;
}

Projection:

public interface NameOnly {
     String getName();
}

Repo:

public interface ModelRepo extends JpaRepository<Model, Integer> {
    @Query(value = "select m.name as name from models m", nativeQuery = true)
    List<NameOnly> getAll();
}

(Notice the need to use aliases in the query: m.name as name.)

If you really need DTO, then you can create, for example, a mapper (I believe it's better to generate it with MapStruct in your case):

@Data
public class ModelDto {
    private String name;
}

@Mapper(componentModel = "spring")
public interface ModelMapper {
    ModelDto toDto(NameOnly nameOnly);
}

Then use it something like that:

@Autowired private ModelRepo repo;
@Autowired private ModelMapper mapper;
// ...
repo.getAll()
    .stream()
    .map(mapper::toDto)
    .forEach(System.out::println);

How to map native query result to POJO?

2 Answers2