SQL Server的timestamp2应该如何在JDBC中运行？ [英] How is SQL Server's timestamp2 supposed to work in JDBC?
问题描述
我在SQL Server 2008中尝试使用timestamp2而不是Timestamp时遇到了一些麻烦。显然，rs.getTimestamp在timestamp和timestamp2之间的行为非常不同。但是，我找不到任何文件说明应该存在差异，或者我应该使用不同的东西。我想知道我是否只是做错了。
I'm having some trouble trying to use timestamp2 instead of Timestamp in SQL Server 2008. Apparently, rs.getTimestamp has very different behavior between timestamp and timestamp2. However, I can't find any documentation stating that there should be a difference, or that I should be using something different. I wonder whether I'm just doing something wrong.
环境：
- 试用SQL Express 2008（10.0）和SQL Server 2008 R2（10.5）。
- sqljdbc4.jar 3.0版，大小为537,303字节，CRC-32 = a0aa1e25，MD5 = 402130141d5f2cee727f4371e2e8fd8a。
- Java 1.6
这是一个演示问题的单元测试。唯一的魔法是Db.getConnection（），您可以使用适当的代码替换它。 datetime和datetime2的测试相同，但datetime2测试失败，日期为2天。我将数据库中的所有时间视为GMT / UTC，并且我没有尝试将时区信息添加到datetime2数据的数据库数据中。
Here is a unit test demonstrating the problem. The only "magic" is the "Db.getConnection()", which you can replace with appropriate code. The test is the same for both datetime and datetime2, but the datetime2 test fails with a date that is 2 days prior. I treat all times in the DB as GMT/UTC, and I haven't attempted to add timezone information into the database data for datetime2 data.
private void testTimestamp(TimeZone gmtTz, Connection conn, String query,
Calendar expectedCal) throws SQLException
{
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
while (rs.next())
{
// Note the expectedCal has a GMT timezone.
Date actualTs = rs.getTimestamp("dt", expectedCal);
// Just print out the time difference
long diff = actualTs.getTime() - expectedCal.getTimeInMillis();
System.out.println("Diff=" + diff);
// Do the test to make sure they are the same
// In practice, this succeeds for datetime and fails for datetime2
Assert.assertEquals(expectedCal.getTimeInMillis(), actualTs.getTime());
}
}
@Test
public void testDateTime() throws SQLException
{
Connection conn = Db.getConnection();
TimeZone gmtTz = TimeZone.getTimeZone("GMT");
String query;
Calendar expectedCal = Calendar.getInstance(gmtTz);
expectedCal.clear();
expectedCal.set(2011, 10, 02, 11, 17);
query = "select CAST('2011-11-02 11:17:00' as datetime) as dt";
testTimestamp(gmtTz, conn, query, expectedCal);
query = "select CAST('2011-11-02 11:17:00.0000000' as datetime2) as dt";
testTimestamp(gmtTz, conn, query, expectedCal); // results in an error
}
我唯一的选择是切换回时间戳？
Is my only option switching back to timestamp?
编辑：对于未来的Google员工，使用sqljdbc4.jar 3.0版，测试在Linux上失败，但在Windows上传递。
我还没有尝试过SQL Server 2012附带的sqljdbc4.jar 4.0版。
For future Googlers, using sqljdbc4.jar version 3.0, the test fails on Linux, but passes on Windows. I have not yet tried sqljdbc4.jar version 4.0 that comes with SQL Server 2012.
推荐答案
我记得没有听过关于官方SQL Server驱动程序和JTDS的好处是首选（虽然我似乎无法找到该链接）。我个人会选择JTDS（当然要经过严格的测试）或者回到不会引起问题的版本。我没有使用SQL Server，但从外观来看，似乎 datetime2 是首选的数据类型，所以我宁愿不回复。选项（d）不是IMO的好选择。 ：）
I remember not hearing good things about the official SQL Server driver and JTDS being preferred (though I can't seem to be finding that link). I would personally go with JTDS (with rigorous testing of course) or move back to the version which doesn't cause an issue. I haven't worked with SQL Server but from the looks of, it seems that datetime2 is the preferred data type so I'd rather not revert back. Option (d) isn't really a good option IMO. :)
这篇关于SQL Server的timestamp2应该如何在JDBC中运行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
