JPA: JOIN in JPQL
129,755
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 { ... }
Author by
Thang Pham
Updated on July 09, 2022Comments
-
Thang Pham almost 2 years
I thought I know how to use
JOIN
inJPQL
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 withGroups
.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 doesfname
andlname
concatenated together insidetemp
or I get aList<String>
back? -
Ced over 8 yearsWhat if instead of selecting fields you selected an entity + a field from another entity. Something like
SELECT b , c.name ...
. Then the first object in the Object[] is gonna be a field of b or the whole entity ?