Doubt with Joins in HQL query

Asked

Viewed 103 times

1

I have the following problem when conducting a bank consultation with HQL

The table tabela1 has approximately 20000 records.

The other tables have "N" records.

The table tabela1 has relationships with other tables tabela2, tabela3 and tabela4.

When executing this command below I expected the return value to be the value equal to the number of records in the tabela 1, but I’m doing something wrong with the JOINS because the command returns me a number greater than 200000 ;

select distinct count(t.id)
from tabela1 t  
left join t.tabela2 t2
left join t.tabela3 t3
left join t.tabela4 t4 
where t2.campoInteiro >-1 ;

The tables are like this:

@OneToMany(cascade = CascadeType.ALL)
 List<Tabela2> tabela2;
 @OneToMany(cascade = CascadeType.ALL)
 List<Tabela3> tabela3;
 @OneToMany(cascade = CascadeType.ALL)
 List<Tabela4> tabela4;
hql

  • Managed to solve?

2 answers

0

You need to define how tables connect. As it stands, all the lines of t1 join those of the t2, t3 and t4.

select distinct count(t.id)
from tabela1 t  
left join t.tabela2 t2 on t2.fk_t1 = t1.pk_t1
left join t.tabela3 t3 on t3.fk_t2 = t2.pk_t2
left join t.tabela4 t4 on t4.fk_t3 = t3.pk_t3
where t2.campoInteiro > -1 ;

  • When I do a hql this way I still need to reference the pk with fk? ex: select distinct Count(t.id) from Tabela1 t left Join t.tabela2 t2

  • The HQL does not work this way it does not recognize the comparison t1.table2.id =t2.id

  • For that link is really wrong, has no need of the on, Join would be done "automatically".. but you need to ensure that there is a relationship between the tables.

  • Gave an exception : 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,classAlia@OneToMany(Cascade = Cascadetype.ALL)

  • My mapping looks like this : List<Table2> table2; @Onetomany(Cascade = Cascadetype.ALL) List<Table3> Tabela3;

  • put the table mapping in the question, to see if it helps

Show 1 more comment

0

In fact, there was a lack, (good practices) in the declaration of the relationship annotations (@oneToMany) the definition of the type of loading of the lists (t2, T3, etc.) when loaded to t1 in which it is related, not to compromise the performance of the query. Would look like this:

@OneToMany(fetch=FetchType.LAZY, cascade = CascadeType.ALL)
List<Tabela2> tabela2;
 @OneToMany(fetch=FetchType.LAZY, cascade = CascadeType.ALL)
 List<Tabela3> tabela3;
 @OneToMany(fetch=FetchType.LAZY, cascade = CascadeType.ALL)
 List<Tabela4> tabela4;

And in the query one should initialize these "Lazy", thus staying: 

select distinct count(t.id)
from tabela1 t  
left join fetch t.tabela2 t2
left join fetch t.tabela3 t3
left join fetch t.tabela4 t4 
where t2.campoInteiro >-1 ;

Browser other questions tagged hql

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