"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)
Most common cause of "java.sql.SQLException: Invalid column index" is 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 other case was you are simply putting wrong column index, like if you 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 next section we will see a real life example of "java.sql.SQLException: Invalid column index" while writing JDBC code.
Example of "java.sql.SQLException: Invalid column index"
here is simple code example of getting some data from PreparedStatement SELECT SQL query. here we have put just one place holder 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. same is true while you are reading data from ResultSet.
Actually ResultSet offer two ways to access column data either by column name or column index. if you access column data using incorrect column name, JDBC will throw "java.sql.SQLException: Invalid column name" while if index is incorrect JDBC will throw "java.sql.SQLException: Invalid column index" . I prefer accessing column data using name because its 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 tutorial you may like:
Tidak ada komentar:
Posting Komentar