How to join three classes in JPQL?

Question

I have the following the classes:

@Entity
public class Equipements implements Serializable{

@Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
    private long idEquipements;

@OneToMany(mappedBy="equipements", cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    private Collection<CheckLists> checks;
}

@Entity
public class CheckLists implements Serializable{

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private long idCheckLists;

private String titreCheck;

    private String recommendation;

    @ManyToOne
    @JoinColumn(name="equipements_id")
    private Equipements equipements;

    @OneToMany(mappedBy="CheckLts", cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    private Collection <ResponsesCheckLists> ResponsesChecks;
}

@Entity
public class ResponsesCheckLists implements Serializable{
@Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private long idResponsesCHeck;

    @ManyToOne
    @JoinColumn(name="missionsId")
    private Missions Respmission;

    @ManyToOne
    @JoinColumn(name="checkLists_Id")
    private CheckLists CheckLts;
}

I'm trying to show the list of checkLists that are answered (the answer is in the "ResponsesCheckLists" class) and not answered yet (the rest of the list is in the "checkLists" class).

I have the following The SQL query:

SELECT * FROM equipements eq
LEFT JOIN check_lists checks
ON eq.id_equipements = checks.equipements_id 
LEFT JOIN responses_check_lists resp 
ON checks.id_check_lists = resp.check_lists_id
AND resp.missions_id = 15 
AND eq.id_equipements = 1 
ORDER BY checks.id_check_lists

I followed the documents on the internet but I do not get the correct transformation of my query in JPQL.

The following query displays only the checkLists that are answered in the class ResponsesCheckLists:

@Query(
            "SELECT checks, r "
            + " FROM CheckLists checks "
            + " inner join checks.ResponsesChecks r"
            + " WHERE checks.equipements.idEquipements = :idEqp "
            + " AND r.Respmission.idMission= :idmiss "
            + " ORDER BY checks.idCheckLists ASC"
        )

I know that the attributes of the query will be replaced by the attributes of the class. But how to join three classes at the same time.

Edit1:

@Query(
            "SELECT c, r "
            +" FROM CheckLists c"
            +" LEFT JOIN c.ResponsesChecks r"
            +" JOIN fetch c.equipements e"
            +" JOIN fetch r.Respmission m"
            +" WHERE e.idEquipements = :idEqp"
            +" AND m.idMission= :idmiss"
            +" ORDER BY c.idCheckLists ASC"
        )

This request does not display all the CheckLists of the "CheckLists" class and the checkLists that are answered (found in the "ResponsesCheckLists" class).

Edit2:

@Query(
    "SELECT e "
         +" FROM Equipements e"
         +" LEFT JOIN fetch e.checks c "
         +" LEFT JOIN fetch c.ResponsesChecks r"
         + " where e.idEquipements = :idEqp "
          + " and r.Respmission.idMission = :idmiss "
          + " order by c.idCheckLists asc"
     )

Error of edit 2:

1- Servlet.service() for servlet [dispatcherServlet] in context with path [/App] threw exception [Request processing failed; nested exception is org.springframework.http.converter.HttpMessageNotWritableException: Could not write content: Infinite recursion (StackOverflowError)

2-WARN 3456 --- [nio-8099-exec-3] .w.s.m.s.DefaultHandlerExceptionResolver : Handling of [org.springframework.http.converter.HttpMessageNotWritableException] resulted in Exceptionjava.lang.IllegalStateException: Cannot call sendError() after the response has been committed

Solution for error Edit2;

Add annotation @JsonIgnore

   @JsonIgnore 
    Public Set <CheckLists> getChecks () 
    { 
    Return checks;
     }

-> After the implementation of this solution the request (edit2) does not display the list of checklists of a Equipments.

Edit3:

@Query(
            "SELECT e "
                    +" FROM Equipements e , CheckLists c"
                    //+" LEFT JOIN fetch e.checks c "
                    +" LEFT JOIN c.ResponsesChecks r"
                    + " where e.idEquipements = :idEqp "
                    + " and r.Respmission.idMission = :idmiss "
                    + " order by c.idCheckLists asc"

     )

Resultat of Edit3:

[
{
"idEquipements": 1,
"nomEq": "Info",
"dateAjoutEq": "2017/04/09 20:37:58",
"dateModificationEq": "2017/04/09 20:48:40",
"checks": [] //133 items
},
{
"idEquipements": 1,
"nomEq": "Info",
"dateAjoutEq": "2017/04/09 20:37:58",
"dateModificationEq": "2017/04/09 20:48:40",
"checks": []//133 items
}

-I made a JPQL query to display list of checkLists that are answered (the answer is in the "ResponsesCheckLists" class) and not answered yet (the rest of the list is in the "checkLists" class). But my test shows a doubling of the Equipment object.

Thank you;

If you had followed JPA docs you would know that joins go across relations. For people to suggest what YOUR joins should be you need to post the entities too, showing the relations, and showing WHAT you have tried — Neil Stockton, May 29 '17 at 14:54

score 0 · Answer 1 · answered May 29 '17 at 21:52

0

     "SELECT e "
        +" FROM Equipements "
        +" LEFT JOIN fetch e.checks c "
        +" LEFT JOIN fetch c.ResponsesChecks r"
        +" WHERE ...."

This will return Equipments entity with the collections already fetched

answered May 29 '17 at 21:52

Zeromus

4,472
8
32
40

Thank you for your answer sir, but this displays the following problem: `Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.SSC.DAO.Entities.Equipements.checks, com.SSC.DAO.Entities.CheckLists.ResponsesChecks]` – Michael1 May 29 '17 at 22:08
That was new for me too... here are some different solution https://stackoverflow.com/questions/4334970/hibernate-cannot-simultaneously-fetch-multiple-bags – Zeromus May 29 '17 at 22:33
Thanks for the link, the problem is solved for join fetch. Concerning the request I tried to execute your proposal and it only displays the information of Equipments. – Michael1 May 29 '17 at 22:46
When you say display what do you mean? – Zeromus May 29 '17 at 22:48
The request you have given returns the object of the Equipment class : ` { "idEquipements": 1, "nomEq": "xd", "dateAjoutEq": "2017/04/09 20:37:58", "dateModificationEq": "2017/04/09 20:48:40" },` – Michael1 May 29 '17 at 22:51
Try to go in debug after the select and check the collection – Zeromus May 29 '17 at 22:53
How Sir? I did not understand ! – Michael1 May 29 '17 at 22:56
How to fix the error I defined in "error of edit2"? Thank you sir @ Zeromus – Michael1 May 30 '17 at 09:12
You should read the basics of [Hibernate](http://hibernate.org/orm/documentation/5.2/) (an implementation of JPA) so that you gain more comprehension on that issues. – joninx May 30 '17 at 09:14
I tried the "Edit3" query but it displays false results. How to fix the query results in JPQL? – Michael1 May 30 '17 at 13:33

How to join three classes in JPQL?

1 Answers1