HQL Constructor With FETCH JOIN

Question

Assuming I have a class like:

public class Primary {
    private int id;
    private Secondary secondary;
    private String otherMember1;
    private float otherMember2;
    etc...
}

I'm trying to create a DTO projection with a constructor in HQL with a query like:

SELECT com.example.Primary(p.id, p.secondary) from Primary p LEFT JOIN FETCH p.secondary

However, I get an error message when attempting to do that:

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

However, I feel like I am selecting it, it's the second argument of the constructor. Most stack questions/answers I've seen tell me just to get rid of the FETCH but the issue is that I actually need it. I need the secondary object data and if I try to remove the FETCH the data is not retrieved or is retrieved later with terrible performance. Additionally, if I simply mark p.secondary as EAGER instead of LAZY in the relationship annotation the performance is atrocious as well.

I've also tried fetching through other means (Entity Graphs) to avoid using the FETCH keyword and it actually results in the same error message.

Is there some way to do a DTO of some kind where I can JOIN FETCH?

score 2 · Answer 1 · answered Jun 11 '20 at 14:13

Conclusion

After fiddling around with this for a long time I've come to the conclusion that you can not use a constructor for a class that is a persistence entity in the HQL and do this. There's something about the fetched entity being a constructor argument that makes it invisible to the parser as being part of the select.

Solution

While it seems like you can't directly create your desired object from the HQL query, you can get pretty close. I ended up changing my query to the following:

SELECT p.id as id, p.secondary as secondary from Primary p LEFT JOIN p.secondary

That along with using a ResultsTransformer like this:

// if you're using an entity manager like me, you need to get a Hibernate session from it first
org.hibernate.Session session = (org.hibernate.Session) entityManager.getDelegate();
org.hibernate.Query query = session.createQuery(hql)
    .setParameter("userId", userId)
    .setResultTransformer(Transformers.aliasToBean(Primary.class))
;

List<Primary> results = (List<Primary>) query.list();

Gets you your typed object of choice with joined/fetched data.

2 important points:

You can see I'm no longer using FETCH in my query. It seems like it's not needed for me anymore as the data is fetched anyway. I think this has to do with the results no longer being placed in a persistence entity class and therefore there are no LAZY annotations making HQL think the data doesn't need to be fetched in the first place so it's just there.
You need to as every property to have the exact name of the class member you want that piece of data to be placed in even if the property name you're selecting is already the same minus the 'p.' part Look at my example query again and you'll see that I want to put p.id in the id member of the Primary class, but even though the property name is already id after the . I still need to alias it using as.

score 0 · Answer 2 · answered Jun 12 '20 at 07:35

This is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A mapping for your model could look as simple as the following

@EntityView(Primary.class)
interface PrimaryView {
  @IdMapping
  int getId();
  SecondaryView getSecondary();
  String getOtherMember1();
  float getOtherMember2();
}
@EntityView(Secondary.class)
interface SecondaryView {
  @IdMapping
  int getId();
  String getSomeOtherMember();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

PrimaryView dto = entityViewManager.find(entityManager, PrimaryView.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

HQL Constructor With FETCH JOIN

2 Answers2

Conclusion

Solution