JDBC Introduction

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with relational databases. It provides a standard interface for connecting to databases, executing SQL queries, and handling database results. JDBC is essential for any Java application that needs to interact with a database, whether it’s for retrieving, updating, or deleting data.

Key Features of JDBC

Database Connectivity: JDBC allows Java applications to establish a connection to different types of databases.
Executing SQL Queries: JDBC provides methods to execute various types of SQL queries, including SELECT, INSERT, UPDATE, and DELETE.
Transaction Management: JDBC supports transaction management, allowing for commit and rollback operations to ensure data consistency.
Exception Handling: JDBC provides robust exception handling to deal with SQL errors and database connection issues.
Support for Multiple Databases: JDBC can connect to various relational databases like MySQL, Oracle, PostgreSQL, etc.

Connecting to MySQL Database Using JDBC

To connect to a MySQL database using JDBC, follow these steps:

Ensure you have the MySQL JDBC driver (e.g., mysql-connector-java.jar) added to your classpath.
Load the MySQL JDBC driver class using Class.forName("com.mysql.cj.jdbc.Driver").
Establish a connection to the database using DriverManager.getConnection() with the database URL, username, and password.
Create a statement object to execute SQL queries.
Process the result set returned from the query.
Close the connection after the operations are completed.

Example Code for MySQL Connection:


import java.sql.*;

public class MySQLJDBC {
    public static void main(String[] args) {
        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // Establish the connection
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "username", "password");
            // Create a statement object
            Statement statement = connection.createStatement();
            // Execute a query
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
            // Process the result set
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
            // Close the connection
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Connecting to Oracle Database Using JDBC

To connect to an Oracle database using JDBC, follow these steps:

Ensure you have the Oracle JDBC driver (e.g., ojdbc8.jar) added to your classpath.
Load the Oracle JDBC driver class using Class.forName("oracle.jdbc.driver.OracleDriver").
Establish a connection to the database using DriverManager.getConnection() with the database URL, username, and password.
Create a statement object to execute SQL queries.
Process the result set returned from the query.
Close the connection after the operations are completed.

Example Code for Oracle Connection:


import java.sql.*;

public class OracleJDBC {
    public static void main(String[] args) {
        try {
            // Load the JDBC driver
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // Establish the connection
            Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");
            // Create a statement object
            Statement statement = connection.createStatement();
            // Execute a query
            ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");
            // Process the result set
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
            // Close the connection
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

JDBC API Classes and Interfaces

The JDBC API provides several classes and interfaces for interacting with a database. Below are some key classes and interfaces in JDBC:

Class/Interface	Description
DriverManager	Manages a list of database drivers. It is used to establish a connection to the database.
Connection	Represents an open connection to a database. It provides methods for creating statements and managing transactions.
Statement	Used to execute SQL queries against the database. It is used to create SQL queries and update operations.
PreparedStatement	A subclass of Statement that allows you to execute precompiled SQL statements. It is more efficient and secure than Statement.
ResultSet	Represents the result set of a query. It contains data retrieved from the database based on an SQL query.
SQLException	Represents any SQL-related exception that occurs while interacting with the database. It provides detailed error messages.

Summary Table of JDBC Classes and Interfaces

Class/Interface	Description	Usage
DriverManager	Manages JDBC drivers.	Used to establish database connections.
Connection	Represents an open connection to the database.	Used to create statements and manage transactions.
Statement	Executes SQL queries.	Used to send SQL commands to the database.
PreparedStatement	Executes precompiled SQL statements.	Used for executing dynamic SQL queries securely.
ResultSet	Represents the result of a query.	Used to retrieve data from the database after executing a query.
SQLException	Exception thrown for SQL errors.	Used for exception handling in JDBC operations.