Convert SQL Query to Hibernate Criteria and Projections

Question

I have query from SQL :

select e.* 
from terminal_remote_deployment e
where id = (select top 1 e1.id
            from terminal_remote_deployment e1
            where e1.Terminal_info_id = e.Terminal_info_id
            order by e1.version desc
           );

I am trying to write it in HQL like this :

final StringBuilder hql = new StringBuilder();
            hql.append(" from TerminalRemoteDeployment e");
            hql.append(" where e.id = (Select TOP 1 e1.id from TerminalRemoteDeployment e1 where e1.terminalInfo.id = e.terminalInfo.id order by e1.version desc)");

            Query query = getEntityManager().createQuery(hql.toString());
            resultList = (List<TerminalRemoteDeployment>) query.getResultList();

I am getting errors with this approach. please help me write criteria in hibernate as I am very new to this.

You can't use `TOP`, because this is SQL Server exclusive and HQL not support that. The only way is create a exclusive `Query` and use the `setMaxResults` method. — Dherik, May 21 '18 at 12:14

Tanveer Singh Bhatia · Answer 1 · 2018-05-21T11:51:19.473

Where clause in inner Subquery of second Append. It should be terminal_Info_id instead of terminalInfo.id.

final StringBuilder hql = new StringBuilder();
        hql.append(" from TerminalRemoteDeployment e");
        hql.append(" where e.id = (Select TOP 1 id from TerminalRemoteDeployment e1 where e1.terminal_Info_id = e.terminal_Info_id order by e1.version desc)");

        Query query = getEntityManager().createQuery(hql.toString());
        resultList = (List<TerminalRemoteDeployment>) query.getResultList();

score 3 · Answer 2 · answered May 21 '18 at 15:45

3

As already told in comment use setMaxResult() method instead of TOP keyword as it's not supported by HQL.

Also in the sub-select you are using id without it's entity alias.

Change it to Select e1.id from TerminalRemoteDeployment e1

answered May 21 '18 at 15:45

Zeromus

4,472
8
32
40

Lukasz Szozda · Answer 3 · 2018-05-23T17:47:35.967

3

First of all I am not an expert on Hibernate. But your first query if SQL Server specific:

select e.* 
from terminal_remote_deployment e
where id = (select top 1 e1.id
            from terminal_remote_deployment e1
            where e1.Terminal_info_id = e.Terminal_info_id
            order by e1.version desc
           );

I would rewrite it using ROW_NUMBER(supported by MySQL 8.0/MariaDB/SQL Server/PostgreSQL/Oracle/H2/Derby/...) and then I would use named query:

SELECT *
FROM(SELECT e.*,
     ROW_NUMBER() OVER(PARTITION BY Terminal_info_id ORDER BY version desc) AS rn
     FROM terminal_remote_deployment e) sub
WHERE rn = 1;

edited May 23 '18 at 17:47

answered May 23 '18 at 16:19

Lukasz Szozda

162,964
23
234
275

1

This is not the exact answer to my question but some how the need to write the sub query in hibernate is less with your approach and its helping me to get rid of writing the sub query. I will accept the answer. thanks – Talib May 30 '18 at 06:23

score 1 · Answer 4 · answered May 30 '18 at 06:36

1

Are you trying to do this.

select e.* 
from  terminal_remote_deployment e1 JOIN 
      (select e.id, max(e.version)
      from terminal_remote_deployment e group by e.id) as a
on e.id = a.id

answered May 30 '18 at 06:36

Bharat Bhagat

381
1
5
14

Convert SQL Query to Hibernate Criteria and Projections

4 Answers4