JDBC — Database Access in Java

JDBC is Java’s standard API for talking to relational databases. Every major database provides a JDBC driver — a JAR you add to your project that implements the JDBC interfaces for that specific database. JDBC gives you direct, low-level SQL control, which makes it a good fit for learning SQL fundamentals, for performance-sensitive queries, or for projects where a full ORM like JPA would be overkill.

Setup

Add the MySQL driver to your project. With Maven:

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

Create the database and table for the examples:

CREATE DATABASE employeedb;
USE employeedb;

CREATE TABLE employees (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL,
    salary     DECIMAL(10, 2) NOT NULL,
    hire_date  DATE NOT NULL
);

Connecting to the Database

Every JDBC operation starts with a Connection. The connection URL encodes the database host, port, and name. Credentials should always come from environment variables or a config file — never hardcoded in source. The try-with-resources block guarantees the connection closes even if an exception is thrown.

import java.sql.*;

public class DatabaseConfig {

    private static final String URL  = "jdbc:mysql://localhost:3306/employeedb?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = System.getenv("DB_PASSWORD"); // read from environment, never hardcode

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }
}

CRUD Operations

CREATE — Insert

PreparedStatement is mandatory for any query that includes user-supplied data. The ? placeholders are sent to the database as parameters, not as SQL text, which makes SQL injection structurally impossible. Statement.RETURN_GENERATED_KEYS tells the driver to hand back the auto-incremented ID after the insert.

public static int addEmployee(String name, String dept, double salary, LocalDate hireDate)
        throws SQLException {
    String sql = "INSERT INTO employees (name, department, salary, hire_date) VALUES (?, ?, ?, ?)";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

        ps.setString(1, name);
        ps.setString(2, dept);
        ps.setDouble(3, salary);
        ps.setDate(4, Date.valueOf(hireDate));

        int affected = ps.executeUpdate();

        // Retrieve the auto-generated primary key
        try (ResultSet keys = ps.getGeneratedKeys()) {
            if (keys.next()) return keys.getInt(1);
        }
        return -1;
    }
}

READ — Query

ResultSet is a cursor over the result rows. rs.next() advances to the next row and returns false when there are no more rows. Column values are retrieved by name (preferred — more readable and resilient to column reordering) or by index.

public record Employee(int id, String name, String department, double salary, LocalDate hireDate) {}

public static List<Employee> getAllEmployees() throws SQLException {
    String sql = "SELECT id, name, department, salary, hire_date FROM employees ORDER BY name";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {

        List<Employee> employees = new ArrayList<>();
        while (rs.next()) {
            employees.add(new Employee(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("department"),
                rs.getDouble("salary"),
                rs.getDate("hire_date").toLocalDate()
            ));
        }
        return employees;
    }
}

public static Optional<Employee> findById(int id) throws SQLException {
    String sql = "SELECT * FROM employees WHERE id = ?";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return Optional.of(new Employee(
                    rs.getInt("id"), rs.getString("name"),
                    rs.getString("department"), rs.getDouble("salary"),
                    rs.getDate("hire_date").toLocalDate()
                ));
            }
        }
    }
    return Optional.empty(); // no row found — return empty Optional, not null
}

public static List<Employee> findByDepartment(String dept) throws SQLException {
    String sql = "SELECT * FROM employees WHERE department = ? ORDER BY salary DESC";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setString(1, dept);
        try (ResultSet rs = ps.executeQuery()) {
            List<Employee> result = new ArrayList<>();
            while (rs.next()) {
                result.add(new Employee(rs.getInt("id"), rs.getString("name"),
                    rs.getString("department"), rs.getDouble("salary"),
                    rs.getDate("hire_date").toLocalDate()));
            }
            return result;
        }
    }
}

UPDATE

executeUpdate() returns the number of rows affected. Checking this return value tells you whether the row existed — if it returns 0, no row matched the WHERE clause.

public static boolean updateSalary(int id, double newSalary) throws SQLException {
    String sql = "UPDATE employees SET salary = ? WHERE id = ?";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setDouble(1, newSalary);
        ps.setInt(2, id);
        return ps.executeUpdate() > 0;  // true if a row was updated; false means no match
    }
}

DELETE

