Java.sql.Date in Java 8 versus Java 6

Question

I know that java.sql.Date must have hours, minutes, seconds and milliseconds set to zero to match the definition of the standard SQL date. This is described here (same in Java 8).

I also know that the DATE type in Oracle has these time fields: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. But without a fractional second and time zone.

I noticed that the same query in Java 6 and 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 PreparedStatement a java.sql.Date "dateCourante" defined in this way (which sets the value for these 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 the value
• with Java 8, I do not do this.

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

which gives:

08/26/2016 00:00:00

So, I understand that:

• in Java 6, the java.sql.Date time fields are set to zero before running the query in the database, whereas
• in Java 8, the java.sql.Date time fields remain as specified in the request.

I could not find documentation about this behavior.

Can anyone confirm or explain this?

As a workaround, I use this as described here : dDate = java.sql.Date.valueOf (dDate.toLocalDate ()); // where dDate is java.sql.Date

Answer 1

So far I have no explanation but to say that many people have reported JDBC problems and violations with the latest generations of Oracle drivers, I can say that you are abusing these classes. And the best classes are available.

java.sql.Date - date only

You have chosen the wrong class on the Java side. While the java.sql.Date class does have a time set to 00:00:00 in UTC inside its internal part, you should ignore this fact, as it is instructed in the class documentation. java.sql.Date is for date values with no time and no time zone.

This class is poor design, bad hack inheriting from java.util.Date , while you should ignore this fact of inheritance.

java.sql.Timestamp - date and time

java.sql.Timestamp is the type you need in your case, not java.sql.Date . (But read on for an even better class.)

The whole set of old time classes with the earliest versions of Java was a bold industry - the first attempt to solve the problem of processing data on a date, but they did not materialize. They are poorly designed, confused and troublesome. Avoid them: java.util.Date , java.util.Calendar , java.util.GregorianCalendar , java.text.DateFormat , java.text.SimpleDateFormat . And if possible, avoid java.sql types as well. Use java.time classes instead.

JDBC 4.2

As with JDBC 4.2, your JDBC driver can have direct access to java.time types from your database. The PreparedStatement::setObject and ResultSet::getObject can display your date and time values ​​from the database as java.time objects.

 Instant instant = myResultSet.getObject( 1 ); 

... or maybe ...

 Instant instant = myResultSet.getObject( 1 , Instant.class ); 

If your driver is not so functional, then briefly convert it to java.sql types and immediately convert it to java.time yourself. Do all your business logic with java.time. Refer to java.sql types only for data exchange with the database. To convert to / from java.time, find new methods added to the old classes. For example, java.sql.Timestamp::toInstant .

Here we extract java.sql.Timestamp from the ResultSet and immediately convert it to Instant . You can do this on two lines instead of one combo 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 a MonthDay class that contains a month and a day. Useful for presenting birthdays.

MonthDay

The YearMonth class covers credit and expiration dates on which people have a date without a specific 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 common methods setObject and getObject will suffice.