How to fetch the lowest price on an entity with Spring Data JPA on a JPARepository? (keywords or JPQL)

Question

How to fetch the lowest price on an entity with Spring Data JPA on a JPARepository? (keywords or JPQL)

I have the following entity in java using spring:

public class ItemVendedor implements Serializable{

    private static final long serialVersionUID = 1L;

    private ItemVendedorPK id = new ItemVendedorPK();
    private BigDecimal preco;
    private Boolean disponivel;
    private Date dt_insert;
    private Date dt_update;

    @EmbeddedId
    public ItemVendedorPK getId() {
        return id;
    }
...GETs AND SETs

This entity is bound to a JPARepository class as below:

@Repository
public interface ItemVendedorRepository extends JpaRepository<ItemVendedor,  ItemVendedorPK >, JpaSpecificationExecutor<ItemVendedor> {

}

What I want to create is a query that returns me an object of type ItemVendedor, but should fetch the lowest priced object.

Does the use of keywords in the repository allow me to use some function similar to the min() of SQL or JPQL? How could I do this using the keywords strategy?

I tried use @ Query in JPQL in respository, as below:

@Query("SELECT min(iv.preco) FROM ItemVendedor iv where (iv.id.produto.id = :produtoId) ")
Optional<ItemVendedor> findCestaFavorita( @Param(value = "produtoId") Long produto);

But in this approach always returns me the following error:

"message": "java.math.BigDecimal cannot be cast to com.eclodir.voucomprei.model.entity.ItemVendedor",

How to find the lowest price product?

0

jpa spring-data spring-jpa

Author: Gonzaga Neto, 2019-02-02

Source

1 answers

score 0 · Accepted Answer

I don't know much about JPA and Spring ecosystem in general, but the cast error is because it defined a function of type Optional<ItemVendedor> but in SQL it returns min(iv.preco) which is of type BigDecimal

So the correct one if you want to get the lowest value is:

@Query("SELECT min(iv.preco) FROM ItemVendedor iv where (iv.id.produto.id = :produtoId)")
BigDecimal findCestaFavorita(@Param(value = "produtoId") Long produto);

But if you want the row that contains the smallest value then, your SQL is incorrect, you should select the columns you want (or * for all) and sort downwards, as you want only the smallest (singular), too put a limit of 1:

@Query("SELECT * FROM ItemVendedor iv where (iv.id.produto.id = :produtoId) ORDER BY iv.preco DESC LIMIT 1")
Optional<ItemVendedor> findCestaFavorita(@Param(value = "produtoId") Long produto);