JPA 2 Criteria API using metamodel case sensitive condition

Question

I have the following line of code to get the results based on like statement using Hibernate 4 API

Predicate predicate = cb.like(emp.get(EmployeeDetail_.empName),
                empName+"%");

The generated sql statement is

 select employeede0_.EMPLOYEE_NAME as EMPLOYEE1_0_ from EMPLOYEES employeede0_ 
        where employeede0_.EMPLOYEE_NAME like 'smith%'

How can I modify my java code to have EMPLOYEE_NAME in lower case? The generated sql output should be like the following

select employeede0_.EMPLOYEE_NAME as EMPLOYEE1_0_ from EMPLOYEES employeede0_ 
    where lower(employeede0_.EMPLOYEE_NAME) like lower('smith%')

Complete code for getting results

   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,
                "empName");
        Predicate predicate = cb.like(emp.get(Employee_.empName),
                empName+"%");
        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<EmployeeDetail> q = entityManager.createQuery(c);
        data.setResult(q.getResultList());

score 3 · Accepted Answer · answered Jan 15 '13 at 08:53

3

Use CriteriaBuilder#lower():

Predicate predicate = cb.like(cb.lower(emp.get(EmployeeDetail_.empName)),
            empName.toLowerCase() + "%");

answered Jan 15 '13 at 08:53

perissf

15,979
14
80
117

For some strange reason no results are coming when this gets executed. Generated sql is `select employeede0_.EMP_NAME as LONG2_0_ from V_EMPLOYEE employeede0_ where low er(employeede0_.EMP_NAME) like ?` – Jacob Jan 15 '13 at 10:02
The query seems correct however... maybe the problem is somewhere else? What is the value of the parameter `empName`? – perissf Jan 15 '13 at 10:47
Value of `empName` is getting it correctly, e.g. value coming as `'smith'` Any way we could see the sql statement instead of question mark(?) ? – Jacob Jan 15 '13 at 10:53
I have found quite a strange thing, `System.out.println(" size "+q.getResultList().size());`. This prints real count of the sql results, however cannot see any records in jsf page. – Jacob Jan 15 '13 at 11:10
Great. Ensure that the query returns the correct result set. If it does, ask a new question with the new problem adding the relevant information. – perissf Jan 15 '13 at 11:19
Problem has been solved. I have to make a similar change in my ManagedBean as well, something like `empName.toLowerCase();` Thanks a lot for your help. – Jacob Jan 15 '13 at 11:38

JPA 2 Criteria API using metamodel case sensitive condition

1 Answers1

Linked