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?

Answer 1

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.