How to fix java.sql.SQLException: Invalid column index? Example

"java.sql.SQLException: Invalid column index" is a frequent error while working in Java Database Connectivity (JDBC). As the name suggests "Invalid column index" its related to accessing or setting column in your SQL Query using prepared statement in Java. I have seen "java.sql.SQLException: Invalid column index" coming mostly due to two reason:

1) Setting column data using setXXXX(int coloumIndex) e.g. setInt(0) setString(0)

2) Getting column data using getXXX(int columnIndex) e.g. getInt(0) getString(0)

The most common cause of "java.sql.SQLException: Invalid column index" is a misconception that column index started 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".

java.sql.SQLException: Invalid column index

That's was the one case another case was you are simply 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" or if your PreparedStatement has 3 place holders but you are trying to set data on 4th column index you will be greeted by "java.sql.SQLException: Invalid column index". 

In the next section, we will see a real-life example of "java.sql.SQLException: Invalid column index" while writing JDBC code.

An example of "java.sql.SQLException: Invalid column index"

here is a sample code example of getting some data from the PreparedStatement SELECT SQL query. here we have put just one placeholder for passing order_id. if you pass place holder anything other than "1" like "0" or "2" you will get "java.sql.SQLException: Invalid column index", just try it on your end and you will get hold of it. The same is true while you are reading data from ResultSet.

Actually, ResultSet offers two ways to access column data either by column name or column index. 

If you access column data using an incorrect column name, JDBC will throw "java.sql.SQLException: Invalid column name" while if the index is incorrect JDBC will throw "java.sql.SQLException: Invalid column index". I prefer accessing column data using the name because it's more readable in code.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class InvalidColumnIndexExample {

    public static void main(String args[]) throws SQLException {

        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1526:TESTID", "root", "root");

        PreparedStatement preStatement = conn.prepareStatement("select distinct item from Order where order_id=?");

    

        preStatement.setString(0, "123456"); //this will throw "java.sql.SQLException: Invalid column index" because "0" is not valid colum index

      

        ResultSet result = preStatement.executeQuery();

      

        while(result.next()){

            System.out.println("Item: " + result.getString(2)); //this will also throw "java.sql.SQLException: Invalid column index" because resultset has only one column

        }

    }

}

Output:

Exception in thread "main" java.sql.SQLException: Invalid column index

        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:7700)

        at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7654)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:910)

That’s all on how to fix “Exception in thread "main" java.sql.SQLException: Invalid column index”  Just beware that column index on ResultSet and PreparedStatement parametric query starts with 1 and not 0 and accessing column with index 0 will result in invalid column index error.

Some Java tutorials you may like:

How to fix java.lang.UnSupportedClassVersionError in Java

How to debug Java program in Eclipse – Java Debugging tips

How to remote debug Java applications in Eclipse

Difference between ClassNotFoundException vs NoClassDefFoundError in Java

How to resolve java.lang.ClassNotFoundException in Java

How to fix Java.lang.OutOfMemroyError in Tomcat Server