CODEWITHSUNDEEP
javamysqljpa
wayne
wayne

Reputation: 91

How to use MySQL's DATE_ADD with JPA Criteria?

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?

Upvotes: 3

Views: 2786

Answers (4)

harsha kumar Reddy
harsha kumar Reddy

Reputation: 1386

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)))

Upvotes: 1

Lwin Min Khant
Lwin Min Khant

Reputation: 11

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);

Upvotes: 1

Archie
Archie

Reputation: 5401

To pass through raw tokens like INTERVAL 1 YEAR through to MySQL, you can use my OpaqueLiteralExpression class which is described here: https://stackoverflow.com/a/69998155/263801

Upvotes: 0

wayne
wayne

Reputation: 91

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

Upvotes: 0

Related Questions