QueryDSL / JPQL: how to build a connection query?

Question

I tried to read QueryDSL docs, but I'm still very confused. I'm used to writing a lot of SQL, but this is my first crack when using QueryDSL w / JPQL (JPA2).

I have the following entity:

@Entity public class Provider implements Serializable { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "id") private Long id; @Version @Column(name = "version") private Integer version; private String name; @ManyToMany(cascade=CascadeType.ALL) @JoinTable(name = "provider_contact", joinColumns = @JoinColumn(name = "contact_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "provider_id", referencedColumnName = "id")) @OrderColumn private Collection<Contact> contact; } 

where Contact is a simple object with id for pk.

 @Entity public class Contact { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "id") private Long id; /** * User first name */ @NotNull private String firstName; /** * User last name */ @NotNull private String lastName; } 

I am trying to write a query that returns a Contact object based on the specific Contact.id and Provider.id. If the Contact object is not part of the supplier’s contact collection, I look for a null value.

I tried the following:

 public Contact getContact( long providerId, long contactId ){ Predicate p = QProvider.provider.id.eq(providerId).and(QContact.contact.id.eq(contactId)); JPQLQuery query = new JPAQuery(em); return query.from(QProvider.provider).innerJoin(QProvider.provider.contact).where(p).singleResult(QContact.contact); } 

but I get the following error:

 Caused by: java.lang.IllegalArgumentException: Undeclared path 'contact'. Add this path as a source to the query to be able to reference it. at com.mysema.query.types.ValidatingVisitor.visit(ValidatingVisitor.java:78) at com.mysema.query.types.ValidatingVisitor.visit(ValidatingVisitor.java:30) at com.mysema.query.types.PathImpl.accept(PathImpl.java:94) 

I suppose this is because my predicate refers to QContact.contact, not the QProvider.provider.contact object, but I'm really at a loss to figure out how it should be done.

Am I even on the right track? I'm not even sure that my connection is also correct.

Answer 1

This should work

 public Contact getContact(long providerId, long contactId) { QProvider provider = QProvider.provider; QContact contact = QContact.contact; return new JPAQuery(em).from(provider) .innerJoin(provider.contact, contact) .where(provider.id.eq(providerId), contact.id.eq(contactId)) .singleResult(contact); }