1

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.

javauser71
  • 4,979
  • 10
  • 27
  • 29

2 Answers2

1

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:

axtavt
  • 239,438
  • 41
  • 511
  • 482
  • I am getting error: "Syntax error: Encountered "where" at line 1, column 52" for both cases. For first case my Query was: "SELECT DISTINCT P FROM ProjectDTO P join P.creator where P.id = 1". The "creator" is a member of "ProjectDTO" class of type: "EmployeeDTO". – javauser71 Jan 16 '11 at 18:18
  • My mistake ! I was using "session.createSQLQuery(...)" while I should have used "session.createQuery(...)". Thanks for the right suggestion. Cheers, – javauser71 Jan 16 '11 at 19:12
0

Your SQL starts with SELECT E from EMPLOYEE E ..., do you mean SELECT E.*, P.* from EMPLOYEE E ...?

Andrew Skirrow
  • 3,402
  • 18
  • 41