java.util.Date to java.sql.Date doesn't contai

问题:

I'm getting a SqlException on a PreparedStatement for violating a uniqueness constraint on a table (dupe key). Essentially my table looks like this:

mytable
=======
mytable_id        PRIMARY KEY INT NOT NULL
fizz_id           INT NOT NULL
buzz_timestamp    DATETIME

The uniqueness constraint is on the buzz_timestamp; that is, no 2 records may have the same date/time "timestamp".

The PreparedStatement that inserts records into this table looks like this:

PreparedStatement ps = conn.prepareStatement(insertQuery);
ps.setDate(1, new java.sql.Date(new java.util.Date().getTime()));

So you can see I'm taking java.util.Date() ("now") and converting it to a java.sql.Date instance.

The exact error I'm seeing (the dupe key) keeps complaining that I'm trying to insert 2015-05-27 00:00:00.0000000 into the table for buzz_timestamp, but that it already exists. So, obviously, I'm using the Date API wrong, and I'm inserting dates that have nulled-out time components, and thereby producing dupe key exceptions.

So I ask: How do I correct this so that I'm truly inserting the date and time for "now"?

回答1:

Use

ps.setTimestamp(new java.sql.Timestamp(System.currentTimeMillis());

See this: Using setDate in PreparedStatement

回答2:

java.sql.Date extends java.util.Date but works differently: In SQL, DATE has no time. Therefore, the Java object also ignores hours, minutes, etc.

Try java.sql.Timestamp instead but you may need a cast (in SQL) to convert it to DATETIME.

Related:

• mssql 2005 datetime and jdbc

回答3:

The accepted answer is correct. I'll add an explanation.

Confusing Hack

In SQL, a DATE is a date-only value lacking a time-of-day or time zone.

The mess that is the old date-time classes bundled with Java lack any such class to represent date-only values.

Instead, the Java team created a hack. They created java.sql.Date by extending java.util.Date which, despite its confusing name, has a date portion and a time-of-day portion. For the SQL-oriented subclass, they set time-of-day portion to zero values. You might think of that as "midnight". So a java.sql.Date has a time-of-day but pretends not to.

To quote the java.SQL.Date doc:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

For a date-and-time value in SQL use the java.sql.Timestamp class as shown in accepted answer.

java.time

These poorly designed date-time classes have been supplanted by the new java.time package built into Java 8 and later. This package was inspired by the Joda-Time library.

The Java.time package includes classes to represent date-only and time-only classes. Eventually the JDBC drivers will be upgraded to directly support the new data types. Until then, use the several conversion methods added to the old and new classes. Search StackOverflow.com for many examples and discussion, as this Question is largely a duplicate.

回答4:

Instead of setDate you need to try setTimestamp

java.sql.Date - A date only (no time part)
java.sql.Time - A time only (no date part)
java.sql.Timestamp - Both date and time