JPQL : geting results from a multi expression select

Question

Here is my JPQL query:

SELECT p, 
   exists( select dp from DocumentPublication dp where dp.documentVersion = p) 
FROM 
   DocumentVersion p where document.id = :id

Here is the code to get the result:

   Query query =   
     getEntityManager().createNamedQuery("DocumentVersion.findByDocumentId");

   query.setParameter("id", docsFilter.getProjectId());

   List<Object[]>  res;
   try
   {
       res = query.getResultList();
   }
   catch (NoResultException e)
   {
       return null;
   }
   // res only contains a list of DocumentVersion / No 'boolean'

I want to retrieve the list of results but when I perform a "getResultList" on my query, I only see the first part of the select ( a list of DocumentVersion), I don't see the boolean that I would like to get.

I am using one of the latest hibernate version as a pesistence provider.

Thank you.

:-(... should have not asked you that. I have been browsing and found http://stackoverflow.com/questions/6804077/is-select-exists-possible-in-jpql; in short `exists` is part of a conditional statement and that only be part of a `WHERE` or `HAVING` clause (check the link provided in the answer). Sorry for the inconvenience. — SJuan76, Jun 23 '13 at 09:55

score 1 · Accepted Answer · answered Jun 23 '13 at 18:10

As SJuan pointed out, exist() cannot be used in the select expression. So I changed the query with a left join which works well. Here is the query:

SELECT p, count(dp.id) 
FROM DocumentVersion p left join  p.documentPublications dp 
where p.document.id  = :id 
group by p.id

With the code to retreive the result:

 List<Object[]>  res;
   try
   {
       res = query.getResultList();
   }
   catch (NoResultException e)
   {
       return null;
   }

   List<DocumentVersion> documentVersions = new ArrayList<>();
   for(Object[] objects : res)
   {
      DocumentVersion documentVersion = (DocumentVersion) objects[0];
      documentVersion.setPublished( (Long) objects[1] >  0);
      documentVersions.add( documentVersion );
   }

JPQL : geting results from a multi expression select

1 Answers1