过去2天内以Java 1.7.0运行的SQL Server中的日期列（MSSQL-JDBC 3.0） [英] Date columns in SQL-Server (MSSQL-JDBC 3.0) running under Java 1.7.0 retrieved as 2 days in the past

问题描述

当使用 Microsoft SQL Server2008检索DATE类型的列时，我有奇怪的效果JDBC-Driver 3.0版本，在官方Oracle JDK 1.7.0下运行。主机操作系统是Windows Server 2003。

过去，所有日期列都被检索为两个天，相对于实际存储在列中的值

我制作了一个最小的代码示例，测试出来（测试表和数据）：

  CREATE TABLE Java7DateTest（
 dateColumn DATE 
）; 
 INSERT INTO Java7DateTest VALUES（'2011-10-10'）; 
  

代码：

 code> public class Java7SQLDateTest {
 
 public static void main（final String [] argv）{
 try {
 Class.forName（com.microsoft.sqlserver。 jdbc.SQLServerDriver）; 
连接连接= DriverManager.getConnection（
jdbc：sqlserver：//192.168.0.1：1433; databaseName = dbNameHere，
user，password）; 
 PreparedStatement statement = connection.prepareStatement（SELECT * FROM Java7DateTest）; 
 ResultSet resultSet = statement.executeQuery（）; 
 while（resultSet.next（））{
 final java.sql.Date date = resultSet.getDate（dateColumn）; 
 final String str = resultSet.getString（dateColumn）; 
 System.out.println（date +（raw：+ str +））; 
} 
 resultSet.close（）; 
 statement.close（）; 
 connection.close（）; 
} catch（final Throwable t）{
 throw new RuntimeException（t.getMessage（），t）; 
} 
} 
 
} 
  

运行此上述配置打印代码：2011-10-08（raw：2011-10-08）。
在JRE 1.6.0_27下打印：2011-10-10（raw：2011-10-10）

我找不到任何似乎关于我的问题与谷歌，所以我假设它的东西愚蠢的我忽略了或没有人在使用Java7。

任何人都可以确认这个问题？如果我仍然想使用Java7，我的替代方案是什么？

编辑：即使使用-Xint运行时也会出现此问题，因此它不是由Hotspot错误引起的。 >

编辑2：旧版驱动程序（Microsoft 1.28）与JDK1.7.0正常工作（我们在两年前才使用该驱动程序）。
jTDS也可以很好的与这个例子。我正在考虑切换到jTDS，但我不愿意这么做，因为我对于我们生产环境的影响可能并不是最明智的。理想情况下，它应该是正常工作，但是当我将开发盒子切换到Java7时我相信。
在生产环境中有一个很胖的数据库，太大，无法创建一个副本，用于测试（或者我们的服务器剩下的磁盘太少）。所以设置一个应用程序的测试环境并不是很明显的，所以我必须针对这个应用程序编写一个缩小的数据库。

Edit3：jTDS有自己的一组catch附上。我发现一个行为差异打破了我们的一个应用程序。 ResultSet.getObject（）根据驱动程序（Short vs Integer）返回SmallInt列的不同对象类型。另外jTDS不实现JDBC4连接接口，不支持Connect.isValid（）。

Edit4：上周我注意到MSSQL-JDBC 3.0拒绝连接到任何DB之后我更新到JDK1.6.0_29。那么jTDS就是...昨天我们切换了生产服务器（我固定了几个应用程序依赖驱动程序的特征），到目前为止我们没有任何问题。

解决方案

我不太了解你的答案。但是，我已经按照你的描述重新创建了你的情况。与jdbc驱动程序v3.101和v3.202以及v4.ctp3在jdk1.7下运行时相同。但是，MS的v2驱动程序可以在jdk1.6和jdk1.7下给出您的预期答案。如果你需要一个快速修复，可以移动到一个旧的jdbc驱动程序，这可能适用于你。

其他的想法是关于MS jdbc驱动程序如何处理日期和转换SQL Server和jvm之间的日期对象。由于日期的存储没有时区，驱动程序对Date对象的解释基于运行jdbc驱动程序的计算机的默认时区。例如，如果您存储2011-10-11 12:00的小日期，并从默认时区设置为GMT-7的机器检索该日期，则Date对象的UTC时间将为2011-10 -11 19:00'。可能是jdk1.7中有一些变化会影响驱动程序中的这个转换过程，导致一个野性的偏移。您可以尝试使用ResultSet.getDate（列，日历）方法来查看具有特定时区的日历是否具有您想要的结果，或者帮助您了解为什么在转化中看到奇怪的偏移量。

