Issue with entity association in spring data

Question

I have three entities DrmAdvertisement , DrmCategoryAdvertisement and DrmCategory . Now I want execute the following database query in my repository,

SELECT a.*
FROM drm_advertisement a
JOIN drm_category_advertisement ac ON ac.adid = a.adid
where a.adtype = 'National'
and ac.categoryid = 2;

So I have created the repository for DrmAdvertisement as follows,

public interface AdvertisementRepository extends
        CrudRepository<DrmAdvertisement, Long> {

 @Query("SELECT a FROM DrmAdvertisement a join DrmCategoryAdvertisement ac ON ac.adid = a.adid where a.adType = :adType and ac.categoryid = :categoryid")
    public List<DrmAdvertisement> findAdByCategory(@Param("adType") String adType, @Param("categoryid") Long categoryid);


}

But as we know JPQL joins rely on associations between entities, and not on an ON clause. So How can I write the query with out ON clause?

Did I miss something else in my code? I am getting the following error,

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [SELECT a FROM com.dooreme.domain.DrmAdvertisement a join DrmCategoryAdvertisement ac ON ac.adid = a.adid where a.adType = :adType and ac.categoryid = :categoryid]
    org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1679)
    org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
    org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1608)
    org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

My Entity classes are,

DrmAdvertisement:

@Entity
@Table(name = "drm_advertisement")
public class DrmAdvertisement implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private long adId;

    @Column(nullable = false)
    private UUID uuid;

    @Column(nullable = true)
    private String adName;

    @Column(nullable = true)
    private String adDetails;

    @Column(nullable = true)
    private String adType;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinTable(name = "drm_category_advertisement", joinColumns = @JoinColumn(name = "adid"), inverseJoinColumns = @JoinColumn(name = "categoryId"))
    private DrmCategory drmCategory;
}

DrmCategory

@Entity
@Table(name = "drm_category")
@NamedQuery(name = "DrmCategory.findAll", query = "SELECT d FROM DrmCategory d")
public class DrmCategory implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private long categoryId;

    @Column(nullable = false)
    private UUID uuid;

    @Column(nullable = true)
    private String categoryName;

    @Column(nullable = false)
    private long parentId;

    @Column(nullable = true)
    private String path;

    @Column(nullable = true)
    private Date createDate;

    @Column(nullable = true)
    private Date modifiedDate;

    @Column(nullable = true)
    private boolean active;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "drm_category_advertisement", joinColumns = @JoinColumn(name = "categoryId"), inverseJoinColumns = @JoinColumn(name = "adid"))
    private Set<DrmAdvertisement> drmAdvertisementSet;

}

DrmCategoryAdvertisement

@Entity
@Table(name = "drm_category_advertisement")
@NamedQuery(name = "DrmCategoryAdvertisement.findAll", query = "SELECT d FROM DrmCategoryAdvertisement d")
public class DrmCategoryAdvertisement implements Serializable{

    private static final long serialVersionUID = 1L;

    @EmbeddedId
    private CatAdvId catAdvId;

     // bi-directional many-to-one association to DrmAdvertisement
    @OneToOne
    @JoinColumn(name = "adId", referencedColumnName = "adid", insertable = false, updatable = false)
    private DrmAdvertisement drmAdvertisement;

    // bi-directional many-to-one association to DrmSubscriber
    @OneToOne
    @JoinColumn(name = "categoryId", referencedColumnName = "categoryid", insertable = false, updatable = false)
    private DrmCategory drmCategory;
}

CatAdvId

@Embeddable
public class CatAdvId implements Serializable {

    private static final long serialVersionUID = 1L;
    private static final int THIRTYTWO = 32;
    private static final int THIRTYTHREE = 32;

    private long categoryId;
    private long adId;

    public CatAdvId()
    {

    }

    public CatAdvId(long categoryId, long adId)
    {
        this.categoryId = categoryId;
        this.adId = adId;
    }

    /* (non-Javadoc)
     * @see java.lang.Object#hashCode()
     */
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + (int) (adId ^ (adId >>> THIRTYTWO));
        result = prime * result + (int) (categoryId ^ (categoryId >>> THIRTYTHREE));
        return result;
    }

    /* (non-Javadoc)
     * @see java.lang.Object#equals(java.lang.Object)
     */
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        CatAdvId other = (CatAdvId) obj;
        if (adId != other.adId)
            return false;
        if (categoryId != other.categoryId)
            return false;
        return true;
    }

}

Answer 1

You have to map the relation between DrmAdvertisement and DrmCategoryAdvertisement, then your query will be:

SELECT a FROM DrmAdvertisement a where a.adType = :adType and a.drmCategory.categoryId = :categoryid

edit:

You can remove your mappings DrmCategoryAdvertisement and CatAdvId, and use 'mappedBy' in DrmCategory:

@OneToMany(fetch = FetchType.LAZY, mappedBy = "drmCategory")
private Set<DrmAdvertisement> drmAdvertisementSet;