Convert Query Result to Object

Question

I have one JPARepository like this:

public interface MenetlevelRepository extends JpaRepository<Menetlevel,Long> {

    @Query(
            value = "select YEAR(datum) as \"ev\",MONTH(datum) as \"ho\", munkagep_id as \"gepid\", SUM(tevekenysegora) as \"sumtev\" from menetlevel group by munkagep_id, YEAR(datum), MONTH(datum)",
            nativeQuery = true
    )
    Collection<MenetlevelStat> getRendetzettMenetlevel();



}

I must convert result to MenetlevelStat class like this:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class MenetlevelStat {

    int ev;
    int ho;
    Long gepid;
    Double sumtev;

}

When I run my code I have some error: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [...... model.MenetlevelStat]

Why is it not working?

I would advise a simple spring tutorial. Just search for one. There are many to find. So you can learn more. — Constantin Beer, Aug 25 '19 at 09:45

score 0 · Answer 1 · answered Aug 25 '19 at 09:54

You should take advantage of a feature called projection interface available in Spring Data JPA. You have to define your interface like this.

public interface MenetlevelStat {

    int getEv();
    int getHo;
    Long gepId;
    ...

}

If you have to stick to Lombok then per documentation:

You can dramatically simplify the code for a DTO by using Project Lombok, which provides an @Value annotation.

Which in your case would end up like this:

@Value
public class MenetlevelStat {

    int ev;
    int ho;
    Long gepid;
    Double sumtev;

}

When annotation is used then:

Fields are private final by default, and the class exposes a constructor that takes all fields and automatically gets equals(…) and hashCode() methods implemented.

score 0 · Accepted Answer · answered Aug 25 '19 at 11:02

0

You can directly convert entites to dtos. Here is the code:

@Repository
public interface LogRepository extends JpaRepository<Log, Long> {
    @Query("SELECT l FROM Log AS l")
    List<Log> findAll();

    @Query("SELECT new test.pckg.dto.LogDTO(l.message, l.date) FROM Log l WHERE l.level = :level")
    List<LogDTO> fetchLogsByLevelAsDTO(@Param("level") String level);
}

and the dto class:

   package test.pckg.dto

   public class LogDTO {
        private String message;
        private LocalDateTime date;
        public LogDTO(String message, LocalDateTime date){
            this.message = message;
            this.date = date;
        }
        //getters and setters
    }

hope it helps.

answered Aug 25 '19 at 11:02

ali4j

522
3
15

happy it helped :) , that's quite easy, just declare another dto for the group by result and change the query using group by. take a look at this thread: https://stackoverflow.com/questions/36328063/how-to-return-a-custom-object-from-a-spring-data-jpa-group-by-query – ali4j Aug 25 '19 at 11:59
that is: localDateTime.getYear() – ali4j Aug 25 '19 at 12:04
I need it in JPQL query :) – Langosfun Aug 25 '19 at 12:08
you can call a function to extract year from date (such as YEAR() in mysql), but I think this will couple your code to your RDBMS, so I suggest you to get it from the DTO. Also you have to change date in dto to another type (such as Integer or String). – ali4j Aug 25 '19 at 12:15

Convert Query Result to Object

2 Answers2