Why not use java.util.Date in Jdbc?

Question

This is a simple question: I know and have heard from almost everyone that using java.util.Date for anything in a Jdbc call is a bad idea. You should use either java.sql.Date or java.sql.Time or java.sql.Timestamp. However, what is the reason not to? I can't find a good blog post or SO post explaining it, except that sometimes people see "weird behavior".

Thanks!

EDIT:

So, I have seen this post. Yeah, the only part of that post that sort of answers my question is

...to most JDBC drivers which will happily devour it as if it was of the correct type but when you request the data afterwards, you may notice that you're actually missing stuff.

However, that doesn't really answer why.

Possible duplicate http://stackoverflow.com/questions/2305973/java-util-date-vs-java-sql-date — shadyabhi, Jan 25 '12 at 20:00
Think about it. `java.sql.Date` maps to SQL `DATE` column type and holds year/month/day only; `java.sql.Time` maps to SQL `TIME` column type and holds hour/minute/second(millis) only; `java.sql.Timestamp` maps to SQL `TIMESTAMP` and `DATETIME` column types and holds the timestamp. Where does `java.util.Date` fit in SQL terms, do you think? — BalusC, Jan 25 '12 at 20:03
Another dupe: http://stackoverflow.com/questions/3323618/handling-mysql-datetimes-and-timestamps-in-java — BalusC, Jan 25 '12 at 20:04
@BalusC The problem is that in a database like Oracle, the `DATE` type actually *stores time information* and thus breaks the SQL-92 standard AFAIK. Some think, as a result, that they should use `java.util.Date` which also has time information in it. However, I know you should stick to the `java.sql.*` types, but I'm not sure why. Again, the best I've heard is that the driver can provide non-deterministic behavior. — daveslab, Jan 25 '12 at 20:10

score 17 · Accepted Answer · edited May 23 '17 at 12:24

Okay, so having read all the information throughout the answers, and the others posts pointed to in comments and so forth, I've decided to summarize what I learned:

The Setup:

From what I can see, there are three layers

JDBC wrapper calls (e.g. Spring's SimpleJdbcTemplate)
|
|
Raw JDBC calls (e.g. PreparedStatement)
|
|
JDBC driver (e.g. Oracle)

The First Reason

Many JDBC class wrappers, such as Spring's famous SimpleJdbcTemplate, allowed you give it a Map<String, Object> as the argument map when executing a SQL statement. This is wonderfully simple, as it hands all the conversions from the objects to the proper java.sql.* types when it uses raw JDBC under the hood. The first problem is here: what happens if you have the following:

Map<String, Object> paramMap = new HashMap<String,Object>();
paramMap.put("p_some_data", new java.util.Date());

What does Spring convert it to? A java.sql.Date? A java.sql.Timestamp? A java.sql.Time? Or does it even cast it to a java.lang.Object? As well explained by @BalusC in this answer to a different question and by another fellow here, there are big differences between those three java.sql types. So, that's the first reason not to use java.util.Date: you can't rely on the internal convention of a framework to handle the conversion for you.

The Second Reason

Now, talking about raw JDBC calls, @The Nail explained that you need these java.sql types to make JDBC calls, and he's absolutely right, which was news to me. However, there is still the dreaded setObject call. From reading the JavaDoc for that call, it seems a little ambiguous as to what it will do if give a java.util.Date. So, the second reason not to use it is because of the ambiguity there.

The Third Reason

Finally, talking about the level of the driver. I can attest with personal experience that sometimes Spring in conjunction with the Oracle driver does work with java.util.Date. Sometimes. And then sometimes it doesn't. So, because I have no clue how any particular version of any particular driver will handle a java.util.Date, it's best to be explicit. This is the third reason.

Conclusion

In general, it looks like the reason is: "JDBC is not supposed to be used with java.util.Date. If you do, you cannot be sure what will happen." This is a good enough reason for me :)

Bhesh Gurung · Answer 2 · 2012-01-25T20:26:41.687

java.sql.Timestamp

A thin wrapper around java.util.Date that allows the JDBC API to identify this as an SQL TIMESTAMP value. It adds the ability to hold the SQL TIMESTAMP fractional seconds value, by allowing the specification of fractional seconds to a precision of nanoseconds. A Timestamp also provides formatting and parsing operations to support the JDBC escape syntax for timestamp values.

It's the level of precision of the timestamp (as provided by the DB) that it can hold vs java.util.Date.

Suppose, if we use a java.util.Date object to represent a timestamp value in DB then the value represented by the object won't represent the same value (as in the DB) because it can not hold that "fractional seconds to a precision of nanoseconds".

score 2 · Answer 3 · answered Jan 25 '12 at 20:03

2

As for Date, it is quite simple: PreparedStatement.setDate requires a java.sql.Date, not a java.util.Date.

You can convert from java.util.Date to java.sql.Date like so:

java.sql.Date sqlDate = new java.sql.Date(date.getTime());

answered Jan 25 '12 at 20:03

The Nail

8,355
2
35
48

OK, maybe the view is somewhat too simplified. But 'using `java.util.Date` for anything in a Jdbc call' is simply impossible afaik. – The Nail Jan 25 '12 at 20:17
No, that's a good view of the problem. The issue is that many drivers will still accept `java.util.Date` as an Object and then do *something* with it. Also, many people use Spring, like myself, which hides these conversions for you. – daveslab Jan 25 '12 at 20:41
Thank you for the clarification. I was just assuming usage of a 'plain' JDBC implementation. We should *ban* those dreaded frameworks hiding important aspects ;-) – The Nail Jan 25 '12 at 20:58

score 2 · Answer 4 · answered Jan 25 '12 at 20:20

2

 java.util.Date

represents a general DateTime Value.Basically a Java Type.

 java.sql.Date

is Specific for mapping Java Object object model to SQL Datatype which is for SQL Data. (Sql Type not Java Type).

answered Jan 25 '12 at 20:20

Suave Nti

3,721
11
54
78