Wild card search on Long value using Criteriabuilder query

Question

I want to use wild card search on a field which is a Long value (in Entity class) and Integer in database.Here Contact is entity class which has field : Id as Long value in entity class and as a Integer in database.The following is code.

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createTupleQuery();
Root<Contact> root = query.from(Contact.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (searchCriteria.getContactId() != null) {
    if(searchCriteria.getContactId().contains("%")){                
       predicates.add(builder.like( 

       (root.get(Contact_.id)),-->Note this line

       builder.literal(searchCriteria.getContactId()+"%")));

        }
    }

Here builder.like will not accept root.get(Contact_.id) as Contact_.id is a long value.We will get compilation error here.Here searchCriteria.getContactId() is string value.

Is there any way to cast the root.get(Contact_.id) as string type?

public static volatile SingularAttribute<Contact, Long> id;

So far I have tried with

((Expression<String>)(root.get(Contact_.id).as(String.class).

But i am getting query syntax exception.It is not able to convert it to perfect syntax. It is giving query like -> ( cast(generatedAlias0.id as varchar(255)) but not as ( cast(generatedAlias0.id as string)) when i look at logs.

Compiler error is expected as `CriteriaBuilder.lower()` method accepts string expressions only. What you actually trying to do is getting lowercase from the long argument. — wypieprz, Apr 23 '14 at 13:37
@wypieprz Hi,Thank you very much for going into this issue.Is there any way to convert this (root.get(Contact_.id)) to string? — GrandPa, Apr 23 '14 at 13:51
Why would you need such conversion anyway? Could you please give an example of what you are trying to achieve, i.e. using JPQL? I'm afraid I don't get the point. — wypieprz, Apr 23 '14 at 14:46
Here i want to use wild card search on a number(its a Long value in entity class and Integer in database).Here the number is Contact_.id(A long value) and searchCriteria.getContactId() will return the number with wild card which is a string.Here it will allow only String expression.So far I have tried with ((Expression)(root.get(Contact_.id).as(String.class).But i am getting query syntex exception.It is not able to convert it to perfect syntex.It is giving query like -> ( cast(generatedAlias0.id as varchar(255)) but not as ( cast(generatedAlias0.id as string)). — GrandPa, Apr 23 '14 at 15:17
Please let me know if my exception is not clear to you.Thank you in advance. — GrandPa, Apr 23 '14 at 15:20
The exception is clear to me. Fortunately there are other ways to deal with the problem - see my answer below... — wypieprz, Apr 23 '14 at 21:04

score 2 · Accepted Answer · edited May 23 '17 at 10:28

2

When i did search with similar tags ,I found a good solution to above problem which uses the criteriabuilder only.I have tried it and Its working fine. We can find it in below link.

JPA: How to perform a LIKE with a NUMBER column in a static JPA MetaModel?

edited May 23 '17 at 10:28

Community

1
1

answered Apr 24 '14 at 09:31

GrandPa

484
9
28

wypieprz · Answer 2 · 2014-04-23T21:08:26.360

Some CriteriaBuilder's methods specified by JPA (like, notLike, concat, substring, trim, lower, upper, length, locate) can only work with strings thus rejecting the integer expression:

predicates.add(
    Expression<Long> idExpr = root.get(Contact_.id);
    builder.like(idExpr), // error: Expression<String> is expected
    builder.literal(searchCriteria.getContactId() + "%")
);

To workaround the problem you may try to use Hibernate extensions, for example:

HCQ (Hibernate Criteria Query) with a little help of a native SQL

Criteria c = session.createCriteria(Contact.class);
c.add(Restrictions.sqlRestriction(" CAST(id AS varchar(255)) LIKE '1%'"));
List<Contact> entities = c.list();

HQL (Hibernate Query Language)

Query q = session.createQuery("FROM Contact c WHERE STR(c.id) LIKE '1%'");
List<Contact> c= q.list();

SQL

SQLQuery q2 = session.createSQLQuery("SELECT * FROM Contact " +
                                     "WHERE CAST(id AS varchar(255)) LIKE '1%'");
List<Contact> entities = q.getResultList();

Looks like only HQL has STR() expression or I cannot find equivalent in HCQ. Anyway STR(c.id) used in HQL will be translated to CAST(id AS VARCHAR(255)) clause.

It will work HCQ,HQL,SQl as you said.Thank you very much.Meanwhile i found in stackoverflow after searching with same topic that [here](http://stackoverflow.com/questions/9802224/jpa-how-to-perform-a-like-with-a-number-column-in-a-static-jpa-metamodel).Here a new class has been created that will work fine using criteria builder. — GrandPa, Apr 24 '14 at 08:14

Wild card search on Long value using Criteriabuilder query

2 Answers2