JPA在MySQL数据库中保存错误的日期 [英] JPA Saving wrong date in MySQL database
问题描述
我的MySQL数据库中有一个表，其中有一个日期列:
I have a table in my MySQL database that has a date column:
+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| type | varchar(50) | NO | | NULL | |
| expiration | date | NO | | NULL | |
我正在将MySQL与JPA一起使用以保存日期.我有一个功能，用户可以选择一个最终日期范围，它将获得所有日期.
I'm using MySQL with JPA to save dates. I have a feature that the user can select a final date range and it will get all the dates.
检查此代码(带有一堆SYSO)以尝试查看发生了什么情况...
Check out this code (with a bunch of SYSOs) to try to see whats going on...
@Override
protected DateTime nextReference(DateTime reference) {
System.out.println("Reference: " + reference.toString("dd-MM-YYYY"));
DateTime plus = reference.plusMonths(1);
System.out.println("One month from now: " + plus.toString("dd-MM-YYYY"));
DateTime result = plus.withDayOfMonth(reference.getDayOfMonth());
System.out.println("Final: " + result.toString("dd-MM-YYYY"));
return result;
}
这部分，结果很好:
Reference: 10-01-2017
One month from now: 10-02-2017
Final: 10-02-2017
Reference: 10-02-2017
One month from now: 10-03-2017
Final: 10-03-2017
Reference: 10-03-2017
One month from now: 10-04-2017
Final: 10-04-2017
Reference: 10-04-2017
One month from now: 10-05-2017
Final: 10-05-2017
Reference: 10-05-2017
One month from now: 10-06-2017
Final: 10-06-2017
Reference: 10-06-2017
One month from now: 10-07-2017
Final: 10-07-2017
Reference: 10-07-2017
One month from now: 10-08-2017
Final: 10-08-2017
Reference: 10-08-2017
One month from now: 10-09-2017
Final: 10-09-2017
Reference: 10-09-2017
One month from now: 10-10-2017
Final: 10-10-2017
Reference: 10-10-2017
One month from now: 10-11-2017
Final: 10-11-2017
Reference: 10-11-2017
One month from now: 10-12-2017
Final: 10-12-2017
Reference: 10-12-2017
One month from now: 10-01-2018
Final: 10-01-2018
好，现在让我们转到保存部分:
Ok, now lets move to the save part:
@Transactional
private void saveTransactions(List<Transaction> transactions) {
for (Transaction t : transactions) {
System.out.println("Saving: " + t.getExpiration().toString("dd-MM-YYYY"));
Transaction saved = dao.save(t);
System.out.println("Saved: " + saved.getExpiration().toString("dd-MM-YYYY"));
}
}
如您所见，我还在调试中添加了一些代码....在继续输出之前，请检查DAO:
As you can see, I put some lines to debug this also.... Before I continue with the output, check out the DAO:
public T save(T entity) {
entityManager.persist(entity);
return entity;
}
没什么大不了的...输出:
No big deal right... The output:
Saving: 10-02-2017
Saved: 10-02-2017
Saving: 10-03-2017
Saved: 10-03-2017
Saving: 10-04-2017
Saved: 10-04-2017
Saving: 10-05-2017
Saved: 10-05-2017
Saving: 10-06-2017
Saved: 10-06-2017
Saving: 10-07-2017
Saved: 10-07-2017
Saving: 10-08-2017
Saved: 10-08-2017
Saving: 10-09-2017
Saved: 10-09-2017
Saving: 10-10-2017
Saved: 10-10-2017
Saving: 10-11-2017
Saved: 10-11-2017
Saving: 10-12-2017
Saved: 10-12-2017
如您所见...应该没事吧?一切都在十号.
As you can see... It should be fine right? Everything on the 10th.
在我再次继续之前，请检查模型和转换器:
Before I continue again, check the model, and the converter:
//Attribute
@Convert(converter = JpaDateConverter.class)
private DateTime expiration;
//Converter
public class JpaDateConverter implements AttributeConverter<DateTime, Date> {
@Override
public Date convertToDatabaseColumn(DateTime objectValue) {
return objectValue == null ? null : new Date(objectValue.getMillis());
}
@Override
public DateTime convertToEntityAttribute(Date dataValue) {
return dataValue == null ? null : new DateTime(dataValue);
}
}
现在查看我的数据库:
mysql> select expiration from tb_transaction where notes = 3 and year(expiration
) = 2017;
+------------+
| expiration |
+------------+
| 2017-01-10 |
| 2017-02-10 |
| 2017-03-09 |
| 2017-04-09 |
| 2017-05-09 |
| 2017-06-09 |
| 2017-07-09 |
| 2017-08-09 |
| 2017-09-09 |
| 2017-10-09 |
| 2017-11-10 |
| 2017-12-10 |
+------------+
12 rows in set (0.00 sec)
出于某些奇怪的神秘原因，某些日期保存在9号而不是10号！
For some, wierd, mystic reason, some dates where saved on the 9ths instead of the 10th !!
没有警告，MySQL驱动程序没有错误，或者什么也没有.
No Warnings, no Errors on MySQL driver or nothing.
请帮助大家！
编辑交易类别:
@Entity
@Table(name = "tb_transaction")
public class Transaction implements Cloneable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Enumerated(STRING)
private TransactionType type;
@Convert(converter = JpaDateConverter.class)
private DateTime expiration;
推荐答案
感谢@RickS，我们解决了此问题.
Thanks to @RickS, we solved this issue.
PS:我很久以前解决了这个问题，两年后又遇到了这个问题.大声笑
PS: I solved this a long time ago, and experienced this issue again 2 years later. lol
解决方案在转换器中:JpaDateConverter，当您根据文档将DateTime Objetivo转换为java.sql.Date时:
The solution is in the converter: JpaDateConverter, when you convert a DateTime Objetivo to java.sql.Date, according to the docs:
如果给定的毫秒值包含时间信息，则驱动程序 会将时间分量设置为默认时区中的时间( 运行应用程序的Java虚拟机的时区) 对应零格林尼治标准时间.
If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.
为解决此问题，我更改了服务器时区:
So to fix this I changed the server timezone:
dpkg-reconfigure tzdata
在MySQL中:
SET @@global.time_zone = '+00:00';
这篇关于JPA在MySQL数据库中保存错误的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
