Doubts about JPQL

Question

Doubts about JPQL

Navigation

#1 by (2 votes)

1

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

Price table:

id | dataLancamento | value | .... product_id |

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

sql jsf hibernate mysqli cdi

asked by anonymous 19.08.2017 / 01:03

1 answer

Why is this replace wrong? Field validation via jQuery correctly

score 2 · Answer 1

To know the last record based on the id and the parameter, it would be enough to 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 the 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();
}