Criteriabuilder

Question

Good evening. Please, I would like to ask you for help for the code below. It returns the total value (total values, saved in the database), but I need to return by month. I tried everything I knew, but I couldn’t solve the problem. I thank you in advance for the help.

EntityManagerFactory emf = Persistence.createEntityManagerFactory("belezaPU");
    EntityManager em = emf.createEntityManager();

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<BigDecimal> criteriaQuery = builder.createQuery(BigDecimal.class);

    Root<Venda> venda = criteriaQuery.from(Venda.class);
    criteriaQuery.select(builder.sum(venda.<BigDecimal> get("precoTotal")));

    TypedQuery<BigDecimal> query = em.createQuery(criteriaQuery);
    BigDecimal totalVendas = query.getSingleResult();

    System.out.println("A soma das vendas é: R$" + totalVendas);

Answer 1

Use the method between of the interface CriteriaBuildertesting the first expression to see if it is between the second and third expression.

In the example I use your code to sum sales in April. Only significant change in your code is the highlighted snippet:

EntityManagerFactory emf = Persistence.createEntityManagerFactory("belezaPU");
EntityManager em = emf.createEntityManager();

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<BigDecimal> criteriaQuery = builder.createQuery(BigDecimal.class);

Root<Venda> venda = criteriaQuery.from(Venda.class);


// ***********************************************************************
Date dataInicial = new SimpleDateFormat("dd/MM/yyyy").parse("01/04/2019");
Date dataFinal= new Date();
criteriaQuery.select(builder.sum(venda.<BigDecimal>get("precoTotal"))).where(builder.between(venda.<Date>get("horario"), dataInicial, dataFinal)); 
// ***********************************************************************

TypedQuery<BigDecimal> query = em.createQuery(criteriaQuery);
BigDecimal totalVendas = query.getSingleResult();

System.out.println("A soma das vendas em abril é: R$" + totalVendas);