How to map JPA query results to a POJO?

Question

@Query("SELECT tt, at.field, at.anotherField from TableTest tt LEFT JOIN AnotherTable at ON at.commonField = tt.commonField")
List<TestPojo> findAllPojo(List<TableTestDTO> TableTestDTOList);

How can I map this JPA query results to a Pojo without native query, like this approach ?

I'm using JPA and Hibernate. Can anyone provide other option?

Answer 1

Try using the constructor:

@Query("SELECT new TestPojo(tt, at.field, at.anotherField) from TableTest tt LEFT JOIN AnotherTable at ON at.commonField = tt.commonField")
List<TestPojo> findAllPojo(List<TableTestDTO> TableTestDTOList);

Of course such constructor must exist and even better would be to place the fully qualified name instead of bare TestPojo.

Answer 2

Well you can use the @SqlResultSetMapping annotation or you can create your own interface to reflect the query fields to it. You can find both examples here:Spring Data JPA map the native query result to Non-Entity POJO

You can call a constructor too, like @Andronicus said.

Answer 3

I found it is similar to JPA : How to convert a native query result set to POJO class collection and JPA : How to convert a native query result set to POJO class collection

I had a answer to suggest the easy and convenient way if you are using spring-boot-starter-data-jpa. I copy the answer here for more convenient.

If you want to map the custom query result directly to an entity without writing any code to map, try this way. In my experience, it is the most convenient way to do, but the downside is to lose the benefit of hibernate ddl-auto:

1. Disable hibernate validation by removing the hibernate.ddl-auto. If not doing this, hibernate can complain about missing table in database.

2. Create a pojo with @Entity for the custom result set without table mapping, something like:

   @Getter
   @Setter
   @Entity
   public class MyCustomeResult implements Serializable {
       @Id
       private Long id;
   
       @Column(name = "name")
       private String name;
   }
   

3. In repository, use the entity to map directly from query.getResultList()

   public List<MyCustomeResult> findByExampleCustomQuery(Long test) {
        String sql = "select id, name from examples where id =:test";
        Query query = entityManager.createNativeQuery(sql, MyCustomeResult.class);
        return query.setParameter("test", test).getResultList();
    }
   

Answer 4

I found two sollution:

1 . Using a class to convert:

@Query("SELECT new com.example.project.TestPojo(tt, at.field, at.anotherField) from TableTest tt LEFT JOIN AnotherTable at ON at.commonField = tt.commonField")
List<TestPojo> findAllPojo(List<TableTestDTO> TableTestDTOList);

Class:

package com.example.project;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class TestPojo {
    private Long tt;
    private String field;
    private String anotherField;
}

2 . Using an interface to convert:

@Query("SELECT tt AS tt, at.field AS field, at.anotherField AS anotherField from TableTest tt LEFT JOIN AnotherTable at ON at.commonField = tt.commonField")
List<TestPojo> findAllPojo(List<TableTestDTO> TableTestDTOList);

Interface:

public interface TestPojo {
    Long getTt();
    String getField();
    String getAnotherField();
}