Search code examples
javasqlhibernatehql

Java Hibernate HQL SQL INNER JOIN query not working- Oracle

Hibernate / Java newbie here, any help will be greatly appreciated!

So...... I have a table called ITEMS and a ITEM_OWNER_JOIN table joined by the

"itemKey" column and the "owners" column which is a Set of String values...

In Item.java I have:

@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>();

and basically I'm trying to run a HQL querying for results where the owners match a searchText param....

so I've tried:

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

and I am getting a org.hibernate.QueryException: cannot dereference scalar collection element: owners [select distinct w.workspaceKey from.....]

I've tried researching for that exception to no avail... :(

Thank you for your time!

Solution

  • Something as below

    HQL

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

    Oracle Query

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

    where 'x' is column name.

    Working example from my application

    From entity:

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

    DB Columns

    RE_RULE NUMBER
RULE_TAG_NAMES

    HQL

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