JPA CriteriaBuilder value like column

Question

I'm trying to create a query using CriteriaBuilder where I need to have a predicate where the value of the predicate is like the value in the database.

Basically, I need to be able to do the following: WHERE myTestValue LIKE columnValue

In native queries, it is an option to do that, but using the CriteriaBuilder or NamedQueries, it does not seem to work.

String myValue = "foo@bar.com";
cb.where(cb.like(myValue, root.get(Entity_.email));

Is there an option in JPA to do it like this? Or should I fall back to native queries?

EDIT I need to be able to check if a given value matches a wildcard entry in database. So the database has a record %@bar.com%, and I need to check if my given value foo@bar.com matches to that record.

Maybe you have forgot to add "%" at the beginning and at the end of your test string? — perissf, Feb 14 '17 at 15:44
It just does not compile. JPA does not like to do it this way ;) — Erates, Feb 14 '17 at 16:11
Show what it complains about. You also might first try JPQL to verify it'll work there. If it doesn't like the string, try passing it in as a parameter instead cb.where(cb.like(cb.parameter(String.class, "myValueParameter"), root.get(Entity_.email)); and then set the parameter in the query query.setParameter("myValueParameter", myValue); — Chris, Feb 14 '17 at 18:16

score 5 · Answer 1 · answered Feb 14 '17 at 15:45

5

I think your params should be other way round:

cb.where(cb.like(root.get(Entity_.email),myValue);

Aditionally you may need to use add this to the second param:

cb.where(cb.like(root.get(Entity_.email),"%"+myValue+"%");

answered Feb 14 '17 at 15:45

Maciej Kowalski

25,605
12
54
63

I need them to be in this order. I need to check if a given email address matches on a wildcard entry in the database. – Erates Feb 14 '17 at 15:47
Found similar question may this help. http://stackoverflow.com/q/30718445/3136973 – Navnath Feb 14 '17 at 15:49
That's just an example of how to implement `WHERE columnValue LIKE myTestValue`, I need it vice versa `WHERE myTestValue LIKE columnValue` – Erates Feb 14 '17 at 15:55
I see, i will try to do some tests later on some live environment. Are you using JPA 2.1? And EclispeLink version? – Maciej Kowalski Feb 14 '17 at 16:19
This is not an answer to the question. This is for column-like-value. – Ismail Yavuz Nov 26 '19 at 11:29

score 2 · Answer 2 · answered Feb 15 '17 at 05:59

Chris found the answer. First I need to "generate" a parameter.

ParameterExpression<String> senderEmailParameter = cb.parameter(String.class, "senderEmailParameter");
Path<String> senderEmailPath = root.get(Entity_.senderEmail);

Predicate predEmail = cb.like(senderEmailParameter, senderEmailPath);

And then I need to fill the parameter in the query.

q.where(predEmail);

return em.createQuery(q).setParameter("senderEmailParameter", senderEmail).getSingleResult();

This works! Thanks Chris!

This worked for me with JPA, hibernate and spring boot 2.1.9, thank you! — Ismail Yavuz, Nov 26 '19 at 11:28

JPA CriteriaBuilder value like column

2 Answers2