Select JPA and Hibernate

Navigation
2

I have five entities (Quotation, Sector, Partner, Items and Leads), where the user will select a quotation and sector and the list of all items related to this sector will appear along with the items that have already been posted ( if any).

I used the following select to bring up the records: 

String jpql = "SELECT i "
            + "FROM Itens i "
            + "INNER JOIN i.setor s "
            + "LEFT JOIN i.itensLancados il "
            + "ON il.parceiro.codParceiro = 50 AND il.cotacao.codCotacao = 1 "
            + "WHERE s.codSetor = 1";

But because the EntityItems feature is EAGER, you are bringing me all the records in this table, ignoring the partner code restriction and quotation code.

I tried using fetch after LEFT JOIN, but then I can not use the ON clause, and if I try to throw the constraint from the ON clause to the WHERE it does not work because it will filter only the data in the Table Leads, not bringing all the items.

Would anyone have any tips on how to resolve this case?

Relationships

Quotation 

@OneToMany(mappedBy = "cotacao", fetch = FetchType.LAZY)
private List<ItensLancados> itensLancados;

@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name = "cotacao_parceiro", joinColumns = @JoinColumn(name = "cotacao_id"), inverseJoinColumns = @JoinColumn(name = "parceiro_id"))
private List<Parceiro> parceiros;

@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name = "cotacao_setor", joinColumns = @JoinColumn(name = "cotacao_id"), inverseJoinColumns = @JoinColumn(name = "setor_id"))
private List<Setor> setores;

Sector 

@OneToMany(mappedBy = "setor", fetch = FetchType.LAZY)
private List<Itens> itens;

@ManyToMany(mappedBy = "setores", fetch = FetchType.LAZY)
private List<Cotacao> cotacoes;

Partner 

@OneToMany(mappedBy = "parceiro", fetch = FetchType.LAZY)
private List<ItensLancados> itensLancados;

@ManyToMany(mappedBy = "parceiros", fetch = FetchType.LAZY)
private List<Cotacao> cotacoes;

Items 

@OneToMany(mappedBy = "itens", fetch = FetchType.LAZY)
private List<ItensLancados> itensLancados;

@ManyToOne
private Setor setor;

Quoted Items 

@Id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "cotacao_id")
private Cotacao cotacao;

@Id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "item_id")
private Itens itens;

@Id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parceiro_id")
private Parceiro parceiro;

The Select you would like to convert to JPQL is as follows: 

    SELECT *
FROM ITENSLANCADOS RIGHT JOIN ITENS
ON ITENSLANCADOS.ITEM_ID = ITENS.IDITENS
AND itenslancados.cotacao_id = 50
and itenslancados.parceiro_id = 1
WHERE setor_codsetor = 1

The SQL generated by JPA follows. 

select
    itens0_.idItens as idItens1_1_0_,
    itenslanca2_.cotacao_id as cotacao_1_2_1_,
    itenslanca2_.item_id as item_id2_2_1_,
    itenslanca2_.parceiro_id as parceiro3_2_1_,
    itens0_.codigoPlu as codigoPl2_1_0_,
    itens0_.descricaoItem as descrica3_1_0_,
    itens0_.eanItem as eanItem4_1_0_,
    itens0_.embalagem as embalage5_1_0_,
    itens0_.setor_codSetor as setor_co7_1_0_,
    itens0_.statusItem as statusIt6_1_0_,
    itenslanca2_.dataLancamento as dataLanc4_2_1_,
    itenslanca2_.qtdDigitada as qtdDigit5_2_1_,
    itenslanca2_.valorDigitado as valorDig6_2_1_,
    itenslanca2_.item_id as item_id2_1_0__,
    itenslanca2_.cotacao_id as cotacao_1_2_0__,
    itenslanca2_.item_id as item_id2_2_0__,
    itenslanca2_.parceiro_id as parceiro3_2_0__ 
from
    Itens itens0_ 
inner join
    Setor setor1_ 
        on itens0_.setor_codSetor=setor1_.codSetor 
left outer join
    ItensLancados itenslanca2_ 
        on itens0_.idItens=itenslanca2_.item_id 
left outer join
    ItensLancados itenslanca3_ 
        on itens0_.idItens=itenslanca3_.item_id 
inner join
    Parceiro parceiro4_ 
        on itenslanca3_.parceiro_id=parceiro4_.codParceiro 
        and (
            parceiro4_.codParceiro=1
        ) 
inner join
    Cotacao cotacao5_ 
        on itenslanca3_.cotacao_id=cotacao5_.codCotacao 
        and (
            cotacao5_.codCotacao=50
        ) 
where
    setor1_.codSetor=1
    
asked by anonymous 21.08.2015 / 04:36

2 answers

1

Let's look at the observations:

1st) INNER JOIN i.setor s

21.08.2015 / 13:58
1

Try the following: 

String jpql = "SELECT i "
            + "FROM Itens i "
            + "JOIN i.setor s "
            + "LEFT FETCH JOIN i.itensLancados il "
            + "LEFT JOIN i.itensLancados ilJoin "
            + "JOIN ilJoin.parceiro parceiro WITH parceiro.codParceiro = 50"
            + "JOIN ilJoin.cotacao cotacao WITH cotacao.codCotacao = 1"
            + "WHERE s.codSetor = 1";

I do not remember how Hibernate solves a query in which, within the ON clause, you apply other operations of JOIN ( il.parceiro and il.cotacao ), just like you did in the question. In fact, I did not even remember that JPQL supported ON ... I always used WITH (which is ON of JPQL / HQL)

    
21.08.2015 / 06:00
Better performance / performance: Smarty, Twig or some other? [closed] Datatable column filter asp net mvc5