Building a Simple JDBC Project: Student Management System

Apply your JDBC knowledge to create a functional CRUD application

Project Overview

We’ll build a Student Management System that performs:

• Create: Add new students.
• Read: View all students.
• Update: Modify student details.
• Delete: Remove students.

Tools Used:

• Java 11+
• MySQL (or PostgreSQL)
• MySQL Connector/J (JDBC driver)
• IDE (IntelliJ, Eclipse, or VS Code)

Step 1: Database Setup

Create Database and Table

Run this SQL script in your database client (e.g., MySQL Workbench):

CREATE DATABASE student_db;
USE student_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE,
    age INT
);

Step 2: Model Class (Student.java)

Create a Student class to represent student data:

public class Student {
    private int id;
    private String name;
    private String email;
    private int age;

    // Constructor, Getters, and Setters
    public Student(String name, String email, int age) {
        this.name = name;
        this.email = email;
        this.age = age;
    }

    // Omitted for brevity (generate getters/setters)
}

Step 3: Database Connection Utility

Create DatabaseConnector.java to handle connections:

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

public class DatabaseConnector {
    private static final String URL = "jdbc:mysql://localhost:3306/student_db";
    private static final String USER = "root";
    private static final String PASSWORD = "root123";

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

Step 4: Data Access Object (StudentDAO.java)

Implement CRUD operations using PreparedStatement:

Add Student

public class StudentDAO {
    public void addStudent(Student student) throws SQLException {
        String sql = "INSERT INTO students (name, email, age) VALUES (?, ?, ?)";
        try (Connection conn = DatabaseConnector.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getEmail());
            pstmt.setInt(3, student.getAge());
            pstmt.executeUpdate();
        }
    }
}

Get All Students

public List<Student> getAllStudents() throws SQLException {
    List<Student> students = new ArrayList<>();
    String sql = "SELECT * FROM students";
    try (Connection conn = DatabaseConnector.getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        while (rs.next()) {
            Student student = new Student(
                rs.getString("name"),
                rs.getString("email"),
                rs.getInt("age")
            );
            student.setId(rs.getInt("id"));
            students.add(student);
        }
    }
    return students;
}

Update and Delete Methods

public void updateStudent(int id, String newName, String newEmail, int newAge) throws SQLException {
    String sql = "UPDATE students SET name=?, email=?, age=? WHERE id=?";
    try (Connection conn = DatabaseConnector.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, newName);
        pstmt.setString(2, newEmail);
        pstmt.setInt(3, newAge);
        pstmt.setInt(4, id);
        pstmt.executeUpdate();
    }
}

public void deleteStudent(int id) throws SQLException {
    String sql = "DELETE FROM students WHERE id=?";
    try (Connection conn = DatabaseConnector.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setInt(1, id);
        pstmt.executeUpdate();
    }
}

Step 5: Main Application (App.java)

Create a menu-driven console interface:

import java.util.List;
import java.util.Scanner;

public class App {
    private static final Scanner scanner = new Scanner(System.in);
    private static final StudentDAO studentDao = new StudentDAO();

    public static void main(String[] args) {
        boolean running = true;
        while (running) {
            printMenu();
            int choice = scanner.nextInt();
            scanner.nextLine(); // Consume newline

            switch (choice) {
                case 1 -> addStudent();
                case 2 -> viewAllStudents();
                case 3 -> updateStudent();
                case 4 -> deleteStudent();
                case 5 -> running = false;
                default -> System.out.println("Invalid choice!");
            }
        }
    }

    private static void printMenu() {
        System.out.println("\nStudent Management System");
        System.out.println("1. Add Student");
        System.out.println("2. View All Students");
        System.out.println("3. Update Student");
        System.out.println("4. Delete Student");
        System.out.println("5. Exit");
        System.out.print("Enter your choice: ");
    }

    private static void addStudent() {
        System.out.print("Enter name: ");
        String name = scanner.nextLine();
        System.out.print("Enter email: ");
        String email = scanner.nextLine();
        System.out.print("Enter age: ");
        int age = scanner.nextInt();
        scanner.nextLine();

        try {
            studentDao.addStudent(new Student(name, email, age));
            System.out.println("Student added!");
        } catch (SQLException e) {
            System.err.println("Error adding student: " + e.getMessage());
        }
    }

    private static void viewAllStudents() {
        try {
            List<Student> students = studentDao.getAllStudents();
            for (Student s : students) {
                System.out.println(s.getId() + ": " + s.getName() + ", " + s.getEmail() + ", " + s.getAge());
            }
        } catch (SQLException e) {
            System.err.println("Error fetching students: " + e.getMessage());
        }
    }

    // Implement updateStudent() and deleteStudent() similarly
}

Step 6: Run the Project

1. Add the JDBC Driver: Ensure the MySQL Connector JAR is in your classpath.
2. Compile and Execute:

javac -cp .:mysql-connector-j-8.0.33.jar *.java
java -cp .:mysql-connector-j-8.0.33.jar App

1. Test the Menu:
   • Add a student: Enter name, email, and age.
   • View all students to confirm.
   • Update or delete records using their ID.

Key Takeaways

• Separation of Concerns: DAO pattern isolates database logic.
• Resource Management: try-with-resources ensures connections are closed.
• Security: PreparedStatement prevents SQL injection.

Expand This Project

• Add validation (e.g., unique email checks).
• Implement search functionality.
• Use connection pooling (e.g., HikariCP).

Next Up: Advanced JDBC Topics – Dive into connection pooling, batch processing, and more!