JDBC Exception Handling – How To Handle SQL Exceptions

This  JDBC Exception Handling tutorial explains ways to handle SQL Exceptions with the help of programming examples:

In the JDBC Transaction Management tutorial of the JDBC tutorial series, we learned JDBC transaction types, data types, transaction management methods, and how to use them in Java programs.

In this tutorial, we will learn about Exceptions in JDBC and how to handle them. In JDBC, if the exception occurred due to Database connectivity or anything related to DB, it will come under SQLException. Here, we will see more information about SQLExceptions.

Let’s get ready to learn about Exceptions in JDBC.

 JDBC Exception Handling

Exceptions occur when there is an error or warning in the execution of the program. When an exception occurs, the normal flow of the program will get disturbed and the program will be terminated abnormally.

The good thing about the exception is we can handle it by using a try-catch block or throws keyword. All exceptions and errors are the subclasses of the class Throwable. A throwable class is the base class of all exceptions and errors.

Java Exception Handling Keywords

There are five keywords in Java Exception Handling. They are as follows:

1. Try: Program statements that can raise the exception should be kept within a try block.
2. Catch: If any exception occurs in the try block, it will be thrown. We can catch that exception using the Catch block and handle it in the code.
3. Throw: System-generated exceptions are automatically thrown by JVM. To manually throw the exceptions, we should use a keyword throw.
4. Throws: Any exception that has been thrown out of a method should be specified by a throws clause.
5. Finally: Any program statements that must be executed after the try block should be kept in the final block.

>> Click here for more information about Exceptions in Java.

SQLException

In JDBC, we may get exceptions when we execute or create the query. Exceptions that occur due to the Database or Driver come under SQL Exception. Using Exception handling, we can handle the SQL Exception like we handle the normal exception.

SQLException is available in the java.sql package. It extends the Exception class which means that we can use the methods available in the Exception class in the SQLException class as well.

Example for SQL Exception

Syntax error in the SQL statement may result in SQL Exception. When such an exception occurs, an object of the SQLException class will be passed to the catch block. By using the information in the SQLException object, we can catch that exception and continue the program.

The SQLException object has the following methods:

How To Handle Exceptions

JDBC-related exception mostly throws SQLException, and it is a checked exception so we must either catch it or throw it. All the business logic and commit data should be done in a Try block, if any exception happens in the block we should catch and handle it in the Catch block. Based on the exception type, we should do the rollbacks or commit in the Catch block.

Categories Of SQLException

Sometimes JDBC driver may throw the subclass of SQLException that represents a common SQL state or a common error state that is specifically not associated with a particular SQL state class value. It will make you handle the exception in a more specific way, and we can handle it in our code. These types of exceptions come under the subclasses of one of the following exceptions:

• SQLNonTransientException: This type of exception will be thrown when an instance where a retry of the same operation fails unless the cause of the SQLException has been corrected.
• SQLTransientException: This type of exception will be thrown when a previously failed operation is able to succeed when we re-tried the operation again without any change/intervention.
• SQLRecoverableException: This type of exception will be thrown when a previously failed operation can succeed when we re-tried the operation again with any change/intervention by the application. While doing that the current connection should be closed and the new connection should be opened.

Other Subclasses of SQLException:

The following are the subclasses of SQLException:

• BatchUpdateException: This type of exception will be thrown if any error has occurred while doing the batch update operation. Besides the SQLException information, BatchUpdateException provides the status of the statements that have been executed/updated before the error has occurred.
• SQLClientInfoException: This type of exception will be thrown if one or more information properties cannot be set on a connection. Besides the SQLException information, SQLClientInfoException has a list of client information properties that were not been set.

In this tutorial, we will see normal SQLException, and then we will see BatchUpdateException. You can exercise the remaining subclasses of SQLException on your system.

In the following example, we will elaborate on how to handle the exception.

All programs are written in Java, in this tutorial. We have used the Java 8 version and Oracle DB.

>> Click here to download the Oracle software

>> Click here to download the Java version 8

It has a step-by-step Java installation process.

Exceptions Example Program

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Exception_Example {

public static void main(String[] args) throws ClassNotFoundException {
// TODO Auto-generated method stub

String update_query = "update employee_details set email='martinL@gmail.com' where empNum1 = 10011";
//Update query to set the email id for the employee whose empNUM is 10011
Class.forName("oracle.jdbc.driver.OracleDriver");
try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{
Statement statemnt1 = conn.createStatement();
ResultSet rs1 =null;
statemnt1 = conn.createStatement();
System.out.println("Executing Update query using executeUpdate method");
int return_rows = statemnt1.executeUpdate(update_query);
System.out.println("No. of Affected Rows = "+ return_rows);
}
catch(SQLException sqe)
{
System.out.println("Error Code = " + sqe.getErrorCode());
System.out.println("SQL state = " + sqe.getSQLState());
System.out.println("Message = " + sqe.getMessage());
System.out.println("printTrace /n");
sqe.printStackTrace();
}
}
}

