Java Hibernate HQL SQL INNER JOIN query not working- Oracle

0
votes

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!

2
javasqlhibernatehql

2 Answers

3
votes

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
0
votes

Try using with IN operator as owners is multiple.

Query hqlQuery = session.createQuery("select distinct i.itemKey from Item i inner join"+  
      " i.owners o where o.owners in :ownersParam");

Then set parameter owners with the owner set value,

Set<String> ownerSet = new HashSet<String>();
ownerSet.add(searchText);

hqlQuery.setParameterList("ownersParam", ownerSet);

//then retrieve result