如何将SQL查询转换为HQL [英] how do i convert SQL query to HQL

问题描述

public class EmployeeUserMap {

    @ManyToOne
    @JoinColumn(name = "EMPLOYEEID", unique = true)
    protected Employee employee;

    @ManyToOne
    @JoinColumn(name="USERID")
    private User user;
}

public class RoleUserMap  {


    @ManyToOne
    @JoinColumn(name = "ROLEID")
    private Role role;


    @ManyToOne
    @JoinColumn(name = "USERID")
    private User user;


}

下面是我的SQL查询.

Below is my SQL query.

select e.id ,e.firstname from employee_user_map o
          left join role_user_map ru on ru.userid = o.userid
          left join employee e on e.id = o.employeeid
          where  ru.roleid =  (select r.id from role r  where r.code='Role_Merchant')

我尝试过的HQL查询.

 SELECT new com.erp.core.repository.value.LookupValue(o.id,o.firstName,o.firstName) FROM EmployeeUserMap o
            LEFT JOIN o.user =
            WHERE /*@{SYSTEM_CRITERIA}*/1=1 AND (UPPER(o.firstName) LIKE UPPER(CONCAT('%',:searchParam,'%')))

推荐答案

假设您拥有正确的映射，则应将hql查询形成为:

Assuming you have your mappings right, you should form the hql query as:

休眠版本<5.1.0:

Hibernate version < 5.1.0:

  select new   com.erp.core.repository.value.LookupValue(e.id,e.firstName) 
      from EmployeeUserMap eu
         , RoleUserMap ru
         , Employee e
         , User u
         , Role r
      where  r.code ='Role_Merchant'
          and eu.user = u
          and ru.user = u
          and ru.role =r
          and eu.employee = e 

不要认为您这里确实需要子查询.

dont think you really need the subquery here..

更新:对于HIbernate 5.1.0+版本，可以使用不相关的实体联接，因此在您的情况下:

Update: for version 5.1.0+ of HIbernate it is possible to use unrelated entity joins, so in your case:

  select new com.erp.core.repository.value.LookupValue(e.id,e.firstName) 
  from EmployeeUserMap o
    left join o.user u
    left join o.employee e
    left join RoleUserMap ru on ru.user.id = u.id
    left join ru.role r
  where  r.code ='Role_Merchant'

这篇关于如何将SQL查询转换为HQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！