Hibernate search in string array

Question

I am new to hibernate and looking for a) A solutions and b) comparison of solutions in terms of performance and query complexity.

Example entity - (Assume required hibernate annotations)

Class Ent{
String name;
List<String> alias;
}

I need a solution which can search for a string 'example' in name and alias array in a single database query.

I am able to do this using hibernate with RAW sql ( select * ...). Can somebody suggest a better solution using HQl, Criteria, etc.. with reason

I believe you wanted to say HQL.. I tried using HQL but facing issues as hibernate is not supporting ANY operator present in SQL. I am working with the IN operator.. — hImAnShU
– hImAnShU, Commented Jan 16, 2016 at 18:24

Dragan Bozanovic · Accepted Answer · 2016-01-17 12:03:17Z

1

Assuming that alias can be empty:

select distinct e from Ent e left outer join e.alias a 
where e.name like :term or a.name like :term

or:

select e from Ent e
where e.name like :term
 or e.id in
  (select e2.id from Ent e2 join e2.alias a where a.name like :term)

I prefer the second option because it should be faster (no distinct) and because it allows easier paging of the results.

answered Jan 17, 2016 at 12:03

Dragan Bozanovic

23.6k5 gold badges50 silver badges117 bronze badges

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

1 Answer 1