如何使用Hibernate的session.createSQLQuery（） [英] How to use JOIN using Hibernate's session.createSQLQuery()

问题描述

我有两个Entity（表） - Employee&项目。员工可以有多个项目。
项目表的CREATOR_ID字段指的是员工表的ID字段。 Employee实体不保留对Project的任何引用 - 但Project实体具有Employee的引用。

使用EntityManager以下查询可以正常工作 - $ / b
$ b

  entityManager.createQuery（
从EmployeeDTO e中选择e，ProjectDTO p
 +其中p.id =？1和pCreator .ID = e.id）; 
  

但是因为我有LAZY关联关系，所以我收到错误信息：

无法初始化代理 - 无
会话

如果我尝试从Employee实体访问Project信息。这是预期的，所以我使用Hibernate的会话来创建查询，如下所示。

 会话会话= HibernateUtil.getSessionFactory（）。的openSession（）; 
 org.hibernate.Query q = session.createSQLQuery（
SELECT E FROM EMPLOYEE_TAB E，PROJECT_TAB P WHERE P.ID =
 + projectId +AND P.CREATOR_ID = E.ID ）
 .addEntity（EmployeeDTO，EmployeeDTO.class）
 .addEntity（ProjectDTO，ProjectDTO.class）; 
  

但是我收到如下错误：列'E'或者不在FROM列表中的任何表中或者在连接规范中出现
，并且超出了连接规范的范围......

任何人都可以提出什么才是正确的JOIN语法案件？如果我使用（SELECT * FROM EMPLOYEE_TAB E，........） - 则会出现其他错误：

java.lang.ClassCastException：
[Ljava.lang.Object;不能转换为
com.im.server.dto.EmployeeDTO

。
$ $ b $ p

解决方案

您不需要使用本地SQL查询来预取 ProjectDTO 加载 EmployeeDTO 时。您的原始查询可以用更优雅的方式重写，如下所示：

 从EmployeeDTO中选择e加入e.projects p其中p。然后你可以添加一个 join fetch  

$ c $>子句来预取项目：



 从EmployeeDTO中选择不同的e e join e.projects p join fetch e。项目，其中p.id =？1 
  

另请参阅：

• 16.3。关联和连接
  

I have two Entity (tables) - Employee & Project. An Employee can have multiple Projects. Project table's CREATOR_ID field refers to Employee table's ID field. Employee entity does not maintain any reference for Project - but Project entity has a reference for Employee.

Using EntityManager following query works fine -

entityManager.createQuery(
    "select e from EmployeeDTO e, ProjectDTO p"
    + " where p.id = ?1 and p.creator.id=e.id");

But since I have the LAZY association relationship, I get error:

Could not initialize proxy - no Session

if I try to access Project info from Employee entity. This is expected and so I am using Hibernate's Session to create query as shown below.

Session session = HibernateUtil.getSessionFactory().openSession();
org.hibernate.Query q = session.createSQLQuery(
    "SELECT E FROM EMPLOYEE_TAB E, PROJECT_TAB P  WHERE P.ID = "
    + projectId + " AND P.CREATOR_ID = E.ID")
    .addEntity("EmployeeDTO ", EmployeeDTO.class)
    .addEntity("ProjectDTO", ProjectDTO.class);

But I get error like: "Column 'E' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification..."

Can anyone suggest what will be the right JOIN syntax for such case? If I use ("SELECT * FROM EMPLOYEE_TAB E, ........") - it gives other error:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.im.server.dto.EmployeeDTO

.

Thanks in advance.

解决方案

You don't need to use a native SQL query to prefetch ProjectDTOs when loading EmployeeDTO. Your original query can be rewritten in more elegant way as follows:

select e from EmployeeDTO e join e.projects p where p.id = ?1

Then you can add a join fetch clause in order to prefetch the projects:

select distinct e from EmployeeDTO e join e.projects p join fetch e.projects where p.id = ?1

See also:

• 16.3. Associations and joins

这篇关于如何使用Hibernate的session.createSQLQuery（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！