11

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.

Alex Salauyou
  • 14,185
  • 5
  • 45
  • 67
MichalD
  • 111
  • 1
  • 6
  • No, you cannot use SQL with JPA Criteria - JPA Criteria is for writing JPQL. If some implementation "offers" it as an extension then you lose portability. As per the JPA spec – Neil Stockton Apr 13 '15 at 12:33

2 Answers2

4

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();
StuPointerException
  • 7,117
  • 5
  • 29
  • 54
  • Thank you for your answer, I will try to do what you have suggested in a moment. I agree with your oppinion about accepting SQL from end-user and I have considered this other solution but what we are trying to do is to develop a new, better app which has the same functionality as existing one and it needs to be backward compatible. In other words, filters already written in SQL should work in our app too. – MichalD Apr 13 '15 at 12:42
  • Hmm, sorry for not being precise or even being wrong but it's all rather new to me. We create queries using hibernate implementation for JPA and not with Hibernate criteria API. And code sample you wrote above seems to work only for Hibernate criteria API, am I right? Or maybe I just mix concepts? – MichalD Apr 13 '15 at 14:16
  • Apologies, I misinterpreted your question. It looks like there is no requirement in the JPA specification for a query to expose the underlying SQL (which makes sense). You may have to drop through to the implementation, I'll update my answer with an example. – StuPointerException Apr 13 '15 at 15:34
  • I've tried what you've suggested (edited part) but I am afraid it's still not what I need. It's working, I get a query string and I can edit it and make another query (I have to remember to copy parameters from first query) but String which I am getting looks like HQL not SQL. SQL generation is taking place later - in my case during execution of "query.getResultList()". What I need to do is to get in the middle of hibernate's internal methods and change something properly. I don't know if it's possible. – MichalD Apr 14 '15 at 14:33
  • I'm convinced (although don't have the means to try it at the moment!) that a combination of the above approaches should achieve what you require. The JPA query is implemented in Hibernate using the Hibernate criteria API, just hidden behind JPA interfaces. If you're able to either cast, or unwrap the JPA query object(s) you should have access to the functionality of the CriteriaQueryTranslator class. Good luck! – StuPointerException Apr 14 '15 at 15:21
2

criteria.add(Restrictions.sqlRestriction(" AND ID in (1,2,3)" ));

Hard Worker
  • 995
  • 11
  • 33