问题描述
使用JPA CriteriaBuilder API将LocalDate
字段(例如'2017-09-27')保存到mySQL Date
列时,结果会有所不同(例如'2017-09-26').
When saving a LocalDate
field (e.g. '2017-09-27') to a mySQL Date
column using JPA CriteriaBuilder API, the result is different (e.g. '2017-09-26').
我已验证使用SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP)
将数据库的时区设置为UTC,因为结果为'00:00:00'.
I have validated that my database's timezone is set to UTC using SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP)
as the result is '00:00:00'.
我正在本地进行测试,我的时区是格林尼治标准时间+ 2,所以我怀疑当从LocalDate
转换为Date
时,会扣除2个小时并在日期前1天产生一个日期请求的日期(假设LocalDate
字段由于没有没有时间信息而被视为00:00:00.
I am testing this locally, and I have a timezone of GMT + 2, so my suspicion is that when the conversion occurs from LocalDate
to Date
, 2 hours are being deducted and producing a date 1 day before the requested date (assuming that the LocalDate
field, as a result of it having no time information, is being treated as 00:00:00.
在这种情况下保存LocalDates的最佳方法是什么?我应该在这里 https://stackoverflow.com/a/29751575/3832047 遵循建议,并显式设置所有LocalDate字段到UTC或类似的东西?
What is the best way to save LocalDates in this situation? Should I be following the advice here https://stackoverflow.com/a/29751575/3832047 and explicitly setting all LocalDate fields to UTC or something similar?
我进行了一项测试,以查看将其转换为代码时会发生什么,并得到以下结果:
I ran a test to see what happens when converting them in code and got the following result:
Date convertedDate = Date.valueOf(localDate);
编辑
这是我用来检索数据的代码示例,其中也发生了奇数日期更改.如果我请求2017-06-27
的数据,我将收到2017-06-26
的结果.
Here is an example of the code I use to retrieve the data, where the odd date change occurs as well. If I request data for 2017-06-27
, I receive results for 2017-06-26
.
CriteriaBuilder criteriaBuilder = sessionFactory.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(HorseAndTrailerRequest.class);
Root<HorseAndTrailerRequest> criteria = criteriaQuery.from(HorseAndTrailerRequest.class);
ParameterExpression<LocalDate> effectiveDateParameter = criteriaBuilder.parameter(LocalDate.class);
criteriaQuery.select(criteria)
.where(
criteriaBuilder.equal(criteria.get("effectiveDate"), effectiveDateParameter)
);
TypedQuery<HorseAndTrailerRequest> query = sessionFactory.getCurrentSession().createQuery(criteriaQuery);
query.setParameter(effectiveDateParameter, date);
return query.getResultList();
推荐答案
由于LocalDate
没有TimeZone,因此可以在数据库模式中将column_date映射为long,然后使用AttributeConverter
将LocalDate
转换为long避免时区转换问题:
Since LocalDate
has no TimeZone, you can map the column_date as long in your database schema, and use AttributeConverter
to convert LocalDate
to long to avoid time zone conversion problems :
import javax.persistence.Converter;
import java.time.LocalDate;
import javax.persistence.AttributeConverter;
@Converter
public class LocalDateToLong implements AttributeConverter<LocalDate, Long> {
@Override
public Long convertToDatabaseColumn(LocalDate date) {
if (date != null) {
long epochDay = date.toEpochDay();
return epochDay;
}
return null;
}
@Override
public LocalDate convertToEntityAttribute(Long epochDay) {
// TODO Auto-generated method stub
if (epochDay != null) {
LocalDate date = LocalDate.ofEpochDay(epochDay);
return date;
}
return null;
}
}
这篇关于保存到MySQL数据库时如何阻止LocalDate更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!