Ok, just wanted to make sure I didn't miss anything and understand the reasoning for your preference :)
Am 09.01.2020 um 13:06 schrieb Steve Ebersole: > Well, again, this is configurable. If you don't want to use it, don't ;) > > On Thu, Jan 9, 2020 at 1:28 AM Christian Beikov > <christian.bei...@gmail.com <mailto:christian.bei...@gmail.com>> wrote: > > Am 08.01.2020 um 20:21 schrieb Steve Ebersole: >> On Wed, Jan 8, 2020 at 9:22 AM Christian Beikov >> <christian.bei...@gmail.com <mailto:christian.bei...@gmail.com>> >> wrote: >> >> >> This is about JPA Criteria whereas I was referring to how a >> literal in >> HQL ends up in the SQL. I agree that with JPA Criteria things >> are >> different as the API for parameters in queries is not very >> neat. A flag >> for controlling how literals should be rendered in for JPA >> Criteria >> sounds ok, but ultimately, a literal should be just that, a >> literal. >> >> >> Well no - its about how literals defined in ORM queries should be >> handled in JDBC. The HQL / criteria distinction is not important >> here. >> >> We'll agree to disagree that query literals should always be >> rendered as SQL literals. > > I got that you are concerned with the security aspect and think > that the DBMS optimizer will not be able to build significantly > better plans than with parameters. Is there any other reason why > you prefer rendering literals as JDBC parameter by default? > > Since string literals in JPQL/HQL are defined syntactically like > in SQL, the security part sounds managable to me as other > (currently existing) literals are non-problematic due to their > nature i.e. being numeric or strictly typed such the > representation can't interferre with the SQL they are embedded into. > > The performance part is for me actually very critical. I attached > a few links[1][2][3][4] to articles that cover this topic. > Essentially, the difference in execution plans come from wrong row > count estimates because of wrong selectivity. When using > parameters, DBMS do a few tricks to overcome the problems of > unknown selectivity due to a missing value. Oracle is pretty good > at that AFAIU, but the behavior is configurable and we might run > in an environment where it isn't configure how we'd expect it to be. > > PostgreSQL seems to use a generic plan(with wrong selectivity > estimates) after the 5th execution and other DBMS probably have > similar problems. In general, using literals is imporant when > there are only a few values or when the row distribution is > non-uniform. The problem gets big when a query has lots of joins > that multiply to the wrong estimates, leading to e.g. full table > scans rather than index scans. > > [1] > > http://davetechnotes.blogspot.com/2009/02/literal-vs-bind-variables-bind-variable.html > > [2] > > https://medium.com/@FranckPachot/postgresql-bind-variable-peeking-fb4be4942252 > > [3] > > https://blog.jooq.org/2018/04/12/why-sql-bind-variables-are-important-for-performance/ > > [4] > > https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-inline-values-in-sql/ > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev