Spring Data, JPA and LEFT JOIN on unrelated tables

Question

I have this 1-N case

class BaseEntity {

  @Id
  Long id;

  String name;
}

class RelId {

  @Column(name = "BU_ID", nullable = false, insertable = true, updatable = true)
  private Long buId;

  @Column(name = "ID", nullable = false, insertable = true, updatable = true)
  private Long id;
}

class ExtA {

  @EmbeddedId
  private RelId id;

  @ManyToOne(optional = false, fetch = FetchType.LAZY)
  @JoinColumn(name = "ID", insertable = false, updatable = false)
  private BaseEntity baseEntity;
}

class ExtB {

  @EmbeddedId
  private RelId id;

  @ManyToOne(optional = false, fetch = FetchType.LAZY)
  @JoinColumn(name = "ID", insertable = false, updatable = false)
  private BaseEntity baseEntity;
}

class BaseEntityEx {

  BaseEntity baseEntity;
  ExtA extA;
  ExtB extB;
}

Now I would like to select the BaseEntityEx where the buId is equals to 2 with a query like this

@Query(value = "SELECT NEW BaseEntityEx(b, ea, eb)
      + " FROM BaseEntity b "
      + " LEFT JOIN ExtA ea ON (ea.id.id=b.id AND ea.id.buId=:buId)"
      + " LEFT JOIN ExtB eb ON (eb.id.id=b.id AND eb.id.buId=:buId)"
      + " WHERE"
      + "   b.id=:id")
public BaseEntityEx getBaseEntityExByRelId(@Param("buId") long buId, @Param("id") long id);

But it does not work. How can I do that?

P.S. I would like to keep the relation only on ExtA and ExtB because when I use BaseEntity I don't want to load lazily or not hundreds of relations.

The error that I obtain is:

antlr.SemanticException: Path expected for join!

Define please "not working". Is there any error? or just no data? Have you seend sql in logs? — Emil Hotkowski, Apr 19 '18 at 18:05
maybe refer to this ?https://stackoverflow.com/questions/43672196/why-do-i-get-path-expected-for-join-when-i-use-join-key-word-in-hql/43672309 — Emil Hotkowski, Apr 19 '18 at 18:28
I would like to keep the relation only on ExtA and ExtB because when I use BaseEntity I don't want to load lazily or not hundreds of relations. — dash1e, Apr 19 '18 at 18:30
So then use JOIN FETCH https://stackoverflow.com/questions/17431312/difference-between-join-and-join-fetch-in-hibernate — Emil Hotkowski, Apr 19 '18 at 18:31
but I don't have the relation from BaseEntity to the other entities — dash1e, Apr 19 '18 at 18:33

Spring Data, JPA and LEFT JOIN on unrelated tables

0 Answers0