0

I want to display all the checklists that are not answered and not answered (response checklists is in the ResponsesCheckLists table) using the following parameters: idequipement and idMission.

@Query("SELECT check,resp,eq FROM Equipements eq INNER JOIN CheckLists check WHERE eq.idEquipements = check.equipements.idEquipements LEFT JOIN ResponsesCheckLists resp ON check.idCheckLists=resp.CheckLts.idCheckLists AND resp.Respmission.idMission = :idmiss WHERE eq.idEquipements = :idEqp ")
    public List<ResponsesCheckLists> ListCheckListsNonRepondu(@Param("idEqp") long idEqp, @Param("idmiss") long idmiss);

After running this query, I displays this error message: 

antlr.NoViableAltException: unexpected token: LEFT
------
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LEFT near line 1, column 148 [SELECT check,resp,eq FROM com.SSC.DAO.Entities.Equipements eq INNER JOIN CheckLists check WHERE eq.idEquipements = check.equipements.idEquipements LEFT JOIN ResponsesCheckLists resp ON check.idCheckLists=resp.CheckLts.idCheckLists AND resp.Respmission.idMission = :idmiss WHERE eq.idEquipements = :idEqp ]
------
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'responsesCheckListsRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.SSC.DAO.JPARepository.ResponsesCheckListsRepository.ListCheckListsNonRepondu(long,long)!

Edit1:

@Query("SELECT check,resp,eq FROM Equipements eq INNER JOIN CheckLists check ON eq.idEquipements = check.equipements.idEquipements"
            + " INNER JOIN ResponsesCheckLists resp ON check.idCheckLists=resp.CheckLts.idCheckLists AND resp.Respmission.idMission = :idmiss AND eq.idEquipements = :idEqp ")
    public List<ResponsesCheckLists> ListCheckListsNonRepondu(@Param("idEqp") long idEqp, @Param("idmiss") long idmiss);

Error of Edit1;

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode +-[IDENT] IdentNode: 'check' {originalText=check}

antlr.SemanticException: Path expected for join!

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.SSC.DAO.JPARepository.ResponsesCheckListsRepository.ListCheckListsNonRepondu(long,long)!

Edit2:

@Query("SELECT check , resp , eq FROM Equipements eq INNER JOIN CheckLists check ON eq.idEquipements = check.equipements.idEquipements"
            + " INNER JOIN ResponsesCheckLists resp ON check.idCheckLists=resp.CheckLts.idCheckLists AND resp.Respmission.idMission = :idmiss AND eq.idEquipements = :idEqp ")
    public List<ResponsesCheckLists> ListCheckListsNonRepondu(@Param("idEqp") long idEqp, @Param("idmiss") long idmiss);

Errors of Edit2:

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode +-[IDENT] IdentNode: 'check' {originalText=check} antlr.SemanticException: Path expected for join!

How to correct this query?

thank you in advance

Michael1
  • 253
  • 1
  • 6
  • 19

1 Answers1

1

You have a Spring annotation there (@Query) that specifies a JPQL query. Your JPQL query is supposed to follow the syntax highlighted in this link (and the JPA spec). Sadly you haven't followed that.

SELECT {result} FROM {from} WHERE {where} ...

Any "JOIN" has to go in the FROM clause. You already put one JOIN in the FROM clause, but for reasons only known to you, you decided to put another JOIN in the WHERE clause!! In fact you have 2 WHERE clauses in that crap.

It is impossible to tell you what your query should be because you don't post your entities, so we don't see what relations they have, or even what you are trying to achieve. We can only point out your error

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
  • I tried to modify the request according to the link. But I have another type of error. I updated the post. @Neil Stockton – Michael1 May 27 '17 at 13:33
  • As the link says, any JOIN has to be along a RELATION (because JPQL is a pseudo-object-oriented query language). As I said above, you don't post what entities/fields you have so only you know where there is a RELATION – Neil Stockton May 27 '17 at 14:06
  • The problem that I have a join of 3 tables in JPQL. I do not know how to ensure good joining them @Neil Stockton – Michael1 May 27 '17 at 14:15
  • A JOIN goes along a relation. There is no "good" or "bad" join, just a valid join, and only you know where your relations are so there is nothing more to say here. – Neil Stockton May 27 '17 at 17:13
  • To explain the result of the application. It is necessary to have all the checkLists that are answered and which are not yet answered according to idEquipment (for example: 1) and idMission (for example: 2) given by user. Table equipment; Contains idEquipment, Name, .. Table checkLists: contains idCheckLists, recommendation, idEquipment ResponseCheckLists: contains idResponseCheckLists, idCheckLists, date, Response, idMission. So, you have to make the jounture between the 3 tables. – Michael1 May 27 '17 at 17:24
  • One final time. JPQL and JPA resolves around entities/fields (relations) NOT tables. Your question is answered (why you got that error) – Neil Stockton May 27 '17 at 17:58
  • I do not know why I have different problems this for that reason I posted in this form to help me. I have the second modification of the code, thanks again Sir @Neil Stockton – Michael1 May 27 '17 at 18:10
  • You really aren't reading are you? I SAID ... JPQL uses CLASSES and FIELDS. YOU HAVEN'T POSTED YOUR ENTITIES. JPQL JOINING USES RELATIONS. You have addressed none of that. Consult a basic JPQL documentation (I gave you the link in my answer) – Neil Stockton May 28 '17 at 05:42