I have an entity called Lots as follows:
public class Lots implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int lotId;
@ManyToOne
private Boats boats;
private int numCrates;
....
And I also have an entity called Boats:
public class Boats implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int boatId;
@ManyToOne
private Users user;
private String name;
I am trying to create a named query on Lots as follows:
@NamedQueries({
@NamedQuery(name = "FindUsersByLot", query = "SELECT b FROM Lots b JOIN Boats a ON (b.boats.boatId = a.boatId) WHERE a.user = :user")
})
But this results in an EJB Exception.
Does anyone know how i can do a JOIN in a named query?
Thanks.
First off, when you're asking why you get an exception, please post the stacktrace. However, I think the problem is that you're still thinking SQL
when you should be thinking JPQL
. Try this instead:
@NamedQuery(name = "FindUsersByLot", query = "SELECT b FROM Lots b JOIN b.boats a WHERE a.user = :user")
Explanation - you're assuming that each Lots
record will contain a boatId
column, which may or may not be the case. In @ManyToOne
the JPA provider will likely opt to storing the relationship on the Boats
side. As for why did I write the query the way I did, look into this.