I have strange effects when retrieving columns of type DATE from SQLServer2008 using the Microsoft JDBC-Driver version 3.0 when running under the official Oracle JDK 1.7.0. Host OS is Windows Server 2003.

All Date columns are retrieved as two days in the past with respect to the value actually stored in the column.

I cooked up a minimal code example the test this out (Test table and data):

CREATE TABLE Java7DateTest (
  dateColumn DATE
);
INSERT INTO Java7DateTest VALUES('2011-10-10');

Code:

public class Java7SQLDateTest {

    public static void main(final String[] argv) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection connection = DriverManager.getConnection(
                    "jdbc:sqlserver://192.168.0.1:1433;databaseName=dbNameHere",
                    "user", "password");
            PreparedStatement statement = connection.prepareStatement("SELECT * FROM Java7DateTest");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                final java.sql.Date date = resultSet.getDate("dateColumn");
                final String str = resultSet.getString("dateColumn");
                System.out.println(date + " (raw: " + str + ")");
            }
            resultSet.close();
            statement.close();
            connection.close();
        } catch (final Throwable t) {
            throw new RuntimeException(t.getMessage(), t);
        }
    }

}

Running this code on above configuration prints: "2011-10-08 (raw: 2011-10-08)". Under JRE 1.6.0_27 it prints: "2011-10-10 (raw: 2011-10-10)"

I could not find anything that seems to relate to my problem with google, so I'm assuming that its either something stupid I overlooked or nobody is using Java7 yet.

Can anybody confirm this problem? What are my alternatives if I still want to use Java7?

Edit: The problem occurs even when running with -Xint, so its not caused by Hotspot bugs.

Edit2: Old drivers (Microsoft 1.28) work properly with JDK1.7.0 (we were using that driver until maybe two years ago, I think). jTDS also works perfectly fine with the example. I am considering switching to jTDS, but I am reluctant to do so because I have not the faintest idea what the effects on our productive environment may be. Ideally it should just work, but that what I believed when I switched my dev box to Java7, too. There is one pretty fat database in the production environment, that is too big to create a copy of, for testing (or rather our server has so little disk left). So setting up a test environment for that one app is not straigthforward, I would have to stitch up a shrinked database for that.

Edit3: jTDS has its own set of catches attached. I found a behavioral difference that breaks one of our applications. ResultSet.getObject() returns different object types for SmallInt columns depending on driver (Short vs Integer). Also jTDS does not implement JDBC4 Connection interface, Connect.isValid() is not supported.

Edit4: I noticed last week that MSSQL-JDBC 3.0 refuses to connect to any DB after I updated to JDK1.6.0_29. jTDS it is then... we switched the productive server yesterday (I fixed tow places where the application was relying on peculiarities of the driver), and so far we had have no problems.

解决方案

I don't quite have an answer for you. But, I've recreated your situation as you described. It is the same with the jdbc driver v3.101 and v3.202 and v4.ctp3 when run under jdk1.7. However, the v2 driver from MS gives your expected answer both under jdk1.6 and jdk1.7. If you need a quick fix and can move to an older jdbc driver, that may work for you.

Other thoughts are on how the MS jdbc driver handles dates and conversion of Date objects between SQL Server and the jvm. Since the storage of the date is without a time zone, the interpretation of the Date object by the driver is based on the default time zone for the machine running the jdbc driver. For instance, if you store a smalldate of '2011-10-11 12:00' and retrieve it from a machine with the default time zone set to GMT-7 then the resulting UTC time of the Date object would be '2011-10-11 19:00'. It could be that there is some change in jdk1.7 that impacts this conversion process in the driver resulting in a wild offset. You might experiment with the ResultSet.getDate(column, Calendar) method to see if a Calendar with a specific time zone gets you the result you want or helps make sense of why you are seeing the strange offset in the conversion.

这篇关于过去2天内以Java 1.7.0运行的SQL Server中的日期列（MSSQL-JDBC 3.0）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！