020. JPQL - dkkahm/study-spring-jpa GitHub Wiki

Courses without Student

  • SQL
SELECT * FROM COURSE
WHERE COURSE.ID NOT IN
      (SELECT COURSE_ID FROM STUDENT_COURSE);
  • JQPL
    TypedQuery<Course> query = em.createQuery("Select c From Course c where c.students is empty", Course.class);

Courses with at least 2 students

    TypedQuery<Course> query = em.createQuery("Select c From Course c where size(c.students) >= 2", Course.class);

Order courses by number of students

    TypedQuery<Course> query = em.createQuery("Select c From Course c order by size(c.students)", Course.class);
  • descending
   TypedQuery<Course> query = em.createQuery("Select c From Course c order by size(c.students) desc", Course.class);

Students with passport in a certain pattern

    TypedQuery<Student> query = em.createQuery("Select s From Student s where s.passport.number like '%123%'", Student.class);

other operators

  • like
  • between 100 and 1000
  • is null
  • upper, lower, trim, length

JOIN

  • Select c, s from Course c JOIN c.students s
  • Do not return courses without students
        Query query = em.createQuery("Select c, s from Course c JOIN c.students s");
        List<Object[]> resultList = query.getResultList();
        logger.info("Result size is {}", resultList.size());
        for(Object[] result : resultList) {
            logger.info("{} {}", result[0], result[1]);
        }

LEFT JOIN

  • Select c, s from Course c LEFT JOIN c.students s
  • Return courses without students

CROSS JOIN

  • Select c, s from Course c, Student s
  • Return all courses X all students
⚠️ **GitHub.com Fallback** ⚠️