JDBC (Java Database Connectivity) is a Java-based API that provides a standardized interface for executing SQL statements. It enables Java applications to interact with relational databases in a uniform manner, regardless of the specific database vendor. The API consists of a collection of Java classes and interfaces that abstract database operations, allowing developers to write database-agnostic code.

This guide demonstrates how to use JDBC for fundamental database operations: creating records, reading data, updating existing entries, and deleting rows. The examples use MySQL as the target database.

Database Setup

Before writing Java code, establish the database schema. Create a test database with a student table containing fields for identifier, name, age, and score.

CREATE DATABASE `school_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE school_db;

CREATE TABLE `students` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    `age` INT NOT NULL,
    `score` DOUBLE NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = MyISAM;

INSERT INTO `students` VALUES (1, 'Alice', 26, 83);
INSERT INTO `students` VALUES (2, 'Bob', 23, 93);
INSERT INTO `students` VALUES (3, 'Charlie', 34, 45);
INSERT INTO `students` VALUES (4, 'David', 12, 78);
INSERT INTO `students` VALUES (5, 'Eve', 33, 96);
INSERT INTO `students` VALUES (6, 'Frank', 23, 46);

Project Structure

The Maven project includes a model class representing the data entity, a utility class for managing database connections, and a DAO class encapsulating all database operations.

Student Entity

package com.example.model;

/**
 * Domain object representing a student record
 */
public class Student {
    private int id;
    private String name;
    private int age;
    private double score;

    public Student() {
    }

    public Student(String name, int age, double score) {
        this.name = name;
        this.age = age;
        this.score = score;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public double getScore() {
        return score;
    }

    public void setScore(double score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return "Student{id=" + id + ", name='" + name + "', age=" + age + ", score=" + score + "}";
    }
}

Database Connection Utility

package com.example.database;

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

/**
 * Singleton utility for managing database connections
 */
public class DatabaseConnection {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/school_db?characterEncoding=utf-8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";
    private static Connection connectionInstance = null;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connectionInstance = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            System.out.println("Database connection established successfully");
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC Driver not found");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("Failed to establish database connection");
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return connectionInstance;
    }
}

Data Access Object

package com.example.repository;

import com.example.model.Student;
import com.example.database.DatabaseConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Data access layer for Student entity operations
 */
public class StudentRepository {
    private final Connection dbConnection;

    public StudentRepository() {
        this.dbConnection = DatabaseConnection.getConnection();
    }

    /**
     * Insert a new student record
     */
    public void create(Student student) {
        String query = "INSERT INTO students(name, age, score) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = dbConnection.prepareStatement(query)) {
            stmt.setString(1, student.getName());
            stmt.setInt(2, student.getAge());
            stmt.setDouble(3, student.getScore());
            stmt.executeUpdate();
            System.out.println("Student record created successfully");
        } catch (SQLException e) {
            System.err.println("Error creating student record");
            e.printStackTrace();
        }
    }

    /**
     * Modify an existing student record
     */
    public void update(Student student) {
        String query = "UPDATE students SET name = ?, age = ?, score = ? WHERE id = ?";
        try (PreparedStatement stmt = dbConnection.prepareStatement(query)) {
            stmt.setString(1, student.getName());
            stmt.setInt(2, student.getAge());
            stmt.setDouble(3, student.getScore());
            stmt.setInt(4, student.getId());
            stmt.executeUpdate();
            System.out.println("Student record updated successfully");
        } catch (SQLException e) {
            System.err.println("Error updating student record");
            e.printStackTrace();
        }
    }

    /**
     * Remove a student record by ID
     */
    public void delete(int studentId) {
        String query = "DELETE FROM students WHERE id = ?";
        try (PreparedStatement stmt = dbConnection.prepareStatement(query)) {
            stmt.setInt(1, studentId);
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Student record deleted successfully");
            }
        } catch (SQLException e) {
            System.err.println("Error deleting student record");
            e.printStackTrace();
        }
    }

    /**
     * Retrieve a single student by ID
     */
    public Student findById(int studentId) {
        String query = "SELECT * FROM students WHERE id = ?";
        try (PreparedStatement stmt = dbConnection.prepareStatement(query)) {
            stmt.setInt(1, studentId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToStudent(rs);
            }
        } catch (SQLException e) {
            System.err.println("Error retrieving student record");
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Retrieve all student records
     */
    public List<Student> findAll() {
        List<Student> studentList = new ArrayList<>();
        String query = "SELECT * FROM students";
        try (PreparedStatement stmt = dbConnection.prepareStatement(query)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                studentList.add(mapResultSetToStudent(rs));
            }
        } catch (SQLException e) {
            System.err.println("Error retrieving student records");
            e.printStackTrace();
        }
        return studentList;
    }

    private Student mapResultSetToStudent(ResultSet rs) throws SQLException {
        Student student = new Student();
        student.setId(rs.getInt("id"));
        student.setName(rs.getString("name"));
        student.setAge(rs.getInt("age"));
        student.setScore(rs.getDouble("score"));
        return student;
    }
}

Application Entry Point

package com.example;

import com.example.repository.StudentRepository;
import com.example.model.Student;

import java.util.List;

public class Application {
    public static void main(String[] args) {
        StudentRepository repository = new StudentRepository();

        // Retrieve and display all records
        System.out.println("=== All Students ===");
        List<Student> allStudents = repository.findAll();
        allStudents.forEach(System.out::println);

        // Update a specific record
        System.out.println("\n=== Update Student ===");
        Student existingStudent = new Student("Jacob", 21, 92.5);
        existingStudent.setId(2);
        repository.update(existingStudent);

        // Retrieve single record
        System.out.println("\n=== Find by ID ===");
        Student found = repository.findById(2);
        System.out.println(found);

        // Create new record
        System.out.println("\n=== Create New Student ===");
        Student newStudent = new Student("Grace", 19, 88.0);
        repository.create(newStudent);

        // Delete a record
        System.out.println("\n=== Delete Student ===");
        repository.delete(4);

        // Display final state
        System.out.println("\n=== Final Student List ===");
        repository.findAll().forEach(System.out::println);
    }
}

Important Implementation Notes

Several critical considerations apply when working with JDBC:

PreparedStatement Usage: Always prefer PreparedStatement over string concatenation for SQL queries. This approach offers three significant advantages: protection against SQL injection attacks, improved code maintainability through clearer parameter binding, and enhanced performance since the database compiles the query plan once and caches it for repeated executions.

Connection Management: When not using connection pooling, ensure database connections are properly closed after use. Implement try-with-resources statements to guarantee cleanup of database resources, preventing connection leaks that can exhaust available connections.

Dependency Configuration: The MySQL connector version must be compatible with your MySQL server installation. Mismatched versions commonly cause connection failures.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>

SQL Injection Vulnerability: The following pattern using string concatenation is inherently dangerous and should never appear in production code:

String unsafeQuery = "SELECT * FROM users WHERE name = '" + username + "' AND password = '" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(unsafeQuery);

This approach allows attackers to manipulate input values and gain unauthorized database access. Prepared statements with parameterized queries eliminate this vulnerability entirely.