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 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.
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();
}
}
}
}
|