SQLException in java

What is hierarchy of java.sql.SQLException ?

-java.lang.Object
-java.lang.Throwable
 -java.lang.Exception
   -java.sql.SQLException



In this post we will take a look at most important and frequently occurring java.sql.SQLException in JDBC java. And we will try to figure out why they happen and find appropriate solution to solve them as well.


java.sql.SQLException: Exhausted Resultset
It is thrown when cursor does not point to any row in ResultSet’s object in JDBC java.


java.sql.SQLException: Exhausted Resultset
It is thrown when cursor does not point point to any row in ResultSet’s object in JDBC java.
is thrown when rs.getString("COLUMN_NAME") is called before calling rs.next() on ResultSet object.
 rs = prepStmt.executeQuery();
 //At this point cursor is before the first position of ResultSet object
 System.out.println(rs.getString("COLUMN_NAME"));//java.sql.SQLException: Exhausted Resultset
java.sql.SQLException: Exhausted Resultset
It is thrown when cursor does not point to any row in ResultSet’s object.
It may be thrown when rs.getString("COLUMN_NAME") is called after iterating on ResultSet object.
 rs = prepStmt.executeQuery();
 while (rs.next()) {
         System.out.println(rs.getString("COLUMN_NAME"));
 }
 //At this point cursor is after the last position of ResultSet object         
 System.out.println(rs.getString("COLUMN_NAME"));//java.sql.SQLException: Exhausted Resultset
java.sql.SQLException: Exhausted Resultset
It is thrown when cursor does not point to any row in ResultSet’s object.
It may be thrown when rs.next() is called after closing the ResultSet object.
 rs = prepStmt.executeQuery();
 rs.close();
 rs.next(); //java.sql.SQLException: Exhausted Resultset





ResultSet type TYPE_FORWARD_ONLY does not allow first(), last(), previous(), absolute or relative()
ResultSet type = ResultSet.TYPE_FORWARD_ONLY
ResultSet concurrency = ResultSet.CONCUR_READ_ONLY or
   ResultSet.CONCUR_UPDATABLE



java.sql.SQLException: Invalid operation for forward only resultset : first
   prepStmt = con.prepareStatement("select * from EMPLOYEE",
                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
   rs = prepStmt.executeQuery();
   rs.last(); //java.sql.SQLException: Invalid operation for forward only resultset : last
   
  
java.sql.SQLException: Invalid operation for forward only resultset : last
   prepStmt = con.prepareStatement("select * from EMPLOYEE",
                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
   rs = prepStmt.executeQuery();
   rs.last(); //java.sql.SQLException: Invalid operation for forward only resultset : last
      
  
java.sql.SQLException: Invalid operation for forward only resultset : previous
   prepStmt = con.prepareStatement("select * from EMPLOYEE",
                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
   rs = prepStmt.executeQuery();
   rs.next();
      rs.previous(); //java.sql.SQLException: Invalid operation for forward only resultset : last
      
  
java.sql.SQLException: Invalid operation for forward only resultset : absolute
   prepStmt = con.prepareStatement("select * from EMPLOYEE",
                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
   rs = prepStmt.executeQuery();
   rs.absolute(1); //java.sql.SQLException: Invalid operation for forward only resultset : absolute
      
java.sql.SQLException: Invalid operation for forward only resultset : relative
   prepStmt = con.prepareStatement("select * from EMPLOYEE",
                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
   rs = prepStmt.executeQuery();
   rs.absolute(1); //java.sql.SQLException: Invalid operation for forward only resultset : relative
      





ResultSet concurrency mode CONCUR_READ_ONLY does not allow first(), last(), previous(), absolute or relative() in JDBC java
ResultSet type = ResultSet.TYPE_FORWARD_ONLY or
ResultSet.TYPE_SCROLL_INSENSITIVE or
ResultSet.TYPE_SCROLL_SENSITIVE
ResultSet concurrency = ResultSet.CONCUR_READ_ONLY



java.sql.SQLException: Invalid operation for read only resultset: updateString
when ResultSet  mode is CONCUR_READ_ONLY it cannot be update.
      prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  
   rs = prepStmt.executeQuery();
  
   rs.next(); //move cursor to first row in ResultSet object
   //Print data in first row
   System.out.println(rs.getString("NAME")); //will print data in first row
  
   rs.updateString("NAME", "ankitUpdated");//java.sql.SQLException: Invalid
                      operation for read only resultset: updateString



java.sql.SQLException: Invalid operation for read only resultset: deleteRow
when ResultSet  mode is CONCUR_READ_ONLY row cannot be deleted from it.
      prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  
   rs = prepStmt.executeQuery();
  
   rs.next(); //move cursor to first row in ResultSet object
   //Print data in first row
   System.out.println(rs.getString("NAME")); //will print data in first row
  
   rs.deleteRow();//java.sql.SQLException: Invalid operation for read only resultset: deleteRow



java.sql.SQLException: Invalid operation for read only resultset: moveToInsertRow
when ResultSet  mode is CONCUR_READ_ONLY row cannot be inserted into it.
prepStmt = con.prepareStatement("select NAME from EMPLOYEE",
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  
   rs = prepStmt.executeQuery();
  
   rs.moveToInsertRow(); //java.sql.SQLException: Invalid operation for read only
                                resultset: moveToInsertRow
   rs.updateString("NAME", "ankitNew");
rs.insertRow();







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.






Solve ora-01000 maximum open cursors exceeded java.sql.SQLException
What causes the Solve ora-01000 maximum open cursors exceeded java.sql.SQLException problem in JDBC java-
  • Not closing the JDBC Statement object can cause maximum open cursors exceeded java.sql.SQLException,
  • Not closing the JDBC PreparedStatement object can cause maximum open cursors exceeded java.sql.SQLException,
  • Not closing the JDBC CallableStatement object can cause maximum open cursors exceeded java.sql.SQLException,
  • Not closing the JDBC ResultSet object and
  • Not closing the JDBC Connections object can cause maximum open cursors exceeded java.sql.SQLException


Solution to ora-01000 maximum open cursors exceeded java.sql.SQLException problem in JDBC java-
You must ensure that you close all the JDBC Statement, PreparedStatement, CallableStatement , ResultSet and Connections in java to avoid ora-01000 maximum open cursors exceeded java.sql.SQLException in java. You must always close all the above mentioned objects in finally block in java because finally block is always executed irrespective of exception is thrown or not by java code.
Example of closing PreparedStatement, ResultSet and Connections in finally block 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 PreparedStatementExecuteQueryTest {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = null;                 
          try {
                 // Logic ..
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}

eEdit
Must read for you :