2

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.

How can I do a JOIN in a named query?

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Paul Blundell
  • 1,857
  • 4
  • 22
  • 27

1 Answers1

8

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.

rdcrng
  • 3,415
  • 2
  • 18
  • 36