Java Database Connectivity (JDBC)

1. What is JDBC?

JDBC (Java Database Connectivity) is a standard Java API that defines how Java applications communicate with relational databases. It is part of the Java SE platform (java.sql and javax.sql packages).

Key characteristics:

• Database-agnostic: The same Java code works with MySQL, PostgreSQL, Oracle, SQLite, and more — you only change the driver and connection URL.
• Driver-based: Each database vendor provides a JDBC driver (a JAR file) that translates JDBC calls into database-specific wire protocol.
• SQL-based: You write standard SQL — JDBC sends it to the database and returns results as Java objects.

2. JDBC Architecture

JDBC follows a layered architecture that decouples your application code from any specific database:

Layer	Component	Role
1	Java Application	Your code — calls JDBC API interfaces.
2	JDBC API	Interfaces: Connection, Statement, ResultSet, etc.
3	DriverManager	Manages registered drivers; creates Connection objects.
4	JDBC Driver	Vendor-specific JAR that translates JDBC calls to database protocol.
5	Database	MySQL, PostgreSQL, Oracle, SQLite, etc.

// The 5-step JDBC workflow
// 1. Load the driver (automatic since JDBC 4.0)
// 2. Establish a connection
Connection conn = DriverManager.getConnection(url, user, pass);

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

// 4. Execute SQL and get results
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

// 5. Process results and close resources
while (rs.next()) { System.out.println(rs.getString("name")); }
rs.close(); stmt.close(); conn.close();

3. JDBC Driver Types

Type	Name	Description	Use Today?
Type 1	JDBC-ODBC Bridge	Converts JDBC calls to ODBC calls. Requires ODBC driver installed on client.	No — removed in Java 8.
Type 2	Native-API Driver	Uses database-specific native libraries. Requires native install on client.	Rare.
Type 3	Network Protocol Driver	Sends JDBC calls over network to a middleware server that talks to the DB.	Rare.
Type 4	Thin Driver (Pure Java)	Pure Java driver that communicates directly with the database using its native network protocol. No native libraries needed.	Yes — standard choice.

4. Setting Up JDBC

Add the database driver to your project. With Maven, add to pom.xml:

<!-- MySQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

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

<!-- SQLite (great for learning, no server required) -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.45.1.0</version>
</dependency>

Since JDBC 4.0 (Java 6+), drivers are auto-loaded via the Service Provider mechanism — no Class.forName() is needed. For legacy code you may still see:

// Legacy: manually load driver class (not needed since JDBC 4.0)
Class.forName("com.mysql.cj.jdbc.Driver");      // MySQL
Class.forName("org.postgresql.Driver");          // PostgreSQL
Class.forName("oracle.jdbc.driver.OracleDriver"); // Oracle

5. Establishing a Connection

DriverManager.getConnection(url, user, password) creates a Connection object. The JDBC URL format varies by database:

import java.sql.*;

public class ConnectionDemo {
    // MySQL connection URL format:
    // jdbc:mysql://host:port/database?parameters
    static final String MYSQL_URL =
        "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";

    // PostgreSQL:
    // static final String PG_URL = "jdbc:postgresql://localhost:5432/mydb";

    // SQLite (file-based, no server needed):
    // static final String SQLITE_URL = "jdbc:sqlite:mydb.sqlite";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                MYSQL_URL, "root", "password")) {

            System.out.println("Connected to: " + conn.getMetaData().getURL());
            System.out.println("Driver:       "
                + conn.getMetaData().getDriverName());
            System.out.println("Auto-commit:  " + conn.getAutoCommit());
            System.out.println("DB Product:   "
                + conn.getMetaData().getDatabaseProductName() + " "
                + conn.getMetaData().getDatabaseProductVersion());

        } catch (SQLException e) {
            System.err.println("Connection failed!");
            System.err.println("SQL State: " + e.getSQLState());
            System.err.println("Error Code: " + e.getErrorCode());
            System.err.println("Message: " + e.getMessage());
        }
    }
}

6. Statement

Statement is used for executing static SQL queries with no parameters. Use executeQuery() for SELECT and executeUpdate() for INSERT, UPDATE, DELETE, and DDL.

import java.sql.*;

public class StatementDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             Statement stmt = conn.createStatement()) {

            // DDL — create a table
            stmt.executeUpdate("""
                CREATE TABLE IF NOT EXISTS products (
                    id    INT AUTO_INCREMENT PRIMARY KEY,
                    name  VARCHAR(100) NOT NULL,
                    price DOUBLE NOT NULL
                )
            """);
            System.out.println("Table created.");

