mysql - 实体级别的 hibernate join 的主要要求是什么

Question

我有下面提到的三个实体，下面还提到了表结构。现在我试图仅获取那些购买了特定商品的客户。我正在尝试此查询，该查询不起作用并面临错误:org.hibernate.hql.internal.ast.ErrorCounter - 加入所需的路径! - 请可能的正确答案。

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)

如您所见，在内连接非关联实体时可以使用经典的 theta 连接。

我还将 cim.tag.itemId 更正为 cim.item.itemId (我不知道标签 代表，我在你的映射中没有看到它)。