CODEWITHSUNDEEP
javaoracle-databasehibernatejpacriteria
Olimpiu POP
Olimpiu POP

Reputation: 5067

JPQL query containing LIKE converted into criteria

I need to use the LIKE operator into an JPA query. I need to use it for types other then String but the JPA criteria API allows me to add only String parameters. I tried using the .as(String.class) but something fails and also tried calling the CAST function from the underlying Oracle that again fails for unknown reasons to me.

I tried writing the query also in JPQL and it works as expected. This is the query:

SELECT p from CustomerOrder p where p.id like '%62%'

UPDATE:

The query must be built in a generic fashion as it is for filtering, so it needs to be created at runtime. On the query that is already created I tried to add the LIKE clause like this:

query.where(builder.like(selectAttributePath.as(String.class), "%"+filterValue.toString().toLowerCase()+"%"));

But this crashes with this exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 156 [select distinct generatedAlias0.id from de.brueckner.mms.proddetailschedact.data.CustomerOrder as generatedAlias0 where cast(generatedAlias0.id as varchar2(255 char)) like :param0]

I executed the same query directly to Oracle using SQLDeveloper, so it should be sound from this point of view. So the problem is the Hibernate is the issue. Any suggestions on how to fix it?

How can I write this query using JPA Criteria?

Upvotes: 0

Views: 1014

Answers (2)

Nayan Wadekar
Nayan Wadekar

Reputation: 11622

You can try the below code, it might require modifications.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CustomerOrder> cq = cb.createQuery(CustomerOrder.class);
Root<CustomerOrder> order = cq.from(CustomerOrder.class);
cq.where(cb.like(Long.valueOf(order.get(CustomerOrder_.id)).toString(), "%62%"));

TypedQuery<CustomerOrder> q = em.createQuery(cq);
List<CustomerOrder> results = q.getResultList();

Upvotes: 0

Olimpiu POP
Olimpiu POP

Reputation: 5067

I fixed the problem by invoking the 'TO_CHAR' function from the underlying Oracle DB and using the LIKE operator like for normal String's.

query.where(builder.like(selectAttributePath.as(String.class), "%" +filterValue.toString().toLowerCase() + "%")

Upvotes: 1

Related Questions