SQL syntax error using JPA Criteria Query (OpenJPA bug?)

Question

I am trying to create a query to recover all the Sites related to a User using a JPA Criteria Query:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Site> query = cb.createQuery(Site.class);
Root<Site> fromSite = query.from(Site.class);
Join<UserSiteClientRolePK, Integer> joinUser = fromSite
    .join(Site_.siteClients)
    .join(SiteClient_.usersSitesClientsRoles)
    .join(UserSiteClientRole_.id)
    .join(UserSiteClientRolePK_.userid);
query = query.select(fromSite);
query = query.where(cb.equal(joinUser, userid));
TypedQuery<Site> typedQuery = em.createQuery(query);
List<Site> sites = typedQuery.getResultList();

However, when I test my code I am obtaining this error:

<openjpa-2.4.0-r422266:1674604 fatal general error> 
org.apache.openjpa.persistence.PersistenceException: 
ERROR: error de sintaxis en o cerca de «AND»

Position: 440 {prepstmnt 737085888 
SELECT ...
WHERE (t3.userid = ? AND  AND ) [params=?]} [code=0, state=42601]

For some reason, OpenJPA is including erroneous SQL code "AND AND)" at the end of the autogenerated SQL script and I can't figure out why.

This is driving me nuts. Could anybody help me, please?

I am using OpenJPA version 2.4.0, by the way.

Thanks a lot. Kind regards.

Answer 1

You are using join in wrong way.

The join methods return an object of type Join<X, Y>, where X is the source entity and Y is the target of the join.

Please check this link:

http://docs.oracle.com/javaee/6/tutorial/doc/gjivm.html#gjiuv

Try this query:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Site> query = cb.createQuery(Site.class);
Root<Site> fromSite = query.from(Site.class);

Join<Site, SiteClient> siteClient = fromSite
    .join(Site_.siteClients);

Join<siteClient, UserSiteClientRole_> userSiteClientRole = siteClient
    .join(SiteClient_.usersSitesClientsRoles);

query = query.select(fromSite);
query = query.where(cb.equal(userSiteClientRole.get(UserSiteClientRole_.userid), userid));
TypedQuery<Site> typedQuery = em.createQuery(query);
List<Site> sites = typedQuery.getResultList();