CODEWITHSUNDEEP
hibernatehql
Mr.Chowdary
Mr.Chowdary

Reputation: 3407

HQL Query with like and or operators

I'm using Hibernate, I want to get records based on like condition which contains or condition. It is something like this,

from Employee where empno in (select empno from Employee where empname like 'A%' or 'B%' or 'C%' )"

Is it correct? Suggest me robust query..

Upvotes: 2

Views: 20946

Answers (4)

MarcL
MarcL

Reputation: 3593

if you want more security and prevent sql injection, you can use createQuery with setParameter and wildcards like this:

@Override
public List<Employee> searchEmployee(String searchName) {
    Session currentSession = sessionFactory.getCurrentSession();
Query<Employee> searchQuery = currentSession.createQuery(
                    "from Employee where lower(empname ) like :theName or lower(empLlastName) like :theName", Employee.class);
searchQuery.setParameter("theName", "%" + searchName.toLowerCase() + "%");
List<Employee> employees = searchQuery.getResultList();
return employees;
}

Upvotes: 0

Visruth
Visruth

Reputation: 3450

from Employee e 
    where e.empno in ( select ee.empno from Employee ee 
                       where ee.empname like 'A%' 
                       or ee.empname like 'B%' 
                       or ee.empname like 'C%' )

Upvotes: 0

beny23
beny23

Reputation: 35008

There's no need for a subquery:

from Employee where empname like 'A%' or empname like 'B%' or empname like 'C%'

Upvotes: 5

Kevin Bowersox
Kevin Bowersox

Reputation: 94429

You must create a like statement for each condition in your where clause. HQL will allow you to perform both in/like comparisons as well as subselects.

from Employee emp where emp.empno in 
(select emp2.empno from Employee emp2 where emp2.empname like 'A%' 
or emp2.empname like 'B%' or emp2.empname like 'C%')

Upvotes: 0

Related Questions