How to change to case of a column to Upper in HQL Hibernate

Question

I want to change a column from a table to uppercase before using like and filtering what is the keyword in HQL?

here's my query

SELECT abc
FROM ABC abc
WHERE abc.id = ?
And upper(abc.description) like '%?%'

Thanks

Answer 1

HQL supports the upper() function defined by the EJB 3.0 specification in the SELECT and WHERE clauses. From the documentation:

14.10. Expressions

• ...
• Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()
• ...

So the following should work:

from DomesticCat cat where upper(cat.name) like 'FRI%'

References

• Hibernate Core Reference Guide
  • 14.10. Expressions
• JPA 1.0 Specification
  • Section 4.6.16.1 "String Functions"

Answer 2

I m writing a sample query in HQL:

session.createQuery(from Certificate where lower(assignedTo)=:userName)
    .setString("userName",username);

Assumptions: a table named Certificate exists with field assignedTo. session is the Hibernate Session.