Wednesday, May 15, 2013

Timestamp myth

In oracle, the column date_visited with Date type, in UI java code its type is java.util.Date. while saving to DB (in EJB entity bean), its type is java.sql.Timestamp. The weird thing happened is that after I saved the record the database and then queried it right away, I can't get the record back even it has been saving in DB.

   
   private static final String GET_SERVICE_LOCATION_QUERY = 
      "select * from service_location where service_recommendation_id=? " +
      "and location_id=? and date_visited=?";
 
   connection = DataSourceAccessUtil.getDataSource().getConnection();
   statement = connection.prepareStatement(GET_SERVICE_LOCATION_QUERY);
   statement.setLong(1, serviceRmdId);
   statement.setLong(2, locationId);
   SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");   
   statement.setTimestamp(3, new Timestamp(dateVisited));
   resultSet = statement.executeQuery();
The reason is in the precision. According to Oracle doc, "The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight)." And java.sql.Timestamp captures time in *nano* level, a fraction of second (0-999,999,999). So the comparsion cannot be equal. I have to change to use to_char to make it work.
   
   private static final String GET_SERVICE_LOCATION_QUERY = 
      "select * from service_location where service_recommendation_id=? " + 
      "and location_id=? and to_char(date_visited, 'DD-MM-YYYY HH24:MI:SS')=?";
 
   connection = DataSourceAccessUtil.getDataSource().getConnection();
   statement = connection.prepareStatement(GET_SERVICE_LOCATION_QUERY);
   statement.setLong(1, serviceRmdId);
   statement.setLong(2, locationId);
   SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");   
   statement.setString(3, format.format(dateVisited));
   resultSet = statement.executeQuery();