How to use JOIN using Hibernate's session.createSQLQuery()
- by javauser71
Hi All,
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 maintains a list of Project.
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.
