JPQL createNativeQuery with join failing

I'm joining 2 entities with a foreign key relationship in the db, but not in the code (saving the why for another question):

em.createNativeQuery("SELECT u.* FROM user u JOIN user_community_organization uco ON "
                + "u.user_id = uco.user_id "
                + "WHERE uco.community_id = :communityId "
                + "AND lower(u.email) = :email", User.class)
                .setParameter("communityId", communityId)
                .setParameter("email", email.toLowerCase());

But the query fails during runtime with:

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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 ':communityId AND 
lower(u.email) = :email' at line 1
Error Code: 1064
Call: SELECT u.* FROM user u JOIN user_community_organization uco ON  
u.user_id = uco.user_id WHERE uco.community_id = :communityId AND lower(u.email) = :email

I've tried different variations, moving the parameters in and out of the ON bit, removing lower. Nothing works.

Before I added the join:

em.createQuery("select object(o) from User as o where lower(o.email) = :email");
        q.setParameter("email", email.toLowerCase());

This query worked fine.

What am I doing wrong? Running on GlassFish3.1, toplink and mySql.

Solution

Your first example is with native query but your second example uses JPQL so it's not really clear what you are trying to do. i think your native query fails because named parameters are not supported in JPA (only positional parameters, but Hibernate for example does support it). So, try this

em.createNativeQuery("SELECT u.* FROM user u JOIN user_community_organization uco ON "
                + "u.user_id = uco.user_id "
                + "WHERE uco.community_id = ?1 "
                + "AND lower(u.email) = ?2", User.class)
                .setParameter(1, communityId)
                .setParameter(2, email.toLowerCase());

As for JPQL version, you didn't post entity code so I'll make a guess about relations, but it would look something like this

em.createQuery("select u from User u where lower(u.email) = :email and u.communityOrganisation.id = :communityId");
        q.setParameter("email", email.toLowerCase());
        q.setParameter("communityId", communityId);