nonfatal user error in JPAQuery (QueryDSL)

Question

I have to add a search function using QueryDSL / JPAQuery. The search should use a table consisting of parent ids:

ID   LABEL  P_ID
1    A      null 
2    AB     1
3    ABC    2

A simple SQL statement gives the right result:

SELECT * FROM ACT
JOIN ORG DEPT ON ACT.ORGID = DEPT.ID
JOIN ORG DEPT2 ON DEPT.P_ID = DEPT2.ID
WHERE DEPT.P_ID = 123

The problem I'm facing is that I have to create the corresponding QueryDSL / JPAQuery using an existing query object.

My code reads:

query.join(act.org, dept).on(act.org.id.eq(dept.id)).where(dept.org.parent.eq(123));

Before and after my query, other statements modify query object. This causes the error:

O DEBUG JPAQuery - select count(distinct dossier_) from Dossier dossier_ left join dossier_.acties actie_ inner join actie_.organisatieStructuur organisatieStructuur_ on actie_.organisatieStructuur.id = organisatieStructuur_.id where dossier_.deleted = ?1 and organisatieStructuur_.ouder = ?2

org.apache.openjpa.persistence.ArgumentException: "Encountered "on" at character 64, but expected: [",", "GROUP", "HAVING", "INNER", "JOIN", "LEFT", "ORDER", "WHERE", ]." while parsing JPQL "select count(distinct dossier_) from Dossier dossier_ left join dossier_.acties actie_ inner join actie_.organisatieStructuur organisatieStructuur_ on actie_.organisatieStructuur.id = organisatieStructuur_.id where dossier_.deleted = ?1 and organisatieStructuur_.ouder = ?2".

[Added 9-Feb:] Domain model (only first part containing the fields are shown, other getter/setter methods not shown because of size):

@Entity
public class Dossier extends AbstractEntity implements Serializable, HasCommunicaties {
private static final String SHAREPOINT_NR_FORMAT = "{0,number,0000}/{1,number,00}";

@Version
private long version;

private int jaar;
private int volgNummer;

private Status status;

public enum Status {
    OPEN, AFGESLOTEN;
}

@Temporal(TemporalType.TIMESTAMP)
private Date datumStatus;

@NotNull
@Column(length = 5000)
@Size(max = 5000)
private String beschrijvingVaststelling;
@Column(length = 5000)
@Size(max = 5000)
private String oorzaakVaststelling;

@ManyToOne
private ZorgDomein zorgDomein;
@ManyToOne(fetch = FetchType.LAZY)
@NotNull
private Bron bron;

@ManyToOne
private KernWaarde kernWaarde;

private String pdca;

@Valid
@OneToMany(mappedBy = "dossier", cascade = CascadeType.ALL, orphanRemoval = true)
@Size(min = 1)
private List<PlaatsVaststelling> plaatsVaststellingen = new ArrayList<PlaatsVaststelling>();

@ManyToOne(fetch = FetchType.LAZY)
private Proces proces;
@NotNull
private String beheerder;
@Temporal(TemporalType.DATE)
@Past
private Date datumVaststelling;
@ManyToOne(fetch = FetchType.LAZY)
private TypeVaststelling typeVaststelling;
@ManyToOne(fetch = FetchType.LAZY)
private Prioritering prioritering;
@ManyToOne(fetch = FetchType.LAZY)
private AuditRapport auditRapport;

@OneToMany(mappedBy = "dossier", cascade = CascadeType.ALL)
@OrderBy("volgnummer")
@Valid
private List<Actie> acties = new ArrayList<Actie>();
@OneToMany(mappedBy = "dossier")
@OrderBy("datum desc")
private List<Communicatie> communicaties = new ArrayList<Communicatie>();
@OneToMany(mappedBy = "dossier", cascade = CascadeType.ALL)
@OrderBy("datum desc")
private List<Historiek> historieks = new ArrayList<Historiek>();

private boolean vertrouwelijk;
@ElementCollection
private List<String> beheerders = new ArrayList<String>();
@ElementCollection
private List<String> lezers = new ArrayList<String>();

private boolean mailPVAVerstuurd;
private Date datumAanpakTegen;


private boolean deleted;

I get the impression that the method join not is supported. Because the moment I use the .join(...) I see the non fatal error. — hush

Timo Westkämper Timo Westkämper · Accepted Answer · 2015-02-09T20:13:34

This SQL

SELECT * FROM ACT
JOIN ORG DEPT ON ACT.ORGID = DEPT.ID
JOIN ORG DEPT2 ON DEPT.P_ID = DEPT2.ID
WHERE DEPT.P_ID = 123

can be expressed via

QAct act = QAct.act;
QOrg dept = new QOrg("dept");
QOrg dept2 = new QOrg("dept2");
query.from(act)
     .join(act.org, dept)
     .join(dept.p, dept2)
     .where(dept2.p.id.eq(123))
     .list(act);

I was not sure how to map the SQL to your domain model, that's why I used entity names that are close to the SQL relations.

nonfatal user error in JPAQuery (QueryDSL)

1 Answers