🗄️ JDBC – Java Database Connectivity

Connect Java applications to relational databases — master connections, PreparedStatements, transactions, and the DAO pattern.

1. What is JDBC?

JDBC (Java Database Connectivity) is an API that allows Java applications to interact with relational databases. It provides a standard set of classes and interfaces to connect to a database, execute SQL queries, and process the results.

JDBC Architecture

The JDBC API decouples the application from the underlying database using a driver manager and database-specific drivers.

[ Java Application ]
         |
[   JDBC API       ]  <-- Interfaces (Connection, Statement, ResultSet)
         |
[ JDBC Driver Mgr  ]  <-- Loads the correct driver
         |
[ JDBC Driver      ]  <-- Database specific (MySQL, PostgreSQL, Oracle)
         |
[   Database       ]

Types of JDBC Drivers

Driver Type	Name	Description
Type 1	JDBC-ODBC Bridge	Translates JDBC calls to ODBC calls. Obsolete and removed in Java 8.
Type 2	Native-API Driver	Converts JDBC calls to native C/C++ API calls of the database. Requires native libraries on the client.
Type 3	Network Protocol Driver	Translates JDBC calls into a middleware vendor's protocol, which is then translated to a database protocol by a middle-tier server.
Type 4	Thin Driver (Pure Java)	Converts JDBC calls directly into the vendor-specific database protocol. Written entirely in Java. Most commonly used today.

The core JDBC API is provided in the java.sql and javax.sql packages.

2. JDBC Core Interfaces

Before writing any JDBC code, you need to understand the primary interfaces involved in database operations:

DriverManager: A class that manages a list of database drivers. It matches connection requests from the java application with the proper database driver using communication subprotocol.
Connection: Represents a physical connection to the database. It is used to create statements and manage transactions.
Statement: Used to execute static SQL statements and return the results it produces.
PreparedStatement: A subinterface of Statement. Used for precompiling SQL statements. It takes parameters, preventing SQL injection and improving performance for repeated execution.
CallableStatement: Used to execute database stored procedures.
ResultSet: Represents the result set of a database query. It acts as an iterator to traverse through the data returned from the database.
ResultSetMetaData: Contains information about the types and properties of the columns in a ResultSet.
DatabaseMetaData: Contains comprehensive information about the database as a whole (tables, views, capabilities).

3. Setting Up

To use JDBC, you must include the specific database driver in your project's classpath. If you are using Maven, add the dependency to your pom.xml.

Adding Dependencies

pom.xml (Maven)XML

<!-- For MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

<!-- For PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>

Connection URL Formats

The connection URL specifies how to locate the database.

MySQL: jdbc:mysql://localhost:3306/db_name
PostgreSQL: jdbc:postgresql://localhost:5432/db_name
Oracle: jdbc:oracle:thin:@localhost:1521:db_name
H2 (In-Memory): jdbc:h2:mem:db_name

Note: In older versions of JDBC (before JDBC 4.0 / Java 6), you had to explicitly load the driver class using Class.forName("com.mysql.cj.jdbc.Driver");. With modern JDBC, DriverManager automatically discovers drivers in the classpath via the SPI (Service Provider Interface).

4. Establishing a Connection

The standard way to connect is using DriverManager.getConnection(). Let's look at a basic example connecting to MySQL.

JDBCConnection.javaJava

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

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

        // Using try-with-resources to automatically close the connection
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to the database successfully!");
            System.out.println("Catalog: " + conn.getCatalog());
        } catch (SQLException e) {
            System.err.println("Database connection failed!");
            e.printStackTrace();
        }
    }
}

5. Statement – Executing SQL

The Statement interface executes plain SQL statements. It provides three main execution methods:

executeQuery(String sql): Used for SELECT statements. Returns a ResultSet.
executeUpdate(String sql): Used for INSERT, UPDATE, DELETE, or DDL (CREATE TABLE). Returns an int representing the number of rows affected.
execute(String sql): Used when the query might return either a ResultSet or an update count. Returns boolean (true if it returned a ResultSet).

