problem with translation in Hibernate when select max(column

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?

CodePudding user response：

I think you need to use an alias for MAX(amount):

@Query(value = "SELECT brand, MAX(amount) as amount FROM cars GROUP BY brand ORDER BY amount DESC", nativeQuery = true)

CodePudding user response：

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()));;