Query specified join fetching, but the owner of the fetched association was not present in the select list


#1
I am trying to execute a DTO projection and observe below error while loading this query. Please help on this issue.

_**org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list**_

SELECT new EmployeeDTO(e.obj1,e.obj2,e.obj3,e.startDate,e.endDate) 
FROM Employee e 
left outer join fetch e.obj1  
left outer join fetch e.obj2 
left outer join fetch e.obj3
where
e.startDate <= :date

QueryImpl consuming more memory
#2

Just remove the fetch keyword and it will work.

The fetch directive tells Hibernate to include the association in the SELECT clause, but you don;t want that.


#3

Thanks Vlad. It works now. But, I see a different error as below

ERROR: Cannot create TypedQuery for query with more than one return

I followed this article - https://vladmihalcea.com/hibernate-query-cache-dto-projection/

If I use this way, it works.

List<EmployeeDTO[]> employeedto = em.createNamedQuery(namedquery, EmployeeDTO[].class)

But, I would like to use as

List employeedto = em.createNamedQuery(namedquery, EmployeeDTO.class)


#4

You forgot to add the code for namedquery.

Most likely, you selected something else besides the DTO constructor result.


#5
Sorry, named query is here.

select new EmployeeDTO(e.obj1,e.obj2,e.obj3,e.startDate,e.endDate) 
FROM Employee e 
left outer join fetch e.obj1  
left outer join fetch e.obj2 
left outer join fetch e.obj3
where
e.startDate <= :date

DTO Constructor:


public EmployeeDTO(Object obj1, Object obj2,Object obj3,Date startDate, Date endDate) {
		this.obj1= obj1;
		this.obj2= obj2;
		this.obj3= obj3;
		this.startDate = startDate;
		this.endDate = endDate;		
	}
	

Here is the entity

public class Employee implements Serializable {

	@Temporal(TemporalType.DATE)
	private Date endDate;
	
		@Temporal(TemporalType.DATE)
	private Date startDate;

	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name = "XYZ")
	private Object1 obj1;

	
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name = "ABX")
	private Object2 obj2;

	
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name = "ABC")
	private Object3 obj3

}



#6

You can use @NamedQuery with DTO projections via JPA constructor result as demonstrated by this test case.

However, you don’t have a DTO projection here. You try to fetch entities: obj1, obj2, …

If you do that, Hibernate will probably pass the entity identifier, not the entire entity.

More, the EmployeeDTO is identical with the Employee entity, so what is the point of using a DTO?

Just use the Employee entity if you need all associations:

SELECT e
FROM Employee e 
left outer join fetch e.obj1  
left outer join fetch e.obj2 
left outer join fetch e.obj3
where
e.startDate <= :date

#7

Vlad, I could do the same to fetch Employee entities as suggested, however, when I do this I see the memory issue as mentioned in this post. Each query fetch will retrieve nearly 20-30K results. As also, I have a requirement where the fetched Employee List will be iterated so that all the child entity objects will in turned be mapped to their respective DTO’s. So I thought I can refine the code so that I can still manage with one DTO.


#8

Try with pagination first.

Then, for the DTO, you have to select just the columns needed by the UI.


#9

I also got this error many times but iCloud Support has helped me a lot in solving the error. I think you should also ask them. I have tried removing the fetch and it worked surprisingly.


#10

That’s basically the solution.