How to use native SQL as a fragment (where clause) of a bigger query made with Criteria API in Hibernate?

Question

I have a following problem. In application, which I am developing, we use Hibernate and every query is written with Criteria API. Now, in some places, we want to add possibility for user to write some SQL code which will be used as part of where clause in a query. So basically, user can filter data displayed to him from database in his own way.

For a few days now, I am trying to find a way to modify our previous queries to acquire result described above. Here is what I know:

1. It looks like you cannot combine Criteria API with native SQL. You can either write whole query in SQL or use only criteria API. Is that right? I am asking this question because it would be the easiest solution, just to use this SQL code as another predicate in where clause in our query. But I don't think it's possbile on this level.

2. I know on which table user wants to filter data. So I could just execute native SQL query and use result list as a parameter to IN clause in criteria query. But I don't know if it is efficient with many records in a result list.

3. So if I cannot do it on criteria API level, I thought that maybe I could somehow influence the SQL generetion process and put my SQL in a proper place but it seems to be impossible.

4. So my real question is: is it somehow possible to have access to SQL code of the query, after SQL generation phase but before actual execution of query? Just to manipulate with it manually? Can it be done safely and as far as possible simply?

5. Or maybe just try to parse this SQL written by user and use it in criteria query?

Changing existing criteria queries into native SQL queries is rather out of discussion.

Answer 1

Yes, you can get the SQL from the Hibernate criteria using the org.hibernate.loader.criteria.CriteriaQueryTranslator class.

This will allow you to append the additional SQL clause(s) to the end and execute it as a native SQL:

CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, criteria, "myEntityName", CriteriaQueryTranslator.ROOT_SQL_ALIAS);
String select = translator.getSelect();    
String whereClause = translator.getWhereCondition();

Personally though, if faced with this requirement I would shy away from accepting SQL from the end-user and give them a user interface to populate some type of filter object. This can then be converted into HQL criterion, which is much safer and doesn't tie your code as tightly to the database implementation.

Edit based on comments

Example of extracting SQL from a JPA query implemented with Hibernate:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> q = builder.createQuery(MyEntity.class);
Root<MyEntity> entity = q.from(MyEntity.class);
q.select(entity).orderBy(builder.desc(entity.get("lastModified")));
TypedQuery<MyEntity> query = entityManager.createQuery(q);

String sql = query.unwrap(org.hibernate.Query.class).getQueryString();