java.sql.Date in Java 8 compared to Java 6

Question

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"

• with Java 6, I find a value,
• with Java 8, I do not.

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 :

• in Java 6, the time fields of a java.sql.Date are set to zero before the query is launched on the database, whereas
• in Java 8, the time fields of 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

Answer 1

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-only

You 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 time

The 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.

JDBC 4.2

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();

Answer 2

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 4.2

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.