JPQL createNativeQuery with join failing

Question

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.

Are you sure you're showing us the real code? The SQL log shows that the parameters have not been replaced. Why do you use a native SQL query and not a JPQL query? — JB Nizet, May 05 '15 at 11:06
@JB Nizet that is the subject for my next question - there's a 3 way link involved and I haven't been able to get it working using jpql, so in the interim I'm doing it the old fashioned way. — gebuh, May 05 '15 at 11:28

score 2 · Accepted Answer · answered May 05 '15 at 11:08

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);

doh, how'd I miss that? Thanks. – gebuh May 05 '15 at 13:46 — gebuh, May 05 '15 at 13:46

JPQL createNativeQuery with join failing

1 Answers1