Add SQL condition to CriteriaQuery

Question

I need add something like Restrictions.sqlRestriction to CriteriaQuery. How to add SQL condition to CriteriaQuery?

My code:

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Installation> criteriaQuery = criteriaBuilder.createQuery(Test.class);
Root<Installation> root = criteriaQuery.from(Test.class);
criteriaQuery.select(root);

score 2 · Accepted Answer · answered May 26 '14 at 18:17

2

JPA Criteria allows function() to be used, or other clauses that have JPQL equivalents. Since you don't say what is the "SQL condition" then that's all that can be said. You certainly can't dump some random SQL into a JPQL filter for example

answered May 26 '14 at 18:17

Neil Stockton

11,383
3
34
29

I need to use Oracle SQL function 'bitand' for int field – Akvel May 27 '14 at 07:19
1

so use JPA 2.1 "function" keyword, available via CriteriaBuilder, and that way you have no need of "SQL" as such – Neil Stockton May 27 '14 at 07:51

score 0 · Answer 2 · answered May 26 '14 at 17:13

I am not sure that it can be done. I suggest you these 3 options:

Implement whatever you need to do in your Hibernate entity, but I guess you have already tried and are here looking for some other solution
Rework your entire query as SQL
A more creative one: define a new Hibernate entity on a database view doing the 'delicate' part of your query, and leave the rest to be managed via Criteria.

score 0 · Answer 3 · answered May 27 '14 at 11:42

Solution for my case (call Oracle function 'bitand')

   criteriaQuery.where(criteriaBuilder.notEqual(criteriaBuilder.function("bitand", Long.class, root.get(Test_.status).get(Status_.id), criteriaBuilder.parameter(Long.class, "status")),0));
   TypedQuery<Installation> query = em.createQuery(criteriaQuery.select(root));
   query.setParameter("status", 1L);

Add SQL condition to CriteriaQuery

3 Answers3