public static boolean deleteEmployee(int id) throws SQLException {
    String sql = "DELETE FROM employees WHERE id = ?";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setInt(1, id);
        return ps.executeUpdate() > 0;
    }
}

Transactions

By default JDBC runs in auto-commit mode — every statement is its own transaction. When multiple operations must succeed or fail as a unit (like a department transfer that updates both the employee record and an audit log), you turn off auto-commit, run all the statements, then commit. If anything fails, rollback() undoes everything.

public static void transferDepartment(int employeeId, String newDept, double salaryAdjustment)
        throws SQLException {
    Connection conn = null;
    try {
        conn = DatabaseConfig.getConnection();
        conn.setAutoCommit(false);  // begin transaction — nothing is committed until we say so

        // Step 1: update department
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE employees SET department = ? WHERE id = ?")) {
            ps.setString(1, newDept);
            ps.setInt(2, employeeId);
            ps.executeUpdate();
        }

        // Step 2: adjust salary
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE employees SET salary = salary + ? WHERE id = ?")) {
            ps.setDouble(1, salaryAdjustment);
            ps.setInt(2, employeeId);
            ps.executeUpdate();
        }

        // Step 3: log the change
        try (PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO transfer_log (employee_id, new_dept, changed_at) VALUES (?, ?, NOW())")) {
            ps.setInt(1, employeeId);
            ps.setString(2, newDept);
            ps.executeUpdate();
        }

        conn.commit();  // all three steps succeeded — make the changes permanent
        System.out.println("Transfer completed successfully");

    } catch (SQLException e) {
        // One step failed — undo all changes so the database stays consistent
        if (conn != null) {
            try { conn.rollback(); } catch (SQLException ex) { /* log rollback failure */ }
        }
        throw e;  // re-throw so the caller knows the operation failed
    } finally {
        if (conn != null) {
            conn.setAutoCommit(true); // restore auto-commit for any future use
            conn.close();
        }
    }
}

Batch Operations

Sending each INSERT individually over the network is slow for large datasets — each statement incurs a round-trip. Batch operations queue multiple statements in memory and send them together in one network call, which can be orders of magnitude faster for bulk inserts.

public static void batchInsert(List<Employee> employees) throws SQLException {
    String sql = "INSERT INTO employees (name, department, salary, hire_date) VALUES (?, ?, ?, ?)";

    try (Connection conn = DatabaseConfig.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        conn.setAutoCommit(false); // wrap the whole batch in one transaction
        for (Employee e : employees) {
            ps.setString(1, e.name());
            ps.setString(2, e.department());
            ps.setDouble(3, e.salary());
            ps.setDate(4, Date.valueOf(e.hireDate()));
            ps.addBatch(); // queue this row — does NOT send it yet
        }
        int[] results = ps.executeBatch(); // send all rows in one round-trip
        conn.commit();
        System.out.println("Inserted " + results.length + " employees");
    }
}

Project: Employee Database Application

Putting it all together — a small program that exercises every operation:

public class EmployeeApp {
    public static void main(String[] args) throws SQLException {
        // Insert sample data
        int id1 = addEmployee("Alice Smith", "Engineering", 95000, LocalDate.of(2022, 3, 1));
        int id2 = addEmployee("Bob Jones",   "Marketing",   72000, LocalDate.of(2021, 7, 15));
        int id3 = addEmployee("Carol White", "Engineering", 88000, LocalDate.of(2023, 1, 10));

        // Read all employees
        System.out.println("All employees:");
        getAllEmployees().forEach(e ->
            System.out.printf("  %d. %-15s %-15s $%.0f%n",
                e.id(), e.name(), e.department(), e.salary()));

        // Filter by department
        System.out.println("\nEngineering team:");
        findByDepartment("Engineering").forEach(e ->
            System.out.println("  " + e.name() + " — $" + e.salary()));

        // Update a salary
        updateSalary(id1, 100000);
        System.out.println("\nAfter raise:");
        findById(id1).ifPresent(e -> System.out.println("  " + e.name() + ": $" + e.salary()));

        // Delete a record
        deleteEmployee(id3);
        System.out.println("\nAfter delete: " + getAllEmployees().size() + " employees");
    }
}