JPA: JOIN in JPQL

Question

I thought I know how to use JOIN in JPQL but apparently not. Can anyone help me?

select b.fname, b.lname from Users b JOIN Groups c where c.groupName = :groupName 

This give me Exception

org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing the query Internal Exception: org.eclipse.persistence.internal.libraries.antlr.runtime.EarlyExitException 

Users have a OneToMany relationship with Groups.

Users.java

@Entity public class Users implements Serializable{      @OneToMany(mappedBy="user", cascade=CascadeType.ALL)     List<Groups> groups = null; } 

Groups.java

@Entity public class Groups implements Serializable {     @ManyToOne     @JoinColumn(name="USERID")     private Users user; } 

My second question is let say this query return a unique result, then if I do

String temp = (String) em.createNamedQuery("***")     .setParameter("groupName", groupName)     .getSingleResult(); 

*** represent the query name above. So does fname and lname concatenated together inside temp or I get a List<String> back?

Answer 1

Join on one-to-many relation in JPQL looks as follows:

select b.fname, b.lname from Users b JOIN b.groups c where c.groupName = :groupName  

When several properties are specified in select clause, result is returned as Object[]:

Object[] temp = (Object[]) em.createNamedQuery("...")     .setParameter("groupName", groupName)     .getSingleResult();  String fname = (String) temp[0]; String lname = (String) temp[1]; 

By the way, why your entities are named in plural form, it's confusing. If you want to have table names in plural, you may use @Table to specify the table name for the entity explicitly, so it doesn't interfere with reserved words:

@Entity @Table(name = "Users")      public class User implements Serializable { ... }