CODEWITHSUNDEEP
javasqlhibernatejpa
Anshu
Anshu

Reputation: 295

How to join an entity with custom select using CriteriaBuilder

I have a scenario where I need to get the row with maximum date. The SQL query for this would be

SELECT c.* 
FROM course c 
INNER JOIN 
(SELECT moduleId ,MAX(endDate) AS max_date
   FROM course
   WHERE moduleId = 12345
   GROUP BY moduleId
) customSelect
ON customSelect.moduleId = c.moduleId AND c.endDate = customSelect.max_date
WHERE c.moduleId = 12345

I need to convert this query to JPA using CriteriaBuilder.Since this isn't a direct entity join, rather a selection join to root entity, I'm having trouble to figure out how to join the custom select part to the root entity Course with below syntax:

   CriteriaBuilder builder = session.getCriteriaBuilder();
   CriteriaQuery<Course> criteriaQuery = builder.createQuery(Course.class);
   Root<Course> root = criteriaQuery.from(Course.class);

And then how to root.join(JoinType.INNER) to join customSelect part?

If someone can show some pointers to the syntax of of joining the root to a custom selection, that would be great.

Upvotes: 0

Views: 1301

Answers (1)

frank
frank

Reputation: 1027

Try this

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Course> criteriaQuery = builder.createQuery(Course.class);
Root<Course> root = criteriaQuery.from(Course.class);

criteriaQuery.select(root).where(cb.equal(root.get(Course_.moduleId), 12345));
criteriaQuery.orderBy(cb.desc(r.get(Course_.endDate)));

TypedQuery<Course> query = em.createQuery(criteriaQuery);
query.setMaxResult(1);
Course result = query.getSingleResult();

Upvotes: 1

Related Questions