This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes JPQL: converting a certain type of dynamic query to a named and static query. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPQL: converting a certain type of dynamic query to a named and static query." Watch "JPQL: converting a certain type of dynamic query to a named and static query." New topic
Author

JPQL: converting a certain type of dynamic query to a named and static query.

Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Hello:

Something which I love about named queries is the fact that are parsed one time only, when the application is deployed. That way I'm able to know if there are syntax errors without the need of testing every query by hand.

In my Java projects I still have some methods where I build JPQL queries dynamically because they depend on whether the user assign values to the existing parameters or don't use them at all. In these cases it's not possible to make a query "version" per every possible parameter combination because there are lots of them and would be crazy. For example, in a method which lists students, the user may filter them by name, surname, age, genre, eye colour, hair colour, etcetera.

In other words, if a parameter is NULL, it's not included in the WHERE clause of the query but, if it has a value, then it's (I'm not using the JPA Criteria API by the way).

Well, I love experimenting and have been thinking about possible ways to replace these kind of dynamic queries with named and static queries. One example which I've considered is:



SELECT st
FROM Student st
WHERE

(:name IS NULL OR LOWER (st.name) LIKE LOWER (:name%))
AND (:surname IS NULL OR LOWER (st.surname) LIKE LOWER (:surname%))
AND (:age IS NULL OR st.age = :age)
AND (:hairColour IS NULL OR st.hairCoulour = :hairColour)
AND ...



As you can see, I'm controlling the possible "nullability" of the named parameters in the query itself, instead of building the WHERE clause dynamically. Because I've used named parameters, I only need to pass every parameter once, although they appear twice in the query.

Do you think that this approach would be good idea in a real project? Or am I ignoring something important?

By the way, I've analysed the query plan that this query causes in PostgreSQL, which is the database that I employ, and it behaves like if the query didn't include the NULL parameters' clauses.

Thank you so much.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2271
    
  28

Why aren;t you using the Criteria API? Seems like a perfect fit for what you need to do.
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Jayesh A Lalwani wrote:Why aren;t you using the Criteria API? Seems like a perfect fit for what you need to do.


Almost all of my projects are still using JPA 1.0, so I can't rely on the Criteria API (yet).

But, even if I could use it, these queries would need to be analysed and translated to SQL every time that I executed them, because they're not static, Right? That repetitive work is what I want to avoid with my approach.

Thanks.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Some JPA providers such as EclipseLink maintain a JPQL parse cache. So if you execute the same dynamic query multiple times, it will not need to re-parse the query.

As long as you use parameters in your query, even with dynamic arguments, the number of probably JPQL strings will be a small set, and be cached.


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

James Sutherland wrote:Some JPA providers such as EclipseLink maintain a JPQL parse cache. So if you execute the same dynamic query multiple times, it will not need to re-parse the query.

As long as you use parameters in your query, even with dynamic arguments, the number of probably JPQL strings will be a small set, and be cached.


Right. I always use parameters for queries because this way I guess that a clever JPA provider might leave the resulting SQL prepared into the database server and re-use it at any time (like many of us did manually before we started to use ORMs).

Time ago I read a little about such kind of cache in EclipseLink, but since I was using Hibernate (into a JBoss Application Server), I preferred not to take that possibility as something "fully guaranteed" always. Although I guess that Hibernate might have something similar surely, of course.

Anyway, even if we considered the possibility of caching the dynamic queries, Would you still prefer them over the type of static query which I introduced in my first message (using the "trick" of checking NULL parameters)? The feature of automatically checking the JPQL syntax on deployment is being so helpful for me, that I consider it really attractive.

Thank you.
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Finally I found an "unresolvable" problem. Checking my projects again I've realized that I can't replace all my dynamic queries with static queries or, at least, without messing the code too much.

The main reason is that, after executing every dynamic query, I always alter it "on the fly" to get the total registries which match certain criteria without applying offsets or limits. I need this total for pagination tasks. Basically what I do is taking the original query, replacing the SELECT clause with "SELECT COUNT (*)" and ignore any SORT clause, so I need to access the query content at runtime, something which is unavailable for named queries, I guess. I could duplicate every query by hand but I believe that would complicate everything too much.

Definitively, I'm going to keep my dynamic queries. As I said in previous messages, my interest on static queries was because they were analysed during the deployment and saved me much time of testing. But I guess that using typical Java test cases is a more complete and convenient way to achieve that.

Another good thing is that static queries are transformed by the JPA provider into prepared statements in the database server... But dynamic queries seem to be converted too, at least with Hibernate . I thought that they were not, but I've checked the PostgreSQL log and every query is executed from a prepared statement. Does it mean that Hibernate keeps some kind of track of the prepared statements created to re-use them? Or does it make a new prepared statement every time (so it's used only once)?
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

