[FIXED] How to check UUID null value in JPQL?

Issue

I am using JPA/Hibernate. So I wanna to do nullCheck in JPQL, but when I do that it does not determine dataType.

JPQL Query:

  @Query("select a from Attribute a where :attributeId is null OR a.id = :attributeId")
  Page<Attribute> findByAttributeId(@Param("attributeId") UUUID attributeId);

EXCEPTION:

Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:103) at jdk.internal.reflect.GeneratedMethodAccessor582.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy398.executeQuery(Unknown Source) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 114 common frames omitted

I searched a lot on Internet on this topic, but can not find soluthion. I do not want to handle it in service layer.

I tried:

1. PostgreSql CAST function
2. check as String
3. @Type annotation.

Is there anyway to check UUID null Value in JPQL?

Solution

After a bit investigation on Internet, I found the solution. As PostgreSQL can not determine dataType, we can declare it from before as TypedParameterValue.

TypedParameterValue attributeId = new TypedParameterValue(PostgresUUIDType.INSTANCE, UUIDUtil.toUUID(attributeId));
Page<Attribute> attributes = attributeRepo.findByAttributeId(attributeId);

Then in JPQL for nullChecking, cast to org.hibernate.type.PostgresUUIDType:
( In IDE, it can be shown as error, but it compiles actually)

 @Query("select a from Attribute a where (cast(:attributeId as org.hibernate.type.PostgresUUIDType) OR a.id = :attributeId)")
 Page<Attribute> findByAttributeId(@Param("attributeId") TypedParameterValue attributeId);

In Native Query:

 @Query(value = "select * from attribute a where (cast(:attributeId as uuid) OR a.id = :attributeId)",nativeQuery = true)
 List<Attribute> findByAttributeId(@Param("attributeId") TypedParameterValue attributeId);

Answered By - Samir Allahverdi