Lesson 24 of 25

Student Database Mini Project

Build a complete SQLite-backed student data layer with Create, Read, Update, Delete, search, and validation operations.

Learning objectives

• Design a simple student database table.
• Represent a row as a Java Student object.
• Separate database access into a repository class.
• Implement Create, Read, Update, and Delete (CRUD).
• Search students with parameterized SQL.
• Validate input before sending it to the database.

1. Project structure

This project separates the data model, connection helper, repository, and console application. That is easier to maintain than putting every SQL statement inside main.

student-database/
├── DatabaseHelper.java
├── DatabaseSetup.java
├── Student.java
├── StudentRepository.java
└── StudentDatabaseApp.java

2. Create the Student model

public class Student {

    private final String studentId;
    private String name;
    private String course;
    private int mark;

    public Student(
            String studentId,
            String name,
            String course,
            int mark) {

        this.studentId = studentId;
        this.name = name;
        this.course = course;
        setMark(mark);
    }

    public String getStudentId() {
        return studentId;
    }

    public String getName() {
        return name;
    }

    public String getCourse() {
        return course;
    }

    public int getMark() {
        return mark;
    }

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

    public void setCourse(String course) {
        this.course = course;
    }

    public void setMark(int mark) {
        if (mark < 0 || mark > 100) {
            throw new IllegalArgumentException(
                "Mark must be from 0 to 100."
            );
        }
        this.mark = mark;
    }

    public String getResult() {
        return mark >= 50 ? "Pass" : "Fail";
    }
}

The model stores student data and protects the mark rule. The database layer should not be responsible for every business rule.

3. Reuse the connection helper

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

public class DatabaseHelper {

    private static final String URL =
        "jdbc:sqlite:student_management.db";

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

4. Create the database table

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseSetup {

    public static void initialize() {
        String sql = """
            CREATE TABLE IF NOT EXISTS students (
                student_id TEXT PRIMARY KEY,
                name       TEXT NOT NULL,
                course     TEXT NOT NULL,
                mark       INTEGER NOT NULL
                    CHECK(mark BETWEEN 0 AND 100)
            )
            """;

        try (Connection connection =
                 DatabaseHelper.getConnection();
             Statement statement =
                 connection.createStatement()) {

            statement.execute(sql);

        } catch (SQLException e) {
            throw new RuntimeException(
                "Could not create database table.", e
            );
        }
    }
}

5. Repository: Create

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

public class StudentRepository {

    public boolean add(Student student) {
        String sql = """
            INSERT INTO students
                (student_id, name, course, mark)
            VALUES (?, ?, ?, ?)
            """;

        try (Connection connection =
                 DatabaseHelper.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {

            statement.setString(
                1, student.getStudentId()
            );
            statement.setString(
                2, student.getName()
            );
            statement.setString(
                3, student.getCourse()
            );
            statement.setInt(
                4, student.getMark()
            );

            return statement.executeUpdate() == 1;

        } catch (SQLException e) {
            throw new RuntimeException(
                "Could not add student.", e
            );
        }
    }

6. Repository: Read and Search

public List<Student> findAll() {
    String sql = """
        SELECT student_id, name, course, mark
        FROM students
        ORDER BY name
        """;

    List<Student> students = new ArrayList<>();

    try (Connection connection =
             DatabaseHelper.getConnection();
         PreparedStatement statement =
             connection.prepareStatement(sql);
         ResultSet rs =
             statement.executeQuery()) {

        while (rs.next()) {
            students.add(mapStudent(rs));
        }

        return students;

    } catch (SQLException e) {
        throw new RuntimeException(
            "Could not load students.", e
        );
    }
}

public Optional<Student> findById(String id) {
    String sql = """
        SELECT student_id, name, course, mark
        FROM students
        WHERE student_id = ?
        """;

    try (Connection connection =
             DatabaseHelper.getConnection();
         PreparedStatement statement =
             connection.prepareStatement(sql)) {

        statement.setString(1, id);

        try (ResultSet rs = statement.executeQuery()) {
            if (rs.next()) {
                return Optional.of(mapStudent(rs));
            }
        }

        return Optional.empty();

    } catch (SQLException e) {
        throw new RuntimeException(
            "Could not search for student.", e
        );
    }
}

7. Repository: Update

public boolean update(Student student) {
    String sql = """
        UPDATE students
        SET name = ?, course = ?, mark = ?
        WHERE student_id = ?
        """;

    try (Connection connection =
             DatabaseHelper.getConnection();
         PreparedStatement statement =
             connection.prepareStatement(sql)) {

        statement.setString(
            1, student.getName()
        );
        statement.setString(
            2, student.getCourse()
        );
        statement.setInt(
            3, student.getMark()
        );
        statement.setString(
            4, student.getStudentId()
        );

        return statement.executeUpdate() == 1;

    } catch (SQLException e) {
        throw new RuntimeException(
            "Could not update student.", e
        );
    }
}

8. Repository: Delete

public boolean delete(String studentId) {
        String sql = """
            DELETE FROM students
            WHERE student_id = ?
            """;

        try (Connection connection =
                 DatabaseHelper.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {

            statement.setString(1, studentId);

            return statement.executeUpdate() == 1;

        } catch (SQLException e) {
            throw new RuntimeException(
                "Could not delete student.", e
            );
        }
    }

    private Student mapStudent(ResultSet rs)
            throws SQLException {

        return new Student(
            rs.getString("student_id"),
            rs.getString("name"),
            rs.getString("course"),
            rs.getInt("mark")
        );
    }
}

9. Test the CRUD operations

public class StudentDatabaseApp {

    public static void main(String[] args) {
        DatabaseSetup.initialize();

        StudentRepository repository =
            new StudentRepository();

        Student student =
            new Student(
                "S001",
                "Amina",
                "Computer Science",
                82
            );

        repository.add(student);

        System.out.println("All students:");
        repository.findAll().forEach(s ->
            System.out.printf(
                "%s | %s | %s | %d | %s%n",
                s.getStudentId(),
                s.getName(),
                s.getCourse(),
                s.getMark(),
                s.getResult()
            )
        );

        repository.findById("S001")
            .ifPresent(found ->
                System.out.println(
                    "Found: " + found.getName()
                )
            );

        student.setMark(88);
        repository.update(student);

        // Uncomment when you want to test deletion:
        // repository.delete("S001");
    }
}

10. CRUD mapping

Mini project extension

Add a findByCourse(String course) method that returns all students in one course. Use a parameterized WHERE course = ? query. Then add an averageMark() query using SQL AVG(mark).

Common mistakes

Summary

You now have a complete reusable CRUD data layer rather than isolated SQL snippets. Lesson 25 combines this repository with Swing to produce a full Student Course Management System.