I've found the solution to my last question. So I'm answering myself, just in case that this may help other people.

My assumption that JPQL dynamic queries couldn't be converted to SQL prepared statements was wrong. Both named/static queries and dynamic queries are susceptible of being converted. Indeed, in the case of Hibernate (or at least the implementation included in JBoss AS 7), it creates prepared statements for every type of query which I've tested.

My mistake was to believe that, because Hibernate handles the transformation of the JPQL queries to prepared SQL statements, it also got some control over the treatment of these statements in lower levels of the process. However, it's the Java Connector Architecture (JCA) plus the corresponding JDBC driver the responsible ones of handling the connections and, if it's supported, also the pool of prepared statements. I've found that JBoss AS 7 relies on IronJacamar as its JCA .

After reading more documentation and doing lots of tests, I've configured the data sources appropriately. In the IronJacamar Data Source subsystem there is an option called <prepared-statement-cache-size> which by default isn't set, so it adopts the value 0. This is the reason why the prepared statements never were re-used.

I've changed this to 100 and the statistics of the JBoss AS 7 control panel have started to move . However, the logs of PostgreSQL showed me that the prepared statements were not being re-used really! What? But then I've remembered that, when I checked the documentation of the PostgreSQL JDBC driver yesterday, I read that you could set a threshold to set from how many calls you wanted to start to re-use existing prepared statements. And in this case the minimum quantity seems to be 5 (I don't know if it's configurable). Once you reach the 5th call, you can check the logs and see how the statement is executed without being re-parsed and re-planned every time .


PS: I'm sorry if some data here is unnacurate or even wrong, but I've not much idea about all these matters. I just wanted to share my solution.
Telemart Network
Greenhorn

Joined: Apr 24, 2012
Posts: 4
Exactly, if you execute the same dynamic query multiple times, it will not need to re-parse the query. Try to change the version.


Career Guidance Package English for Beginners
Michael Buenting
Greenhorn

Joined: Aug 18, 2012
Posts: 2
Hi Avor,

Your approach looks sound to me. I have used it for years. Please see another example at

http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName

However Postgresql appears to be the only database that does not yet support this in a static mode.

The above JPQL fails at runtime with postgresql 9.1-901-1.jdbc4 and Hibernate 4.1.1.Final or EclipseLink 2.4.0 due to this issue at https://github.com/pgjdbc/pgjdbc/issues/3
The reason why it works in your case may be that your JPA provider works around this by creating a dynamic PreparedStatement after evaluating the null conditions.
I am guessing this because you wrote: "the query didn't include the NULL parameters' clauses.". What is your JPA provider?
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Michael: Hello and thank you for your interesting answer. I'm sorry for the delay, but I've been busy trying to refresh my mind about this issue, he he he. I had forgotten how I had done my tests and needed to test all these things again :S .

I had written a long message to you, but then I realized why it worked for me in certain cases. It wasn't because of my JPA provider (Hibernate). It was because I had been using PostgreSQL 8.2.9 in my production environment (it's an old application). If my memory serves me right, since version 8.3 or 8.4, PostgreSQL removed almost all implicit casts. And that's the cause why these kind of static JPQL queries fail with the most modern versions of PostgreSQL.

However, I believe that I also tested it successfully in my development environment, which uses PostgreSQL 9.0. How could it be possible? Well, maybe I added implicit casts in the application's database and forgot about this fact. I remember that a few times I re-created the implicit casts from version 8.2 into version 9.0:



