java.sq.SQLException: Column not found

Question

I am receiving the following error:

HTTP Status 500 - Request processing failed; nested exception is
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar 
[SELECT id, name FROM track WHERE category_id = 1 ORDER BY name]; nested exception is
java.sql.SQLException: Column 'category_id' not found.

But when I copy and paste the very select statement listed in the error into a mysql shell, I get the result, which is expected as the table track has the column category_id.

What could be a reason for this error?

Here is the table create statement for track:

CREATE TABLE track (
 id SERIAL
,name VARCHAR(50)
,category_id BIGINT UNSIGNED -- This references a serial (bigint unsigned)
,CONSTRAINT track_id_pk PRIMARY KEY (id)
,CONSTRAINT track_category_id_fk FOREIGN KEY
  (category_id) REFERENCES category (id)
);

Here are some lines from my dao class regarding the track table:

private static final class TrackMapper implements RowMapper<Track> {
    @Override
    public Track mapRow(ResultSet resultSet, int rowNum) throws SQLException {
        Track track = new Track();
        track.setId(resultSet.getInt("id"));
        track.setName(resultSet.getString("name"));
        track.setCategoryId(resultSet.getInt("category_id"));
        return track;
    }
}
public List<Track> getTracks(int categoryId) {
    String sql = "SELECT id, name FROM track WHERE category_id = " + categoryId + " ORDER BY name";
    return jdbcTemplate.query(sql, new TrackMapper());
}

It's likely the data type. Try resultSet.getLong instead of getInt — Thomas
– Thomas, Commented Sep 28, 2013 at 3:09
In my case, I had been specifying my params in single quotes, i.e. do NOT do this: "WHERE value = ':myvalue'" — Janac Meena
– Janac Meena, Commented Apr 13, 2020 at 15:43

kevinpeterson · Accepted Answer · 2013-09-28 04:23:45Z

34

Check your SQL statement -- you need to include the category_id in the column list:

String sql = "SELECT id, name, category_id FROM track WHERE category_id = " + categoryId + " ORDER BY name";

It is failing because you're trying to extract category_id from the ResultSet and it isn't there.

answered Sep 28, 2013 at 4:23

kevinpeterson

1,1729 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prashanth-g Over a year ago

when you miss selecting few columns in the SQL statement you can get this error.

Collectives™ on Stack Overflow

java.sq.SQLException: Column not found

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related