java.sql.SQLException: Invalid column index

The exception may be caused by using 0 or a number larger than the number of columns in your sql as the column index. While in Java the convention of a collection count starts from 0, it starts from 1 in database query.

A particular situation is using alias in query with union. The alias instead of the real column name should be used in the order by clause. Otherwise, it will throw the exception.

Example 1: Select

String sql = "SELECT name, id, address from employee";

ResultSet rs = statement.executeQuery(sql);

while (rs.next()) {
      //exception because 0 if an invalid index.
      String c0 = rs.getString(0);
      String c1 = rs.getString(1);
      long id = rs.getLong(2);
      String address = rs.getString(3);
      //Exception because the sql has only 3 columns
      long deptId = rs.getLong(4);
}

Example 2: Insert

String sql = "INSERT INTO employee (name, id, department, empdate) values (?, ?, ?, ?)";

PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "John Young");
ps.setLong(2, 90000);
ps.setLong(3, 1);
ps.setInt(4, 2017200);
//Exception because the sql has only 4 columns
ps.setString(5, "Excellent in public speaking.");

Example 3: Union

Select storeName as "store name", storeAddress, storePhone from store where storeName like '%Mart%'
UNION
Select storeName as "store name", storeAddress, storePhone from store where storePhone like '505%'
//Exception because it cannot identify the storeName
order by storeName;

The working query is
Select storeName as "store name", storeAddress, storePhone from store where storeName like '%Mart%'
UNION
Select storeName as "store name", storeAddress, storePhone from store where storePhone like '505%'
order by "store name";


-------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.