JodaTime and BeanPropertySqlParameterSource

Question

The situation is something like this:

PostgreSQL Database table has a field dateAdded which is timestamp

The Pojo Model object maps this field as

class MyModel{
   org.joda.time.DateTime dateAdded;

}

My Dao Implementation is in Spring JDBC Template and it goes as:

SqlParameterSource parameters = new BeanPropertySqlParameterSource(patient);
jdbcInsert.execute(parameters);

I read model from the client and build the object using @Model. Everything is fine so far. When I execute this, the database throws an exception saying:
_{[Edit Erwin]: Turns out the exception is not coming from the database.}

org.postgresql.util.PSQLException: Bad value for type timestamp : 2011-10-10T21:55:19.790+03:00

I don't want to do the formatting manually by implementing a full INSERT statement as there are many fields involved.

What is the best possible solution here? Is there a way to configure toString() of DateTime on all calls. I also thought about creating an inherited class from DateTime but ... mmhh.. it's a final.

--

Edit

Per Erwin, I tested DateTime value '2011-10-10T21:55:19.790+03:00' by inserting into a dummy table and it's working. But can't get to work with JDBC. Something related to the JDBC driver?

Answer 1

The problem here is that JdbcTemplate is using a prepared statement and then binding the values. The field in question is a of type timestamp - so it needs to be set as a java.sql.Date. In this case spring is calling the generic setObject method passing it the Joda Time DateTime instance. The driver doesn't know how to convert this into a java.sql.Date - hence the error.

To fix this problem you can extend BeanPropertySqlParameterSource override the getValue method. If the type of object is joda.time.DateTime convert it to a java.util.Date object and return it. That should fix the issue.

class CustomBeanPropertySqlParameterSource extends BeanPropertySqlParameterSource {
  @Override
  Object getValue(String paramName) {
     Object result = super.getValue(paramName);
     if (result instanceof DateTime) {
        return ((DateTime) result).toDate();
     } else {
        return result;
     }
  }
}