Tutorial :Best Practice: How to check for a specific java.util.Calendar/Date in SQL.DATE by JDBC?



Question:

This is something I struggle with since yesterday.

I have appointments to save in a database. They consist of a date and a time, like:

01.02.1970 14:00  

(german format, in american I think it would be something like 02/01/1970 2:00pm).

First idea: Save it as a SQL.DATE!

So i created a table:

CREATE TABLE appointments (id NUMBER(10) NOT NULL, datum DATE NOT NULL, PRIMARY KEY id)  

So far so good.

Now I wrote a DAO saving my appointment entered via web form. Afterwards I wanted to write a unit test, to check if the appointment is saved properly.

The relevant test part is as follows:

JdbcDao myDao = new JdbcDao();  myDao.setDataSource(jdbcTemplate.getDataSource());        myDao.saveAppointment(appointmentModel);    // Not needed but I saw, the appointment is saved in the database  setComplete();    // And now for the (sorry for the harsh words) pain in the *** part    String sql = "SELECT id, datum FROM appointments WHERE datum ... // <--  

<--: This is just the part, where I don't know what to enter to see if on a specific day a date already is in the database.

I tried:

datum = ?  

the the following call of

jdbcTemplate.query(sql, args, rowMapper);  

had a java.util.Date, a java.util.Calendar or a java.lang.String ('dd.MM.yyyy') in the args-array, which holds the arguments replacing the ? in the prepared statement.

Sure, this was a bad idea, because the database has something like

DD.MM.YYYY HH:MI   

in the table row (DD=day, MM=month, YY=year, HH=hour, MI=minute).

So I found the BETWEEN sql command, refactoring (and trying all kind of formats, inputs, strings, object to pass in the args-array) the SELECT-command to:

String sql = "SELECT id, datum FROM appointments WHERE datum BETWEEN to_date( ?, 'DD.MM.YYYY HH24:MI:SS') AND to_date( ?, 'DD.MM.YYYY HH24:MI:SS')  

which works, like many other tries, if I enter it via a sql-tool directly, e.g.

SELECT * FROM appointments WHERE datum BETWEEN to_date('01.02.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND to_date('01.02.1970 23:59:59', 'DD.MM.YYYY HH24:MI:SS')  

outputs for example:

ID                      DATUM                     ----------------------  -------------------  70                      01.02.1970 11:11:11  

but my jdbc-call in java always results in an empty resultset.

Long story, short question:

What is the best practice to query a database, if a date, represented by a java-object, exists in a sql.DATE column in a database, independet from the given time?


Solution:1

The Answer by Sacre is correct but outdated. The modern way is with the java.time classes.

tl;dr

StringBuild sql = new StringBuilder()  sql.append( "SELECT id_, datum_ " );  sql.append( "FROM appointments_ " );  sql.append( "WHERE datum_ >= ? " );  // Start is *inclusive*…  sql.append( "AND datum_ < ? ; " );   // …while end is *exclusive*. (Half-Open approach)  

And pass a pair of Instant objects.

pstmt.setObject( 1 , start ) ;  // Pass `java.time.Instant` object directly via `PreparedStatement::setObject` method.  pstmt.setObject( 2 , stop ) ;  

Use objects not strings

Avoid using strings for fetching/storing date-time values in your database. Use date-time objects for date-time values. Java and JDBC have rich support for date-time objects.

Using java.time

The java.sql.Date class is intended to represent a date-only value without a time-of-day and without a time zone. Actually it carries both those extras, but pretends not too. The modern replacement is LocalDate. The LocalDate class represents a date-only value without time-of-day and without time zone.

But java.sql.Date (and LocalDate too) is the wrong class to use as your column seems to be defined not as a date-only type but as a date-time type.

The critical issue here is being ignored by your Question: time zone. A time zone is crucial in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId z = ZoneId.of( "America/Montreal" );  LocalDate today = LocalDate.now( z );  

Regarding time zone in your database…

  • Your database almost certainly stores date-time values carrying time zone or offset-from-UTC info as UTC values in a type such as the SQL standard TIMESTAMP WITH TIME ZONE.
  • If using TIMESTAMP WITHOUT TIME ZONE, then the values have no time zone or offset-from-UTC info, and do not represent actual moments on the timeline, but may be appropriate for appointments far enough into the future that storing them as zoned values carries the risk of running afoul of politicians changing the time zone definition such as adjusting/adopting/dropping Daylight Saving Time.

If stored as zoned values, you need to determine the starting and ending moments of your date in your desired time zone, then convert that pair of points to UTC values, and query the database for that pair of UTC values.

Generally the best practice in date-time work for defining spans of time, such as the duration of the day as needed here, is to use the Half-Open approach where the beginning of the span is inclusive while the ending is exclusive. So a day runs from first moment of the day and goes up to, but does not include, the first moment of the following day.

Do not assume the first moment of the day is 00:00:00. Anomalies such of Daylight Saving Time (DST) means the day may start at a time such as 01:00:00. Let java.time determine the first moment of the day.

ZonedDateTime startZdt = localDate.atStartOfDay( z );  ZonedDateTime stopZdt = localDate.plusDays( 1 ).atStartOfDay( z );  

Also remember that the span of time between that start and stop is not necessarily 24-hours. Anomalies such as DST mean the day may run 23 or 25 hours, or some other length.

Convert those to UTC by extract Instant object. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

Instant start = startZdt.toInstant();  // Convert to UTC value.  Instant stop = stopZdt.toInstant();  

To do the SQL query, do not use BETWEEN. Instead of the Half-Open approach, that command uses the Closed approach where both beginning and ending is inclusive.

StringBuild sql = new StringBuilder()  sql.append( "SELECT id_, datum_ " );  sql.append( "FROM appointments_ " );  sql.append( "WHERE datum_ >= ? " );  // Start is *inclusive*…  sql.append( "AND datum_ < ? ; " );   // …while end is *exclusive*. (Half-Open approach)  PreparedStatement pstmt = conn.prepareStatement( sql.toString() );  

Specify your pair of Instant objects as the arguments to be used by the SQL statement.

pstmt.setObject( 1 , start ) ;  // Pass java.time.Instant object directly via `setObject` method.  pstmt.setObject( 2 , stop ) ;  

Sidebar: Append a trailing underscore to all your SQL identifiers to avoid any name collision with the one thousand reserved words, per a promise by the SQL specification.

Note how we called setObject to word directly with our java.time types in JDBC.

If your JDBC driver does not yet support JDBC 4.2 or later, you must fall back to using the legacy java.sql types. In this case we would need java.sql.Timestamp. But do so only briefly â€" try to stay in java.time as much as possible.

pstmt.setTimestamp( 1 , java.sql.Timestamp.from( start ) ) ;  pstmt.setTimestamp( 2 , java.sql.Timestamp.from( stop ) ) ;  

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.


Solution:2

Something like this:

PreparedStatement ps = conn.prepareCall("SELECT * FROM table WHERE someDate = ?");  ps.setDate(1, javaDate)  

(From memory so the syntax might not quite be right)

You do have to convert java.util.Date objects to java.sql.Date objects though.

This is fairly simple:

java.sql.Date myDate = new java.sql.Date(oldDate.getTime());  

Where oldDate is a java.util.Date object.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »