If you're using criteria queries, you can do like the Hibernate manual says here:
---------------------
There are quite a range of built-in criterion types (Expression subclasses), but one that is especially useful lets you specify SQL directly.
List cats = sess.createCriteria(Cat.class)
.add( Expression.sql("lower({alias}.name) like lower(?)", "Fritz%", Hibernate.STRING) )
.list();
---------------------
Otherwise, you can use a native SQL query in Hibernate, which is really useful for cases where you have vendor-specific SQL to use, such as a rule hint for your optimizer:
-------------------
13.1. Creating a SQL based Query
SQL queries are exposed through the same Query interface, just like ordinary HQL queries. The only difference is the use of Session.createSQLQuery().
Query sqlQuery = sess.createSQLQuery("select {cat.*} from cats {cat}", "cat", Cat.class);
sqlQuery.setMaxResults(50);
List cats = sqlQuery.list();
-------------------
Here's a link to the full Hibernate docs, I think what you're looking for is in Chapter 13:
http://www.hibernate.org/hib_docs/reference/en/html_single/ hernan