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 ;)
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