How to search for the lowest price in an entity with Spring Data JPA in a Jparepository? (Keywords or JPQL)

Question

I have the following java entity 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 linked 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 to me an object of type Itemvendedor, but should fetch the object of lower price.

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

I tried using @Query in JPQL in the 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 priced product?

Answer 1

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

So the correct thing 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 lowest value then your SQL is incorrect, you should select the columns you want (or * for all) and order in a descending fashion, as you just want to the smallest (singular), also place 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);