Is there a way to check for null ZonedDateTime in JPQL?

Question

In some E2E tests I'm faced with a problem. Let's say, I have the following JPQL query:

Query query = entityManager.createQuery(
        " select d from Document d left join d.audit da " +
        " where " +
        " (cast(:startDate as java.time.ZonedDateTime)) is null " +
        "    or truncate_for_minutes(da.dateCreate, 'UTC') >= " +
        " truncate_for_minutes(:startDate, 'UTC')")
        .setParameter("startDate", ZonedDateTime.now());

In the query string I use named parameter startDate which can be null. The query above works. But if I pass null, the following exception is thrown:

Caused by: org.postgresql.util.PSQLException: 
ERROR: cannot cast type bytea to timestamp without time zone

Without type casting the following exception is thrown:

Caused by: org.postgresql.util.PSQLException: 
ERROR: could not determine data type of parameter $1

Without check for null the following exception is thrown:

Caused by: org.postgresql.util.PSQLException: 
ERROR: function pg_catalog.timezone(unknown, bytea) does not exist
No function matches the given name and argument types. 
You might need to add explicit type casts.

I use this query in Spring Data repository by using @Query string. Function truncate_for_minute(...) - is just a small customization for the PostgreSQL function date_trunc(...).

I know that I can implement custom repository and build query string dynamically, but why I can't check for null ZonedDateTime in JPQL string? Maybe there is a way to do it?

My environment:

• Java 11
• PostgreSQL 11.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
• Hibernate 5.3.7 Final

Answer 1

Another solution is to use Criteria API and build the query dynamically.

@Repository
@RequiredArgsConstructor
public class CustomDocumentRepositoryImpl implements CustomDocumentRegistry {

    private final EntityManager em;

    @Override
    public Page<Document> findDocumentsForExpertByFilter(SearchDocumentCriteria criteria,
                                                         Pageable pageable) {
        final String AUDIT_TABLE = "...";
        final String USER_TABLE = "...";

        final String ID_FIELD = "id";
        final String FIRST_NAME_FIELD = "...";
        final String LAST_NAME_FIELD = "...";
        final String MIDDLE_NAME_FIELD = "...";
        final String WORKSTATION_FIELD = "...";

        final String DATE_CREATE_FIELD = "...";

        final String LIKE_MASK = "%%%s%%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Document> query = cb.createQuery(Document.class);
        Root<Document> root = query.from(Document.class);

        Path<ZonedDateTime> dateCreatePath = 
                root.get(AUDIT_TABLE).get(DATE_CREATE_FIELD);
        Path<String> lastNamePath = 
                root.get(AUDIT_TABLE).get(USER_TABLE).get(LAST_NAME_FIELD);
        Path<String> firstNamePath = 
                root.get(AUDIT_TABLE).get(USER_TABLE).get(FIRST_NAME_FIELD);
        Path<String> middleNamePath = 
                root.get(AUDIT_TABLE).get(USER_TABLE).get(MIDDLE_NAME_FIELD);

        root.fetch(AUDIT_TABLE, JoinType.LEFT)
            .fetch(USER_TABLE, JoinType.LEFT);

        Predicate documentIdsPredicate;
        List<Long> documentIds = criteria.getIds();
        if (isNull(documentIds) || documentIds.isEmpty()) {
            documentIdsPredicate = cb.isNotNull(root.get(ID_FIELD));
        } else {
            documentIdsPredicate = root.get(ID_FIELD).in(criteria.getIds());
        }

        Predicate startDatePredicate;
        ZonedDateTime startDate = criteria.getStartDate();
        if (isNull(startDate)) {
            startDatePredicate = cb.isNotNull(dateCreatePath);
        } else {
            startDatePredicate = cb.greaterThanOrEqualTo(dateCreatePath, startDate);
        }

        Predicate endDatePredicate;
        ZonedDateTime endDate = criteria.getEndDate();
        if (isNull(endDate)) {
            endDatePredicate = cb.isNotNull(dateCreatePath);
        } else {
            endDatePredicate = cb.lessThanOrEqualTo(dateCreatePath, endDate);
        }

        Predicate lastNamePredicate = cb.like(cb.upper(lastNamePath), 
                format(LIKE_MASK, criteria.getLastName().toUpperCase()));
        Predicate firstNamePredicate = cb.like(cb.upper(firstNamePath), 
                format(LIKE_MASK, criteria.getFirstName().toUpperCase()));
        Predicate middleNamePredicate = cb.like(cb.upper(middleNamePath), 
                format(LIKE_MASK, criteria.getMiddleName().toUpperCase()));

        Predicate fullNamePredicate = 
                cb.and(lastNamePredicate, firstNamePredicate, middleNamePredicate);

        Predicate compositePredicate = cb.and(
            fullNamePredicate,
            documentIdsPredicate,
            startDatePredicate,
            endDatePredicate
        );

        query.where(compositePredicate);

        Query limitedQuery = em.createQuery(query
            .orderBy(cb.desc(root.get(AUDIT_TABLE).get(DATE_CREATE_FIELD))))
            .setFirstResult(nonNull(criteria.getSize()) ?
                    criteria.getPage() * criteria.getSize() :
                    criteria.getPage());
        if (nonNull(criteria.getSize())) {
           limitedQuery.setMaxResults(criteria.getSize());
        }

        List<Document> documents = limitedQuery.getResultList();

        return new PageImpl<>(documents, pageable, criteria.getSize());
    }
}

Generates the following SQL:

select
    document0_.id as id1_3_0_,
    user1_.id as id1_13_1_,
    document0_.created_dt as created_2_3_0_,
    document0_.updated_dt as updated_3_3_0_,
    document0_.created_user_id as created_6_3_0_,
    document0_.updated_user_id as updated_7_3_0_,
    document0_.name as name4_3_0_,  
    user1_.first_name as first_na2_13_1_,
    user1_.last_name as last_nam3_13_1_,
    user1_.middle_name as middle_n5_13_1_
from
    some_scheme.document document0_ 
left outer join
    some_scheme.s_user user1_ 
        on document0_.created_user_id=user1_.id cross 
join
    some_scheme.s_user user2_ 
where
    document0_.created_user_id=user2_.id 
    and (
        upper(user2_.last_name) like '%LASTNAME%'
    ) 
    and (
        upper(user2_.first_name) like '%FIRSTNAME%'
    ) 
    and (
        upper(user2_.middle_name) like '%MIDDLENAME%'
    )   
    and (
        document0_.id in (
            2 , 1
        )
    ) 
    and document0_.created_dt>=... 
    and document0_.created_dt<=...
 order by
    document0_.created_dt desc limit 10;

Answer 2

In my case, the problem was as follows. I registered customization of SQL function date_trunc:

public class CustomSqlFunction implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
                "truncate_for_minutes",
                new SQLFunctionTemplate(
                        StandardBasicTypes.TIMESTAMP,
                        "date_trunc('minute', (?1 AT TIME ZONE ?2))"
                )
        );
    }
}

If change the StandardBasicTypes.TIMESTAMP to ZonedDateTime.INSTANCE and pass ZonedDateTime in one parameter, then the comparison in the JPQL query does not cause errors:

public class CustomSqlFunction implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "truncate_for_minutes",
            new SQLFunctionTemplate(
                ZonedDateTimeType.INSTANCE,
                "date_trunc('minute', ?1)"
            )
        );
    }
}