Output:

Explanation:

#1) Created one select query which has the column name that is not in the EMPLOYEE_DETAILS table.

Create Query:

String update_query = "update employee_details set email='martinL@gmail.com' where empNum1 = 10011";

#2) Created Connection, statement, and executed the select QUERY in the try block.

#3) In the Catch block, we handled the exception.

#4) We are displaying the Error of the exception using the getError() method, SQLState of the exception using the getSQLState() method, and the message of the exception using the getMessage() method, and print the stack trace of the exception using printStackTrace method.

BatchUpdateException Example

We have created one new table to illustrate the BatchUpdateException example. The table’s name is EMPLOYEE. It has 3 columns.

They are:

1. ID which is a primary key
2. LASTNAME
3. FIRSTNAME

Syntax to create a table in ORACLE DB:

CREATE TABLE EMPLOYEE (
  ID int NOT NULL PRIMARY KEY,
  LastName varchar(255),
  FirstName varchar(255)
);

Java Program:

package com.STH.JDBC;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Statement_ExecuteBatch_Example {

public static void main(String[] args) throws ClassNotFoundException, SQLException
{

//Inserting the following 3 rows in EMPLOYEE_DETAILS Table
String insert_query1 = "insert into employee values(101,'Patterson','Tony')";
String insert_query2 = "insert into employee values(102,'Potter','Harry')";
String insert_query3 = "insert into employee values(102,'Wong','Jane')";
Class.forName("oracle.jdbc.driver.OracleDriver");
//Opening Oracle DB Connection
try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{
Statement statemnt1 = conn.createStatement();
//Adding the 3 insert queries into the Statement object using addBatch method
statemnt1.addBatch(insert_query1);
statemnt1.addBatch(insert_query2);
statemnt1.addBatch(insert_query3);
int No_of_Afffected_Rows[]=  statemnt1.executeBatch();
//After inserting the data
System.out.println("No of rows affected = " +No_of_Afffected_Rows.length);
}
//Catching the BatchUpdateException
catch(BatchUpdateException be)
{
//getting the updated rows status before the exception has occurred
int[] updateCount = be.getUpdateCounts();
int count = 1;
for (int i : updateCount) {
//Using for loop, printing the statement which has been successfully executed
if  (i == Statement.EXECUTE_FAILED) {
System.out.println("Error on Statement " + count +": Execution failed");
}
else {
System.out.println("Statement  " + count +": is executed");
}
count++; //Incrementing the count to display the next updated row no.
}
//System.out.println("Error on statemet "+be.getUpdateCounts());
be.printStackTrace();

}
catch (SQLException e) {
e.printStackTrace();
}
}
}

OUTPUT:

Explanation:

What we have done in the above program is create 3 INSERT queries add them in a batch and execute it. The 3rd query has the same ID value as the 1^st query since the ID column is a primary key of the EMPLOYEE table, the program has thrown a BatchUpdateException.

1. Created the 3 insert queries to insert it into the EMPLOYEE table. The first and third query has the same ID value. The id column is a primary key to the EMPLOYEE table.
2. Created statement object and added these 3 queries into it using the addBatch() method. Then called executeBatch() method is to execute it.
3. Since the first and third query has the same ID value. When the executeBatch() tries to execute the third query it will throw BatchUpdateException.
4. In the BatchUpdateException catch block, we called the getUpdateCounts() method to get the status of the updated row.
5. Using for loop, we are checking one by one whether the status of the particular statement is executed failed or not. If the particular statement has not failed, then it will print the row number of the statement.
6. After that, it will print the printStackTrace of the BatchUpdateException.
7. In the above example, since the exception occurred because of the third statement, statements 1 and 2 have been printed. Then the full trace of the exception has been printed in the output console.

Points to remember:

• The exception that occurred due to the Database will come under SQLException.
• Exceptions in Java can be handled using the try: catch block.
• SQLException is the checked exception so we can handle it using the try: catch block.
• We have some subclasses of SQLException. They are SQLNonTransientException, SQLTransientException, SQLRecoverableException, BatchUpdateException and SQLClientInfoException.

Frequently Asked Questions

Conclusion

Exceptions can be handled using a try-catch block or by throwing it. Exceptions that occurred due to the database are known as SQLException. We have a separate class for SQLException which is a subclass of Exception. We have the methods to get to know about the SQL exception more specifically.

The methods are getMessage(), getErrorCode(), getSQLState(), getNextException and printStackTace. getNextException will be used in the Exception Chained case.

<<PREV Tutorial | NEXT Tutorial>>