            // DML — insert rows
            int rows = stmt.executeUpdate(
                "INSERT INTO products (name, price) VALUES ('Laptop', 999.99)");
            System.out.println("Rows inserted: " + rows);

            stmt.executeUpdate(
                "INSERT INTO products (name, price) VALUES ('Mouse', 29.99)");

            // DQL — select rows
            ResultSet rs = stmt.executeQuery("SELECT * FROM products");
            while (rs.next()) {
                System.out.printf("ID: %d | Name: %-10s | Price: %.2f%n",
                    rs.getInt("id"), rs.getString("name"), rs.getDouble("price"));
            }
            rs.close();
        }
    }
}

7. PreparedStatement

PreparedStatement is pre-compiled SQL with placeholders (?) filled in at runtime. It is the correct tool for any query with parameters because:

• Prevents SQL injection — parameters are always treated as data, never as SQL code.
• Better performance — the query is compiled once and can be reused many times.
• Cleaner code — no string concatenation of SQL fragments.

import java.sql.*;

public class PreparedStatementDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb";

        try (Connection conn = DriverManager.getConnection(url, "root", "password")) {

            // INSERT with PreparedStatement
            String insertSQL = "INSERT INTO products (name, price) VALUES (?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSQL)) {
                ps.setString(1, "Keyboard"); // parameter index starts at 1
                ps.setDouble(2, 79.99);
                int rows = ps.executeUpdate();
                System.out.println("Inserted rows: " + rows);

                // Reuse the same statement with different values
                ps.setString(1, "Monitor");
                ps.setDouble(2, 349.99);
                ps.executeUpdate();

                ps.setString(1, "Webcam");
                ps.setDouble(2, 59.99);
                ps.executeUpdate();
                System.out.println("All products inserted.");
            }

            // SELECT with parameters
            String selectSQL = "SELECT * FROM products WHERE price < ? ORDER BY price";
            try (PreparedStatement ps = conn.prepareStatement(selectSQL)) {
                ps.setDouble(1, 200.00); // find products under $200

                try (ResultSet rs = ps.executeQuery()) {
                    System.out.println("\nProducts under $200:");
                    while (rs.next()) {
                        System.out.printf("  %-10s $%.2f%n",
                            rs.getString("name"), rs.getDouble("price"));
                    }
                }
            }

            // UPDATE with parameters
            String updateSQL = "UPDATE products SET price = ? WHERE name = ?";
            try (PreparedStatement ps = conn.prepareStatement(updateSQL)) {
                ps.setDouble(1, 89.99);
                ps.setString(2, "Keyboard");
                int updated = ps.executeUpdate();
                System.out.println("\nRows updated: " + updated);
            }
        }
    }
}

8. CallableStatement

CallableStatement executes stored procedures defined in the database. It supports IN parameters (input), OUT parameters (output), and INOUT parameters.

import java.sql.*;

public class CallableStatementDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb";

        // Assume this stored procedure exists in MySQL:
        // CREATE PROCEDURE get_product_count(IN min_price DOUBLE, OUT total INT)
        // BEGIN
        //   SELECT COUNT(*) INTO total FROM products WHERE price >= min_price;
        // END

        try (Connection conn = DriverManager.getConnection(url, "root", "password")) {

            // Call stored procedure: {call procedure_name(?, ?)}
            try (CallableStatement cs = conn.prepareCall(
                    "{call get_product_count(?, ?)}")) {

                // Set IN parameter
                cs.setDouble(1, 50.00);

                // Register OUT parameter — specify SQL type
                cs.registerOutParameter(2, Types.INTEGER);

                cs.execute();

                // Retrieve the OUT parameter value
                int count = cs.getInt(2);
                System.out.println("Products with price >= $50: " + count);
            }

            // Stored procedure with only IN params that returns a ResultSet
            // CREATE PROCEDURE get_products_by_price(IN max_price DOUBLE)
            // BEGIN
            //   SELECT * FROM products WHERE price <= max_price;
            // END
            try (CallableStatement cs = conn.prepareCall(
                    "{call get_products_by_price(?)}")) {
                cs.setDouble(1, 100.00);
                try (ResultSet rs = cs.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getString("name")
                            + ": $" + rs.getDouble("price"));
                    }
                }
            }
        }
    }
}

9. ResultSet

A ResultSet is a cursor pointing to the rows returned by a SELECT query. Initially it points before the first row. Call next() to advance one row at a time. Retrieve column values by name or index.

