[FIXED] problem with translation in Hibernate when select max(column_name)

Issue

I've got a database table Cars, each row represent a different car model, has different id, brand, model_name and amount. I'd like to write a query using Hibernate to get the number of most popular cars by brand, example - having in DB 5 Volkswagen Polo, 3 Volkswagen Tiguan, 4 Skoda Octavia, 8 Skoda Rapid, I'd like to have a result: Volkswagen : 5 Skoda : 8

That's my query:

@Query(value = "SELECT brand, MAX(amount) FROM cars GROUP BY brand ORDER BY amount DESC", nativeQuery = true)
List<Car> getAmountOfMostPopularCarsByBrand();

And interface Car looks like this:

interface Car {
    String getBrand();
    Long getAmount();
}

The query works fine in IntelliJ, it brings the expected results, however when I try to use it in application:

List<Car> cars = getAmountOfMostPopularCarsByBrand()

I get the list of objects with good "brand" field, but with null "amount" field, it's just not getting mapped (translated) correctly. Any idea how to fix it?

Solution

Solved the issue by mapping it by myself, so the query method returns :

@Query(value = "SELECT brand, MAX(amount) as amount FROM cars GROUP BY brand ORDER BY amount DESC", nativeQuery = true)
List<Object> getAmountOfMostPopularCarsByBrand();

And then I can do:

Collection cars = getAmountOfMostPopularCarsByBrand()
.stream()
.map(c -> return new Car((String)c[0], (long)c[1]))
.collect(Collectors.toList()));;

Answered By - Rolf
Answer Checked By - David Marino (JavaFixing Volunteer)