Executing these SQL queries allowed me to mimic the behaviour of version 8.2 in version 9.0, approximately. And it also allowed the static JPQL queries with NULL named parameters to work properly. Although I needed to put the IS NULL restriction at the end, not at the beginning (I can't remember why, but I guess that it was to help PostgreSQL get the right cast from the context, by itself):



About the link to GitHub which you have suggested, Do you think that the JDBC driver maintainer might add support for something like that someday? Is there a place where we can vote that feature?

Thank you.
Michael Buenting
Greenhorn

Joined: Aug 18, 2012
Posts: 2
Avor,

Your reply is amazing. This is the most productive discussion on the internet for this type of problem. I don't think the driver will be fixed. You could discuss this on the mailing list pgsql-jdbc@postgresql.org, preferably with a reference to this page. The following links might be of interest:

https://hibernate.onjira.com/browse/JPA-28
https://hibernate.onjira.com/browse/JPA-31
https://bugs.eclipse.org/bugs/show_bug.cgi?id=387545
https://issues.apache.org/jira/browse/DERBY-5728

Meanwhile the latest EclipseLink version has introduced a cast() function which solves the problem on JPQL level. A similar solution might work with Hibernate, too.

I hope you will be able to take this to a new level so we don't need these workarounds. Thank you and all the best.
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Michael Buenting: It's really nice to find more people interested in this issue, because it has been driving me crazy for years (among other silly JPA/JPQL limitations). Unfortunately I have not been able to find much useful information until now, and have had to do "dirty tricks" here and there (which is entertaining some times, but exasperating and not elegant others).

About the function CAST, I tried to use it on Hibernate much time ago (it was already available), but I was not able to make it work. Surprisingly, I've just tested it in JBoss AS 7.1.1 Final, which includes Hibernate 4.0.1 Final, and it has worked as expected. An example:



This leads me to think that my past tests were wrong on the type to cast. I've used "Text" or "TEXT" as type literals but it only recognises "text", as is, all in lower case. You may also use "string" but not "String". It's weird, because I would bet that I also tried "text" time ago. I always type in lower case by default, so...

I did not found much information on this respect when I dug into the Hibernate documentation. It only said that the function CAST must be supported by the underlying data base, which clarifies that it can't be used "safely" everywhere. By the way, tested on JBoss AS 5.1.0 GA and it's OK too. Thanks for making me test all this mess again or, otherwise, I still would be wrong about the CAST implementation in Hibernate.

It's also very informative that you mentioned that other JDBC drivers have been updated to take care of this issue, because I thought that this was a common issue with all databases that do not use implicit casts. This weekend I'll take a look to the links which you've suggested. Thanks you.
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Updating this thread again, only for informative purpose...

Michael was right and it's unlikely that the JDBC implementation change the default behaviour. I've been checking different documentation and all this indicate something similar. The point 6.1.5 of the JDBC guide clarifies that the method setObject (<position>, null) won't accept null unless you specify thetype with the corresponding 3rd argument: http://docs.oracle.com/javase/6/docs/technotes/guides/jdbc/getstart/preparedstatement.html#1003559 .

So, internally, the JPA implementations need to use either the method setObject (<position>, null, <type>) or setNull (<type>) . But I guess that none of these methods is useful for the JPQL queries, because at compile time they do not know what type of parameters are supposed to receive, and because the Query.setParameter method does not allow to specifiy the data type for nulls.

Moreover, I believe that even if the developers of the PostgreSQL JDBC driver implemented the method form setObject (<position>, null), it might fail on the server side. Because some times PostgreSQL is not able to infer the type from the context: http://www.postgresql.org/docs/9.2/interactive/sql-prepare.html . This point and the fact that PostgreSQL does not contain the implict casts that it got in the past, are a big barrier. In this respect, the first link posted by Michael clarifies it much better than me.

I may be wrong, but believe that JPA would need a way to set the type of query parameters explicitly when they may be null. Either in JPQL (with some kind of operator) or implenting a method like Query.setNullParameter (<type>).


Possible workaround?

Using the non-JPQL function CAST provided by Hibernate and EclipseLink is easier. But it depends on support from the database, so I've thought about a possible universal workaround... I may be wrong, but what if we did something like this?


In the code side, we would check if every parameter is null. If it's, we would set its respective :xxxIsNull parameter to true, and the parameter itself to an arbitrary value which is not null. Because the condition ":xxxIsNull IS TRUE" would be evaluated first (or at least, it would be the cheapest comparison for the database query planner), the arbitrary value would be discarded and would not affect the performance (in theory).

I'm speaking in conditional because it's late and can't test it just now, he he he :P .
Justin Kennedy
Greenhorn

Joined: Aug 14, 2013
Posts: 1
Hi Avor,

Your solution works great! I was searching around as I had a similar problem and luckily I found this thread. The only change I had to make was instead of "IS TRUE" I used "= TRUE".

Thanks
Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 95

Hi Justin. It makes me happy that it has been of help to someone. I know that it's not an ideal solution, but it's valid while we wait for a fix to this issue (if it ever arises). I wish the CAST function were implemented as part of JPQL :/ .
 
wood burning stoves
 
subject: JPQL: converting a certain type of dynamic query to a named and static query.
 
Similar Threads
How to do optional wildcard select in JPA named queries?
Dynamically setting sort direction
JPA native Query - Select... where... IN, How to set list of values to setParameter()?
dynamic named queries
How to create dynamic query string