JDBC Tutorial

JDBC (Java Database Connectivity) is a standard Java API that allows Java applications to connect to relational databases. It provides a set of interfaces and classes to send SQL queries, retrieve results and manage database connections. With JDBC, developers can build database-driven applications that are portable across various databases, including MySQL, PostgreSQL, Oracle and others.

Features of JDBC

• Platform Independent: Write once, run anywhere (Java-based).
• Database Independent: Works with any relational database using drivers.
• Standard API: Provides a standard set of interfaces (Connection, Statement, ResultSet, etc.).
• Secure: Uses Java security model and exception handling.
• Easy to Use: Simplifies database connectivity with simple method calls.
• Supports SQL: Can execute SQL queries directly from Java code.

1. JDBC Architecture

JDBC Architecture defines how Java applications interact with databases through drivers using a standard API.

JDBC architecture consists of two main layers

1. JDBC API (Application Layer)

• Provides Java applications with the interface to interact with databases.
• Contains classes and interfaces in the java.sql and javax.sql packages.
• Example interfaces: Connection, Statement, PreparedStatement, ResultSet.

2. JDBC Driver API (Driver Layer)

• Acts as a bridge between JDBC API calls and the actual database.
• Converts Java method calls into database-specific calls.

2. Real-world Example of JDBC

Suppose there are two places, A (Java Application) and B (Database), where people do not understand each other’s language. To communicate, they use a vehicle with a translator that carries messages between them.

• Java Application (Place A) prepares a request (SQL query)
• The request is placed inside a box (Statement object)
• A vehicle (JDBC Driver) carries this box to the database
• The Connection acts as the road linking both places
• The Database (Place B) processes the request and prepares the result
• The result is placed in another box (ResultSet)
• The vehicle brings the result back to the Java application

This entire communication system is what we call Java Database Connectivity (JDBC)

3. Types of JDBC Drivers

JDBC drivers act as a mediator between Java applications and databases.

• Type-1 driver or JDBC-ODBC bridge driver (Bridge Driver): Uses ODBC driver, now deprecated.
• Type-2 driver or Native-API driver (Native API): Converts JDBC calls into native DB API calls.
• Type-3 driver or Network Protocol driver (Network Protocol): Uses middleware server for database communication.
• Type-4 driver or Thin driver (Native protocol): Directly communicates with the database, most commonly used.

4. JDBC Classes and Interfaces

Some popular interfaces of JDBC API are listed below:

• Driver interface: Handles communication between Java application and the database.
• Connection interface: Represents a session/connection with a specific database.
• Statement interface: Used to execute simple SQL queries without parameters.
• PreparedStatement interface: Used to execute precompiled parameterized SQL queries.
• CallableStatement interface: Used to call stored procedures in the database.
• ResultSet interface: Represents the data retrieved from a SELECT query.
• ResultSetMetaData interface: Provides information about columns of a ResultSet.
• DatabaseMetaData interface: Provides information about the database (tables, drivers, etc.).
• RowSet interface: A scrollable and updatable version of ResultSet.

Some important Classes of JDBC API are listed below:

• DriverManager class: Manages and loads database drivers to establish connections.
• Blob class: Represents binary large objects (e.g., images, audio, video).
• Clob class: Represents character large objects (e.g., text files, large strings).
• Types class: Defines constants for SQL data types.

5. Establishing JDBC Connection

To perform database connection using JDBC, follow these standard steps:

1. Import JDBC package: Include java.sql classes for database operations.
2. Load and register driver: Load the JDBC driver class to enable communication using forname() method
3. Establish connection: Use DriverManager to connect to the database.
4. Create statement: Create Statement or PreparedStatement to send SQL queries.
5. Execute query: Run SQL commands like SELECT, INSERT, UPDATE or DELETE using
6. Process results: Retrieve and handle data from ResultSet.
7. Close resources: Release database connections and objects.

6. JDBC Example

Here’s a simple example using MySQL database.

SQL Setup

Suppose we have a table students:

CREATE DATABASE school;
USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO students VALUES (1, 'Amit', 21), (2, 'Riya', 22);

Java Code

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        try {
            // 1. Load Driver (Optional for newer JDBC versions)
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Establish Connection
            Connection con = DriverManager.getConnection(url, user, password);

            // 3. Create Statement
            Statement stmt = con.createStatement();

            // 4. Execute Query
            ResultSet rs = stmt.executeQuery("SELECT * FROM students");

            // 5. Process Results
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " " + rs.getString("name") + " " + rs.getInt("age"));
            }

            // 6. Close resources
            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

7. Crud Operation with JDBC

CRUD stands for Create, Read, Update, Delete, which are the four basic operations performed on a database using JDBC.

• Create: Used to insert new records into the database (INSERT query).
• Read: Used to fetch records from the database (SELECT query).
• Update: Used to modify existing records in the database (UPDATE query).
• Delete: Used to remove records from the database (DELETE query).

8. JDBC Exception Handling

When we communicate  with databases,some problems occur like:

• Invalid SQL syntax bad query
• Connection time out problem 
• Wrong data types mentioned
• miss  database drivers

To handle these issues gracefully, JDBC provides exception handling mechanisms

Example:

catch (SQLException e) {
    System.out.println("Error: " + e.getMessage());
    System.out.println("SQL State: " + e.getSQLState());
    System.out.println("Error Code: " + e.getErrorCode());
}

9. Transactions in JDBC

A transaction is a sequence of SQL operations that are executed as a single unit of work. Transactions help maintain data consistency and integrity in applications. By default, JDBC runs in auto-commit mode (each SQL statement is committed immediately). To manage transactions manually:

• setAutoCommit(false): disables auto-commit.
• commit(): permanently saves changes.
• rollback(): undoes changes since last commit.

Example:

con.setAutoCommit(false);

PreparedStatement ps1 = con.prepareStatement("UPDATE accounts SET balance=balance-100 WHERE id=1");
PreparedStatement ps2 = con.prepareStatement("UPDATE accounts SET balance=balance+100 WHERE id=2");

ps1.executeUpdate();
ps2.executeUpdate();

con.commit();  // commit if both succeed
con.rollback(); // rollback if error