Get sql results based on like '123%' using hibernate

Question

I am using Hibernate 4 and I have the following code in my DAO class where I would like to get results from my table

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
        Root<Employee> emp = c.from(Employee.class);
        c.select(emp);
List<Predicate> criteria = new ArrayList<Predicate>();
ParameterExpression<String> pexp = cb.parameter(String.class,
                "employeeNumber");
Predicate predicate = cb.like(emp.get(Employee_.employeeNumber),
                pexp);

        criteria.add(predicate);

if (criteria.size() == 1) {
            c.where(criteria.get(0));           
        } else if (criteria.size() > 1) {
            c.where(cb.and(criteria.toArray(new Predicate[0])));

        }

TypedQuery<Employee> q = entityManager.createQuery(c);
if (employeeNumber != null) {
            q.setParameter("employeeNumber", employeeNumber);
data.setResult(q.getResultList());

When page runs, from log I could see sql as

select employeede0_.EMPLOYEE_NUMBER as EMPLOYEE1_0_ from EMPLOYEES employeede0_ 
where employeede0_.EMPLOYEE_NUMBER like ?

Because of this I am not able to see any results. Ideally I would like to have a query like

select employeede0_.EMPLOYEE_NUMBER as EMPLOYEE1_0_ from EMPLOYEES employeede0_ 
    where employeede0_.EMPLOYEE_NUMBER like '123%'

How can I achieve this?

? means hibernate will be getting this value at runtime.You cant see that value in the log.You have to make sure that you are setting the property correctly. — Renjith, Jan 14 '13 at 10:43
@Renjith so `like ?` means it is executing as `EMPLOYEE_NUMBER like '123%'? ` Besides how can I set it properly? — Jacob, Jan 14 '13 at 10:45
@KevinBowersox Yes it is, `public static volatile SingularAttribute employeeNumber;` — Jacob, Jan 14 '13 at 10:47
Example : [.add( Restrictions.like("name", "Fritz%") )] http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html#querycriteria-narrowing — TJ-, Jan 14 '13 at 10:49

score 2 · Accepted Answer · answered Jan 14 '13 at 10:54

2

You want to end up executing

Predicate predicate = cb.like("employeeNumber", "123%");

So including the percent.

answered Jan 14 '13 at 10:54

flup

26,937
7
52
74

Thanks for this. I have managed like this `Predicate predicate = cb.like(emp.get(Employee_.empName), empName+"%");` If I would want to have case sensitive statement, how could I sorround with lower()? – Jacob Jan 14 '13 at 12:29
I am not entirely sure how you mean, `ilike()` is the case insensitive version of `like()` so I'd expect this to be case sensitive. I have not tried it out, though. In the documentation, I see that SimpleExpression has a method ignoreCase() so you could try `Predicate predicate = cb.like("employeeNumber", "123%").ignoreCase();` – flup Jan 14 '13 at 16:15
`Predicate predicate = cb.like(emp.get(Employee_.empName), empName+"%");` doesn't have `ignoreCase();` method. – Jacob Jan 15 '13 at 00:49
But it is a SimpleExpression too. So change Predicate to that. – flup Jan 15 '13 at 06:05
Flup I have managed to get this working using metamodel. `Predicate predicate = cb.like(cb.lower(emp.get(Employee_.empName)), empName.toLowerCase() + "%");` Courtesy [here](http://stackoverflow.com/questions/14333997/jpa-2-criteria-api-using-metamodel-case-sensitive-condition) Thanks a lot for your help. – Jacob Jan 15 '13 at 13:46
My pleasure! ( I still suspect that `Predicate predicate = cb.like(emp.get(Employee_.empName), empName+"%").ignoreCase();` will work too. ) – flup Jan 15 '13 at 14:57

score 1 · Answer 2 · answered Jan 14 '13 at 11:02

1

This should work for you:

criteria.add ( Restrictions.like ("employeeNumber", "123%" ) );

answered Jan 14 '13 at 11:02

Rakesh Juyal

35,919
68
173
214

Get sql results based on like '123%' using hibernate

2 Answers2