Java Hibernate HQL SQL INNER JOIN query not working- Oracle

Question

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!

marc_s · Accepted Answer · 2017-02-24 21:52:31Z

3

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

edited Feb 24, 2017 at 21:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 26, 2014 at 3:32

Ankur Singhal

26.1k17 gold badges92 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ZvKa Over a year ago

Thank you! changing io.owners to io just before the like fixed the error, do you guys know why it is throwing that error? I have a column named "owners" inside the ITEM_OWNER_JOIN table since the column was not explicitly annotated. Maybe it is confused because the Set of Strings "owners" and the join table's column "owners" is named the same?

Wundwin Born · Accepted Answer · 2014-08-26 04:09:54Z

0

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

answered Aug 26, 2014 at 4:09

Wundwin Born

3,4751 gold badge21 silver badges37 bronze badges

Collectives™ on Stack Overflow

Java Hibernate HQL SQL INNER JOIN query not working- Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related