Justin Johnson Blog of Wonders

28Aug/12Off

Inserting DATETIME’s with Spring’s JdbcTemplate and NamedParameterJdbcTemplate

Ran into another bit of trouble today that cost me another sizable chunk of time. When using Spring's NamedParameterJdbcTemplate to insert into a DATETIME column, the following doesn't work:

public class ExampleDao {
    protected NamedParameterJdbcTemplate jdbcTemplate;

    public void insert(ExampleModel model) {
        jdbcTemplate.update(
            "INSERT INTO `sometable` (`some_datetime_column`) VALUES(:datetime)",
            new MapSqlParameterSource().addValue("datetime", new Date(), Types.DATE)
        );
    }
}

Your date will be happily insert, but you'll be sad to know that the time component has been truncated to something like "2012-08-28 00:00:00." Using Types.TIMESTAMP has the same effect.

Using Types.TIMESTAMP is actually correct, but the secret is that you have to wrap your java.util.Date in a java.sql.Date.

public class ExampleDao {
    protected NamedParameterJdbcTemplate jdbcTemplate;

    public void insert(ExampleModel model) {
        jdbcTemplate.update(
            "INSERT INTO `sometable` (`some_datetime_column`) VALUES(:datetime)",
            new MapSqlParameterSource().addValue(
                "datetime", 
                new java.sql.Date(new Date().getTime()), 
                Types.TIMESTAMP
            )
        );
    }
}

If you're reaction is one of disappointment, then you're feeling the same way I did.

For simple JdbcTemplates, there is sufficient behind the scenes magic that you don't run into this kind of problem. The following works without any translations or other such boilerplate (making the above that much more ... dissatisfying).

public class ExampleDao {
    protected JdbcTemplate jdbcTemplate;

    public void insert(ExampleModel model) {
        jdbcTemplate.update(
            "INSERT INTO `sometable` (`some_datetime_column`) VALUES(?)",
            new Object[] { new Date() }
        );
    }
}

I leave you with a list of SQL types and their Java equivalents, which is how I finally solved my problem with NamedParameterJdbcTemplate.

Comments (0) Trackbacks (0)

Sorry, the comment form is closed at this time.

Trackbacks are disabled.