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.

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 class Throwable. 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 which 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 finally 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:

Method NameDescription
getErrorCode()It returns the error number
getMessage()It returns the error message
getSQLState()It returns the SQLState of the SQLException object. It can return null as well. For Database error, it will return XOPEN SQL State
getNextException()It returns the next exception in the exception chain.
printStackTrace()It prints the current exception and its backtrace to a standard error stream
setNextException(SQLEXception ex)It is used to add another SQL exception in the chain

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 happened 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 would fail 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 is 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 which have been executed/updated before the error has occurred.
  • SQLClientInfoException: This type of exception will be thrown if one or more information properties could not be set on a connection. Besides the SQLException information, SQLClientInfoException 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 Java 8 version and Oracle DB.

>> Click here to download the Oracle software

>> Click here to download the Java version 8

It has the 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:

Exceptions Example Program

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, 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)
);

batchupdateException_CREATE_QUERY

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:

BatchUpdateException java progm

Explanation:

What we have done in the above program is we have created 3 INSERT queries and added it in a batch and executed it. The 3rd query has the same id value of 1st 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 to execute it.
  3. Since the first and third query has the same ID value. When the executeBatch() tried 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 is 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, so the statement 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 try: catch block.
  • We have some subclasses of SQLException. They are SQLNonTransientException, SQLTransientException, SQLRecoverableException, BatchUpdateException and SQLClientInfoException.

Frequently Asked Questions

Q #1) What is an SQL Exception?

Answer: An exception occurred because of Database is known as SQL Exception. An exception that provides information about database related is also known as SQL Exception. We have an SQLException class in Java, which is used to provide information about the exception. It has the following methods:

  • getErrorCode()
  • getMessage()
  • getSQLState()
  • printStackTrace()
  • getNextException()

Q #2) How to handle the exception in SQL?

Answer: Write the business logic in the try block. If any error or exception has occurred in it, catch it in the Catch block and write the appropriate message to find the exception easily.

Try–Catch block is used to handle the exception.

Q #3) When SQLException can occur in Java?

Answer: SQLException occurs if there is an error in the database access or other errors related to the database. When SQLException occurs, an object of type SQLException will be passed to the catch clause. We can handle it in the Catch block.

Q #4) What is the Exception chain in Java and what is the use of it?

Answer: One exception that causes another exception is known as Exception Chain or Chained Exception. In most of the cases we need to chain the exception such that relate one exception with another exception, it will make the logs clear and easy tracking. It will be helpful for the programmer in the debugging process.

For example:

Consider a method that throws an Arithmetic exception due to divide by zero. The actual cause of the exception is an I/O error, which makes the divisor to be zero. The method will only throw an Arithmetic exception to the programmer. So that the caller/ programmer will not come to know about the actual cause of the Exception. In this type of situation, we can use a chained exception.

Conclusion

Exceptions can be handled using a try-catch block or by throwing it. Exceptions occurred due to the database is 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 in more specific.

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

<<PREV Tutorial | NEXT Tutorial>>