Select JPA and Hibernate

Question

I have five entities (Quotation, Sector, Partner, Items and Items), 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 launched (if they have).

I used the following select to bring 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 entity Itensreleased as EAGER, is bringing me all the records of this table, ignoring the restriction of partner code and quotation code.

I tried to fetch after the LEFT JOIN, but then I can’t use the ON clause, and if I try to play the restriction of the ON class to the WHERE it doesn’t work, it will filter only the data of the table Itenslancados, not bringing all the items.

Would anyone have any tips on how to solve 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;

Points

@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

Follow the SQL generated by JPA.

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

Answer 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 can’t remember how Hibernate solves a query in which, inside the clause ON you apply other operations of JOIN (il.parceiro and il.cotacao), as you asked in the question. Actually, I didn’t even remember that JPQL supported ON... always used the WITH (which is the ONof JPQL/HQL)

Answer 2

Let’s go to the remarks:

1st) INNER JOIN i.setor s << this JOIN is unnecessary

When you map the tables and the relationships between them in Hibernate, it already performs these joins automatically in your querys.

The JOINs are needed when we are seeking arrays, in your case the i.itensLancados

2nd) Do not use EAGER, use the LAZY

With EAGER Hibernate will always find the list you mapped before you even filter it in the query. With LAZY you have more control over your operations, as you only do how and when you want.

3rd) To search arrays using HQL you must use the LEFT JOIN FETCH or JOIN FETCH

To the Hibernate, it’s like a SELECT separate in which it seeks the elements of the array and fill it for you

---

Let’s go to the query:

From what I understand:

• A sector has a list of items and a list of quotes.
• A quote has a list of items

Then your query should search by sector: so it will bring the items of that sector, you filter the quotation list to fetch the quotation you are looking for and within the quotation you will have the items launched.

Correct?

SELECT s
  FROM Setor s
  INNER JOIN FETCH s.cotacoes cotacoes
  INNER JOIN FETCH cotacoes.parceiros parceiros= 50
  WHERE 1=1 
    AND s.codSetor = 1
    AND cotacoes.codCotacao = 1
    AND parceiros.codParceiro 

So you will have a Sector with the items of that sector, and a "quotation list" (which in case will have only the quotation with code = 1 inside), and in that quotation you will have the items

For reported error caused by Multiple bags, read these 2 references:

guj

devmedia