QueryDSL Date arithmetic

3
votes

I have a table like this:

CREATE  TABLE IF NOT EXISTS `sma`.`MIGRation` (
`MIGR_ID` BIGINT NOT NULL AUTO_INCREMENT ,
`MIGR_From` VARCHAR(1024) NOT NULL ,
`MIGR_To` VARCHAR(1024) NOT NULL ,
`MIGR_Validity_Days` INT NOT NULL ,
`MIGR_CreationDate` DATETIME NOT NULL ,
PRIMARY KEY (`MIGR_ID`) );

How would you write an expression in QueryDSL to find the entries which are still valid (creationDate + validity_Days)?

SELECT * FROM MIGRation WHERE DATE_ADD(MIGR_CreationDate, INTERVAL MIGR_Validity_Days DAY) > now()
1
javajpaquerydsl

1 Answers

7
votes

For arithmetic queries take a look at the com.querydsl.core.types.dsl.Expressions class. In this case it is possible to use Expressions::dateTimeOperation:

import com.querydsl.core.types.Ops;
import com.querydsl.core.types.dsl.*;
import com.querydsl.sql.SQLQuery;
...
    @Transactional
    public List<MIGRationEntity> getMigration() {
        SQLQuery<MIGRationEntity> query = selectFrom(QMIGRation.MIGRation)
            .where(
                Expressions.dateTimeOperation(
                    Date.class, Ops.DateTimeOps.ADD_DAYS,
                    QMIGRation.MIGRation.mIGRCreationDate,
                    QMIGRation.MIGRation.mIGRValidityDays)
                .gt(DateTimeExpression.currentTimestamp()));
        String sql = query.getSQL().getSQL();
        System.out.print(sql);
        return query.fetch();
    }
...

The created SQL: 

select MIGRation.MIGR_CreationDate, MIGRation.MIGR_From, MIGRation.MIGR_ID, MIGRation.MIGR_To, MIGRation.MIGR_Validity_Days
from MIGRation MIGRation
where date_add(MIGRation.MIGR_CreationDate, interval MIGRation.MIGR_Validity_Days day) > current_timestamp