JPQL createEnativeQuery，加入失败

Question

我正在加入2B中具有外键关系的2个实体，而不是在代码中（为另一个问题保存原因）：

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

但是查询在运行时失败：

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

我尝试了不同的变体，将参数输入和移出位，较低。没有什么可用。

在添加加入之前：

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

此查询工作正常。

我在做什么错？在glassfish3.1，toplink和mysql上运行。

Answer 1

您的第一个示例是本机查询，但是您的第二个示例使用JPQL，因此并不清楚您要做什么。我认为您的本地查询失败是因为在JPA中不支持命名参数（仅位置参数，但例如Hibernate确实支持它）。所以，尝试这个

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

至于jpql版本，您没有发布实体代码，所以我会猜测关系，但看起来像这样

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