APA api criteria with CONTAINS function

Question

I am trying to execute a Criteria API request using the CONTAINS (MS SQL) function:

select * from com.t_person where it contains (last_name, 'xxx')

CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Person> cq = cb.createQuery(Person.class); Root<Person> root = cq.from(Person.class); Expression<Boolean> function = cb.function("CONTAINS", Boolean.class, root.<String>get("lastName"),cb.parameter(String.class, "containsCondition")); cq.where(function); TypedQuery<Person> query = em.createQuery(cq); query.setParameter("containsCondition", lastName); return query.getResultList(); 

But getting exception: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node:

Any help?

Answer 1

If you want to use CONTAINS , it should be something like this:

 //Get criteria builder CriteriaBuilder cb = em.getCriteriaBuilder(); //Create the CriteriaQuery for Person object CriteriaQuery<Person> query = cb.createQuery(Person.class); //From clause Root<Person> personRoot = query.from(Person.class); //Where clause query.where( cb.function( "CONTAINS", Boolean.class, //assuming 'lastName' is the property on the Person Java object that is mapped to the last_name column on the Person table. personRoot.<String>get("lastName"), //Add a named parameter called containsCondition cb.parameter(String.class, "containsCondition"))); TypedQuery<Person> tq = em.createQuery(query); tq.setParameter("containsCondition", "%näh%"); List<Person> people = tq.getResultList(); 

It seems that some of your code is missing from your question, so I make a few assumptions in this snippet.

Answer 2

You can try using the CriteriaBuilder like function instead of the CONTAINS function:

 //Get criteria builder CriteriaBuilder cb = em.getCriteriaBuilder(); //Create the CriteriaQuery for Person object CriteriaQuery<Person> query = cb.createQuery(Person.class); //From clause Root<Person> personRoot = query.from(Person.class); //Where clause query.where( //Like predicate cb.like( //assuming 'lastName' is the property on the Person Java object that is mapped to the last_name column on the Person table. personRoot.<String>get("lastName"), //Add a named parameter called likeCondition cb.parameter(String.class, "likeCondition"))); TypedQuery<Person> tq = em.createQuery(query); tq.setParameter("likeCondition", "%Doe%"); List<Person> people = tq.getResultList(); 

This will result in a query similar to the following:

 select p from PERSON p where p.last_name like '%Doe%';