英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论