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 two places, A and B. People in A don’t know the language of people in B and vice versa. To communicate, they need a translator.

• Place A -> Java Application
• Place B -> Database
• Translator -> JDBC Driver (converts Java calls to DB calls and back)
• Road -> Connection (pathway between Java and DB)
• Vehicle -> Statement (carries SQL queries)
• Requirement -> SQL Query
• Small Block (Response) -> ResultSet (data returned from DB)

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.

To read more about JDBC driver-> JDBC drivers

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 operations 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.

To read more about JDBC Operation-> operations using JDBC

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

To read more about JDBC Exception Handling-> 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