Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using native SQL as a fragment (where clause) of a bigger query made with Criteria API in Hibernate.

 
Michał Duczmal
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have a following problem. In application, which I am developping, 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.

Thank you in advance for your time and effort,
Michal
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Warning: I don't use Hibernate so maybe I'm on the wrong track here!

But why can't you use the Criteria API to add e.g. filter clauses to the base query, based on what the user specifies? You'd need to handle AND/OR clauses etc, of course.

Also, can you be sure your user will enter safe, valid and efficient SQL? What do you do if they manage to submit malicious SQL (SQL injection), or if they simply provide poor selection criteria that stop you using indexes, for example?
 
Tim Holloway
Saloon Keeper
Posts: 18365
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hibernate introduces an abstraction to SQL. That's useful because it makes the app more easily portable between different vendors - and versions! - of databases. It's also useful because the Hibernate runtime can attempt to optimize queries. Including the possibility that they might monitor performance and re-formulate queries for better performance relative to the types of queries being done. How much, if any of that is actually being done right now, I don't know, but it's a mirror of the just-in-time load-based optimizing that JVMs have tried.

So it's not like the JPQL queries are compiled straight-to-SQL. They're parsed into abstract syntax trees and those trees are then rendered into concrete server/version-specific SQL. If you were to attempt to introduce a native DB2 query fragment into a JPQL query and run against an Oracle server... Well, you can imagine, I hope!

I think that Hibernate may include some backend manipulation stuff like what you are envisioning, but I'd lay good odds that in order to use it, some of the normal Hibernate optimizations will be precluded. If, how, and how much backend SQL meddling you can do. I'll leave you to discover. That's what the manual is for!
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Michał Duczmal,

First of all, a warm welcome to CodeRanch!

Michał Duczmal wrote: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.

I once wrote a query builder, so users could create their own queries (to a certain extent) without having to know any (native) SQL syntax. In this case no Hibernate was involved, just plain SQL syntax for a Microsoft SQL Server. It's a long time ago, but if I remember correctly: users could mainly create a where clause with as many conditions (using and, or and not) as he wanted. No joining between tables, no grouping by, no (aggregate) functions,... So depending on the complexity of the additional SQL code, I would think something similar can be done using the Criteria API as well.

Here you'll find a similar SO question. And here you'll find an example of how you can add a constraint expressed in SQL to your Criteria using the sqlRestriction() method from the org.hibernate.criterion.Restrictions class. And here you'll find the Hibernate documentation of native SQL queries.

Hope it helps!
Kind regards,
Roel

 
Michał Duczmal
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:But why can't you use the Criteria API to add e.g. filter clauses to the base query, based on what the user specifies? You'd need to handle AND/OR clauses etc, of course.

I could do that but I would have to know what the user specified. To know that, I would have to parse SQL written by the user and I hoped that someone already did that part. And unfortunately, at the moment, allowing plain SQL written by user is unchangable requirement.

chris webster wrote:Also, can you be sure your user will enter safe, valid and efficient SQL? What do you do if they manage to submit malicious SQL (SQL injection), or if they simply provide poor selection criteria that stop you using indexes, for example?

Well, solution like that works already in old version of the app which I am developing and writing own SQL is allowed only for admins. They write filters for all normal users when they are asked for it. I think that the approach is following: "if you want your installation of the app to work well, then write valid SQL or don't write it at all". I think that preparing an interface for users to create their conditions without real SQL to use would be better but we rather cannot change the way this funcionality has to work.

Tim Holloway wrote:So it's not like the JPQL queries are compiled straight-to-SQL. They're parsed into abstract syntax trees and those trees are then rendered into concrete server/version-specific SQL. If you were to attempt to introduce a native DB2 query fragment into a JPQL query and run against an Oracle server... Well, you can imagine, I hope!

I see now that my first idea of solution to the problem was rather unrealistic. What I want to do now is to try to get generated SQL before executing query and maybe change it manually. Im just not sure if it's possible to do it safely. Maybe it would be better to parse the SQL written by the user and use only Criteria API without further combinations? But it seems a little nonsense to do it like this: SQL -> JPQL -> ... -> SQL. And omiiting the sense part, I don't really know how complex this task would be.

Tim Holloway wrote:I think that Hibernate may include some backend manipulation stuff like what you are envisioning, but I'd lay good odds that in order to use it, some of the normal Hibernate optimizations will be precluded. If, how, and how much backend SQL meddling you can do. I'll leave you to discover. That's what the manual is for!

Ok, thanks. I'll look into it.

Roel De Nijs wrote:First of all, a warm welcome to CodeRanch!

Thank you!

Roel De Nijs wrote:I once wrote a query builder, so users could create their own queries (to a certain extent) without having to know any (native) SQL syntax.
...
I would think something similar can be done using the Criteria API as well.

Yes, I think so too but for now, I have to allow user to write his own where clause in SQL. That's because, this is how it worked in the old version of the app and we are doing our best to write a better app without changing any of the functionalities. I don't know whether it's the best approach but it's not my decision to make. I would need to have very strong arguments to force solution where filters are not written in plain SQL by admins. So now I am exploring possibilities to do it expected way.

Roel De Nijs wrote:Here you'll find a similar SO question. And here you'll find an example of how you can add a constraint expressed in SQL to your Criteria using the sqlRestriction() method from the org.hibernate.criterion.Restrictions class. And here you'll find the Hibernate documentation of native SQL queries.

Thank you for the links, I'll take a close look at the content and I'll let you know is there any progress.

Thanks and regards to you all,
Michał
 
Dave Tolls
Ranch Hand
Posts: 2103
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Michał Duczmal wrote:Yes, I think so too but for now, I have to allow user to write his own where clause in SQL. That's because, this is how it worked in the old version of the app and we are doing our best to write a better app without changing any of the functionalities. I don't know whether it's the best approach but it's not my decision to make. I would need to have very strong arguments to force solution where filters are not written in plain SQL by admins. So now I am exploring possibilities to do it expected way.


You'd have thought someone would have figured this bit out prior to choosing Criteria as the way forward.
Unless there is some form of backend manipulation (as suggested by Tim above) then you have two conflicting things...the use of Criteria and the desire to stick to hand written WHERE clauses.

Still, I suppose it's what we get paid for.
It'll give you good practice at sucking air between your teeth and tutting..."it'll cost ya"...
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Michał Duczmal wrote:Yes, I think so too but for now, I have to allow user to write his own where clause in SQL. That's because, this is how it worked in the old version of the app and we are doing our best to write a better app without changing any of the functionalities. I don't know whether it's the best approach but it's not my decision to make. I would need to have very strong arguments to force solution where filters are not written in plain SQL by admins. So now I am exploring possibilities to do it expected way.

Then your best option would probably be using the sqlRestriction() method from the org.hibernate.criterion.Restrictions class.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic