CODEWITHSUNDEEP
javasqlxmljpajoin
carlkassar
carlkassar

Reputation: 7

JPA JOIN query list giving only one item instead of all items

the following sub is giving me only one item( Carte) from the database instead of giving me all of the items . i have spent hours trying to find the error please help me guys 

  @Path("userid/{clientid}")
  @Produces({"application/xml", "application/json"})
    public ArrayList<Carte> findUserid(@PathParam("clientid") Integer clientId) {
    Utilisateur u1 = new Utilisateur();
    u1.setId(clientId);
    ArrayList<Carte > arraycarte= new ArrayList<Carte >();

    Query query1=em.createQuery("select ca FROM Carte ca left join Compte  c on c=ca.compteid left join Utilisateur u on u= c.clientid where u.id=:clientid");
    arraycarte.addAll(query1.setParameter("clientid", u1.getId()).getResultList());

    return arraycarte;

}

Upvotes: 0

Views: 491

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Here is your query:

SELECT ca
FROM Carte ca LEFT JOIN
     Compte c
     ON c = ca.compteid LEFT JOIN
     Utilisateur u
     ON u = c.clientid
WHERE u.id = :clientid;

Your where clause is undoing the LEFT OUTER JOIN, because the result is NULL when the user does not match. You can fix this by moving it to the ON clause:

SELECT ca
FROM Carte ca LEFT JOIN
     Compte c
     ON c = ca.compteid LEFT JOIN 
     Utilisateur u
     ON u.id = c.clientid AND u = :clientid;

You say your query works, but it seems odd that you are referring to table names as column names. I assume this is the result of a typo. In other words, I would expect a query like this:

SELECT ca.*
FROM Carte ca LEFT
-----------^
     Compte c
     ON c.compte_id = ca.compteid 
----------------------^ LEFT JOIN
     Utilisateur u
     ON u.id = c.clientid AND u.id = :clientid;
------------------------------^

Upvotes: 2

Related Questions