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

[shiva]

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

[vlad]

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.

What you want is a DTO projection instead which does not require the FETCH directive.

[shiva]

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)

[vlad]

You forgot to add the code for namedquery.

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

[shiva]

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

}

[vlad]

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

[shiva]

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.

QueryImpl consuming more memory

What you see there is probably the objects that haven’t been collected by the JVM GC, but the proble might be that you are fetchcing more data than necessary. Try using DTO projections because they are more efficient both on the database and on the client side. More, if you have huge Result Sets but only require 10 entries, better use pagination.

[vlad]

Try with pagination first.

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

[Kapil_Sharma]

But if we don’t use fetch then N+1 query problem occurs. Is there any way that we can use fetch and as well as this problem can be solved.

[vlad]

You are allowed to use fetch to avoid N+1 query issues. This question was about using JOIN FETCH with a projection, which is not a good combination. Check out this article for more details about avoiding N+1 query issues.

[Kapil_Sharma]

I used fetch to avoid N+1 query but when i am using fetch and applying limit using pageable then i am getting this execption org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list . I am using specification and fetching using left outer join type.

[vlad]

I used fetch to avoid N+1 query

That’s only required if you fetch entities, not DTOs.

I am using specification and fetching using left outer join type.

What you want is a DTO projection instead which does not require the FETCH directive.

[Kapil_Sharma]

That’s only required if you fetch entities, not DTOs.

Yes I am fetching entities.

DTO Projection i cannot use as my column name for where condition changes based on condition. so, for this purpose, i used specification. In DTO projection, a column name cannot be a variable.

Can you please suggest something that i can use fetch and specification and this exception could be resolved.

[vlad]

If you read the entire thread carefully, you are going to find the solution.

[laxman20]

Hi, I read the threads, but I don’t see how they solve the issue.

I have a query that looks something like this:

SELECT NEW DTO(attribute1, attribute2, entity)
FROM Entity entity
LEFT JOIN FETCH entity.collection

That’s a very basic illustration of what I’m trying to do. Hibernate doesn’t allow this. If I remove the “fetch”, then I will run into the N+1 select problem when accessing dto.entity.collection.

[vlad]

I have a query that looks something like this:

SELECT NEW DTO(attribute1, attribute2, entity)
FROM Entity entity
LEFT JOIN FETCH entity.collection

That’s not a valid JPQL query.

You either select entities:

SELECT entity
FROM Entity entity
LEFT JOIN FETCH entity.collection

or a projection which wraps the Object[] into a DTO:

SELECT NEW DTO(attribute1, attribute2, entity.name, col.title)
FROM Entity entity
LEFT JOIN entity.collection col

You cannot combine fetching entities and DTOs.

[Kapil_Sharma]

My point is my column name is changing too.

SELECT NEW DTO(attribute1, attribute2, entity.name, col.title)
FROM Entity entity
LEFT JOIN entity.collection col where :columnName = :columnValue

but @param for columnName does not work. That’s why I used specification instead of projection. And in specification i tried root.join also which cause N+1 Query issue while root.fetch is causing this exception org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present

[vlad]

Then you need to use Criteria API if the filtering criteria is variable.