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;
    if(property.getOperator().equals(Operator.LIKE)){
     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);
    }
    else{
     predicate = criteriaBuilder.isNull(path);
    }
    predicatesList.add(criteriaBuilder.and(predicate));
  }

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

Any help is appreciated on this.

Thanks

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.

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

Code:

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 =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();
        MyEntity e = new MyEntity("test");
        em.persist(e);
        em.getTransaction().commit();

        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");
  c.select(root);
 
  // 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();
        System.out.println(resultList);

        em.close();
        emf.close();
    }

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

Output:

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

The following conditions work:

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

and

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

but any combination of

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

and

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.

Verified that it works well with 2.2.7_08.

Thanks!