CODEWITHSUNDEEP
javapostgresqljpacriteria
Purport Tees
Purport Tees

Reputation: 504

Criteria JPA - Call Postgres CAST function

I'm trying to call a Postgres function with Criteria but it's not working. I need to use the LIKE clause in a UUID field, so I need to convert into VARCHAR first.

The result I need:

SELECT * FROM my_table WHERE cast(uuid as varchar(36)) like '%1234%';

What I'm doing in Criteria:

final Path<UUID> uuidField = from.get("uuid");
var cast = cb.function("cast", String.class, uuidField, cb.literal("as varchar(36)"));
cb.like(cast, String.format("%%%s%%", stringValue));

The query which is being generated:

HQL: select generatedAlias0 from com.MyTable as generatedAlias0 where function('cast', generatedAlias0.uuid, 'as varchar(36)') like '%1234%' order by generatedAlias0.name asc

Error:

2022-08-08 18:38:48,549 WARN  [io.ver.cor.imp.BlockedThreadChecker] (vertx-blocked-thread-checker) Thread Thread[vert.x-eventloop-thread-9,5,main] has been blocked for 2393 ms, time limit is 2000 ms: io.vertx.core.VertxException: Thread blocked
    at antlr.ASTFactory.make(ASTFactory.java:342)
    at antlr.ASTFactory.make(ASTFactory.java:352)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.jpaFunctionSyntax(HqlBaseParser.java:4633)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1075)

The log is not so clear (I'm using Quarkus + Hibernate Reactive), but I suspect it crashed in database because the function('cast', generatedAlias0.uuid, 'as varchar(36)').

I think it should be something like: function('cast', generatedAlias0.uuid, as varchar(36)) (without quotes). But I don't know how to achieve this result to test my theory.

How can I call this CAST function?

Upvotes: 0

Views: 2043

Answers (2)

Ben
Ben

Reputation: 1

You must use ".as()" JPA function so the "cast" is called natively.

ie with your use case:

cb.like(from.get("uuid").as(String.class), String.format("%%%s%%", stringValue));

Upvotes: 0

Purport Tees
Purport Tees

Reputation: 504

After investigating some possible solutions (I'm avoiding to create custom database routines) I found something interesting in a answer from another question:

Currently JPA does not have APIs for replace() and cast(string as numeric). But you can use CriteriaBuilder.function(...) to create database native functions if database portability is not critical.

Source: JPA criteria builder: how to replace and cast a string to numeric in order-by?

I don't know if this is documented is some place, but assuming that there is no way to call CAST(x AS y) using Criteria, I tried a workaround to force the UUID to VARCHAR cast without using the probably unsupported CAST function.

I tested this direct SQL query to database:

SELECT * FROM my_table WHERE concat(uuid, '') like '%123%';

And it works. This CONCAT forces the cast to VARCHAR and the LIKE function does his job. Knowing this, I did:

final Path<UUID> uuidField = from.get("uuid");
var cast = cb.function("concat", String.class, uuidField, cb.literal(""));
cb.like(cast, String.format("%%%s%%", stringValue));

Worked perfectly. I hope this help someone else.

As @HaroldH said, it's a weird requirement, but happened in my project.

Upvotes: 1

Related Questions