How to use left Join fetch in Criteriabuilder by returning a dto

Asked

Viewed 20 times

1

in the implementation below, in which return a dto built with the entity Lancamento, in which it has uni-directional relationship with category. To avoid the n+1 problem I tried using left Join fetch between Lancamento and category in the following ways:

root.fetch("categoria", JoinType.LEFT);

Or

Fetch<Lancamento, Categoria> categoriaFetch = root.fetch("categoria", JoinType.LEFT);

In the two above situations, the same error has arisen:

"...query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.sisworks.sisbase.model.Lancamento.categoria,tableName=categoria,tableAlias=categoria1_,origin=lancamento lancamento0_,columns={lancamento0_.id_categoria,className=com.sisworks.sisbase.model.Categoria}}]"

This is the entity Lancamento:

@Entity
public class Lancamento implements Serializable {

private OffsetDateTime dataVencimento;

private BigDecimal valor;

//uni-directional many-to-one association to Categoria
@ManyToOne
@JoinColumn(name="id_categoria", nullable=false)
private Categoria categoria;

This is the criteria method:

public List<LancamentoEstatisticaCategoria> porCategoria(LocalDate mesReferencia) {
    CriteriaBuilder criteriaBuilder = manager.getCriteriaBuilder();
    CriteriaQuery<LancamentoEstatisticaCategoria> criteriaQuery = criteriaBuilder
            .createQuery(LancamentoEstatisticaCategoria.class);
    Root<Lancamento> root = criteriaQuery.from(Lancamento.class);
    criteriaQuery.select(criteriaBuilder.construct(LancamentoEstatisticaCategoria.class, 
            root.get(Lancamento_.categoria),
            criteriaBuilder.sum(root.get(Lancamento_.valor))));
    
    LocalDate primeiroDia = mesReferencia.withDayOfMonth(1);
    LocalDate ultimoDia = mesReferencia.withDayOfMonth(mesReferencia.lengthOfMonth());
    
    criteriaQuery.where(
            criteriaBuilder.greaterThanOrEqualTo(root.get(Lancamento_.dataVencimento),
                    primeiroDia),
            criteriaBuilder.lessThanOrEqualTo(root.get(Lancamento_.dataVencimento),
                    ultimoDia));
    criteriaQuery.groupBy(root.get(Lancamento_.categoria));
    
    TypedQuery<LancamentoEstatisticaCategoria> typedQuery = manager.createQuery(criteriaQuery);
    return typedQuery.getResultList();
}
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.