What is the primary requirements for hibernate join on entity level

Question

I have three entities mentioned below, Table structure is also mentioned below. Now I'm trying to fetch only those customer who have purchased specific items. I'm trying this query which is not working and facing ERROR: org.hibernate.hql.internal.ast.ErrorCounter - Path expected for join! - Please possible right answer.

SELECT customer FROM Customer customer JOIN CustomerItemMapping cim on customer.customerId=cim.customerId where cim.item.itemId in (:itemIdList);

@Entity @Cacheable
@Table(name = "customer_item_mapping")
@DynamicInsert(value=true)
@DynamicUpdate(value=true)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class CustomerItemMapping implements Serializable {

private static final long serialVersionUID = 3500101963230957017L;

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "mapping_id", unique = true, nullable = false)
private Integer mappingId;

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "item_id", nullable = false)
private Item item;

@Column(name = "customer_id", nullable = false)
private Integer customerId;

}

@Entity @Cacheable
@Table(name = "customer")
@DynamicInsert(value=true)
@DynamicUpdate(value=true)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Customer implements Serializable{

private static final long serialVersionUID = 3886876059389214345L;

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "customer_id", unique = true, nullable = false)
private Integer customerId;

@JoinColumn(name = "name", nullable = false)
private String customerName;

@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "customer_item_mapping", joinColumns = @JoinColumn(name = "customer_id"), inverseJoinColumns = @JoinColumn(name = "item_id"))
private Set<Item> itemSet;

}

@Entity @Cacheable
@Table(name = "item")
@DynamicInsert(value=true)
@DynamicUpdate(value=true)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Item implements Serializable{

private static final long serialVersionUID = 3886876059389214345L;

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "item_id", unique = true, nullable = false)
private Integer itemId;

@JoinColumn(name = "name", nullable = false)
private String name;

}


CREATE TABLE customer_item_mapping ( mapping_id int(11) unsigned NOT NULL AUTO_INCREMENT, item_id int(100) NOT NULL, customer_id int(11) NOT NULL, PRIMARY KEY (mapping_id))

CREATE TABLE customer ( customer_id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, PRIMARY KEY (customer_id) )

CREATE TABLE item ( item_id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, PRIMARY KEY (item_id) )

Answer 1

SELECT distinct customer FROM Customer customer, CustomerItemMapping cim
where customer.customerId = cim.customerId and cim.item.itemId in (:itemIdList)

As you see, you can use classic theta join when inner-joining non-associated entities.

I also corrected cim.tag.itemId to cim.item.itemId (I don't know what tag stands for, I see it nowhere in your mappings).