Doubt about JPQL

Question

I honestly have a certain difficulty with comics, and I need a JPQL that returns only one record. I have a Price entity and a Product entity. I need to bring the last registered price by the user. I put a date and an ID, I imagine it could be the last ID or even the last registered date (I have a date). But how do I do it ? My BD is MYSQL and I am using Hibernate, CDI and JSF.

Price list:

id | dateLanking | value | .... product_id |

public Preco findByIdProdutoUltimoPreco(Integer produtoId) {
    TypedQuery<Preco> query = em.createQuery(
            "????",
            Preco.class);
    query.setParameter("pid", produtoId);
    return query.getSingleResult();
}

Answer 1

To know the latest record based on id and in the parameter, just use:

• ORDER BY id DESC: sort records in descending order based on id
• LIMIT 1: limit to return only 1 record:
• WHERE produto_id = :pid: return records with specific product

Then your query would look like this:

SELECT id, dataLancamento, valor, produto_id FROM nameTable 
WHERE produto_id = produtoId ORDER BY id DESC limit 1

Adapting in your code would look like this:

public Preco findByIdProdutoUltimoPreco(Integer produtoId) {
    TypedQuery<Preco> query = em.createQuery(
            "SELECT id, dataLancamento, valor, produto_id FROM nameTable 
    WHERE produto_id = :pid ORDER BY id DESC limit 1",
            Preco.class);
    query.setParameter("pid", produtoId);
    return query.getSingleResult();
}