# How to convert an SQL query to HQL or JPQL

**URL:** <https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881>\
**Category:** Hibernate ORM\
**Created:** [December 13, 2018, 3:28pm UTC](https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881 "2018-12-13T15:28:36Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matteo\_Santangelo](https://avatars.discourse-cdn.com/v4/letter/m/87869e/32.png) [@Matteo\_Santangelo](https://discourse.hibernate.org/u/Matteo_Santangelo)\
**Post date:** [December 13, 2018, 3:28pm UTC](https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881/1 "2018-12-13T15:28:36Z")

</div>

I’m going crazy behind the hql, how do I convert this query into that language?

```auto
select n.*
from nominativo n
left join nominativo_istituzione ni on n.token_nominativo = ni.token_nominativo
left join associato_ente ae on ni.token_istituzione = ae.token_associato
left join adesione a on ae.token_ente = a.token_nominativo
where a.token_adesione = 1750011366

```

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [December 13, 2018, 4:56pm UTC](https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881/2 "2018-12-13T16:56:32Z")

</div>

First, you need to map the tables to entities. Then, the joins should be expressed as associations.

Once you do that, the HQL would look like this:

```auto
select n
from Nominativo n
left join n.istituzione i 
left join i.associatoEnte ae  
left join ae.adesione a
where a.tokenAdesione = :token

```

For more details about mapping entities, check out the [Hibernate User Guide](http://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#domain-model).

---

<div class="post-metadata">

**Author:** ![Matteo\_Santangelo](https://avatars.discourse-cdn.com/v4/letter/m/87869e/32.png) [@Matteo\_Santangelo](https://discourse.hibernate.org/u/Matteo_Santangelo)\
**Post date:** [December 14, 2018, 10:02am UTC](https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881/3 "2018-12-14T10:02:47Z")

</div>

> [@vlad](#):
>
> etails about mapping e

unfortunately the mapping is different and I can not change it.  
In NominativoIstituzione there is the Nominativo joinColumn.  
In Adesione there is the Nominativo JoinColumn.  
in associato ente is the following  
associatoEnte  
@ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, optional = false)  
@JoinColumn(name = “token\_associato”)  
private Nominativo associato;

```
@ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, optional = false)
@JoinColumn(name = "token_ente")
private Nominativo ente;

```

---

<div class="post-metadata">

**Author:** ![Matteo\_Santangelo](https://avatars.discourse-cdn.com/v4/letter/m/87869e/32.png) [@Matteo\_Santangelo](https://discourse.hibernate.org/u/Matteo_Santangelo)\
**Post date:** [December 14, 2018, 10:12am UTC](https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881/4 "2018-12-14T10:12:14Z")

</div>

unfortunately the mapping is different and I can not change it.  
In NominativoIstituzione there is the Nominativo joinColumn.  
In Adesione there is the Nominativo JoinColumn.  
in associato ente is the following  
associatoEnte

```auto
@ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, optional = false)
@JoinColumn(name = “token_associato”)
private Nominativo associato;

```

```auto
@ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE }, optional = false)
@JoinColumn(name = "token_ente")
private Nominativo ente;

```

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [December 14, 2018, 10:53am UTC](https://discourse.hibernate.org/t/how-to-convert-an-sql-query-to-hql-or-jpql/1881/5 "2018-12-14T10:53:46Z")

</div>

Without seeing the existing JPA entity mapping, I won’t be able to tell you if you can map that query to JPQL.