Bad Sql Grammar exception in JDBC spring

Question

I am the getting

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select cid, clinician-code, password, first-name, last-name from Clinician where clinician-code= ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'clinician' in 'field list'

error on the following code, You can also see the Table in the screen shot, except for cid all other attributes are VARCHAR(45)

Clinician Table

Row mapper class

public class CClinicianRowMapper implements RowMapper {

@Override
public Object mapRow(ResultSet rs, int line) throws SQLException {
    CClinicianResultSetExtractor extractor = new CClinicianResultSetExtractor();
    return extractor.extractData(rs);
}

}

Result Extractor Class public class CClinicianResultSetExtractor implements ResultSetExtractor {

  @Override
  public Object extractData(ResultSet rs) throws SQLException {
    CClinician clinician = new CClinician();
    clinician.setCid(rs.getLong("cid"));
    clinician.setClinicianCode(rs.getString("clinician-code"));
    clinician.setPassword(rs.getString("password"));
    clinician.setFirstName(rs.getString("first-name"));
    return clinician;
  }

}

Class for selecting data from table

public List<CClinician> findClinician(CClinician _clinician) {
    // TODO Auto-generated method stub
    JdbcTemplate select = new JdbcTemplate(dataSource);
    try
    {
    return select.query("select cid, clinician-code, password, first-name, last-name from Clinician where clinician-code= ?",
            new Object[] {_clinician.getClinicianCode()}, new CClinicianRowMapper());

    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
    return null;
}

jeffkempf jeffkempf · Accepted Answer · 2016-03-23T13:50:24

I know this is an old thread, but hopefully anyone stumbling across this question will find this answer useful.

I was getting the same exception in my spring app. Based on the output, I thought I had a syntax issue with my query. It turns out that I actually had a syntax error in my mapper method. I came across this issue because I originally created my Product class with different field names than the column names.

public class ProductMapper implements RowMapper<Product> {
public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
  Product product = new Product();
  product.setProduct_id(rs.getInt("product_id"));  //was id.  was causing BadSqlGrammarException
  product.setProduct_name(rs.getString("product_name")); //was name.  was causing BadSqlGrammarException
  product.setPrice(rs.getDouble("price"));
  product.setQuantity(rs.getInt("quantity"));
  return product;
   }
}

Once I made the above changes (my fields are named product_id and product_name), my app worked correctly. Just remember that any changes you make to column names or java fields should be made not only to your query statements, but also to your mapper method as well. I see that the OP did this correctly, but I thought it was worth reiterating. I hope someone finds this helpful.

Bad Sql Grammar exception in JDBC spring

2 Answers