Java Hibernate HQL SQL INNER JOIN 查询不起作用 - Oracle

Question

这里是 Hibernate/Java 新手，任何帮助将不胜感激!

所以......我有一个名为ITEMS的表和一个由

连接的 ITEM_OWNER_JOIN 表

“itemKey”列和“owners”列(一组字符串值)...

在 Item.java 中我有:

@ForeignKey(name="FK_ITEM_OWNER_FK")
@ElementCollection(targetClass=java.lang.String.class, fetch = FetchType.Eager)
@JoinTable(name= "ITEM_OWNER_JOIN", joinColumns=@JoinColumn(name="itemKey"))
private Set<String> owners = new HashSet<String>();

基本上，我正在尝试运行 HQL 查询，以获取所有者与搜索文本参数....

所以我尝试过:

Query q = session.createQuery("select distinct i.itemKey from Item i inner join"+  
          " i.owners o where o.owners like '"+searchText+"'");

我收到了 org.hibernate.QueryException: Cannot dereference scalar collection element:owners [select different w.workspaceKey from...]

我尝试研究该异常但无济于事......:(

感谢您的宝贵时间!

Answer 1

如下

HQL

select i 
from Item i 
inner join i.owners io 
where io like 'searchText';

Oracle 查询

SELECT Distinct(i.itemKey) 
FROM Item i, ITEM_OWNER_JOIN io 
WHERE i.itemKey  = io.itemKey and io.x like '%%';

其中“x”是列名称。

我的应用程序的工作示例

来自实体:

 @ElementCollection
    @JoinTable(name = "rule_tagged_name", joinColumns = @JoinColumn(name = "re_rule", referencedColumnName = "id"))
    private List<String>         ruleTagNames;

数据库列

RE_RULE NUMBER
RULE_TAG_NAMES

HQL

Select ru FROM Rule ru inner join ru.ruleTagNames rt_name WHERE rt_name in :tagNameList