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.

Answer 1

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();

Answer 2

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