In this post we will find out reasons and solution for >
- java.sql.SQLException: Invalid column index
- java.sql.SQLException: Invalid column name
First let's discuss java.sql.SQLException: Invalid column index and solve it in java.
--Before executing java program execute these database scripts >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;
--If table already exists then execute the DROP command >
drop table EMPLOYEE;
EMPLOYEE table will look like this >
JDBC- Program1 - Which can throw java.sql.SQLException: Invalid column index in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class InvalidColumnIndexExample {
public static void main(String... arg) throws ClassNotFoundException, SQLException {
Connection con = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
// registering Oracle driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
prepStmt = con.prepareStatement("select ID, NAME from EMPLOYEE where ID=?");
prepStmt.setInt(2, 7); //It will throw java.sql.SQLException: Invalid column index
rs = prepStmt.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt("ID")+" ");
System.out.println(rs.getString("NAME"));
}
}
}
/*OUTPUT
Connection established successfully!
Exception in thread "main" java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4605)
at oracle.jdbc.driver.OraclePreparedStatement.setInt(OraclePreparedStatement.java:4594)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setInt(OraclePreparedStatementWrapper.java:198)
at InvalidColumnIndexExample.main(InvalidColumnIndexExample.java:24)
*/
|
Solution to java.sql.SQLException: Invalid column index in above java program?
Using prepStmt.setInt(1, 7); will SOLVE java.sql.SQLException: Invalid column index
And output of program will be -
Connection established successfully!
7 ankit
|
But why java.sql.SQLException: Invalid column index was thrown in above java program?
Because prepStmt.setInt(2, 7) was searching for second occurrence of ? in
"select ID, NAME from EMPLOYEE where ID=?" while there was only one ? in whole query (searching was done at invalid column index).
But,
prepStmt.setInt(1, 7)
searched for first occurrence of ? in
"select ID, NAME from EMPLOYEE where ID=?" and replace first occurrence of ? in whole query.
Now, let's discuss java.sql.SQLException: Invalid column name and solve it in java.
JDBC- Program2 - Which can throw java.sql.SQLException: Invalid column name in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class InvalidColumnNameExample {
public static void main(String... arg) throws ClassNotFoundException, SQLException {
Connection con = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
// registering Oracle driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
prepStmt = con.prepareStatement("select ID, NAME from EMPLOYEE where ID=?");
prepStmt.setInt(1, 7);
rs = prepStmt.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt("EMP_ID")+" "); //It will throw java.sql.SQLException: Invalid column index
System.out.println(rs.getString("NAME"));
}
}
}
/*OUTPUT
Connection established successfully!
Exception in thread "main" java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3367)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2009)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:434)
at InvalidColumnIndexExample.main(InvalidColumnIndexExample.java:27)
*/
|
Solution to java.sql.SQLException: Invalid column name in above java program?
Using rs.getInt("ID"); will SOLVE java.sql.SQLException: Invalid column name
And output of program will be -
Connection established successfully!
7 ankit
|
But why java.sql.SQLException: Invalid column name was thrown in above java program?
Because rs.getInt("EMP_ID") was searching for column name EMP_ID in "select ID, NAME from EMPLOYEE where ID=?" while there was no such column name (neither in database nor as any alias name in query). (searching was done with invalid column name)
But,
rs.getInt("ID")
searched for column ID in
"select ID, NAME from EMPLOYEE where ID=?" and found it.
RELATED LINKS>
12 Differences between Statement, PreparedStatement and CallableStatement in java
java.sql.PreparedStatement - using executeUpdate and executeQuery methods - CREATE, INSERT, UPDATE and DELETE