StatementDemo.javaJava

import java.sql.*;

public class StatementDemo {
    public static void main(String[] args) {
        String url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1"; // H2 in-memory DB
        
        try (Connection conn = DriverManager.getConnection(url, "sa", "");
             Statement stmt = conn.createStatement()) {
            
            // 1. Execute DDL (Create Table)
            String createSql = "CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50))";
            stmt.executeUpdate(createSql);
            System.out.println("Table created.");

            // 2. Execute DML (Insert Data)
            stmt.executeUpdate("INSERT INTO employees VALUES (1, 'Alice')");
            stmt.executeUpdate("INSERT INTO employees VALUES (2, 'Bob')");
            
            // 3. Execute DQL (Select Data)
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
            
            // 4. Iterate over the ResultSet
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. PreparedStatement (The Standard Choice)

PreparedStatement extends Statement and provides a way to parameterize SQL queries using ? placeholders.

Why use PreparedStatement?

Prevents SQL Injection: It automatically escapes special characters in parameters.
Better Performance: The database parses and compiles the query once, caching the execution plan for subsequent runs with different parameters.
Cleaner Code: Easier to set varying data types (dates, blobs) without ugly string concatenation.

Program 1: CRUD Operations

PreparedStatementCRUD.javaJava

import java.sql.*;

public class PreparedStatementCRUD {
    public static void main(String[] args) {
        String url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
        
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            // Setup table
            conn.createStatement().executeUpdate("CREATE TABLE Student (id INT, name VARCHAR(50), gpa DOUBLE)");
            
            // INSERT with PreparedStatement
            String insertSql = "INSERT INTO Student (id, name, gpa) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
                pstmt.setInt(1, 101);
                pstmt.setString(2, "John Doe");
                pstmt.setDouble(3, 3.8);
                int rows = pstmt.executeUpdate();
                System.out.println(rows + " row(s) inserted.");
            }
            
            // UPDATE
            String updateSql = "UPDATE Student SET gpa = ? WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
                pstmt.setDouble(1, 3.9);
                pstmt.setInt(2, 101);
                pstmt.executeUpdate();
            }
            
            // SELECT
            String selectSql = "SELECT * FROM Student WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(selectSql)) {
                pstmt.setInt(1, 101);
                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        System.out.println("Student: " + rs.getString("name") + " - GPA: " + rs.getDouble("gpa"));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Program 2: Batch Execution

Executing multiple queries individually is slow. Batching allows grouping multiple SQL statements and sending them to the DB in one network trip.

BatchInsertDemo.javaJava

import java.sql.*;

public class BatchInsertDemo {
    public static void main(String[] args) {
        String url = "jdbc:h2:mem:testdb";
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            conn.createStatement().executeUpdate("CREATE TABLE Logs (id INT, message VARCHAR(100))");
            
            String sql = "INSERT INTO Logs (id, message) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for (int i = 1; i <= 1000; i++) {
                    pstmt.setInt(1, i);
                    pstmt.setString(2, "Log message " + i);
                    pstmt.addBatch(); // Add to batch instead of executing
                    
                    // Execute in chunks of 100 to avoid out-of-memory errors
                    if (i % 100 == 0) {
                        pstmt.executeBatch();
                    }
                }
                System.out.println("Batch insert completed.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Program 3: SQL Injection Vulnerability

This demonstrates why regular Statement with string concatenation is dangerous.

SQLInjectionDemo.javaJava

import java.sql.*;

public class SQLInjectionDemo {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
        conn.createStatement().executeUpdate("CREATE TABLE Users (user VARCHAR(50), pass VARCHAR(50))");
        conn.createStatement().executeUpdate("INSERT INTO Users VALUES ('admin', 'secret123')");

        // Malicious user input
        String usernameInput = "admin' OR '1'='1";
        String passwordInput = "anything";

        // VULNERABLE APPROACH (Using Statement)
        Statement stmt = conn.createStatement();
        String badSql = "SELECT * FROM Users WHERE user='" + usernameInput + "' AND pass='" + passwordInput + "'";
        ResultSet badRs = stmt.executeQuery(badSql);
        if (badRs.next()) {
            System.out.println("Access Granted! (Hacked)"); // Will print this!
        }

        // SECURE APPROACH (Using PreparedStatement)
        String goodSql = "SELECT * FROM Users WHERE user=? AND pass=?";
        PreparedStatement pstmt = conn.prepareStatement(goodSql);
        pstmt.setString(1, usernameInput);
        pstmt.setString(2, passwordInput);
        ResultSet goodRs = pstmt.executeQuery();
        if (goodRs.next()) {
            System.out.println("Access Granted! (Secure)");
        } else {
            System.out.println("Access Denied! (Safe)"); // Will print this!
        }
    }
}

7. ResultSet In Depth

By default, a ResultSet is forward-only and read-only. You iterate using next(). However, you can configure it to be scrollable and updatable.

TYPE_FORWARD_ONLY: Default.
TYPE_SCROLL_INSENSITIVE: Scrollable, but doesn't reflect changes made by others.
TYPE_SCROLL_SENSITIVE: Scrollable and reflects changes made by others.
CONCUR_READ_ONLY: Default. Cannot update the database via ResultSet.
CONCUR_UPDATABLE: Can update database using ResultSet methods (e.g., updateString(), updateRow()).

ResultSetDemo.javaJava

import java.sql.*;

public class ResultSetDemo {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
        Statement initStmt = conn.createStatement();
        initStmt.execute("CREATE TABLE Items (id INT, name VARCHAR(50))");
        initStmt.execute("INSERT INTO Items VALUES (1, 'Apple'), (2, 'Banana'), (3, 'Cherry')");

        // Creating a Scrollable ResultSet
        Statement scrollStmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = scrollStmt.executeQuery("SELECT * FROM Items");

        rs.last(); // Move to last row
        System.out.println("Last Item: " + rs.getString("name"));

        rs.absolute(2); // Move to 2nd row
        System.out.println("Second Item: " + rs.getString("name"));

        // ResultSetMetaData
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        System.out.println("Number of columns: " + columnCount);
        for (int i = 1; i <= columnCount; i++) {
            System.out.println("Column " + i + ": " + metaData.getColumnName(i) + " (" + metaData.getColumnTypeName(i) + ")");
        }
    }
}

8. Transaction Management

By default, JDBC operates in auto-commit mode. This means every SQL execution is immediately committed to the database. For operations requiring multiple steps (like a bank transfer), you must disable auto-commit to ensure ACID properties.

conn.setAutoCommit(false) - Starts the transaction.
conn.commit() - Applies the changes permanently.
conn.rollback() - Reverts the changes if an error occurs.

TransactionDemo.javaJava

import java.sql.*;

public class TransactionDemo {
    public static void main(String[] args) {
        String url = "jdbc:h2:mem:bank";
        
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            // Setup
            Statement stmt = conn.createStatement();
            stmt.execute("CREATE TABLE Accounts (acc_id INT, balance DOUBLE)");
            stmt.execute("INSERT INTO Accounts VALUES (1, 1000.0), (2, 500.0)");

            // Start transaction
            conn.setAutoCommit(false);

            try (PreparedStatement withdraw = conn.prepareStatement("UPDATE Accounts SET balance = balance - ? WHERE acc_id = ?");
                 PreparedStatement deposit = conn.prepareStatement("UPDATE Accounts SET balance = balance + ? WHERE acc_id = ?")) {
                
                double transferAmount = 200.0;

                // Step 1: Withdraw from Account 1
                withdraw.setDouble(1, transferAmount);
                withdraw.setInt(2, 1);
                withdraw.executeUpdate();

                // Step 2: Deposit to Account 2
                deposit.setDouble(1, transferAmount);
                deposit.setInt(2, 2);
                deposit.executeUpdate();

                // If everything is successful, commit!
                conn.commit();
                System.out.println("Transfer successful!");

            } catch (Exception ex) {
                // If any step fails, rollback all changes
                conn.rollback();
                System.err.println("Transaction failed. Rolled back.");
            } finally {
                // Restore default behavior
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Transaction Isolation Levels

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ_UNCOMMITTED	Yes	Yes	Yes
READ_COMMITTED	No	Yes	Yes
REPEATABLE_READ	No	No	Yes
SERIALIZABLE	No	No	No

Set via conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

9. CallableStatement – Stored Procedures

A CallableStatement is used to execute SQL stored procedures. The syntax to call a procedure is {call procedureName(?, ?)}.

Stored procedures can have IN parameters (input), OUT parameters (output), and INOUT parameters.

CallableStatementDemo.javaJava

import java.sql.*;

public class CallableStatementDemo {
    public static void main(String[] args) {
        // Assuming MySQL connection
        String url = "jdbc:mysql://localhost:3306/testdb";
        try (Connection conn = DriverManager.getConnection(url, "root", "pass")) {
            
            // Create a stored procedure (MySQL syntax)
            // CREATE PROCEDURE getEmployeeName(IN emp_id INT, OUT emp_name VARCHAR(50)) ...

            String sql = "{call getEmployeeName(?, ?)}";
            try (CallableStatement cstmt = conn.prepareCall(sql)) {
                
                // Set IN parameter (emp_id = 10)
                cstmt.setInt(1, 10);
                
                // Register OUT parameter type
                cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
                
                // Execute
                cstmt.execute();
                
                // Retrieve OUT parameter value
                String name = cstmt.getString(2);
                System.out.println("Employee Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

10. Connection Pooling

Creating a new database connection (via DriverManager) is a very expensive network operation. Connection Pooling maintains a cache of database connections that can be reused when future requests to the database are required.

The standard interface for getting a pooled connection is javax.sql.DataSource. HikariCP is the industry standard and fastest connection pool.

HikariCPDemo.javaJava

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class HikariCPDemo {
    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/testdb");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10);
        
        dataSource = new HikariDataSource(config);
    }

    public static void main(String[] args) throws Exception {
        // Borrowing a connection from the pool
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT 1")) {
            
            if (rs.next()) {
                System.out.println("Connection Pool is working: " + rs.getInt(1));
            }
        }
        // Connection is returned to the pool, NOT closed permanently.
    }
}

11. Complete Mini Project: Student Management System (DAO Pattern)

The Data Access Object (DAO) pattern abstracts and encapsulates all access to the data source. Let's build a clean, real-world application structure.

Student.javaJava

public class Student {
    private int id;
    private String name;
    
    // Constructors, Getters, and Setters omitted for brevity
}

StudentDAO.javaJava

import java.util.List;

public interface StudentDAO {
    void addStudent(Student student);
    Student getStudent(int id);
    List<Student> getAllStudents();
    void updateStudent(Student student);
    void deleteStudent(int id);
}

StudentDAOImpl.javaJava

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl implements StudentDAO {
    private Connection conn;

    public StudentDAOImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public void addStudent(Student student) {
        String sql = "INSERT INTO students (id, name) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Student getStudent(int id) {
        String sql = "SELECT * FROM students WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new Student(rs.getInt("id"), rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // Other methods omitted for brevity...
}

12. JDBC Best Practices

Best Practice	Reason
Use try-with-resources	Ensures `Connection`, `Statement`, and `ResultSet` are automatically closed, preventing memory and connection leaks.
Always use PreparedStatement	Prevents SQL injection attacks and improves performance due to query caching.
Use Connection Pooling	Use HikariCP or Tomcat JDBC Pool in production instead of opening physical connections for every request.
Externalize configuration	Never hardcode credentials or URLs. Use properties files or environment variables.
Handle SQLExceptions properly	Do not swallow exceptions. Log the SQLState and Error Code to help debug database-specific issues.

←

Java 26 Features

Spring Core & DI

→