import java.sql.*;

public class ResultSetDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT id, name, price FROM products ORDER BY price DESC")) {

            System.out.println("=== Product Catalog ===");

            // Iterate all rows
            while (rs.next()) {
                int    id    = rs.getInt("id");        // by column name
                String name  = rs.getString(2);         // by column index (1-based)
                double price = rs.getDouble("price");

                // Check for SQL NULL
                if (rs.wasNull()) {
                    System.out.println("Price is NULL for: " + name);
                } else {
                    System.out.printf("%-3d  %-12s  $%8.2f%n", id, name, price);
                }
            }

            System.out.println("=======================");

            // Scrollable ResultSet — can move backwards
            Statement scrollStmt = conn.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
            ResultSet scrollRs = scrollStmt.executeQuery("SELECT * FROM products");

            scrollRs.last();  // move to last row
            System.out.println("Total rows: " + scrollRs.getRow());

            scrollRs.first(); // move back to first row
            System.out.println("First product: " + scrollRs.getString("name"));

            scrollRs.close();
            scrollStmt.close();
        }
    }
}

10. CRUD Example

A complete Create, Read, Update, Delete example using PreparedStatement and try-with-resources throughout:

import java.sql.*;

public class CRUDDemo {
    static final String URL  = "jdbc:mysql://localhost:3306/mydb";
    static final String USER = "root";
    static final String PASS = "password";

    // CREATE
    public static int createEmployee(String name, String dept, double salary)
            throws SQLException {
        String sql = "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql,
                 Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setString(2, dept);
            ps.setDouble(3, salary);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) {
                    int id = keys.getInt(1);
                    System.out.println("Created employee ID: " + id);
                    return id;
                }
            }
        }
        return -1;
    }

    // READ
    public static void readEmployees() throws SQLException {
        String sql = "SELECT * FROM employees ORDER BY id";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            System.out.println("\n--- All Employees ---");
            while (rs.next()) {
                System.out.printf("  [%d] %-15s %-12s $%.2f%n",
                    rs.getInt("id"), rs.getString("name"),
                    rs.getString("department"), rs.getDouble("salary"));
            }
        }
    }

    // UPDATE
    public static void updateSalary(int id, double newSalary) throws SQLException {
        String sql = "UPDATE employees SET salary = ? WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, newSalary);
            ps.setInt(2, id);
            int rows = ps.executeUpdate();
            System.out.println("Updated " + rows + " row(s) for ID: " + id);
        }
    }

    // DELETE
    public static void deleteEmployee(int id) throws SQLException {
        String sql = "DELETE FROM employees WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            int rows = ps.executeUpdate();
            System.out.println("Deleted " + rows + " row(s) for ID: " + id);
        }
    }

    public static void main(String[] args) throws SQLException {
        int id1 = createEmployee("Alice Smith", "Engineering", 95000.0);
        int id2 = createEmployee("Bob Jones",  "Marketing",   72000.0);
        int id3 = createEmployee("Carol White","Engineering", 88000.0);

        readEmployees();

        updateSalary(id1, 102000.0);
        readEmployees();

        deleteEmployee(id3);
        readEmployees();
    }
}

11. Transactions

By default, JDBC operates in auto-commit mode — every statement is committed immediately. For operations that must succeed or fail together (e.g., transferring money between accounts), disable auto-commit and manage the transaction manually.

import java.sql.*;

