Read carefully the org.springframework.jdbc.BadSqlGrammarException

Read carefully the org.springframework.jdbc.BadSqlGrammarException

In data access code driven by Spring JdbcTemplate, you may get an exception like:

org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar
[SELECT first_name, last_name, last_update FROM people WHERE person_id = ?];
nested exception is java.sql.SQLException: Invalid column name

You may think the query is wrong, maybe one of the column names is misspelled. But the actual error is in the row mapper or in the row extractor.

You may have code similar to the following snippet:

  1. public Person getPersonById(String personId) {
  2.   if(StringUtils.hasText(personId)) {
  3.     List<Person> people = getJdbcTemplate().query(
  4.       "SELECT first_name, last_name, last_update FROM people WHERE person_id = ?"
  5.       , new PersonMapper()
  6.       , personId
  7.     );
  8.     if(people.size() > 0)
  9.       return people.get(0);
  10.   }
  11.   return null;
  12. }
  13.  
  14. private static final class PersonMapper implements RowMapper<Person> {
  15.   public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
  16.     Person person = new Person();
  17.     person.setPersonId(rs.getString("person_id")); // the invalid column name.
  18.     person.setFirstName(rs.getString("first_name"));
  19.     person.setLastName(rs.getString("last_name"));
  20.     person.setLastUpdate(rs.getTimestamp("last_update"));
  21.     return person;
  22.   }
  23. }

Add "person_id" in the SELECT clause of the query to solve the problem.

Comments

Thanks man..This post was a by Ramesh (not verified)
Thanks .... Very Useful by Anonymous (not verified)
c by Anonymous (not verified)
Awesome thank you!!!! by Anonymous (not verified)
Brilliant. Got me out of a by Anonymous (not verified)
everything is perfect as you by Anonymous (not verified)
Nice it is very helpful by Anonymous (not verified)
thanks alot... its really by Anonymous (not verified)
thank you very much! you just by flymanFromEarth (not verified)
Worked for me :) by Anonymous (not verified)
not useful......... by Anonymous (not verified)
Thanks a lot for this nice!! by Anonymous (not verified)
Very useful information by Anonymous (not verified)
Probably saved me a few more by IcedDante (not verified)

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

More information about formatting options

To prevent automated spam submissions leave this field empty.