Re: compering date with the data base
Arne Vajh??j wrote:
Lew wrote:
Arne Vajh??j wrote:
Lew wrote:
Arne Vajh??j wrote:
On the Calendar object you can set hh, mm and ss to 0.
I suggest using java.sql.Date and its siblings to interact with the
java.sql interfaces like PreparedStatement and ResultSet. They are
direct subclasse of java.util.Date, set up specifically for SQL
frameworks to recognize.
This is opposite to the usual best practice of using a supertype
declaration whenever feasible, but in this case it is justified.
The java.sql type documents in the code the mapping from SQL to
Java, made precise by the choice of java.sql.Date, Time or Timestamp
as the object type.
Put another way, the java.sql classes belong to the data-access
ontology, so it's OK to use them instead of supertypes when in that
ontology.
If the database actually has a column type with date but no time,
then it is a nice solution.
But if not then I would consider it messy.
Which "it"?
Your solution.
I meant which of the three. I mentioned three.
For the column that uses the SQL TIMESTAMP type there is
java.sql.Timestamp, mentioned /supra/. That is the point of the
self-documenting nature of these classes. Use java.sql.Date for DATE,
java.sql.Time for TIME, and java.sql.Timestamp for TIMESTAMP.
Beautifully matched.
They match if the database has the types.
It had better. They are SQL standard types, all three.
Storing a java.sql.Date in a column that only takes the date part
is perfect.
And only for that column, of SQL type DATE. We agree here. I said as much in
my posts, as you cited.
But for a database that only has one column type for date and time
SQL databases are supposed to have all three types, in accordance with the SQL
standard.
that includes both data and time, then I would prefer to truncate
the time part in Java and store it as java.sql.Timestamp instead
of crossing my fingers and hope that a java.sql.Date gets
stored with only the date part.
No need to hope. One can be certain that java.sql.Date stores only the date
part by making certain that it does. java.sql.Date is not made for TIMESTAMP,
and its documentation indicates as much.
To conform with the definition of SQL DATE, the millisecond values
wrapped by a java.sql.Date instance must be 'normalized'
by setting the hours, minutes, seconds, and milliseconds to zero
in the particular time zone with which the instance is associated.
<http://java.sun.com/javase/6/docs/api/java/sql/Date.html>
This is part of what makes the java.sql implementation ugly. It wouldn't
matter in a real database, but for that toy one you describe the fix is
java.util.Calendar sinceWhen = Calendar.getInstance();
java.sql.Date since = rs.getDate( "sinceWhen" );
if ( since == null )
{
throw new NullPointerException( "dateless" );
}
calSince.setTime( since );
calSince.set( Calendar.HOUR, 0 );
calSince.set( Calendar.MINUTE, 0 );
calSince.set( Calendar.SECOND, 0 );
calSince.set( Calendar.MILLISECOND, 0 );
In a compliant database the guard step of eliminating time values could be
unnecessary, since DATE doesn't hold the time fields. Switch from the toy
database and use one that supports the SQL datetime types, including time zones.
Anyway, I've been saying what you are saying, that java.sql.Date is for the
SQL DATE type, not the TIME or TIMESTAMP types. To avoid information loss
from a datetime type that includes time, one should not translate to Date but
to Timestamp.
I suspect an argument where the parties agree in the first place could last a
really long time.
--
Lew