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
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.
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)
You forgot to add the code for namedquery.
Most likely, you selected something else besides the DTO constructor result.
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
}
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
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.
Try with pagination first.
Then, for the DTO, you have to select just the columns needed by the UI.
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.
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.
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.
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.
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.
If you read the entire thread carefully, you are going to find the solution.
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.
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.
2 Likes
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
Then you need to use Criteria API if the filtering criteria is variable.