What is the best way to Retrieve List from reposit

Question

There is class name DailyElectricity which is in DTO package and it contain max , min, sum , average with getter and setter

public class DailyElectricity implements Serializable {

  private static final long serialVersionUID = 3605549122072628877L;


  private LocalDate date;

  private Long sum;

  private Double average;

  private Long min;

  private Long max;


}

there is interface which its job is to get data from database

@RestResource(exported = false)
public interface HourlyElectricityRepository 
    extends PagingAndSortingRepository<HourlyElectricity,Long> {
  Page<HourlyElectricity> findAllByPanelIdOrderByReadingAtDesc(Long panelId,Pageable pageable);


  @Query("SELECT max(a.generatedElectricity), sum(a.generatedElectricity),min(a.generatedElectricity),max(a.generatedElectricity)  from HourlyElectricity a where DATE_FORMAT(reading_at,'%Y-%m-%d')=DATE_FORMAT(CURDATE()-1,'%Y-%m-%d') and  panel_id=:panelId")
  List<DailyElectricity> getStaticsDailyElectricity(@Param("panelId")Long panelId);

}

it complie without any exception but when i call API it give

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.techtrial.dto.DailyElectricity]

it can not covert to dto class

Answer 1

If you are using a class-based projections (DTO) they must contain a constructor. Try to add it to your DTO.

But it's better to use Lombok to avoid boilerplate code:

@Value
public class DailyElectricity {
  private LocalDate date;
  private Long sum;
  private Double average;
  private Long min;
  private Long max;
}

Another way is to use interface-based projections:

public interface DailyElectricity {
  LocalDate getDate();
  Long getSum();
  Double getAverage();
  Long getMin();
  Long getMax();
}

IMO it's better to use them because they are simpler and they have some advantages (see the provided manual).

Note that a good practice is to use aliases in queries when you are using projections. They must match with correspondent field names/getters in the projection, for example:

"select max(a.generatedElectricity) as max, ..."

UPDATED

Unfortunately in Spring Boot 2.0+ the class-based projections don't work as expected (unlike SB 1.5+ - see may working demo). Until this bug will be fixed we can use DTO constructor in the query.

UPDATED 2

I was mistaken - when we are using class-based projections with a custom query we must use its constructor regardless of the version of Spring Boot.

Answer 2

The issue is that there is no way for spring to figure out how to convert the result of your query to the custom object DailyElectricity you are expecting from; in order to make this mapping possible you need to do two things:

1. create a constructor to so you can create a new object and initialize it through the value resulted from each row of you query:

   public DailyElectricity (Long max,Long sum,Long min,Double average){    
       this.sum=sum;
       this.average=average;
       this.min=min;
       this.max=max;   
   }
   

2. then use the following structure for your query in HourlyElectricityRepository

   @Query("SELECT new com.example.DailyElectricity( max(a.generatedElectricity), sum(a.generatedElectricity),min(a.generatedElectricity),avg(a.generatedElectricity))  from HourlyElectricity a where DATE_FORMAT(reading_at,'%Y-%m-%d')=DATE_FORMAT(CURDATE()-1,'%Y-%m-%d') and  panel_id=:panelId")
      List<DailyElectricity> getStaticsDailyElectricity(@Param("panelId")Long panelId);
   

   • Please notice the package name I have used in the Query (com.example.DailyElectricity) and make sure you are using the right package name corresponding to you project before testing.