I am building a query using JPA Criteria API. When I created two restriction predicates using javax.persistence.criteria.Path#in(Collection<?>)
method the generated SQL query was a little bit different than I excpected.
The first predicate which was build over int
attribute produced SQL with all elements of parameter collection inlined: in (10, 20, 30)
.
The second predicate which was build over String
attribute produced parametrized SQL: in (?, ?, ?)
.
Let me show:
Entity:
@Entity
public class A {
@Id
private Integer id;
private int intAttr;
private String stringAttr;
//getter/setters
}
Query:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<A> q = cb.createQuery(A.class);
Root<A> root = q.from(A.class);
q.where(
root.get("intAttr").in(Arrays.asList(10, 20, 30)),
root.get("stringAttr").in(Arrays.asList("a", "b", "c"))
);
entityManager.createQuery(q).getResultList();
Log:
select
a0_.id as id1_0_,
a0_.intAttr as intAttr2_0_,
a0_.stringAttr as stringAt3_0_
from
A a0_
where
(
a0_.intAttr in (
10 , 20 , 30
)
)
and (
a0_.stringAttr in (
? , ? , ?
)
)
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [a]
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [b]
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [VARCHAR] - [c]
My questions:
- Why are elements of Integer list inlined directly to sql and elements of String list are handled as prepared statement parameters?
- Is this feature Hibernate specific or is it guaranteed by JPA?
- From DB perspective which of two should be preferred?
- Is this int-yes string-no inlining somehow related to sql injection?
- Is this somehow related to limitation of number of values in sql IN clause the RDMBS can process?
- How to write a criteria query which will handle Integer parameter list the same way as String parameter list.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…