英文:
How to use MySQL's DATE_ADD with JPA Criteria?
问题
我需要在MySQL和JPA Criteria中给日期添加一个间隔（两个列都在表中）。我知道需要使用以下函数：
MySQL的DATE_ADD：
DATE_ADD(date, INTERVAL expr unit)
CriteriaBuilder的function：
function(String name, Class<T> type, Expression<?>... args)
我还有一个用于创建函数表达式的类：
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();
}
}
可以像这样使用它来创建一个分钟单位：new SqlFunctionExpression(null, String.class, "MINUTE")。
然而，如何创建表达式INTERVAL expr unit，其中expr是一个表列呢？
英文:
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?
答案1
得分: 1
注册函数
public class CommonMySQLDialect extends MySQL57Dialect {
public CommonMySQLDialect() {
super();
registerFunction("date_add", new SQLFunctionTemplate(TimestampType.INSTANCE, "date_add(?1, INTERVAL ?2 ?3)"));
}
}
添加到 Hibernate 配置
<property name="hibernate.dialect">yourpackage.CommonMySQLDialect</property>
准备使用
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);
英文:
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);
答案2
得分: 1
criteriaBuilder.equal(exceptionsJoinOnRoot.get(MyModelClass_.tartDate),
Date.from(LocalDate.now().atStartOfDay(ZoneId.systemDefault()).plus(Period.ofDays(1))
.toInstant()))
如果这不起作用，我们可以使用ADDDATE MySQL函数
criteriaBuilder.equal(exceptionsJoinOnRoot.get(MyModelClass_.tartDate),
criteriaBuilder.function("ADDDATE", Date.class, criteraBuilder.currentTimestamp(), criteriaBuilder.literal(1)))
英文:
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)))
答案3
得分: 0
我使用了MySQL的ADDTIME作为一个（不太理想的）解决方法：
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");
}
// 每个所需单位的方法
希望这有助于你的工作。
英文:
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
答案4
得分: 0
可以使用我的 OpaqueLiteralExpression 类将原始令牌像 INTERVAL 1 YEAR 传递到 MySQL，详情请参考这里: https://stackoverflow.com/a/69998155/263801
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论