为什么在Java中将查询日期BC更改为AD？ [英] Why querying a date BC is changed to AD in Java?

问题描述

我的Java程序连接到包含许多日期的数据库（Oracle XE 11g）（OracleXE的日期格式设置为syyyy / mm / dd）。

My program in Java connects to a Database (Oracle XE 11g) which contains many dates (date format of OracleXE is set to syyyy/mm/dd).

在带有负日期的数据库中进行查询（在基督之前）可以正常工作。当我用Java编写代码时，它们全部更改为AD（Anno Domini）。

Doing a query in the database with negative dates (before Christ) works fine. When I do it in Java, they are all changed to AD (Anno Domini). How can I retrieve dates in Java respecting AD/BC?

我在这里的Java代码向数据库进行查询并将结果放入表中。

My Java code here does the query to the DB and puts the result in a table.

try {
    Object item=cbPD.getSelectedItem();                                 
    String dacercare=item.toString();
    query = "SELECT DISTINCT PD.Titolo,PD.Inizio,(Select E.nome From Evento E WHERE PD.Inizio_Evento=E.CODE),
            PD.Fine, (Select E.nome From Evento E  WHERE PD.Fine_Evento=E.CODE ) FROM Periododelimitato PD WHERE PD.Titolo=?";
    PreparedStatement stAccess = Login.connessione.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    stAccess.setString(1,dacercare);
    rset = stAccess.executeQuery(); 
    j = modelPD.getRowCount();
    for (i=0; i<j; i++) modelPD.removeRow(0);
    Date data;
    while (rset.next()) {
        data = rset.getDate(2);
        modelPD.addRow(new Object[]{rset.getString(1),data, rset.getString(3), rset.getString(4), rset.getString(5)});
    }       
}

这里有一个示例使用特定查询：

Here an Example using a specific Query:

try {
    query = "SELECT PD.Inizio FROM PeriodoDelimitato PD WHERE PD.CodP=?";
    String dacercare="8"; //look for record with this specific Primary key
    PreparedStatement stAccess = Login.connessione.prepareStatement(query,
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    stAccess.setString(1, dacercare);
    rset = stAccess.executeQuery(); 
    while(rset.next()) {
        Date dateBC = rset.getDate(1);
        modelPD.addRow(new Object[]{null, dateBC, null, null, null});
    }

输出在Java中是：

0509-01-01

在SQL开发人员中使用相同的查询（用指定的主键替换？）进行

输出：

-0509/01/01

请注意以下查询：

添加信息： DBMS是Oracle（XE 11g），已经建立了DB在IDE（SQL开发人员）上。该程序通过Netbeans 8.2用Java编写。我连接到Netbeans中的数据库，并添加了库 ojdbc6.jar。

Adding information: DBMS is Oracle (XE 11g), DB has been built on IDE (SQL developer). The program is written in Java through Netbeans 8.2. I connect to the database in Netbeans adding the Library "ojdbc6.jar".

推荐答案

首先，目前尚不清楚应该怎么做处理历史性的日期，尤其是史前的日期，以及您应该如何期待它们的行为。这不是我所知道的，但我怀疑今天使用的任何日历是否都在公元前6世纪（在通用时代之前， BC）使用过。也许您已经意识到，我只想向其他阅读此答案的人提及。

First, it’s not immediately clear how you should handle historic and not least prehistoric dates and how you should expect them to behave. It’s not something I know, but I doubt that any calendar in common use today was used in the 6th century BCE (before the common era, "BC"). Maybe you were already aware, I just wanted to mention it for anyone else reading this answer.

多亏了Basil Bourque（现在已删除）的答案，您观察到的似乎是 java.sql.Date 的预期行为。我尝试打印从公元2年（公元前）到公元前2年的日期，然后进行比较。前2个CE：

With thanks to Basil Bourque’s (now deleted) answer, what you have observed seems to be the intended behaviour with java.sql.Date. I tried printing dates from year 2 CE (common era, "AD") and then year 2 BCE and compared. First 2 CE:

    LocalDate ld = LocalDate.of(2, 1, 1);
    java.sql.Date sqlDate = java.sql.Date.valueOf(ld);
    System.out.println("java.sql.Date " + sqlDate + " millis " + sqlDate.getTime());

java.sql.Date 0002-01-01 millis -62104237200000

java.sql.Date 0002-01-01 millis -62104237200000

这与预期的一样。对于2 BCE，我们需要提供-1到 LocalDate ，因为0表示1 BCE，-1表示2 BCE。在上面的代码中插入 LocalDate.of（-1，1，1），输出为

This is as expected. For 2 BCE we need to supply -1 to LocalDate since 0 means 1 BCE, and -1 means 2 BCE. Insert LocalDate.of(-1, 1, 1) in the above code, and the output is

java.sql.Date 0002-01-01 millis -62198931600000

java.sql.Date 0002-01-01 millis -62198931600000

我们注意到日期打印在相同。 0002几乎是完全不正确的，但是它并不能告诉我们是公元2年还是BCE年。我相信这可以解释您观察到的行为。接下来，我们注意到毫秒值是不同的，因此日期应该是不同的。时差为94694400000毫秒，如果其中之一是or年，则等于1096天或3年。 year年可能会令人惊讶，但否则我认为这是正确的。

We note that the date is printed the same. 0002 is hardly downright incorrect, but it doesn’t tell us whether it’s year 2 CE or BCE. I believe that this explains the behaviour you observed. Next we note that the millisecond values are different, so the dates are different as they should be. The diffirence is 94694400000 milliseconds, which equals 1096 days or 3 years if one of them is a leap year. The leap year may surprise, but otherwise I think it’s correct.

不过有一些可疑之处。当我将sql日期转换回 LocalDate 时，那个时代已经丢失了，我总是得到一个通用时代的日期。由于您不需要这种转换，因此您可能不需要关心。

There is something fishy, though. When I converted the sql date back into a LocalDate, the era was lost, I always got a date in the common era. Since you don’t need this conversion, you probably don’t need to care.

我相信，好的解决方案是删除过时的完全日期类，并始终使用现代的 LocalDate 。您应该注意，这遵循所谓的阳历公历，它不一定总能给出与日期完全相同的日期。另外，这还需要兼容JDBC 4.2的驱动程序，因此您的 ojdbc6.jar 不会这样做。即使这可能意味着您被阻止了，但我还是建议其他人一起阅读。我还没有测试过，但是我认为以下方法应该起作用：

I believe the good solution will be to drop the outdated Date class completely and use the modern LocalDate throughout. You should be aware that this follows the so-called proleptic Gregorian calendar, which may not always give the exact same dates as Date. Also this requires JDBC 4.2 compliant driver, so your ojdbc6.jar won’t do. Even though this may mean you’re prevented, I am letting the suggestion stand for anyone else reading along. I have not tested, but I think the following should work:

LocalDate dateBC = rset.getObject(1, LocalDate.class);

这篇关于为什么在Java中将查询日期BC更改为AD？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！