QueryDSL, Spring Data: Select datetime between constant and database value with Predicate

Question

Hi I would like to generate a Predicate for spring data QueryDSLRepository that would generate following query (or equivalent):

SELECT * FROM USER user JOIN PASSWORD_POLICY policy
ON
    user.password_policy_oid = policy.password_policy_oid
WHERE
    user.password_expiration_date BETWEEN CURRENT TIMESTAMP - (SELECT EXPIRATION_WARN_PERIOD_IN_DAYS FROM PASSWORD_POLICY subQueryPolicy WHERE subQueryPolicy.password_policy_oid = policy.password_policy_oid) DAYS AND CURRENT TIMESTAMP

The meaning of this query would be: Get me all users that password is about to expire

and by about to expire I mean - password expiration date is between now and (now - EXPIRATION_WARN_PERIOD_IN_DAYS from PASSWORD_POLICY table)

Is it possible?

score 1 · Accepted Answer · answered Feb 16 '16 at 08:58

This probably it can't be done by using only Spring Data predicate runner. AFAIK jpql does not support such datetime manipulation (add days etc). So what you can do if you still want use Querydsl is to use native JPASQLQuery. Unfortunately joining mapped entities is not easy, another drawback is that datetime manipulation capabilities are also not so nice in Querydsl. But I was able to manage your problem.

Assumptions:

User contains @ManyToOne PassworPolicy field, which is mapped by PASSWORD_POLICY_OID column.

DB2 datatabase

import static model.QPasswordPolicy.passwordPolicy;
import static model.QUser.user;
import static com.mysema.query.sql.SQLExpressions.datediff;
import static com.mysema.query.types.expr.DateTimeExpression.currentTimestamp;
...
NumberPath<Integer> userPasswordPolicyOidPath = new NumberPath<>(Integer.class, QUser.user, "PASSWORD_POLICY_OID");
    QPasswordPolicy subQueryPolicy = new QPasswordPolicy("subQueryPolicy");

    List<Integer> userIds =
            new JPASQLQuery(entityManager, new DB2Templates())
                    .from(user)
                    .join(passwordPolicy).on(passwordPolicy.passwordPolicyOid.eq(userPasswordPolicyOidPath))
                    .where(datediff(DatePart.day, currentTimestamp(DateTime.class), user.passwordExpirationDate)
                            .lt(new JPASubQuery().from(subQueryPolicy)
                                    .where(passwordPolicy.passwordPolicyOid.eq(subQueryPolicy.passwordPolicyOid))
                                    .unique(passwordPolicy.expirationPeriodInDays)))
                    .list(user.userOid);

probably some one more condition expirationDate < currentTimeStamp is needed to satisfy the logic, but I will leave it to you :)

PS userPasswordPolicyOidPath is ugly but I don't have idea how to get rid of this :(

so with Predicates only it is impossible... Thanks for you answer. As you said it is not quite clean and not portable, but at least you gave me direction in which I can move. I will think about your solution. — Piotr Tempes, Feb 16 '16 at 11:20

QueryDSL, Spring Data: Select datetime between constant and database value with Predicate

1 Answers1