JPA Criteria API join with subquery

Question

Say I have 3 classes

class Student {
  @Id
  private Long id;
  private String name;

  @ManyToOne(mappedBy = "student")
  private Set<TestResult> testResults;

  @OneToOne(mappedBy = "student")
  private StudentCard studentCard;
}

class TestResult {
  @Id
  private Long id;

  @ManyToOne
  @JoinColumn(name = "studentId")
  private Student student;

  private String mark;
}

class StudentCard {
  @Id
  private Long id;

  @OneToOne
  @JoinColumn(name = "studentId")
  private Student student;

  private String cardNumber;
}

Now I want a query that'll select student id, name, List of test results mark, student card number. I can't really map the result into List, so I've created a DTO to select the mark_str as string with a , delimeter, and split them later into a List

class StudentInfoDTO {
  private Long id;
  private String name;
  private String markStr;
  private String studentCardNumber;
}

My code so far:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<StudentInfoDTO> cr = cb.createQuery(StudentInfoDTO.class);
Root<Student> studentRoot = cr.from(Student.class);

Join testResultJoin = studentRoot.join("testResults", JoinType.LEFT);
Join studentCardJoin = studentRoot.join("studentCard", JoinType.LEFT);

Expression<String> getMarkFunction = cb.function(
                "string_agg",
                String.class,
                testResultJoin.get("mark"),
                cb.literal(",")
        );

cr.select(
  cb.construct(
    StudentInfoDTO.class,
    studentRoot.get("id"),
    studentRoot.get("name"),
    getMarkFunction,
    studentCardJoin.get("cardNumber")
  )
);
cr.groupBy(studentRoot.get("id"),studentRoot.get("name"), studentCardJoin.get("cardNumber"));

List<StudentInfoDTO> results = entityManager.createQuery(cr).getResultList();

This works, but the string_agg function returns multiple repeated results, and more selection column I add the more I have to add in groupBy. Let's just say if I add a couple more classes and each class has 3 more field the query becomes really messy. Is there a way to generate this sql using JPA criteria API?

select s.id, s.name, trj.mark_str, sc.card_number
from students s
left join (select tr.student_id, string_agg(tr.mark, ',') as mark_str
           from test_results tr
           group by tr.student_id
           ) trj
on s.id = trj.student_id
left join student_cards sc
on s.id = sc.student_id;

As far as I can tell I can't join with subquery since JPA subquery doesn't allow multiple columns selection. Is there another way to do this?

Answer 1

To achieve the desired result without using subqueries in JPA Criteria API and without getting repeated results, you can use a combination of multiple joins and fetches along with a group by clause. Here's how you can modify your code to achieve that:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<StudentInfoDTO> cr = cb.createQuery(StudentInfoDTO.class);
Root<Student> studentRoot = cr.from(Student.class);

Join<Student, TestResult> testResultJoin = studentRoot.join("testResults", JoinType.LEFT);
Join<Student, StudentCard> studentCardJoin = studentRoot.join("studentCard", JoinType.LEFT);

// Construct a map to hold student id and concatenated marks
Map<Long, String> marksMap = new HashMap<>();

// Fetch the necessary data and populate the marksMap
List<Object[]> resultList = entityManager.createQuery(cr.multiselect(
        studentRoot.get("id"),
        studentRoot.get("name"),
        testResultJoin.get("mark"),
        studentCardJoin.get("cardNumber")
    ).getResultList();

for (Object[] result : resultList) {
    Long studentId = (Long) result[0];
    String mark = (String) result[2];
    
    if (!marksMap.containsKey(studentId)) {
        marksMap.put(studentId, mark);
    } else {
        String existingMarks = marksMap.get(studentId);
        marksMap.put(studentId, existingMarks + "," + mark);
    }
}

// Construct StudentInfoDTO objects using the populated marksMap
List<StudentInfoDTO> results = new ArrayList<>();
for (Object[] result : resultList) {
    Long studentId = (Long) result[0];
    String name = (String) result[1];
    String cardNumber = (String) result[3];
    
    String markStr = marksMap.get(studentId);
    
    StudentInfoDTO studentInfoDTO = new StudentInfoDTO(studentId, name, markStr, cardNumber);
    results.add(studentInfoDTO);
}

return results;

This approach avoids using subqueries and ensures that you don't get repeated results. It first fetches the necessary data and populates a map with student id and concatenated marks. Then, it constructs the StudentInfoDTO objects using the populated map.