CODEWITHSUNDEEP
postgresqljpa
evenoners
evenoners

Reputation: 53

Criteriabuilder like, how to do it for Long?

i try use "like" method from Criteriabuilder for get all record based on pattern " 10% ".

I want get record where ID is - 101, 10002, 1003,1000 etc...

I've use this code:

Predicate p = cb.like(r.<String>get("ID").as(String.class), "10%")

but i got Exception where i see what postgres can't execute query like this:

SELECT ID, NAME, SOMETHING FROM TABLE WHERE ID LIKE 10%

That is JPA (Glassfish 4.x) generate wrond query.

Right query must like that : 

SELECT ID, NAME, SOMETHING FROM TABLE WHERE CAST (ID as TEXT) LIKE '10%'

How to build query via Criteria API that i got a right query for postgres ?

Updated:

I try write a CAST function :

Expression<String> postgresqlCastFunction = cb.function("CAST", String.class, r.<String>get("ID").as(String.class));
Predicate p = cb.like(postgresqlCastFunction, "10%");

but got a query like this :

FROM TABLE WHERE (CAST(ID) LIKE ?)

, so, how to add need expression in function for this right result -

FROM TABLE WHERE (CAST(ID as TEXT) LIKE ?) ..

Upvotes: 5

Views: 4812

Answers (1)

wypieprz
wypieprz

Reputation: 8219

An example implementation using PostgreSQL native TO_CHAR function may look as follows:

JPQL

SELECT r FROM Records r 
WHERE FUNCTION('TO_CHAR', r.ID, 'FM9999999999') LIKE :pattern

Criteria API

Path<String> id = r.get("ID");
Expression<String> format = cb.literal("FM9999999999");
Expression<String> function= cb.function("TO_CHAR", String.class, id, format);
ParameterExpression<String> pattern = cb.parameter(String.class, "pattern");
Predicate like = cb.like(function, pattern);
cq.where(like);

also you can build the query as an one-liner:

cq.where(cb.like(cb.function("TO_CHAR", String.class, r.get("ID"), cb.literal("FM9999999999")), cb.parameter(String.class, "pattern")));

Execute the above query:

Query q = em.createQuery(cq).setParameter("pattern", "10%");

Upvotes: 3

Related Questions