Java Database Connectivity (JDBC) serves as the backbone for Java applications when interacting with databases. While establishing connections and executing queries, we developers often encounter SQLExceptions, which are inevitable in the real world. Handling those exceptions is crucial in the development of applications. Now let’s understand more about SQL Exception.
Understanding SQL Exception
Exception is a type of condition when a program encounters a problem in execution and quits with a problematic error message. In JDBC, when the program has trouble with a data source, it throws SQLException.
Note: An SQLException can occur in the JDBC Driver or inside the database.
SQL Exception methods in Java:
METHOD NAME |
DESCRIPTION |
---|---|
getErrorCode() |
Gets the error number associated with the exception. |
getMessage() |
This method gets the JDBC driver’s error message for |
getSQLState() |
Gets the XOPEN SQLstate string. This method can return null. |
getNextException() |
In the exception chain, gets the next Exception object |
printStackTrace() |
Prints the current exception, or throwable, and it’s backtrace to a standard error stream. |
printStackTrace(PrintStream s) |
To the print the stream we have specified, prints this throwable and its backtrace. |
printStackTrace(PrintStream s) |
Prints this throwable and it’s backtrace to the print writer you specify. |
The below-described code describes how to handle SQL Exception.
- Initial part of the code is to connect to the sql server i.e. username and password are declared.
- The resultantSet is initialized and declared.
- Once the server gets connected then the query gets executed.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class SQLExceptionExample {
public static void main(String[] args) {
Connection connection = null ;
PreparedStatement preparedStatement = null ;
try {
// Load the JDBC driver
Class.forName( "com.mysql.cj.jdbc.Driver" );
// establish a connection to the database
connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/your_database" , "username" , "password" );
// Your SQL query with an intentional error (table name is misspelled)
String sql = "SELECT * FROM non_existent_table" ;
// create a PreparedStatement
preparedStatement = connection.prepareStatement(sql);
// execute the query (this will throw an SQLException in case of an error)
preparedStatement.executeQuery();
} catch (SQLException | ClassNotFoundException e) {
// handle the SQL exception
handleSQLException(e);
} finally {
// close the resources in the finally block
try {
if (preparedStatement != null ) {
preparedStatement.close();
}
if (connection != null ) {
connection.close();
}
} catch (SQLException e) {
handleSQLException(e);
}
}
}
private static void handleSQLException(SQLException e) {
// handle the SQL exception
int errorCode = e.getErrorCode();
String sqlState = e.getSQLState();
String errorMessage = e.getMessage();
System.out.println( "SQL Error Code: " + errorCode);
System.out.println( "SQL State: " + sqlState);
System.out.println( "Error Message: " + errorMessage);
e.printStackTrace();
}
} |
Note: To load the JDBC Driver, first connect to the database (SQL) and fetch that link to establish the connection.
Output:
The Output of the above code will intentionally create SQL Exception error since there is no non-existence table in the database. This code lets you to understand the SQL Exception Methods.
Below is the entire source code which follows the connection steps.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLException_Example {
// Define JDBC Driver
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ;
// Database URL, username, and password
static final String dbuser = "root" ;
static final String dbpass = "root" ;
public static void main(String[] args) throws SQLException {
Connection con = null ;
Statement stmt = null ;
try {
// Connecting to the server and database
con = DriverManager.getConnection(dburl, dbuser, dbpass);
// Initialize Statement
stmt = con.createStatement();
// SQL Query
String query = "SELECT * FROM ITEM" ;
// Execute Query And Get ResultSet
ResultSet rset = stmt.executeQuery(query);
while (rset.next()) {
System.out.print( "ID: " + rset.getInt( 1 ));
System.out.print( " Product: " + rset.getString( 2 ));
System.out.println( " Price: " + rset.getString( 3 ));
}
} catch (SQLException e) {
// Catch SQLException, print error and stack trace
System.err.println( "Cannot connect! " );
e.printStackTrace();
} finally {
// Finally block to ensure closing connection
System.out.println( "Closing the connection." );
if (con != null ) try { con.close(); } catch (SQLException ignore) {}
}
}
} |
- The above program establishes a connection to the MySQL database (STOREDB) using the provided URL, username, and password.
- To load the JDBC Driver, first connect to the database (SQL) and fetch that link to establish the connection.
- A Statement object is created to execute SQL queries.
- The SELECT query is executed, and the results are stored in a ResultSet.
- The program iterates through the result set and prints the values of the ID, Product, and Price columns.
- If a SQLException occurs during the execution of the program, it is caught, and its details are printed.
- The details that are printed is included in the output of methods of SQL Exception.
- The finally block ensures that the connection is closed, whether an exception occurs or not.
Output:
The output will be displayed based on the Java code written. This article only deals with handling SQL Exceptions in JDBC.