Get fields of spring jpa interface projection

Question

I have this stored procedure that I am calling in spring jpa repository and I am using interface based projection.

Whenever I try to call the interface projection method i get this error

Invoked method public abstract java.lang.Long ConfirmationDTO.memberID() is no accessor method!

Here is my projection Interface

public interface ConfirmationDTO {
       Long memberID();
       LocalDate dateEntry();
}

and the DAO

@Query(value=" CALL get_confirmation(:startDate) ", nativeQuery=true)
List<ConfirmationDTO> getConfirmation(LocalDate startDate);

Is it possible to get the field values from the interface projection ?

Please don't use an `Optional` for collecetions? It will always return an empty collection when there is nothing to query, so `Optional` is overkill. — M. Deinum, Oct 01 '21 at 07:47

score 1 · Accepted Answer · edited Oct 15 '21 at 06:45

I found another SO thread which uses Tuple, this helped me to achieve the goal of my above question.

how-to-map-sql-native-query-result-into-dto-in-spring-jpa-repository

Here is the sample code from that thread :

    @Repository
    public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {
    
   @Query(value = "SELECT stock_akhir.product_id AS productId, stock_akhir.product_code AS productCode, SUM(stock_akhir.qty) as stockAkhir "
    + "FROM book_stock stock_akhir "
    + "where warehouse_code = (:warehouseCode) "
    + "AND product_code IN (:productCodes) "
    + "GROUP BY product_id, product_code, warehouse_id, warehouse_code", nativeQuery = true)
List findStockAkhirPerProductIn(@Param("warehouseCode") String warehouseCode, @Param("productCodes") Set productCode); }

and them map the Tuple in the service:

public List<StockTotalResponseDto> findStocktotal() {
List<Tuple> stockTotalTuples = stockRepository.findStocktotal();

List<StockTotalResponseDto> stockTotalDto = stockTotalTuples.stream()
        .map(t -> new StockTotalResponseDto(
                t.get(0, String.class), 
                t.get(1, String.class), 
                t.get(2, BigInteger.class)
                ))
        .collect(Collectors.toList());

return stockTotalDto;
}

score 1 · Answer 2 · answered Oct 15 '21 at 07:19

Let me try to explain how you can easily do this.

  public class Confirmation {
        private Long memberId;
        private LocalDate dateEntry;
        //add other fields
        //provide getters and setters

    }
    //tuple
    public inteface ConfirmationTuple {
        Long getMemberId ();
        LocalDate getDateEntry ();

    }
        //Your repository
    @Query(value = " CALL get_confirmation(:startDate) ", nativeQuery = true)
    List<ConfirmationTuple> getConfirmation (LocalDate startDate);

Spring will do the rest for you. To get the memberId from the first tuple, all you do is

yourDAO.getConfirmation(startDate).get(0).getMemberId();

The catch here is the get methods in your tuple must correspond to the field names being returned by your query in your repository. For example, if your query is returning the following columns [memberName,myDate] your Tuple interface must have getMemberName and getMyDate() for these values to be assigned.

What does the concrete class "Confirmation" do? – Fancy Oct 15 '21 at 08:21 — Fancy, Oct 15 '21 at 08:21
@Fancy, that is the model or entity class. – eunelyky Oct 15 '21 at 08:27 — eunelyky, Oct 15 '21 at 08:27

score 0 · Answer 3 · answered Oct 01 '21 at 07:16

You could create an implementation component and it would be autowired, but it's not recommended to annotate DTO classes.

The easiest way is to turn your interface into a class.

At the end of the day it's just a DTO, it have no logic, and on tests you can mock it as you wish just filling the properties.

I don't see the point on your DTO being an interface, unless a Class somewhere is implementing more than one interface and this one is among them.

If this is the case, I would rethink the implementation - e.g. implement TheOtherInterface extend Person.

Get fields of spring jpa interface projection

3 Answers3