CODEWITHSUNDEEP
javahibernatejpa
Ram
Ram

Reputation: 465

How to use sql function with CriteriaBuilder in Java?

How to use CONVERT_TZ function with CriteriaBuilder in Java Persistence? I am able to use it with Hibernate HQL like below:

"and CONVERT_TZ(PR.validFrom,'"+serverTZ+"','"+clientTZ+"') >= ?"

Current CriteriaBuilder like below:

criteria.add(
   cb.greaterThanOrEqualTo(
      root.get("executionDate"),
      historyCriteria.getStartDate()
   )
);

Upvotes: 1

Views: 5537

Answers (1)

SternK
SternK

Reputation: 13041

You can try to use something like this:

ParameterExpression<String> serverTZ = cb.parameter( String.class );
ParameterExpression<String> clientTZ = cb.parameter( String.class );
ParameterExpression<LocalDateTime> startDate = cb.parameter( LocalDateTime.class );

cb.and(
    cb.greaterThanOrEqualTo(
        cb.function(
            "CONVERT_TZ",
            LocalDateTime.class,
            root.get("executionDate"),
            serverTZ,
            clientTZ
        ), startDate
    ),
    // ...
);
        
entityManager.createQuery( criteria )
   .setParameter( serverTZ, "GMT" )
   .setParameter( clientTZ, "MET" )
   .setParameter( startDate, LocalDateTime.of(...) )
   .getResultList();

Upvotes: 1

Related Questions