[FIXED] Spring JPA criteria builder greaterThan : Can I pass String value for comparing Number type in database

Issue

I am using Spring Data JPA criteria builder greaterThan API for doing a comparison.

The database field rating is of type NUMBER(5,0). Entity class has a field rating which is of type Integer. And I am passing the value of type String for the comparison.

Even though I am not passing the Integer value for the comparison, still it is returning valid results. Please help me understand how this is possible.

Does it mean, I can pass the java String version of the database field to the greaterThan method when the actual data type of the field in the database is of a different type.

and my comparison block of code looks like this

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery cq = cb.createQuery();

Root<Customer> root = cq.from(Customer.class);
//you can see here , value 20 I am passing as String
Predicate comparisonPredicate = cb.greaterThan(root.get("rating"), "20");

cq.where(comparisonPredicate );

cq.select(root);

TypedQuery<Customer> query = entityManager.createQuery(cq);

Solution

This behavior is not documented.
Looking at the openJPA source code for Expressions.GreaterThan, and more specifically the toKernelExpression,

@Override
org.apache.openjpa.kernel.exps.Expression toKernelExpression(ExpressionFactory factory, CriteriaQueryImpl<?> q){
  Value val1 = Expressions.toValue(e1, factory, q);
  Value val2 = Expressions.toValue(e2, factory, q);
  Expressions.setImplicitTypes(val1, val2, e1.getJavaType(), q);
  return factory.greaterThan(val1, val2);
}

JPA uses the type of the first expression to build the query. This behavior looks to be an accidental feature more than an intentional implementation. You should not rely on this and should explicitly cast the variable. You could also use a MetaModel to get a compiler error when trying to compare apples to oranges.

Answered By - Lookslikeitsnot