I am having an invalid path issue with Hibernate and Spring JPA Criteria API. I have the following error:
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: ‘generatedAlias4.applicationId’ [select generatedAlias0 from ie.ul.ethics.scieng.applications.models.applications.Application as generatedAlias0 where :param0 in (select generatedAlias1.username from ie.ul.ethics.scieng.applications.models.applications.SubmittedApplication as generatedAlias2 inner join generatedAlias2.assignedCommitteeMembers as generatedAlias3 inner join generatedAlias3.user as generatedAlias1 where generatedAlias3.applicationId=generatedAlias4.applicationId)
Basically, the alias generatedAlias4 is presumably an alias that was meant to appear from a JOIN performed somewhere but the alias is not defined anywhere in the SQL leading to the error.
The SQL I am trying to emulate is:
SELECT a FROM SubmittedApplication a WHERE ‘test’ IN(SELECT m.username FROM SubmittedApplication a1 JOIN AssignedCommitteeMember assigned JOIN User WHERE a.applicationId = a1.applicationId);
I understand that might not be correct HQL (quickly wrote it to give a rough idea) but I am using the CriteriaQuery and CriteriaBuilder API to dynamically create the query.
The code creating the query is as follows:
CriteriaQuery<Object> query = criteriaBuilder.createQuery();
Root<SubmittedApplication> applicationRoot = query.from(SubmittedApplication.class);
Subquery<String> subQuery = query.subquery(String.class);
Root<SubmittedApplication> subApplication = subQuery.from(SubmittedApplication.class);
Root<SubmittedApplication> correlated = subQuery.correlate(applicationRoot);
Join<?, ?> assignedJoin = subApplication.joinList("assignedCommitteeMembers");
Join<?, ?> assignedUserJoin = assignedJoin.join("user");
subQuery.select(assignedUserJoin.get("username"));
subQuery.where(criteriaBuilder.equal(assignedJoin.get("applicationId"), correlated.get("applicationId")));
query.select(applicationRoot)
.where(criteriaBuilder.in(criteriaBuilder.literal(criteria.getValue())).value(subQuery));
return query.getRestriction();
It returns a Predicate for use in a Specification to search using a repository extending the JpaSpecificationExecutor interface. I used correlate to correlate the application root into the subquery, which theoretically means the last of the subquery where statement should be where generatedAlias3.applicationId=generatedAlias0.applicationId and not generatedAlias3.applicationId=generatedAlias4.applicationId
The query is meant to ask, find all Applications where the username ‘test’ exists in the list of AssignedCommitteeMembers. The SubmittedApplication class is a subclass of the Application class (a class that represents an application form being created on the system) that contains a list with OneToMany mapping of these AssignedCommitteeMembers. The first I noticed, selects from the base Application class. Shouldn’t it be selected from the SubmittedApplication class? Could this be the issue? select generatedAlias0 from ie.ul.ethics.scieng.applications.models.applications.Application as generatedAlias0 instead of select generatedAlias0 from ie.ul.ethics.scieng.applications.models.applications.SubmittedApplication as generatedAlias0
The entity for the SubmittedApplication class (with irrelevant properties stripped. The applicationId field is contained in the superclass Application. It is a String ID separate to the database ID used for application management):
@Entity
public class SubmittedApplication extends Application {
/**
* The list of assigned committee members
*/
@OneToMany(cascade = CascadeType.ALL)
protected List<AssignedCommitteeMember> assignedCommitteeMembers;
.. other attributes
}
The AssignedCommitteeMember class is here:
@Entity
public class AssignedCommitteeMember {
/**
* The database ID of this object
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* The ID of the application the member is assigned to
*/
private String applicationId;
/**
* The committee member that is assigned
*/
@OneToOne
private User user;
/**
* Determine if the committee member has finished their review
*/
private boolean finishReview;
I hope I gave enough detail. If more information is needed I am happy to give it. Any help is appreciated
