JPA use IN clause with objects

Question

I am facing a problem usign JPA, and more specifically using a IN clause.

The best way is, I think, to show you my code :

@NamedQuery(name = "Commande.findCustom", query = "SELECT DISTINCT [myFields] "
            + "FROM Commande c WHERE "
            + "[SomeCriterias] AND "
            + "c.ID IN (SELECT t.ID FROM SubTable t "
            + "WHERE t.IDX IN :param) AND [otherCriterias]"),

I then get an error from MySQL :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.ID FROM SubTable t1 WHERE (t1.IDX IN (168)'

I am looking for a response but can't find anything ...

I tied to delete a IN clause, the problem is still the same (so it's not a double IN use problem)

my :param is a List of objects, that i got by using the Object.find() method. As you can see, it returns the ID (168). But I can't seem to find the problem ...

Any help would be greatly appreciated, thank you

EDIT : Full query

@NamedQuery(name = "Commande.findCustom", query = "SELECT DISTINCT c.idChargement, c.libelle, "
            + "c.codeTransporteur, c.reference, c.dateCreation, c.dateChargementPrevu, "
            + "c.dateValidationChargement, c.dateLivraisonPrevue, c.codeDestinataire, "
            + "c.raisonSocialeDestinataire, c.adresseDestinataire, c.codePostalDestinataire, "
            + "c.villeDestinataire, c.paysDestinataire, c.contactDestinataire, "
            + "c.telephoneDestinataire, c.mailDestinataire, c.poidsCommande, c.nombreColis, "
            + "c.nombreUniteManutention, c.typeUniteManutention, c.prendreRDV, c.commentaires "
            + "FROM Commande c WHERE "
            + "c.idChargement = :idChargement AND c.codeTransporteur = :codeTransporteur AND "
            + "(c.dateCreation BETWEEN :dateDebut AND :dateFin) AND "
            + "c.idDernierStatut IN (SELECT l.idListeStatutsCommande FROM Listestatutscommande l "
            + "WHERE l.idStatut IN :idStatut) AND c.raisonSocialeDestinataire = :raisonSociale AND "
            + "c.adresseDestinataire = :adresseDestinataire AND c.codeDestinataire = :codeDestinataire "
            + "AND c.codePostalDestinataire = :codePostal AND c.villeDestinataire = :villeDestinataire "
            + "AND c.paysDestinataire = :codePays")

And the Error Message

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.idListeStatutsCommande FROM listestatutscommande t1 WHERE (t1.IdStatut IN (168)'

@PrasadKharkar No I'm not, I checked the problem with the parenthesis, it's not that too ... — , Apr 01 '15 at 08:39
I would say that something is wrong with `.ID` not with `IN`clause — Antoniossss, Apr 01 '15 at 08:39
@Antoniossss I agree, but then what ? As you cann see in the error, the IDs are there — , Apr 01 '15 at 08:40
I have just tested response message of MYSQL, and in case of error "near" is beginning at the point of error. Don't know what is wrong tough. — Antoniossss, Apr 01 '15 at 08:43
could you please post full query that you are executing? It seems you have kept placeholder in the question instead of real query — Prasad Kharkar, Apr 01 '15 at 08:43
@Antoniossss oh, that's very helpful, I am going to investigate on that, thank you — , Apr 01 '15 at 08:43
@trichetriche also update your error message to correspond to the full query. — Antoniossss, Apr 01 '15 at 08:47
is there any possibility that `idListeStatutsCommande` is misstyped? — Antoniossss, Apr 01 '15 at 08:50
@Antoniossss I'm sorry, here you go. And no, I copy them directly from the class — , Apr 01 '15 at 08:51
@Antoniossss as you said earlier, I think it should throw a jpql error message if it was misspelled and not sql error. — Prasad Kharkar, Apr 01 '15 at 08:54

Antoniossss · Answer 1 · 2015-04-01T08:50:16.173

1

As you are getting error from MYSQL not Hibernate, you could try to find out what query actually is generated. To do this, use proxy invoker like p6spy and then everything should be clear. Check p6spy site. When you do, try to invoke such generated SQL yourself and try to fix it. I used such method when I had some troubles using JPA's joins fetches and stuff. Very helpfull in diagnosing such problems.

edited Apr 01 '15 at 08:50

answered Apr 01 '15 at 08:45

Antoniossss

31,590
6
57
99

even I had to follow the same approach while learning jpql. Good suggestion. Upvote from e. – Prasad Kharkar Apr 01 '15 at 08:51

score 0 · Accepted Answer · answered Apr 01 '15 at 09:37

0

Ok so I found the problem, and then the answer. Thanks @Antoniossss, the fact is that I was looking at the wrong part of the query.

The error was here : c.idDernierStatut IN ...

The fact is that this part is a foreign key. And when you want to search on it, you have to consider it as an object. So the correct form is c.idDernierStatut.idListeStatutsCommande IN to get the ID.

Thank you to both of you for your time anyway !

answered Apr 01 '15 at 09:37

good that you could solve. It is called a path expression. in JPQL, we are dealing with objects and not with database tables, thats why you'll have to traverse using object properties. Happy learning. – Prasad Kharkar Apr 01 '15 at 09:40
@PrasadKharkar I'm in fact learning it, that's an useful piece of advice. Thank you – Apr 01 '15 at 09:42

JPA use IN clause with objects

2 Answers2