”;
Exception handling allows you to handle exceptional conditions such as program-defined errors in a controlled fashion.
When an exception condition occurs, an exception is thrown. The term thrown means that current program execution stops, and the control is redirected to the nearest
applicable catch clause. If no applicable catch clause exists, then the program”s execution ends.
JDBC Exception handling is very similar to the Java Exception handling but for JDBC, the most common exception you”ll deal with is java.sql.SQLException.
SQLException Methods
An SQLException can occur both in the driver and the database. When such an exception occurs, an object of type SQLException will be passed to the catch clause.
The passed SQLException object has the following methods available for retrieving additional information about the exception −
Method | Description |
---|---|
getErrorCode( ) | Gets the error number associated with the exception. |
getMessage( ) | Gets the JDBC driver”s error message for an error, handled by the driver or gets the Oracle error number and message for a database error. |
getSQLState( ) | Gets the XOPEN SQLstate string. For a JDBC driver error, no useful information is returned from this method. For a database error, the five-digit XOPEN SQLstate code is returned. This method can return null. |
getNextException( ) | Gets the next Exception object in the exception chain. |
printStackTrace( ) | Prints the current exception, or throwable, and it”s backtrace to a standard error stream. |
printStackTrace(PrintStream s) | Prints this throwable and its backtrace to the print stream you specify. |
printStackTrace(PrintWriter w) | Prints this throwable and it”s backtrace to the print writer you specify. |
By utilizing the information available from the Exception object, you can catch an exception and continue your program appropriately. Here is the general form of a try block −
try { // Your risky code goes between these curly braces!!! } catch(Exception ex) { // Your exception handling code goes between these // curly braces, similar to the exception clause // in a PL/SQL block. } finally { // Your must-always-be-executed code goes between these // curly braces. Like closing database connection. }
Using try catch block in JDBC Example
Study the following example code to understand the usage of try….catch blocks. In this example, we”ve four static string for database connection string, username, password and a sql query to call a stored procedure. In main method, we”re preparing connection to the database using DriverManager.getConnection() method. We prepared a CallableStatement using connection.prepareCall() method. As next step, place holder values are set and out parameter is registered. Finally stored procedure is called using CallableStatement.execute() method and employee name is retrieved using getString() method. In catch() statement, we are handling a SQL exception. Being try with resources, finally statement is not needed and connection object is automatically closed after try-catch statements completion.
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class JDBCExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "{call getEmpName (?, ?)}"; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); CallableStatement stmt = conn.prepareCall(QUERY); ) { // Bind values into the parameters. stmt.setInt(1, 1); // This would set ID // Because second parameter is OUT so register it stmt.registerOutParameter(2, java.sql.Types.VARCHAR); //Use execute method to run stored procedure. System.out.println("Executing stored procedure..." ); stmt.execute(); //Retrieve employee name with getXXX method String empName = stmt.getString(2); System.out.println("Emp Name with ID: 1 is " + empName); } catch (SQLException e) { e.printStackTrace(); } } }
Now, let us compile the above example as follows −
C:>javac JDBCExample.java C:>
When you run JDBCExample, it produces the following result if there is no problem, otherwise the corresponding error would be caught and error message would be displayed −
C:>java JDBCExample Executing stored procedure... Emp Name with ID: 1 is Zara C:>
Try the above example by passing wrong database name or wrong username or password and check the result.
Facing SQLException for Invalid Table Name and using Error Codes in JDBC Example
In this example, we”ve three static string for database connection string, username, password. In main method, we”re preparing connection to the database using DriverManager.getConnection() method. We prepared a Statement using connection.createStatement() method. Using statement.executeQuery(), a query with invalid table name is executed which results in a SQLException. Exception is handled in catch block and error code, sql state and a detailed error message is printed.
import java.sql.*; public class JDBCExceptionExample { static final String MYSQL_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER_NAME = "guest"; static final String PASSWORD = "guest123"; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection(MYSQL_URL, USER_NAME, PASSWORD); Statement stmt = conn.createStatement(); // Giving incorrect table name to get Exceptions ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES10"); while( rs.next()){ System.out.println("ID:" + rs.getInt("id")); System.out.println(", Age: " + rs.getInt("age")); } }catch(SQLException e){ int errorCode = e.getErrorCode(); String sqlState = e.getSQLState(); String errorMsg = e.getMessage(); System.out.println("Error code: " + errorCode); System.out.println("SqlState: " + sqlState); System.out.println("Error Message: " + errorMsg); e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:>javac JDBCExceptionExample.java C:>
When you run JDBCExceptionExample, it produces the following result −
C:>java JDBCExceptionExample Error code: 1146 SqlState: 42S02 Error Message: Table ''tutorialspoint.employees10'' doesn''t exist java.sql.SQLSyntaxErrorException: Table ''tutorialspoint.employees10'' doesn''t exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1286) at JDBCExceptionExample.main(JDBCExceptionExample.java:26) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:484) at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:208) at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:135) C:>
Facing SQLException for Invalid Column Name and using Error Codes in JDBC Example
In this example, we”ve three static string for database connection string, username, password. In main method, we”re preparing connection to the database using DriverManager.getConnection() method. We prepared a Statement using connection.createStatement() method. Using statement.executeQuery(), a query with invalid column name is executed which results in a SQLException. Exception is handled in catch block and error code, sql state and a detailed error message is printed.
import java.sql.*; public class JDBCExceptionExample { static final String MYSQL_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER_NAME = "guest"; static final String PASSWORD = "guest123"; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection( MYSQL_URL, USER_NAME, PASSWORD); System.out.println("Connection to db established.."); String query = "select * from employees"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query); System.out.println("Successfully executed query."); while(rs.next()){ System.out.println("Intentionally giving emp_id column name, when the correct " + "one is: id. Giving this, to see exception handling."); System.out.println("ID: " + rs.getString("emp_id")); } }catch(SQLException e){ Throwable t = e.getCause(); System.out.println("Exception caught. Cause: " + t.toString()); System.out.println("------------------------------------"); e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:>javac JDBCExceptionExample.java C:>
When you run JDBCExceptionExample, it produces the following result −
C:>java JDBCExceptionExample Connection to db established.. Successfully executed query. Intentionally giving emp_id column name, when the correct one is: id. Giving this, to see exception handling. Exception in thread "main" java.lang.NullPointerException: Cannot invoke "java.lang.Throwable.toString()" because "" is null at JDBCExceptionExample.main(JDBCExceptionExample.java:55) C:>
”;