java.sq.SQLException: Column not found
51,278
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.
Author by
Matthew Moisen
Backend engineer specializing in Python and RDBMS.
Updated on September 28, 2020Comments
-
Matthew Moisen over 3 years
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 columncategory_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()); }
-
prashanth-g almost 7 yearswhen you miss selecting few columns in the SQL statement you can get this error.