3

I have noticed following, when running JPA-CriteriaAPI with Hibernate.

Database table

CREATE TABLE kernel_group (
  groupId NUMBER(10,0) NOT NULL,
  groupName VARCHAR(64) NOT NULL UNIQUE,
  autoGroup NUMBER(1,0) DEFAULT (0),
  jpaVersion NUMBER(20,0) NOT NULL,

  PRIMARY KEY(groupId),

  CONSTRAINT cons_kernel_group_autoGroup CHECK(autoGroup IN (0,1)) ENABLE
);

JPA Entity

@Entity
@Table(name="kernel_group")
public class KernelGroup implements Serializable {
    private static final long serialVersionUID = 1L;

    private int groupId;
    private boolean autoGroup;
    private String groupName;
    private List<KernelUserGroup> kernelUserGroups;
    private long jpaVersion; 
    ....
}

JPA Criteria-API

  • String In Where-Clause

    CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<KernelGroup> cQuery = cb.createQuery(KernelGroup.class);
Root<KernelGroup> root = cQuery.from(KernelGroup.class);
cQuery.where(cb.equal(root.get(KernelGroup_.groupName), "Group1"));
KernelGroup group = em.createQuery(cQuery).getSingleResult();

    Query String on OracleDB(Parameterized) 

    select distinct kernelgrou0_.groupId as groupId1_14_, kernelgrou0_.autoGroup as autoGrou2_14_, kernelgrou0_.groupName as groupNam3_14_, kernelgrou0_.jpaVersion as jpaVersi4_14_ from kernel_group kernelgrou0_ where kernelgrou0_.groupName=:1

  • Boolean In Where-Clause 

    CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<KernelGroup> cQuery = cb.createQuery(KernelGroup.class);
Root<KernelGroup> root = cQuery.from(KernelGroup.class);
cQuery.where(cb.equal(root.get(KernelGroup_.autoGroup), true));
KernelGroup group = em.createQuery(cQuery).getSingleResult();

    Query String on OracleDB(Parameterized) 

    select distinct kernelgrou0_.groupId as groupId1_14_, kernelgrou0_.autoGroup as autoGrou2_14_, kernelgrou0_.groupName as groupNam3_14_, kernelgrou0_.jpaVersion as jpaVersi4_14_ from kernel_group kernelgrou0_ where kernelgrou0_.autoGroup=:1

  • Number In Where-Clause 

    CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<KernelGroup> cQuery = cb.createQuery(KernelGroup.class);
Root<KernelGroup> root = cQuery.from(KernelGroup.class);
cQuery.where(cb.equal(root.get(KernelGroup_.groupId), 2));
KernelGroup group = em.createQuery(cQuery).getSingleResult();

    Query String on OracleDB(Not Parameterized) 

    select distinct kernelgrou0_.groupId as groupId1_14_, kernelgrou0_.autoGroup as autoGrou2_14_, kernelgrou0_.groupName as groupNam3_14_, kernelgrou0_.jpaVersion as jpaVersi4_14_ from kernel_group kernelgrou0_ where kernelgrou0_.groupId=2

why JPA/Criteria-API behaves differently with String/Boolean and Number in where Clause? why for String and boolean, it creates Parameterized-Query? But not for Numbers?

It must be to take advantage of Parameterized-Query by protecting against SQL-Injection and make query performance faster, so DB does not need to same parse query all the time.

But for query with Number in Where clause I need to use ParameterExpression in my code to make it parameterized. Is there any other generalized way in JPA, by which all my queries with Number in where clause become parameterized and i do not need to do it manually in my code?

Tarana
  • 119
  • 1
  • 9
  • 1
    "JPA" does not behave differently; it is simply an API. The question should be why does Hibernate not parameterise the query ... other JPA providers likely do it differently. Updated the question to reflect this – Neil Stockton Sep 15 '16 at 14:24
  • This behavior is one more reason to avoid Hibernate's Criteria implementation. We have already abandoned Criteria entirely due to intolerable locking issues in the ClassLoader. That problem is described well at http://dimovelev.blogspot.com/2015/02/performance-pitfalls-hibernate-criteria.html and (another) bug has been opened with the hibernate team: https://hibernate.atlassian.net/browse/HHH-10746. – Rob Sep 15 '16 at 15:37
  • 1
    Perhaps if you put your literals in the queries as __parameters__, rather than literal values, then it is more of a hint to parameterise them ?;-) – Neil Stockton Sep 15 '16 at 15:37
  • Answer to why Hibernate behaves like this : http://stackoverflow.com/questions/36811521/why-hibernate-inlines-integer-parameter-list-passed-to-jpa-criteria-query?rq=1 – Tarana Sep 16 '16 at 10:05

0 Answers0