Sunday, April 13, 2014

Spring JdbcTemplate/ NamedJdbcTemplate tutorial : Resolve Date insertion

Removed
I was working with Spring JdbcTemplate to insert rows in the data base. My query is:
insert into person (person_id, name, birth_date) values(?, ?, ?)

@Autowired
JdbcTemplate jdbcTemplate;
private String query ="insert into person (person_id, name, birth_date) values(?, ?, ?)"

public void store(Person person){

       jdbcTemplate.update(query , person.getId(), person.getName(), person.getBirthDate());

}

Using this code, I get this error

 org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into
...]
; nested exception is org.postgresql.util.PSQLException: ERROR: column "birth_date" is of type timestamp with time zone but expression is of type character varying

So the database is not accepting the java.util.Date.
Next olution is to use the DATE '2004-02-02' of postgres, so I have modified my query:
insert into person (person_id, name, birth_date) values(?, ?, DATE ?)

So even with this modification I got :
nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "$3"

Ok, so maybe I need to add qotes. My query is now :
insert into person (person_id, name, birth_date) values(?, ?, DATE '?')
This didn't resolve the problem and I got this error:
PSQLException: The column index is out of range: 3, number of columns: 2

Ok, so my last chance is to use NamedParameterJdbcTemplate.
I have modified my code:
1- In the aplicationContext, I added:

<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" >
        <constructor-arg ref="myDataSource" />
</bean>

2- In my code :

@Autowired
protected NamedParameterJdbcTemplate namedJdbcTemplate;

        private String query ="insert into person (person_id, name, birth_date) values(:id, :name, :birthDate)";

public void store(Person person){

       MapSqlParameterSource params = new MapSqlParameterSource();
   params.addValue("id", person.getId());
       params.addValue("name",  person.getName());
       params.addValue("birthDate", person.getBirthDate(), Types.DATE);
       jdbcTemplate.update(query , params);

}

Adding the Types.DATE ( don't forget to import java.sql.Types;), has resolved the problem, and now the insertion is working without any ProBleM ;)


No comments :

Post a Comment

Articles les plus consultés