18 – JDBC Exception Handling

18.1 Overview of JDBC exception handling

With the help of exception handling we can take the appropriate action in case something goes wrong like committing the data or rolling it back etc. JDBC execute statement or connection related exception throws SQLException which is a checked Exception

18.2 SQLException

SQLException class is available in java.sql package and extends Exception class which means all methods that we used to use in Exception will be available in SQLException also. There are other relevant methods added in SQLException class which we will discuss in this section.

18.2.1 Exception Handling Approach

All the  JDBC execute statement or connection related exception throws SQLException and its a checked exception so we must either catch it or throw it.

All the statements should be there in a try block include committing the data. We should catch the exception and based on the exception type we should do the rollbacks or rollback for a given savepoint and commit in the catch block.

We should have a finally block which will be used to close the connection, resultset , statements etc so the occupied resources will be released. Refer below 

    try{
         // all statements including commit
    }catch(SQLException e)
    {
        // rollback  or commit if rollback for a given savepoint
    }
   Finally{
        // close connection or statements or resultsets
   }

18.2.2 SQLException Useful methods

There are several methods available but below are the most commonly used methods.

  1. int getErrorCode()-  this method can be used to get the vendor-specific exception or error code.
  2. SQLException getNextException()- This method is used to get the chained exception which is being set using  setNextException(SQLException ex) method.
  3. Iterator<Throwable> iterator()-  This method is used to iterate all the chained exceptions in SQL Exception.
  4. void setNextException(SQLException ex)- This method is used to add another SQL exception in chain.
  5. String getSQLState() – This method is used to retrieve the SQLState for SQLException object. This method can return null as well.
 

18.3 Example of JDBC exception handling

We will use the MySQL database .

  1.  Use the below sql statement to create a schema

Create schema JDBCTutorial;

  1. Create table Account with 2 columns  (account number and balance ) using below SQL.

       CREATE  TABLE `Account` ( `accountNo` INT NOT NULL ,   `balance` INT NULL ,   PRIMARY KEY (`accountNo`) ); 

18.3.1 – Write an Example to demonstrate Exception handling.

Solution –

Below program has a wrong SQL statement and will print the SQL exception details. All method call in try block , rollback in catch and closing statement and connection in finally block. 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class ExceptionHandlingExample {

   public static void main(String args[]) throws Exception
    {
        Scanner scanner = new Scanner(System.in);

        Connection conn=getConnection();
        PreparedStatement stmt=null;
        try{
            conn.setAutoCommit(false);

            String sql;
            System.out.println("Enter Sender Account No");
            int accountNo1= scanner.nextInt();

            System.out.println("Enter Receiver Account No");
            int accountNo2= scanner.nextInt();
            System.out.println("Enter Balance to transfer");
            int amount= scanner.nextInt();

            sql = "update account set balance=balance-? where accountNo=?";

            stmt= conn.prepareStatement(sql);

            stmt.setInt(1, amount);
            stmt.setInt(2, accountNo1);
            stmt.executeUpdate();

            sql = "update accountx set balance=balance+? where accountNo=?";
            stmt= conn.prepareStatement(sql);
            stmt.setInt(1, amount);
            stmt.setInt(2, accountNo2);
            stmt.executeUpdate();

            conn.commit();

        }
        catch(SQLException e)
        {
            System.out.println("Error Code=" + e.getErrorCode());
            System.out.println("SQL State=" + e.getSQLState());
            System.out.println("Message =" + e.getMessage());
 
            conn.rollback();
            throw e;
        }
        finally{
            stmt.close();
            conn.close();
        }
    }

    private static Connection getConnection() {

        Connection con = null;
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUsername = "root";
        String dbPassword="password";
        String dbHostname="localhost";
        String dbPort="3306";
        String schema="JdbcTutorial";
        try {
               String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
               Class.forName(dbDriver);
                con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }

}

On Running above program, following will be the output

Enter Sender Account No

1234

Enter Receiver Account No

5678

Enter Balance to transfer

123

Error Code=1146

SQL State=42S02

Message =Table 'jdbctutorial.accountx' doesn't exist

 

Like us on Facebook