JDBC (Java Database Connectivity) Explained

Java Database Connectivity (JDBC) is an API that allows Java programs to communicate with databases. It provides a standard method to connect to a database, send SQL queries, and retrieve results, making it possible for Java developers to perform tasks like storing, updating, or deleting data from a database.

---

---

Why is JDBC Important?

Databases play a vital role in storing and managing data for applications, and JDBC is the bridge that allows Java applications to work with relational databases (like MySQL, Oracle, PostgreSQL). Using JDBC, a Java application can manage the database’s data—adding new records, retrieving data, or updating existing records in a structured way.

---

Relational Databases and SQL

Relational databases organize data in tables, with rows and columns. Each row is a record, and each column holds data of a specific type (like integer, text, or date). For example, a table called “Employees” could have the following columns:

• EmployeeID (int)
• Name (text)
• Department (text)
• Salary (decimal)

SQL (Structured Query Language) is the language used to perform operations on the database. Common operations include:

• CREATE: Create new tables or records.
• READ: Query the database to retrieve records.
• UPDATE: Modify existing records.
• DELETE: Remove records from the database.

---

JDBC Architecture

JDBC is built on a layered architecture, consisting of:

1. Driver Manager: Responsible for loading and managing different types of database drivers.
2. Driver: Each database (like MySQL or Oracle) has its own JDBC driver, which is a set of classes that handle communication with that specific database.
3. Connection: A connection object that represents the connection between the Java application and the database.
4. Statement: Used to execute SQL queries and send them to the database.
5. ResultSet: Stores the data retrieved from the database in response to a query.

This architecture ensures that Java can interact with any database using the same set of methods and functions.

---

JDBC Driver Types

There are four types of JDBC drivers, each offering different advantages based on their use case:

1. Type 1 – JDBC-ODBC Bridge Driver:

• Translates JDBC calls into ODBC (Open Database Connectivity) calls. This is outdated and rarely used today.

1. Type 2 – Native-API Driver:

• Converts JDBC calls into database-specific calls using native code libraries. It offers better performance than Type 1 but requires native library installation.

1. Type 3 – Network Protocol Driver:

• Translates JDBC calls into a database-independent protocol. The calls go to a middleware server, which then communicates with the database.

1. Type 4 – Thin Driver:

• A pure Java driver that communicates directly with the database, eliminating the need for middleware or native code. It’s the most commonly used type.

---

Steps to Work with JDBC

Here’s a breakdown of how to perform basic tasks using JDBC:

1. Install the Database and JDBC Driver

First, you need a database (like MySQL) and the corresponding JDBC driver. The driver translates Java calls into database-specific actions.

---

2. Load the Driver and Establish a Connection

• Load the JDBC driver using Class.forName():
  java Class.forName("com.mysql.jdbc.Driver");
• Establish a connection to the database using DriverManager.getConnection():
  java Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "user", "password");

---

3. Create a Statement

After establishing a connection, you create a Statement object to send SQL queries to the database:

Statement stmt = conn.createStatement();

---

4. Execute SQL Queries (CRUD Operations)

Inserting Data

Use INSERT INTO to add a new record:

String sql = "INSERT INTO Employees VALUES (1001, 'John Doe', 'Sales', 50000)";
stmt.executeUpdate(sql);

---

Reading Data

Use SELECT to retrieve data:

String sql = "SELECT * FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("EmployeeID");
    String name = rs.getString("Name");
    String dept = rs.getString("Department");
    float salary = rs.getFloat("Salary");
    System.out.println(id + " " + name + " " + dept + " " + salary);
}

Updating Data

Use UPDATE to modify an existing record:

String sql = "UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1001";
stmt.executeUpdate(sql);

Deleting Data

Use DELETE to remove a record:

String sql = "DELETE FROM Employees WHERE EmployeeID = 1001";
stmt.executeUpdate(sql);

---

5. Closing the Connection

Finally, after completing all database operations, close the Connection, Statement, and ResultSet objects to release resources:

rs.close();
stmt.close();
conn.close();

---

Example Program

Below is an example Java program that demonstrates how to use JDBC for basic CRUD operations:

import java.sql.*;

public class JdbcExample {
    static final String jdbcDriver = "com.mysql.jdbc.Driver";
    static final String dbURL = "jdbc:mysql://localhost/mydb";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // Load the MySQL driver
            Class.forName(jdbcDriver);

            // Establish a connection to the database
            conn = DriverManager.getConnection(dbURL, "root", "password");

            // Create a statement object
            stmt = conn.createStatement();

            // Insert a record into the Employees table
            String sql = "INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES (1002, 'Jane Doe', 'Marketing', 75000)";
            stmt.executeUpdate(sql);

            // Retrieve records from the Employees table
            sql = "SELECT * FROM Employees";
            rs = stmt.executeQuery(sql);

            // Process and print the result set
            while (rs.next()) {
                int id = rs.getInt("EmployeeID");
                String name = rs.getString("Name");
                String dept = rs.getString("Department");
                float salary = rs.getFloat("Salary");
                System.out.println(id + " " + name + " " + dept + " " + salary);
            }

        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}