Home Spring Boot Fix for - java.sql.SQLException: Invalid column index

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

Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories