java.sql.SQLException: Invalid column index

"java.sql.SQLException: Invalid column index"

Trying to access the invalid column in java

Some of the possible causes of the exception as as follows:

1) Setting or getting data from 0th column index.

Suppose there is an query 
"select productname from product where productid=456"
To get the productname 
String productName = null;
while(rs.next())
{
     productName = rs.getString(0);// this will throw java.sql.SQLException: Invalid column index
}

There is misconception that column index starts with "0" like array or String index but that's not true instead column index starts with "1" so whenever you try to get or Set column data with column index "0" you will get "java.sql.SQLException: Invalid column index".

2) Putting wrong column index, like if your query is returning 3 columns in ResultSet and you are trying to access 4th column, JDBC will throw "java.sql.SQLException: Invalid column index" .

Suppose the query is like "select employename from employee where employeeid=123"
and while retrieving the data from the ResultSet
String employeeName = null;
String employeeStatus =  null;
while(rs.next())
{
     employeeName  = rs.getString(1);
     employeeStatus  = rs.getString(2); // this will throw java.sql.SQLException: Invalid column index
}

Here while fetching the data the SQL query had only one column name in the Select statement and while getting the records from the ResultSet it triedd to get the second column and hence there was an exception.

3) if your PreparedStatement has 3 place holders but you are trying to set data on 4th column index you will get "java.sql.SQLException: Invalid column index".

Suppose the query is like 
String query = "Select employeename, employeestatus, employeeaddress from employee where employeeid = ? and employeeExperience = ?"
String empid = "123456";
String employeeExp = "5";
PreparedStatement ps= conn.prepareStatement(query);
ps.setString(1, empid);
ps.setString(2, employeeExp);
ps.setString(3, empid); //this will throw java.sql.SQLException: Invalid column index

4) Syntax error : If you put single quotes around the placeholder assuming that it is string then there will be exception.

For e.g.
String query = "Select employeename, employeestatus, employeeaddress from employee where employeeid = '?' and employeeExperience = '?'"
'?' -this act as a literal value where ? is a values and not a placeholder, it should be just ?
and while binding the parameters it won't get any placeholder for e.g.


String empid = "123456";
String employeeExp = "5";
PreparedStatement ps= conn.prepareStatement(query);
ps.setString(1, empid);//this will throw java.sql.SQLException: Invalid column index
ps.setString(2, employeeExp);

As there was no placeholder and it was trying to bind the parameter.

Note: Using PreparedStatement, all the placeholders(?) should have equal number of bind parameter values.
This happens mostly while inserting a record where there are chances of having many columns so for each column placeholder will be there and for each column a value should get bind.


5) Trying to SELECT a column that does not exist or Trying to ORDER BY a column that does not exist.
Run the query in any sql client and verify that the column exist or not.

6) Mistakenly added the value instead of placeholder
String query = "Select employeename, employeestatus, employeeaddress from employee where employeeid = '9866' and employeeExperience = ?"
String empid = "123456";
String employeeExp = "5";
PreparedStatement ps= conn.prepareStatement(query);
ps.setString(1, empid);//this will throw java.sql.SQLException: Invalid column index

ps.setString(2, employeeExp);