JPA criteria API don't bind numeric field

Description

When you use JPA criteria API, the numeric value are not binded but are directly set in the SQL instead.

eq: you have a generated SQL like:
select ... from ... where age=12;
instead of
select ... from ... where age=?;
With '12' as parameter.

when you test the following code:

Environment

None

Activity

Show:
Steve Ebersole
July 7, 2016, 9:52 PM

Also, I think it is important to note that there should be different understood "types" of literals. For example, "source" literals would be coming from explicit literals in the query. But we might also have literals for multi-tenancy values, partition columns, etc. I can see some of those wanting different handling rules.

Piotr Szarański
February 9, 2017, 2:44 PM
Edited

This error means much more than you think. Every single criteria query "SELECT x FROM Entity x WHERE x.id IN (...)" lands in QueryPlanCache as an HQLQueryPlan instance, eventually thrashing legit HQLQueryPlan instances and causing GC pain (witl long IN clauses). This issue should have higher priority.

Greg Wiley
March 17, 2018, 4:06 PM

I found this issue while investigating a Spring Data Specification API query that is performing poorly. You'll note the Specification API basically wraps the JPA criteria API.

Based on the queries our DBA is showing us, the SQL generated by the JpaSpecificationExecutor is hard-coding numeric values into the SQL. In our case, those numeric values are customer account ids. So the SQL generated for each customer appears different and we never benefit from statement caching.

For us, a property that would allow us to control this behavior would be very helpful.

Arnaud Esteve
July 23, 2018, 9:24 AM

I do agree with Gregory Wiley, and am facing the exact same issue.

We're heavily relying on the criteria API, and especially the `CriteriaBuilder.equal` method to construct requests dynamically.

Everytime a request hits the DB, the literals might be different, but the overall query is just the same, not benefiting from statement caching.

This is leading us to huge huge performance issues, unfortunately.

I wish you could do something about that, or provide a way (even a dirty hack, to be honest... at this point) to "force" the specification executor to use parameter binding.

Thanks for your help, but if you were wondering if this ticket was legit or not, I can confirm it's kinda disastrous for us.

Vlad Mihalcea
February 6, 2019, 6:05 PM

This was fixed by HHH-9576.

What you need to do is to set the following configuration property:

For more details, check out this article.

Duplicate

Assignee

Steve Ebersole

Reporter

gwa

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

Yes, likely

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure