Robert Field Robert Field - 6 months ago 38
SQL Question

How to convert my native SQL query into a JPQL query

I'm trying to convert a native SQL query into a JPQL query. Presented first are the JPA entities and the repository interface:


LearnerActivity


@Entity
@Table(name = "learner_activity")
public class LearnerActivity implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToMany
@JoinTable(name = "learner_activity_unit",
joinColumns = @JoinColumn(name="learner_activitys_id", referencedColumnName="ID"),
inverseJoinColumns = @JoinColumn(name="units_id", referencedColumnName="ID"))
private Set<Unit> units = new HashSet<>();

@ManyToMany
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@JoinTable(name = "learner_activity_performance_criteria",
joinColumns = @JoinColumn(name="learner_activitys_id", referencedColumnName="ID"),
inverseJoinColumns = @JoinColumn(name="performance_criterias_id", referencedColumnName="ID"))
private Set<PerformanceCriteria> performanceCriterias = new HashSet<>();
}



LearnerJobOnSiteChecklistSectionItem


@Entity
@Table(name = "learner_job_on_site_checklist_section_item")
public class LearnerJobOnSiteChecklistSectionItem implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToOne
@JoinColumn(name = "learner_activity_id")
private LearnerActivity learnerActivity;
}



LearnerPortfolioPerformanceCriteriaAchievement


@Entity
@Table(name = "learner_portfolio_performance_criteria_achievement")
public class LearnerPortfolioPerformanceCriteriaAchievement implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToOne
@JoinColumn(name = "performance_criteria_id")
private PerformanceCriteria performanceCriteria;

@ManyToMany
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@JoinTable(name = "learner_portfolio_performance_criteria_achievement_learner_job_on_site_checklist_section_item",
joinColumns = @JoinColumn(name="learner_portfolio_performance_criteria_achievements_id", referencedColumnName="ID"),
inverseJoinColumns = @JoinColumn(name="learner_job_on_site_checklist_section_items_id", referencedColumnName="ID"))
private Set<LearnerJobOnSiteChecklistSectionItem> learnerJobOnSiteChecklistSectionItems = new HashSet<>();
}


I'm looking to get all
LearnerPortfolioPerformanceCriteriaAchievement
s which have the same
PerformanceCriteria
as a given
LearnerJobOnSiteChecklistSectionItem
's
Activity
.


LearnerPortfolioPerformanceCriteriaAchievementRepository


public interface LearnerPortfolioPerformanceCriteriaAchievementRepository extends JpaRepository<LearnerPortfolioPerformanceCriteriaAchievement,Long> {
@Query("select distinct lppca from LearnerJobOnSiteChecklistSectionItem si inner join si.learnerActivity la inner join LearnerPortfolioPerformanceCriteriaAchievement lppca where lppca.performanceCriteria member of la.performanceCriterias and si.id =:sectionItemId")
public List<LearnerPortfolioPerformanceCriteriaAchievement> findForSectionItem(@Param("sectionItemId") Long sectionItemId);
}



Native SQL


SELECT
a.id
FROM
learner_job_on_site_checklist_section_item AS i
JOIN
learner_activity_performance_criteria AS c
ON
c.learned_activitys_id = i.learned_activitys_id
INNER JOIN
learner_portfolio_performance_criteria_achievement AS a
ON
a.performance_criteria_id = c.performance_criterias_id
where i.id = 2


Understanding that I can replace my SQL
ON
with
WHERE
clauses, my best JPQL attempt so far is this:

SELECT
DISTINCT lppca
FROM
LearnerJobOnSiteChecklistSectionItem si
INNER JOIN
si.learnerActivity la
INNER JOIN
LearnerPortfolioPerformanceCriteriaAchievement lppca
WHERE
lppca.performanceCriteria MEMBER OF la.performanceCriterias
AND si.id =:sectionItemId


However, running this JPQL throws the following exception:

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode
\-[IDENT] IdentNode: 'lppca' {originalText=lppca}

Answer

The following query will work:

SELECT
  a
FROM
  LearnerPortfolioPerformanceCriteriaAchievement  a
  , LearnerActivity                               v
  , LearnerJobOnSiteChecklistSectionItem          i
WHERE
  i.id                                            = :sectionItemId
AND i.learnerActivity                             = v
AND a.performanceCriteria                         MEMBER OF v.performanceCriterias
AND i                                             MEMBER OF a.learnerJobOnSiteChecklistSectionItems

Have created a sample application to test the query (I have used shorter and simpler class names to make it easy to read).

Comments