How to get multiselect results containing both properties and lists via JPA 2.1 in Java?

Question

The goal is to get the firstName, lastName, and the list of todos for an employee by using multiselect:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root emp = q.from(Employee.class);

q.multiselect(
    emp.get("firstName").alias("fname"),
    emp.get("lastName").alias("lname"),
    emp.get("toDoList").alias("toDoList")
).where(cb.equal(emp.get("id"), 12345));

List<Tuple> tuples = em.createQuery(q).getResultList();
Iterator<Tuple> iter = tuples.iterator();

while(iter.hasNext()){
    Tuple t = iter.next();
    //t.get("fName");     // returns String
    //t.get("lName");     // returns String
    //t.get("toDoList");  // returns String

    //?????
    //...

}

The toDoList is just a simple list of Strings. Let's assume employee 12345 has 4 todos. This means I get the following result set:

---------------------------------
| firstName | lastName | toDo   |
---------------------------------
| John      | Doe      | sleep  |
---------------------------------
| John      | Doe      | eat    |
---------------------------------
| John      | Doe      | play   |
---------------------------------
| John      | Doe      | laugh  |
---------------------------------

Now I'm looking for a smart way of creating an instance of ONE Employee and setting its firstName, lastName, and toDoList ONCE:

Employee employee = new Employee();
employee.setFirstName(...);
employee.setLastName(...);
employee.setToDoList(...); 

What would be the best approach? Thing would get even more complicated if I would add additional relationships (i.e. favoriteSongs).

Answer 1

Unfortunately, you would have to write code for every combination of your multiselect call to get the data from the tuples list, and that code can be very dirty.

There are major disadvantages getting relationships with one query:

• the java code for reading the data will get very dirty
• One query sound like good performance at first, but this will retrieve duplicate data for every fetched row

So I actually believe that executing separate queries to get the data is a better approach in many cases (like in my case). In other words, I will avoid messy code and use single selects instead. The resulting code for getting the projection's data is much better to maintain and much more readable. Here are some important links I found:

• SQL for JPQL with join fetch

SQL for JPQL with join fetch

A user may want every relationship loaded, but join fetching every relationship, in particular every ToMany relationships will lead to a huge join (outer joins at that), fetching a huge amount of duplicate data.

• Fetch Join in JPQL

2. Fetch Join in JPQL

And the main disadvantage is that we need to write additional code which executes the query. But it gets even worse, if the entity has multiple relations and we need to initialize different relations for different use cases. In this case we need to write a query for every required combination of fetch joined relations. This can become quite messy.