I am writing a JPQL query (with Hibernate as my JPA provider) to get a Company object and a few of its associations. This works great with my "simple" ManyToMany associations, for example:
@Entity @Table(name = "company") @NamedQueries({ @NamedQuery( name = "Company.profile.view.byId", query = "SELECT c " + "FROM Company AS c " + "INNER JOIN FETCH c.city AS city " + <-- @ManyToOne "LEFT JOIN FETCH c.acknowledgements " + <-- @ManyToMany "LEFT JOIN FETCH c.industries " + <-- @ManyToMany "WHERE c.id = :companyId" ) }) public class Company { ... }
Hibernate creates one query to retrieve the above, which is good. However, my Company object also has a many-to-many relationship with the data stored in the staging table, which is why they are compared as @OneToMany and @ManyToOne associations between the three objects.
Company <- CompanyService → Service
These are the three objects that I have in my code. Thus, a Company instance has a set of CompanyService objects, each of which is associated with a Service instance. I hope this makes sense - otherwise, please check the source code at the end of the question.
Now I would like to receive services for this company by changing the above request. I read in advance that JPA does not allow nesting attachments or even nicknames for joins, but some JPA providers support it, so I tried my luck with Hibernate. I tried to modify the query as such:
@Entity @Table(name = "company") @NamedQueries({ @NamedQuery( name = "Company.profile.view.byId", query = "SELECT c " + "FROM Company AS c " + "INNER JOIN FETCH c.city AS city " + "LEFT JOIN FETCH c.acknowledgements " + "LEFT JOIN FETCH c.industries " + "LEFT JOIN FETCH c.companyServices AS companyService " + "LEFT JOIN FETCH companyService.service AS service " + "WHERE c.id = :companyId" ) }) public class Company { ... }
Now, instead of creating a single request, Hibernate creates the following requests:
#1 select ... from company company0_ inner join City city1_ on company0_.postal_code = city1_.postal_code [...] left outer join company_service companyser6_ on company0_.id = companyser6_.company_id left outer join service service7_ on companyser6_.service_id = service7_.id where company0_.id = ?
Request # 1 I refused the irrelevant associations, because they are in order. It seems to display all the data I need, including services and data of intermediate entities ( CompanyService ).
Request # 2 This request simply retrieves the company from the database and its City . The city association is looking forward to life, but a request is still being made, even if I change it to a lazy choice. So honestly, I don’t know what this request is for.
Request # 3 + Request # 4 These requests look for Service instances based on the identifier, presumably based on the identifiers of the services received in request # 1. I do not see the need for this request because this data has already been loaded into Query # 1 (also as the data from Query # 2 has already been loaded into Query # 1). In addition, this approach is clearly not sufficiently scaled if the company has many services.
The strange thing is that it seems that query number 1 does what I want, or at least it retrieves the data I need. I just don’t know why Hibernate creates request # 2, # 3 and # 4. Therefore, I have the following questions:
- Why is Hibernate creating request # 2, # 3 and # 4? And can I avoid this?
- Does Hibernate support nesting, even if JPA does not? If so, how do I do this in my case?
- Is this behavior normal, or is it because what I'm trying to do is simply not supported, and therefore am getting strange results? This seems odd because request # 1 looks great.
Any error pointers or alternative solutions to achieve what I want will be greatly appreciated. Below is my code (excluding getters and setters). Thank you very much in advance!
Company
@Entity @Table(name = "company") @NamedQueries({ @NamedQuery( name = "Company.profile.view.byId", query = "SELECT c " + "FROM Company AS c " + "INNER JOIN FETCH c.city AS city " + "LEFT JOIN FETCH c.acknowledgements " + "LEFT JOIN FETCH c.industries " + "LEFT JOIN FETCH c.companyServices AS companyService " + "LEFT JOIN FETCH companyService.service AS service " + "WHERE c.id = :companyId" ) }) public class Company { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column private int id; // ... @ManyToOne(fetch = FetchType.EAGER, targetEntity = City.class, optional = false) @JoinColumn(name = "postal_code") private City city; @ManyToMany(fetch = FetchType.LAZY) @JoinTable(name = "company_acknowledgement", joinColumns = @JoinColumn(name = "company_id"), inverseJoinColumns = @JoinColumn(name = "acknowledgement_id")) private Set<Acknowledgement> acknowledgements; @ManyToMany(fetch = FetchType.LAZY) @JoinTable(name = "company_industry", joinColumns = @JoinColumn(name = "company_id"), inverseJoinColumns = @JoinColumn(name = "industry_id")) private Set<Industry> industries; @OneToMany(fetch = FetchType.LAZY, mappedBy = "company") private Set<CompanyService> companyServices; }
CompanyService Object
@Entity @Table(name = "company_service") @IdClass(CompanyServicePK.class) public class CompanyService implements Serializable { @Id @ManyToOne(targetEntity = Company.class) @JoinColumn(name = "company_id") private Company company; @Id @ManyToOne(targetEntity = Service.class) @JoinColumn(name = "service_id") private Service service; @Column private String description; }
Service object
@Entity @Table(name = "service") public class Service { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column private int id; @Column(length = 50, nullable = false) private String name; @Column(name = "default_description", nullable = false) private String defaultDescription; }
Data retrieval
public Company fetchTestCompany() { TypedQuery<Company> query = this.getEntityManager().createNamedQuery("Company.profile.view.byId", Company.class); query.setParameter("companyId", 123); return query.getSingleResult(); }