Blog of Wonders

DATETIME

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:

1

2

3

4

5

6

7

8

9

10

publicclassExampleDao{

protectedNamedParameterJdbcTemplate jdbcTemplate;

publicvoidinsert(ExampleModel model){

jdbcTemplate.update(

"INSERT INTO `sometable` (`some_datetime_column`) VALUES(:datetime)",

newMapSqlParameterSource().addValue("datetime",newDate(),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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

publicclassExampleDao{

protectedNamedParameterJdbcTemplate jdbcTemplate;

publicvoidinsert(ExampleModel model){

jdbcTemplate.update(

"INSERT INTO `sometable` (`some_datetime_column`) VALUES(:datetime)",

newMapSqlParameterSource().addValue(

"datetime",

newjava.sql.Date(newDate().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).