Building a Simple Student Management System Using Java, JDBC, and MySQL (in WSL)

Vijay PrajapatiVijay Prajapati
6 min read

Table of contents

Welcome to this guide where we’ll show you how to create a basic Student Management System using Java and MySQL. This project is run from a Linux environment in Windows (WSL) and demonstrates how to use JDBC (Java Database Connectivity) to communicate with a MySQL database.

1. Introduction

In this project, you'll develop a simple console application that lets you add, view, and delete student records. The application demonstrates the following concepts:

  • JDBC: A Java API for connecting and executing queries with databases.

  • SQL Operations: Using commands such as INSERT, SELECT, and DELETE.

  • WSL: Running your project in a Linux environment on Windows.

2. Prerequisites

Before you start, ensure you have the following:

  • Java Development Kit (JDK): Verify by running:

      java -version

  • MySQL installed in WSL: You can install it using:

      sudo apt update
  sudo apt install mysql-server

  • WSL (Windows Subsystem for Linux): Set up and ready to use.

  • A Text Editor/IDE: VS Code, Nano, or Vim will work.

  • MySQL JDBC Driver (Connector): Download from the MySQL Connector/J page.

3. Setting up MySQL in WSL

Step 3.1: Install and Start MySQL

First, update your package list and install MySQL:

sudo apt update
sudo apt install mysql-server

Once installed, start the MySQL service:

sudo service mysql start

Step 3.2: Log in to MySQL

Log in to MySQL as root:

sudo mysql -u root

4. Creating the Database and Table

Now create a database called student_db and a table called students.

In the MySQL shell, type:

CREATE DATABASE student_db;
USE student_db;
CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

This creates a database and a table with three columns:

  • id: an auto-incremented primary key.

  • name: a field for the student’s name.

  • age: a field for the student’s age.

Exit MySQL:

EXIT;

5. Creating Your Java Project

Step 5.1: Create a Project Folder in WSL

Open your WSL terminal and create a folder for your project:

mkdir ~/jdbc_project
cd ~/jdbc_project

Step 5.2: Create the Java Source File

Create a new file for your Java code:

touch StudentApp.java

You can open it with an editor like Nano:

nano StudentApp.java

Later, paste the provided Java code into this file.

6. Adding the MySQL JDBC Connector

Step 6.1: Download the Connector

Download the “Platform Independent” ZIP from the MySQL Connector/J download page.

Step 6.2: Move the Connector JAR to Your Project Folder

If your downloaded file is in your Windows Downloads folder and you’re currently in the WSL terminal, use:

cp /mnt/c/Users/<YourUsername>/Downloads/mysql-connector-j-9.2.0.jar ~/jdbc_project/

Replace <YourUsername> with your Windows username.

Once done, you should see the file in your project folder by running:

ls ~/jdbc_project

7. Writing the Java Code

Open the StudentApp.java file and add the following code. This code creates a menu-driven application that uses JDBC to insert, view, and delete student records:

import java.sql.*;
import java.util.Scanner;

public class StudentApp {
    // JDBC URL, username, and password to connect to the database
    static final String DB_URL = "jdbc:mysql://localhost:3306/student_db";
    static final String USER = "root";
    static final String PASS = "your_password"; // Replace "your_password" with your MySQL root password

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);

        try {
            // Step 1: Establish connection to MySQL database
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected to the database successfully!");

            // Step 2: Enter a loop for the menu-driven system
            while (true) {
                System.out.println("\n--- Student Management ---");
                System.out.println("1. Add Student");
                System.out.println("2. View Students");
                System.out.println("3. Delete Student");
                System.out.println("4. Exit");
                System.out.print("Enter your choice: ");
                int choice = sc.nextInt();
                sc.nextLine(); // To consume the newline character

                if (choice == 1) {
                    // Add a student
                    System.out.print("Enter name: ");
                    String name = sc.nextLine();
                    System.out.print("Enter age: ");
                    int age = sc.nextInt();

                    String sql = "INSERT INTO students(name, age) VALUES (?, ?)";
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.setString(1, name);
                    pst.setInt(2, age);
                    pst.executeUpdate();
                    System.out.println("Student added successfully.");

                } else if (choice == 2) {
                    // View all students
                    String sql = "SELECT * FROM students";
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);

                    System.out.println("\n--- Student List ---");
                    while (rs.next()) {
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        int age = rs.getInt("age");
                        System.out.println(id + " - " + name + " - " + age);
                    }

                } else if (choice == 3) {
                    // Delete a student by ID
                    System.out.print("Enter student ID to delete: ");
                    int id = sc.nextInt();

                    String sql = "DELETE FROM students WHERE id = ?";
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.setInt(1, id);
                    int rows = pst.executeUpdate();

                    if (rows > 0)
                        System.out.println("Student deleted successfully.");
                    else
                        System.out.println("Student not found.");

                } else if (choice == 4) {
                    // Exit the program
                    System.out.println("Goodbye!");
                    break;
                } else {
                    System.out.println("Invalid choice.");
                }
            }
            // Step 3: Close the database connection
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
}

Remember: Replace "your_password" in the code with the actual MySQL root password that you set up.

8. Compiling and Running the Project

Step 8.1: Compile the Code

Make sure you are inside your project folder (~/jdbc_project) and compile with the JDBC driver in the classpath:

javac -cp .:mysql-connector-j-9.2.0.jar StudentApp.java

Note: In Linux/WSL, classpath elements are separated by a colon (:).

Step 8.2: Run the Program

Run your compiled Java program with the JDBC connector:

java -cp .:mysql-connector-j-9.2.0.jar StudentApp

Your application should now launch, displaying a menu such as:

--- Student Management ---
1. Add Student
2. View Students
3. Delete Student
4. Exit
Enter your choice:

You can follow the prompts to add, view, and delete student records.

9. How It All Works

Overview of the Project Workflow

  1. User Input:
    You enter choices and student data through the console.

  2. Java Application (StudentApp.java):

    • Reads the inputs.

    • Uses the JDBC API to formulate SQL queries.

  3. JDBC Driver:

    • The MySQL JDBC connector (JAR file) translates your Java SQL commands into MySQL-understandable queries.

  4. MySQL Database:

    • The database (student_db) receives the queries, inserts/updates/fetches data from the students table.

    • All data is stored locally on your WSL system (in the MySQL data directory).

  5. Output:

    • The results (like a list of students) are shown back on the console.

Visual Map (Textual Representation)

[User Input (Console)]
         │
         ▼
[StudentApp.java (Java Code)]
         │
         ▼
[JDBC Driver (mysql-connector-j-9.2.0.jar)]
         │
         ▼
[MySQL Server in WSL (student_db)]
         │
         ▼
[Data Stored in "students" Table]

10. Conclusion

In this project, you learned how to:

  • Set up MySQL on WSL and create a database/table.

  • Write a Java program that uses JDBC to interact with the database.

  • Compile and run a Java application in a Linux environment on Windows.

  • Perform basic CRUD operations (Insert, Select, Delete) using SQL from a Java program.

This project lays the foundation for more advanced database applications in Java. You can later expand it by adding more features such as updating records, searching by name, or even building a graphical user interface (GUI).

1
Subscribe to my newsletter

Read articles from Vijay Prajapati directly inside your inbox. Subscribe to the newsletter, and don't miss out.

JDBCMySQL

Written by

Vijay Prajapati
Vijay Prajapati

I'm an enthusiastic engineering graduate with a passion for a challenging career. Proficient in Java programming and blockchain technology, I'm excited to contribute to the world of engineering and technology.