Hibernate/SQL Average of a table with multiple relationships

Asked

Viewed 82 times

0

I have the following tables

TABLE 1
id
value
table 2

TABELA2
id
Tabela3

TABELA3
id

And with this, I would like to average TABLE 1, with WHERE in the parameters TABLE 3 with Hibernate/persistence, and my tables are objects.

It would look like this: SELECT AVG(value) FROM Tabela1 WHERE Tabela1.table2.Tabela3.id = N ?

java hibernate jpa persistence

  • These relationships are all @OneToOne or @ManyToOne?

  • I think that what you want to do is right. Have you tried it? If yes, there has been a mistake?

  • Is related to Manytoone

  • The last table is table 3 right? ._.

  • Your question is how to do this with a Hibernate or jpa(hql, criteria, Query, etc....)?

  • @Igorventurelli Yes

  • @Brunospy if it was a normal SQL, I would use the Inner Joins, and until today I had no need to select a third table and I would like to know how it is done, would put Inner Join, or only using reference of the normal object, like the Select simple.

Show 2 more comments

1 answer

1


whereas:

  • Tables are correctly mapped into classes;

  • The classes are called respectively, Tabela1, Tabela2 and Tabela3;

  • Class attributes are called tabela2 and tabela3:

So you can do:

select avg(t.valor) from Tabela1 t where t.tabela2.tabela3.id = {valor}
  • 1

    I’ll be testing :)

  • 1

    this.entityManager.createQuery("SELECT AVG(t.value) FROM Tabela1 t WHERE t.tabela2.Tabela3 = :table3_id"). setParameter("table3_id", variable). getSingleResult();

Browser other questions tagged java hibernate jpa persistence

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