Fix for – java.sql.SQLException: Invalid column index

When you are working with JDBC, “java.sql.SQLException: Invalid column index” is a common error that you will get often. Let’s have a look into most common mistakes that causes for issue and look into some examples on how do we fix them.

There are 3 most common reasons that causes “java.sql.SQLException: Invalid column index“.

Cause 1

Example :

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				
				PreparedStatement ps = con.prepareStatement("SELECT * FROM USER_ where USERNAME LIKE ? AND USERTYPE=?");
				ps.setString(0, "PeterM");
				ps.setString(1, UserType.STUDENT.toString());
				
				return ps;
			}

Issue : Positional parameters count Starts from 1 not from zero.

Fix : To set positional parameter values, parameter count starts from 1.

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				
				PreparedStatement ps = con.prepareStatement("SELECT * FROM USER_ where USERNAME LIKE ? AND USERTYPE=?");
				ps.setString(1, "PeterM");
				ps.setString(2, UserType.STUDENT.toString());
				
				return ps;
			}

Cause 2

Example :

Query : String sql = "SELECT ID, USERNAME, PASSWORD, CREATEDTIME FROM USER_ where USERNAME LIKE ?";

			public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<User> values = new ArrayList<User>();
				while (rs.next()) {
					User u = new User();
					u.setId(rs.getLong(1));
					u.setUserName(rs.getString(2));
					u.setPassword(rs.getString(3));
					u.setCreatedTime(rs.getDate(4));
					u.setUserType(UserType.valueOf(rs.getString(5)));

					values.add(u);
				}
				return values;
			}

Issue : Notice the query, it retrieving 4 columns results. But in code accessing 5th value from ResultSet. Which is common mistake when you have large number of projections in query.

Fix : It’s good to retrieve values using column label instead of using column index.

			public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<User> values = new ArrayList<User>();
				while (rs.next()) {
					User u = new User();
					u.setId(rs.getLong("ID"));
					u.setUserName(rs.getString("USERNAME"));
					u.setPassword(rs.getString("PASSWORD"));
					u.setCreatedTime(rs.getDate("CREATEDTIME"));
					
					values.add(u);
				}
				return values;
			}

Cause 3

Example :

		User user = getUser();
		int created = jdbcTemplate.update(
				"INSERT INTO USER_ (USERNAME, PASSWORD, CREATEDTIME, UPDATEDTIME, USERTYPE, DATEOFBIRTH)"
						+ " VALUES(?,?,?,?,?,?)",
			        user.getId(),
				user.getUserName(),
				user.getPassword(),
				user.getCreatedTime(),
				user.getUpdatedTime(),
				user.getUserType().toString(), user.getDateofBirth());

Issue : Providing more number of values than the number of parameters.

Fix : Number of parameters and number of providing values must be same. When you have large number of positional parameters it’s recommended to use named parameters.

		User user = getUser();
		int created = jdbcTemplate.update(
				"INSERT INTO USER_ (USERNAME, PASSWORD, CREATEDTIME, UPDATEDTIME, USERTYPE, DATEOFBIRTH)"
						+ " VALUES(?,?,?,?,?,?)",
				user.getUserName(),
				user.getPassword(),
				user.getCreatedTime(),
				user.getUpdatedTime(),
				user.getUserType().toString(), user.getDateofBirth());

Conclusion

We have covered the most common reasons that causes for “java.sql.SQLException: Invalid column index” with some basic examples. Please comment if you find any other reason.

Other Spring JDBC Examples :

1. Spring JdbcTemplate – Batch Update
2. Spring JdbcTemplate – Query
3. Spring JdbcTemplate – Stored Procedures
4. Spring JdbcTemplate – IN Clause
5. Spring JdbcTemplate – Auto-generated Keys
6. Spring Boot – Loading Initial Data
7. Spring JdbcTemplate – Pagination

References

1. SQLException
2. Spring JdbcTemplate query