I know that a java.sql.Date should have hours, minutes, seconds and milliseconds set to zero, to comply with the definition of standard SQL date. This is documented here (the same in Java 8).
I also know that the Oracle DATE type does have these time fields of YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. But no fractional second nor time zone.
I noticed that the same query in Java 6 and in Java 8 does not behave the same :
private static final String REQUETE_LISTE_CALENDRIER_DATE =
" SELECT ID_DATE, JOUR" +
" FROM CALENDRIER " +
" WHERE ID_DATE = ? ";
Binding to the PreparedStatement
a java.sql.Date "dateCourante" defined like this (which sets a value to those time fields) :
GregorianCalendar gregorianCalendar = new GregorianCalendar(); // "now"
java.sql.Date dateCourante = new java.sql.Date(gregorianCalendar.getTime().getTime()); // date AND time of "now"
In my database, the date has hours, minutes, seconds to zero. We can check with the following query :
select to_char(id_date, 'DD/MM/YYYY HH24:MI:SS')
from calendrier
where id_date = to_date('26/08/2016', 'DD/MM/YYYY');
that gives :
26/08/2016 00:00:00
So, what I understand, is that :
java.sql.Date
are set to zero before the query is launched on the database, whereas java.sql.Date
are left as is in the query.I have not been able to find documentation about this behavior.
Can anybody confirm or explain that ?
As a workaround, I use this, as explained here : dDate = java.sql.Date.valueOf(dDate.toLocalDate()); // where dDate is java.sql.Date
While I do not have an explanation, except to say many folks have reported problems and violations of JDBC with the latest generations of Oracle drivers, I can say that you are misusing those classes. And better classes are available.
java.sql.Date
is date-onlyYou have selected the wrong class on the Java side. While the java.sql.Date
class does indeed have a time set to 00:00:00
in UTC within its internals, you are supposed to ignore that fact, as instructed by the class documentation. The java.sql.Date
is intended for a date-only value, without a time-of-day and without a time zone.
This class is a poor design, a bad hack, inheriting from java.util.Date
while telling you to ignore that inheritance fact.
java.sql.Timestamp
is date and timeThe java.sql.Timestamp
is the type you need in your case rather than java.sql.Date
. (But read on for an even better class.)
The whole bunch of old date-time classes from the earliest versions of Java were a brave industry-first attempt at tackling the problem of date-time handling, but they fell short. They are poorly designed, confusing, and troublesome. Avoid them: java.util.Date
, java.util.Calendar
, java.util.GregorianCalendar
, java.text.DateFormat
, java.text.SimpleDateFormat
. And if possible, avoid the java.sql types as well. Instead use the java.time classes.
As of JDBC 4.2, your JDBC driver may be able to directly access java.time types from your database. The PreparedStatement::setObject
and ResultSet::getObject
methods may be able to render your date-time values from the database as java.time objects.
Instant instant = myResultSet.getObject( 1 );
…or perhaps…
Instant instant = myResultSet.getObject( 1 , Instant.class );
If your driver is not so capable, then convert briefly to the java.sql types and immediately convert to java.time yourself. Do all your business logic using java.time. Resort to java.sql types only for exchanging data with the database. To convert to/from java.time, look for new methods added to the old classes. For example, java.sql.Timestamp::toInstant
.
Here we extract a java.sql.Timestamp
from the ResultSet
and immediately convert it to an Instant
. You may want to do this on two lines instead of one combined line for debugging purposes.
Instant instant = myResultSet.getTimestamp( 1 ).toInstant();
Java SE 8 also has classes for some other common use cases.
YearMonth
There is the MonthDay
class, which contains a month and day. Useful for representing birthdays.
MonthDay
The YearMonth
class covers the credit card start date and expiration date use cases and scenarios in which people have a date with no specified day.
JDBC in Java SE 8 will support these new types, but there will be no public JDBC API changes. The existing generic setObject and getObject methods will be sufficient.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With