java.util.Date
vs java.sql.Date
: when to use which and
why?
Sunday 19 November 2017
java.util.Date vs java.sql.Date
Answer
Congratulations, you've hit my favorite pet
peeve with JDBC: Date class handling.
Basically
databases usually support at least three forms of datetime
fields which are date, time and timestamp. Each of these have a corresponding class in
JDBC and each of them extend href="http://docs.oracle.com/javase/8/docs/api/index.html?java/util/Date.html"
rel="noreferrer">java.util.Date
.
Quick semantics of each of these three are the
following:
- href="http://docs.oracle.com/javase/8/docs/api/index.html?java/sql/Date.html"
rel="noreferrer">java.sql.Date
corresponds to SQL
DATE which means it stores years, months and days while
hour, minute, second and millisecond are ignored. Additionally
sql.Date
isn't tied to
timezones. - href="http://docs.oracle.com/javase/8/docs/api/index.html?java/sql/Time.html"
rel="noreferrer">java.sql.Time
corresponds to SQL
TIME and as should be obvious, only contains information about hour, minutes,
seconds and milliseconds. - href="http://docs.oracle.com/javase/8/docs/api/index.html?java/sql/Timestamp.html"
rel="noreferrer">java.sql.Timestamp
corresponds to
SQL TIMESTAMP which is exact date to the nanosecond (note that
util.Date
only supports milliseconds!) with
customizable
precision.
One
of the most common bugs when using JDBC drivers in relation to these three types is that
the types are handled incorrectly. This means that
sql.Date
is timezone specific,
sql.Time
contains current year, month and day et cetera et
cetera.
Finally: Which one to
use?
Depends on the SQL type of the
field, really. PreparedStatement
has setters for all three
values, #setDate()
being the one for
sql.Date
, #setTime()
for
sql.Time
and #setTimestamp()
for
sql.Timestamp
.
Do note
that if you use ps.setObject(fieldIndex, utilDateObject);
you
can actually give a normal util.Date
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.
I'm really saying that none of the Dates
should be used at all.
What I am saying that
save the milliseconds/nanoseconds as plain longs and convert them to whatever objects
you are using ( rel="noreferrer">obligatory joda-time plug). One hacky way which
can be done is to store the date component as one long and time component as another,
for example right now would be 20100221 and 154536123. These magic numbers can be used
in SQL queries and will be portable from database to another and will let you avoid this
part of JDBC/Java Date API:s entirely.
php - file_get_contents shows unexpected output while reading a file
I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...
-
I have an app which needs a login and a registration with SQLite. I have the database and a user can login and register. But i would like th...
-
I would like to use enhanced REP MOVSB (ERMSB) to get a high bandwidth for a custom memcpy . ERMSB was introduced with the Ivy Bridge micro...
-
According to my understanding, and my calculator, cos(90 degrees) equals 0 . In my code, I have a funct...
No comments:
Post a Comment