public class TransactionDemo {
    public static void transferFunds(Connection conn, int fromId,
                                     int toId, double amount)
            throws SQLException {
        conn.setAutoCommit(false); // begin transaction

        try {
            // Step 1: Debit from source account
            String debit = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(debit)) {
                ps.setDouble(1, amount);
                ps.setInt(2, fromId);
                int rows = ps.executeUpdate();
                if (rows == 0) throw new SQLException("Source account not found: " + fromId);
            }

            // Step 2: Check for sufficient funds
            String check = "SELECT balance FROM accounts WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(check)) {
                ps.setInt(1, fromId);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next() && rs.getDouble("balance") < 0) {
                        throw new SQLException("Insufficient funds in account: " + fromId);
                    }
                }
            }

            // Step 3: Credit to destination account
            String credit = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(credit)) {
                ps.setDouble(1, amount);
                ps.setInt(2, toId);
                int rows = ps.executeUpdate();
                if (rows == 0) throw new SQLException("Destination account not found: " + toId);
            }

            conn.commit(); // ALL steps succeeded — commit
            System.out.println("Transfer of $" + amount + " completed successfully.");

        } catch (SQLException e) {
            conn.rollback(); // ANY step failed — rollback everything
            System.err.println("Transfer failed! Transaction rolled back: " + e.getMessage());
            throw e; // re-throw so caller knows it failed
        } finally {
            conn.setAutoCommit(true); // restore default behavior
        }
    }

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        try (Connection conn = DriverManager.getConnection(url, "root", "password")) {
            transferFunds(conn, 1, 2, 500.00); // transfer $500 from account 1 to 2
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

12. ResultSetMetaData

ResultSetMetaData describes the structure of a ResultSet — number of columns, their names, types, and more. Useful for building generic database utilities that work with any table.

import java.sql.*;

public class MetaDataDemo {
    public static void printTable(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT * FROM " + tableName + " LIMIT 10")) {

            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();

            System.out.println("Table: " + tableName);
            System.out.println("Columns: " + colCount);
            System.out.println();

            // Print column headers dynamically
            for (int i = 1; i <= colCount; i++) {
                System.out.printf("%-15s", meta.getColumnName(i));
                System.out.println("  [" + meta.getColumnTypeName(i)
                    + "(" + meta.getColumnDisplaySize(i) + ")]");
            }

            System.out.println("---");

            // Print data rows
            while (rs.next()) {
                for (int i = 1; i <= colCount; i++) {
                    System.out.printf("%-15s", rs.getString(i));
                }
                System.out.println();
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb";
        try (Connection conn = DriverManager.getConnection(url, "root", "password")) {
            printTable(conn, "products");
        }
    }
}

13. Connection Pooling

Opening a raw DriverManager connection for every request is expensive — it involves a TCP handshake, authentication, and setup on each call. In production applications, a connection pool maintains a set of pre-opened connections that are reused, dramatically improving throughput.

HikariCP is the fastest, most widely used connection pool for Java. Add the dependency:

<!-- Maven -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

public class ConnectionPoolDemo {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("password");

        // Pool settings
        config.setMaximumPoolSize(10);       // max 10 concurrent connections
        config.setMinimumIdle(2);            // keep 2 idle connections ready
        config.setConnectionTimeout(30000);  // wait up to 30s for a connection
        config.setIdleTimeout(600000);       // close idle after 10 min
        config.setMaxLifetime(1800000);      // retire connections after 30 min
        config.setPoolName("MyAppPool");

        dataSource = new HikariDataSource(config);
        System.out.println("Connection pool initialized.");
    }

    public static void queryWithPool() throws SQLException {
        // getConnection() borrows from pool; close() returns it
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                 "SELECT COUNT(*) AS total FROM products");
             ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                System.out.println("Total products: " + rs.getInt("total"));
            }
        }
        // Connection is returned to pool here — NOT closed
    }

    public static void main(String[] args) throws Exception {
        // Simulate multiple concurrent requests reusing pool connections
        for (int i = 0; i < 5; i++) {
            queryWithPool();
        }
        dataSource.close(); // shutdown pool on app exit
    }
}

14. JDBC Best Practices

• Always use PreparedStatement — never concatenate user input into SQL strings. This is the single most important rule for security.
• Always use try-with-resources — Connection, Statement, and ResultSet all implement AutoCloseable. Failing to close them causes connection leaks and crashes in production.
• Use a connection pool — never call DriverManager.getConnection() on every request in production. Use HikariCP, Apache DBCP, or c3p0.
• Handle SQLException properly — log getSQLState() and getErrorCode() alongside the message for meaningful diagnostics.
• Use transactions for multi-step operations — set autoCommit(false), commit() on success, and rollback() in the catch block.
• Never store credentials in source code — use environment variables, a secrets manager, or an application config file outside version control.
• Use batch updates for bulk inserts — addBatch() + executeBatch() sends multiple rows in one round trip, massively reducing latency.

// Batch insert example
String sql = "INSERT INTO logs (level, message) VALUES (?, ?)";
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    conn.setAutoCommit(false);

    for (int i = 0; i < 10000; i++) {
        ps.setString(1, "INFO");
        ps.setString(2, "Log message " + i);
        ps.addBatch(); // queue the row

        if (i % 500 == 0) {        // flush every 500 rows
            ps.executeBatch();
            ps.clearBatch();
        }
    }
    ps.executeBatch(); // flush remaining rows
    conn.commit();
    System.out.println("10,000 rows inserted in batch.");
}