How to use MySQL's DATE_ADD with JPA Criteria?

user_11926254 0
Viewed 1110

I need to add an interval to a date (both columns are in a table) using MySQL and JPA Criteria. I know the following functions are required:

MySQL's DATE_ADD:

DATE_ADD(date,INTERVAL expr unit)

CriteriaBuilder's function:

function(String name, Class<T> type, Expression<?>... args)

I also have a class to create function expressions:

public class SqlFunctionExpression extends BasicFunctionExpression<String> implements Serializable {

    public SqlFunctionExpression(CriteriaBuilderImpl criteriaBuilder,
                                 Class<String> javaType,
                                 String functionName) {
        super(criteriaBuilder, javaType, functionName);
    }

    @Override
    public String render(RenderingContext renderingContext) {
        return getFunctionName();
    }
}

which can be used like new SqlFunctionExpression(null, String.class, "MINUTE") to create a minute unit.

However, how do I create the expression INTERVAL expr unit where expr is a table column?

jpa mysql java
4 Answers

Register function

    public class CommonMySQLDialect extends MySQL57Dialect {
        public CommonMySQLDialect() {
        super();
        registerFunction("date_add", new SQLFunctionTemplate(TimestampType.INSTANCE, "date_add(?1, INTERVAL ?2 ?3)"));
        }
    }

Add to hibernate configuration

    <property name="hibernate.dialect">yourpackage.CommonMySQLDialect</property>

Ready to use

    Expression<String> unit = new SqlFunctionExpression(null, String.class, "MINUTE");
    Expression<Integer> num = cb.literal(10);
    Expression<Date> newDate = cb.function("date_add", Date.class, cb.currentTimestamp(), num, unit);
user_132257300

or we can directly do date add from java side and send that as argument for comparison like so

criteriaBuilder.equal(exceptionsJoinOnRoot.get(MyModelClass_.tartDate),
                    Date.from(LocalDate.now().atStartOfDay(ZoneId.systemDefault()).plus(Period.ofDays(1))
                            .toInstant()))

if this doesn't work we can use ADDDATE mysql function

criteriaBuilder.equal(exceptionsJoinOnRoot.get(MyModelClass_.tartDate),
                    criteriaBuilder.function("ADDDATE",Date.class,criteraBuilder.currentTimestamp(),criteriaBuilder.literal(1)))
user_47815710

I used MySQL's ADDTIME as a (less than ideal) workaround:

public static Expression<LocalDateTime> addDate(CriteriaBuilder cb,
                                                Expression<LocalDateTime> dateTime,
                                                Expression<Long> value,
                                                SqlFunctionExpression unit) {
    return cb.function("ADDTIME", LocalDateTime.class, dateTime, getValueString(cb, value, unit));
}

private static Expression<String> getValueString(CriteriaBuilder cb,
                                                 Expression<Long> value,
                                                 SqlFunctionExpression unit) {
    if (unit.equals(SqlUnits.DAY)) {
        return getDaysString(cb, value);
    } else if (unit.equals(SqlUnits.MINUTE)) {
        return getMinutesString(cb, value);
    }
    return cb.literal("");
}
private static Expression<String> getDaysString(CriteriaBuilder cb,
                                                Expression<Long> days) {
    return cb.concat(days.as(String.class), " 0:0:0");
}
private static Expression<String> getMinutesString(CriteriaBuilder cb,
                                                   Expression<Long> minutes) {
    return cb.concat(minutes.as(String.class), ":0");
}
// one method for each unit required
user_119262540
user_2638010
Related