java 如何使用 Hibernate 的 session.createSQLQuery() 使用 JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4695094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use JOIN using Hibernate's session.createSQLQuery()
提问by javauser71
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.
我有两个实体（表）-员工和项目。一个员工可以有多个项目。Project 表的 CREATOR_ID 字段指的是 Employee 表的 ID 字段。Employee 实体不维护任何对 Project 的引用——但 Project 实体有一个对 Employee 的引用。
Using EntityManager following query works fine -
使用 EntityManager 以下查询工作正常 -
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:
但由于我有 LAZY 关联关系，我得到错误：
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.
如果我尝试从员工实体访问项目信息。这是预期的，所以我使用 Hibernate 的会话来创建查询，如下所示。
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..."
但我收到如下错误：“列 'E' 要么不在 FROM 列表中的任何表中，要么出现在连接规范中并且超出了连接规范的范围......”
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:
谁能建议这种情况下正确的 JOIN 语法是什么？如果我使用("SELECT * FROM EMPLOYEE_TAB E, ........")- 它会给出其他错误：
java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.im.server.dto.EmployeeDTO
java.lang.ClassCastException: [Ljava.lang.Object; 不能转换为 com.im.server.dto.EmployeeDTO
.
.
Thanks in advance.
提前致谢。
采纳答案by axtavt
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:
您不需要ProjectDTO在加载时使用原生 SQL 查询来预取s EmployeeDTO。您的原始查询可以以更优雅的方式重写，如下所示：
select e from EmployeeDTO e join e.projects p where p.id = ?1
Then you can add a join fetchclause in order to prefetch the projects:
然后你可以添加一个join fetch子句来预取项目：
select distinct e from EmployeeDTO e join e.projects p join fetch e.projects where p.id = ?1
See also:
也可以看看：
回答by Andy Skirrow
Your SQL starts with SELECT E from EMPLOYEE E ..., do you mean SELECT E.*, P.* from EMPLOYEE E ...?
你的 SQL 以 开头SELECT E from EMPLOYEE E ...，你的意思是SELECT E.*, P.* from EMPLOYEE E ...？
