Custom Queries with Java JPA returning "NULL"

Question

I'm currently having some first time experience with JAVA SpringBoot for a university project, in which we are using a H2 in-mem database for storage. For statistics i want to use a custom query to get data - which works perfectly fine. However when getting to work with the returned data i have quite the issues and need help.

My query returns 4 columns (name, avgTasteRating,avgDesignRating,avgPriceRating). To parse the information within Java i built a custom Entity (WineDTO) consisting of the same columns. However only the name is parsed - the rest is casted as "null" for some reason..

Does anyone have any ideas for possible errors or fixes? Greatly appreciated! Thanks you so much!!!

WineRepository.java

    @Query(value =
        "SELECT w.NAME, AVG(r.taste_rating) AS avgTasteRating, AVG(r.design_rating) AS avgDesignRating,AVG(r.price_rating) AS avgPriceRating" +
                "            FROM WINE w JOIN RATING r ON w.ID = r.wine_id" +
                "            GROUP BY w.ID, w.NAME" +
                "            ORDER BY avgTasteRating DESC", nativeQuery = true)
List<WineDTO> findWinesWithAverageRatings();

WineDTO.java

@Entity
public class WineDTO {

private String name;
private String avgTasteRating;
private String avgDesignRating;
private String avgPriceRating;
@Id
private Long id;

// constructer, set and get to follow

Answer 1

Use Spring Data JPA Projection Interface instead of an Entity, you need to define a projection interface that includes the fields you want to select. Here's an example:

Projection Interface:

public interface WineDTO {

    String getName();
    Double getAvgTasteRating();
    Double getAvgDesignRating();
    Double getAvgPriceRating();
    Long getId();
}

In this interface, the method names should match the names of the fields you are selecting in your query.

JPA Repository:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface WineRepository extends JpaRepository<Wine, Long> {

    @Query(value =
            "SELECT w.NAME, AVG(r.taste_rating) AS avgTasteRating, AVG(r.design_rating) AS avgDesignRating, AVG(r.price_rating) AS avgPriceRating" +
                    " FROM WINE w JOIN RATING r ON w.ID = r.wine_id" +
                    " GROUP BY w.ID, w.NAME" +
                    " ORDER BY avgTasteRating DESC", nativeQuery = true)
    List<WineDTO> findWinesWithAverageRatings();
}

Ensure that the method in the repository returns a List of the defined projection interface (WineDTO in this case).

Remember that Spring Data JPA will map the results of the native query to the fields in your projection interface based on their names, so the names in the interface should match the aliases in your native query.

If you want to read more on Spring Data JPA Projections, check the official doc here - https://docs.spring.io/spring-data/jpa/reference/repositories/projections.html