JPA在MySQL数据库中保存错误的日期

  • Z时代
  • 2024-01-10
  • 分类：问答

我的MySQL数据库中有一个表，其中有一个日期列：

+-------------------+---------------+------+-----+---------+----------------+
| 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一起使用以保存日期。我有一个功能，用户可以选择最终日期范围，它将获得所有日期。

查看此代码（带有一堆SYSO）以尝试查看发生了什么…

@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

好的，现在让我们转到保存部分：

@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：

public T save(T entity) {
    entityManager.persist(entity);
    return entity;
}

没什么大不了的…输出：

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

如您所见…应该没事吧？一切都在十号。

在我再次继续之前，请检查模型和转换器：

    //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号！

无警告，MySQL驱动程序无错误或什么都没有。

请帮助大家！

交易类别：

@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，我们解决了这个问题。

PS：我很久以前解决了这个问题，两年后又遇到了这个问题。大声笑

解决方案在转换器中：JpaDateConverter，当您根据文档将DateTime Objetivo转换为java.sql.Date时：

如果给定的毫秒值包含时间信息，则驱动程序会将时间分量设置为默认时区（运行应用程序的Java虚拟机的时区）中与GMT零对应的时间。

因此，要解决此问题，我更改了服务器时区：

dpkg-reconfigure tzdata

在MySQL中：

SET @@global.time_zone = '+00:00';

以上是 JPA在MySQL数据库中保存错误的日期 的全部内容， 来源链接： utcz.com/qa/399616.html

回到顶部