Home » Support » Forum » Help with 'not like/lower' query

Help with 'not like/lower' query


Hi all,

  Honestly, I'm fairly new with JPA2 Criteria queries - but as far as code effort goes, it has saved me a bit of pain. I'm writing a dynamic query builder (only 1 level deep for basic entities) - but  even though I get most queries to work, I am getting the following error. This is using Criteria with a combination of LOWER/UPPER - NOT LIKE:

SELECT COUNT($1.id) FROM User $1
WHERE ($1.owningCompany=:p1) AND LOWER($1.email) NOT LIKE :p2

So in essence, I'm trying to do a count (with a subsequent fetch of a page of 20 results per table view on the UI) where a user belongs to a company, and the lower case email does NOT match the parameter :p2


The bind is as follows: 

com.x.y.z.services.helpers.SearchQueryHelper| - Binded search Param: p2 - Value: %will%


The issue I am getting is:

Caused by: com.objectdb.o.UserException: Unexpected query token '$1.email'
        at com.objectdb.o.MSG.d(MSG.java:61)
        at com.objectdb.o.TKN.J(TKN.java:755)
        at com.objectdb.o.TKI.s(TKI.java:229)
        at com.objectdb.o.QPR.o(QPR.java:158)
        at com.objectdb.o.QRC.<init>(QRC.java:122)
        at com.objectdb.o.QRM.US(QRM.java:245)
        at com.objectdb.o.MST.US(MST.java:884)
        at com.objectdb.o.WRA.US(WRA.java:286)
        at com.objectdb.o.WSM.US(WSM.java:113)
        at com.objectdb.o.STC.r(STC.java:421)
        at com.objectdb.o.SHN.ah(SHN.java:468)
        at com.objectdb.o.SHN.J(SHN.java:146)
        at com.objectdb.o.HND.run(HND.java:133)
        ... 1 more


So I am definitely saying - maybe my query is stuffed.. which is basically the following (an excerpt of my visitor class for handling String properties in entities):

ParameterExpression<String> stringProp = criteriaBuilder.parameter(String.class);
   Path<String> path = root.get(property.getFieldName());
    Predicate predicate;
     predicate = criteriaBuilder.like(criteriaBuilder.lower(path), stringProp);
     stringMapOfValues.put(stringProp, property.getWildcardValue());
    }else if(property.getOperator().equals(Operator.NOT_LIKE)){
     predicate = criteriaBuilder.notLike(criteriaBuilder.lower(path), stringProp);
     stringMapOfValues.put(stringProp, property.getWildcardValue());
    }else if(property.getOperator().equals(Operator.EQUAL)){
     predicate = criteriaBuilder.equal(path, stringProp);
     stringMapOfValues.put(stringProp, property.getValue());
    }else if(property.getOperator().equals(Operator.NOT_EQUAL)){
     stringMapOfValues.put(stringProp, property.getValue());
     predicate = criteriaBuilder.notEqual(path, stringProp);
     predicate = criteriaBuilder.isNull(path);

The binds are taken care of with the 'mapOfStringValues' later. 


Any help is appreciated on this.




You are talking about criteria queries but your first query is a JPQL query.

Is it the JPQL that was generated by ObjectDB from a criteria query using toString?

Anyway, please provide a simple test case following these instructions.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Yes sorry, the query is generated - I print that out for sanity purposes with a debug statement. 



package com.objectdb.forum;

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Root;

public final class ObjectDBTest {

    public static void main(String[] args)  {
        EntityManagerFactory emf =
        EntityManager em = emf.createEntityManager();

        MyEntity e = new MyEntity("test");

        CriteriaBuilder builder = em.getCriteriaBuilder();
  CriteriaQuery<MyEntity> c = builder.createQuery(MyEntity.class);
  ParameterExpression<String> stringProp = builder.parameter(String.class);
  Root<MyEntity> root = c.from(MyEntity.class);
  Path<String> path = root.get("name");
  // Add condition here
  c.where(builder.like(builder.lower(path), stringProp));
  TypedQuery<MyEntity> query = em.createQuery(c);
        query.setParameter(stringProp, "test");
        List<MyEntity> resultList = query.getResultList();


    public static class MyEntity {
        private String name;
        MyEntity(String name) {
            this.name = name;
        public String toString() {
            return name;




Exception in thread "main" [ObjectDB 2.2.7_04] SELECT $1 FROM MyEntity $1 WHERE LOWER ==> ($1.name) <==  NOT LIKE :p1
Unexpected query token '$1.name' (error 752)


The following conditions work:

c.where(builder.like(builder.lower(path), stringProp));


c.where(builder.like(builder.lower(path), stringProp));


but any combination of

c.where(builder.notLike(builder.lower(path), stringProp));


c.where(builder.notLike(builder.upper(path), stringProp));


produces the error.


Thanks again


Thank you for this test case.

It demonstrates a bug in parsing queries with NOT after ().

Please try build 2.2.7_08 that should fix this bug.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Verified that it works well with 2.2.7_08.


Post Reply

To post a reply and/or subscribe to update notifications - please login