Handling SQL Exceptions

JDBC / By

Handling SQL Exceptions

The java.sql package provide the SQLException class, which is derived from the java.lang.Exception class.
The SQLException class provides the following error information:
1. Error Message: It is a string that describes the error.
2. Error code: It is an integer value that is associated with the error.
3. SQL state: It is an X/OPEN error code that identify the error.
The SQLException class contains various methods that provide error information. The methods in the SQLException class are:
a. int getErrorCode(); It return the error code that associated with the error occurred.
b. String getSQLState(): It returns X/OPEN error code.
c. SQLException getNextException(): It returns the next exception in the chain of the exceptions.
Example: Try
{
String str=”DELETE FROM tablename WHERE au_id=’2323244’”;
Statement stmt=con.createStatement();
int count=stmt.executeUpdate(str);
}
catch(SQLException sqlExceptionobject)
{
System.out.println(“Display error code”);
System.out.println(“SQL Exception”
+sqlExceptionobject.getErrorCode());
}
Here if the DELETE statement at runtime throws an SQLException then it is handling using the try catch block. The sqlexceptionObject is an object of the SQLException class and is used to invoke the getErrorCode() method.
JDBC Steps – Basic steps in writing a JDBC Application
Here you get a brief description of JDBC Steps for making connection with the database, executing the query and showing the data to the user. In this application we have connected to the MySQL database and retrieved the employee names from the database. Here are the JDBC Steps to be followed while writing JDBC program:
• Loading Driver
• Establishing Connection
• Executing Statements
• Getting Results
• Closing Database Connection
Before explaining you the JDBC Steps for making connection to the database and retrieving the employee from the tables, we will provide you the structure of the database and sample data.
Here is the sql script to create table and populate the table with data:
— Table structure for table `employee`
CREATE TABLE `employee` (
`employee_name` varchar(50) NOT NULL,
PRIMARY KEY (`employee_name`)
);
INSERT INTO `employee` (`employee_name`) VALUES
(‘Deepak Kumar’),
(‘Harish Joshi’),
(‘Rinku roy’),
(‘Vinod Kumar’);
Data inserting in MySQL database table:
mysql> insert into employee values(‘Deepak Kumar’);
Query OK, 1 row affected (0.24 sec)
mysql> insert into employee values(‘Harish Joshi’);
Query OK, 1 row affected (0.05 sec)
mysql> insert into employee values(‘Harish Joshi’);
ERROR 1062 (23000): Duplicate entry ‘Harish Joshi’ for key 1
mysql> insert into employee values(‘Rinku roy’);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee values(‘Vinod Kumar’);
Query OK, 1 row affected (0.04 sec)
mysql> select *from employee;
+—————+
| employee_name |
+—————+
| Deepak Kumar |
| Harish Joshi |
| Rinku roy |
| Vinod Kumar |
+—————+
4 rows in set (0.04 sec)
Here is the code of java program that retrieves all the employee data from database and displays on the console:
/*
Import JDBC core packages.
Following statement imports the java.sql package, which contains the JDBC core API.
*/
import java.sql.*;
public class RetriveAllEmployees{
public static void main(String[] args) {
System.out.println(“Getting All Rows from employee table!”);
Connection con = null;
String url = “jdbc:mysql://localhost:3306/”;
String db = “jdbc”;
String driver = “com.mysql.jdbc.Driver”;
String user = “root”;
String pass = “root”;
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
Statement st = con.createStatement();
ResultSet res = st.executeQuery(“SELECT * FROM employee”);
System.out.println(“Employee Name: ” );
while (res.next()) {
String employeeName = res.getString(“employee_name”);
System.out.println(employeeName );
}
con.close();
}
catch (ClassNotFoundException e){
System.err.println(“Could not load JDBC driver”);
System.out.println(“Exception: ” + e);
e.printStackTrace();
}
catch(SQLException ex){
System.err.println(“SQLException information”);
while(ex!=null) {
System.err.println (“Error msg: ” + ex.getMessage());
System.err.println (“SQLSTATE: ” + ex.getSQLState());
System.err.println (“Error code: ” + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException(); // For drivers that support chained exceptions
}
}
}
}
Explanation of JDBC Steps:
• Loading Driver
Loading Database driver is very first step towards making JDBC connectivity with the database. It is necessary to load the JDBC drivers before attempting to connect to the database. The JDBC drivers automatically register themselves with the JDBC system when loaded. Here is the code for loading the JDBC driver:
Class.forName(driver).newInstance();
• Establishing Connection
In the above step we have loaded the database driver to be used. Now its time to make the connection with the database server. In the Establishing Connection step we will logon to the database with user name and password. Following code we have used to make the connection with the database:
con = DriverManager.getConnection(url+db, user, pass);
• Executing Statements
In the previous step we established the connection with the database, now its time to execute query against database. You can run any type of query against database to perform database operations. In this example we will select all the rows from employee table. Here is the code that actually execute the statements against database:
ResultSet res = st.executeQuery( “SELECT * FROM employee” );
• Getting Results
In this step we receives the result of execute statement. In this case we will fetch the employees records from the recordset object and show on the console. Here is the code:
while (res.next()) {
String employeeName = res.getInt( ” employee_name ” );
System.out.println( employeeName );
}
• Closing Database Connection
Finally it is necessary to disconnect from the database and release resources being used. If you don’t close the connection then in the production environment your application will fail due to hanging database connections. Here is the code for disconnecting the application from database:
con.close();
In this section you learnt about the JDBC Steps necessary for performing database operations.
Output of program:
C:\vinod>javac RetriveAllEmployees.java
C:\vinod>java RetriveAllEmployees
Getting All Rows from employee table!
Employee Name:
Deepak Kumar
Harish Joshi
Rinku roy
Vinod Kumar