Phase 8B: Demystifying JDBC – 5-User Login System Practical Mastery

A step-by-step practical blueprint for exam questions asking to build a 5-user database authentication & login system using Java Database Connectivity (JDBC).

1. The 5-Step JDBC Login Architecture

In university exams, login questions evaluate whether you can securely connect Java to a database, populate initial records, query credentials using PreparedStatement, and handle exceptions.

┌─────────────────┐       ┌─────────────────┐       ┌────────────────────────┐
│  GUI / Console  │ ──1──►│  JDBC Driver    │ ──2──►│ Database (SQLite/MySQL)│
│  User Inputs    │       │ (DriverManager) │       │  `users` Table         │
└─────────────────┘       └─────────────────┘       └────────────────────────┘
        │                                                       │
        │ ──3. PreparedStatement ("SELECT * WHERE u=? AND p=?") ┤
        │                                                       │
        ◄──4. ResultSet (rs.next() == true ? Authenticated) ────┘
        │
        └──5. Close Resources (Connection, Statement, ResultSet)
        

2. Database Schema Creation & 5-User Pre-Seeding (SQL)

When an exam prompt says: "Using a database with 5 users, create a login system using JDBC...", you should provide both the SQL table creation (DDL) and the 5 pre-seeded user rows (DML):

A. SQL DDL & DML Script

-- Step 1: Create Users Table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    role VARCHAR(20) NOT NULL
);

-- Step 2: Seed Exactly 5 Default Users
INSERT INTO users (username, password, role) VALUES 
('admin', 'admin123', 'ADMIN'),
('student1', 'pass123', 'STUDENT'),
('student2', 'pass456', 'STUDENT'),
('lecturer1', 'prof789', 'LECTURER'),
('guest', 'guest123', 'GUEST');
        

B. The 5 Pre-Seeding Accounts:

User ID	Username	Password	Role
1	admin	`admin123`	ADMIN
2	student1	`pass123`	STUDENT
3	student2	`pass456`	STUDENT
4	lecturer1	`prof789`	LECTURER
5	guest	`guest123`	GUEST

3. Modular Java JDBC Authentication Method

Here is the clean, exam-tested backend method using Java 7+ Try-With-Resources for automatic resource cleanup:

import java.sql.*;

public class UserAuthDAO {
    // Database URL (SQLite example: jdbc:sqlite:users.db, MySQL: jdbc:mysql://localhost:3306/auth_db)
    private static final String DB_URL = "jdbc:sqlite:users.db";
    private static final String DB_USER = "root";
    private static final String DB_PASS = "password";

    /**
     * Authenticates a user against the 5-user database.
     * @return User object or role string if valid, null if invalid.
     */
    public static String authenticateUser(String inputUsername, String inputPassword) {
        // Parametrized query to PREVENT SQL INJECTION!
        String sql = "SELECT role FROM users WHERE username = ? AND password = ?";

        // Try-with-resources auto-closes Connection, PreparedStatement, and ResultSet
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Bind parameters (1-indexed!)
            pstmt.setString(1, inputUsername.trim());
            pstmt.setString(2, inputPassword.trim());

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    // Match found! Return role
                    return rs.getString("role");
                }
            }
        } catch (SQLException e) {
            System.err.println("Database Error during authentication: " + e.getMessage());
            e.printStackTrace();
        }
        
        // Return null if no match found
        return null;
    }
}
        

💡 Examiner Distinction Hack: Preventing SQL Injection

Examiners specifically check whether you used PreparedStatement or raw string concatenation.

❌ WRONG (SQL Injection Vulnerable):
String sql = "SELECT * FROM users WHERE username='" + user + "' AND password='" + pass + "'";
If user enters ' OR '1'='1 as password, authentication is bypassed!

✅ CORRECT (PreparedStatement):
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
pstmt.setString(1, user); pstmt.setString(2, pass);

4. Complete End-to-End GUI + JDBC Login System

Below is the complete, runnable Java application combining an AWT/Swing GUI interface with JDBC database initialization, 5-user table seeding, and authentication:

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class JdbcLoginSystemApp extends JFrame implements ActionListener {
    private JTextField txtUser;
    private JPasswordField txtPass;
    private JButton btnLogin, btnReset;
    private JLabel lblStatus;

    private static final String DB_URL = "jdbc:sqlite:system_users.db";

    public JdbcLoginSystemApp() {
        super("SCS2108 JDBC 5-User Login Portal");
        
        // 1. Initialize DB Table and 5 Users on startup
        initDatabase();

        // 2. Setup GUI Layout
        setLayout(new BorderLayout(10, 10));
        
        JLabel lblHeader = new JLabel("System Login Portal", JLabel.CENTER);
        lblHeader.setFont(new Font("Outfit", Font.BOLD, 20));
        add(lblHeader, BorderLayout.NORTH);

        JPanel centerPanel = new JPanel(new GridLayout(2, 2, 10, 10));
        centerPanel.setBorder(BorderFactory.createEmptyBorder(15, 15, 15, 15));
        
        centerPanel.add(new JLabel("Username:"));
        txtUser = new JTextField(15);
        centerPanel.add(txtUser);

        centerPanel.add(new JLabel("Password:"));
        txtPass = new JPasswordField(15);
        centerPanel.add(txtPass);

        add(centerPanel, BorderLayout.CENTER);

        JPanel southPanel = new JPanel(new FlowLayout(FlowLayout.CENTER, 10, 10));
        btnLogin = new JButton("Login");
        btnReset = new JButton("Reset");
        lblStatus = new JLabel("Status: Enter credentials", JLabel.CENTER);

        btnLogin.addActionListener(this);
        btnReset.addActionListener(this);

        southPanel.add(btnLogin);
        southPanel.add(btnReset);
        southPanel.add(lblStatus);

        add(southPanel, BorderLayout.SOUTH);

        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(420, 230);
        setLocationRelativeTo(null);
        setVisible(true);
    }

    // Initialize DB and Seed 5 Users
    private void initDatabase() {
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement()) {
            
            // Create Table if not exists
            String createTableSql = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "username TEXT UNIQUE NOT NULL, " +
                    "password TEXT NOT NULL, " +
                    "role TEXT NOT NULL);";
            stmt.execute(createTableSql);

            // Check if DB already populated
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS total FROM users");
            if (rs.next() && rs.getInt("total") == 0) {
                // Seed 5 Default Users
                stmt.execute("INSERT INTO users (username, password, role) VALUES ('admin', 'admin123', 'ADMIN')");
                stmt.execute("INSERT INTO users (username, password, role) VALUES ('student1', 'pass123', 'STUDENT')");
                stmt.execute("INSERT INTO users (username, password, role) VALUES ('student2', 'pass456', 'STUDENT')");
                stmt.execute("INSERT INTO users (username, password, role) VALUES ('lecturer1', 'prof789', 'LECTURER')");
                stmt.execute("INSERT INTO users (username, password, role) VALUES ('guest', 'guest123', 'GUEST')");
                System.out.println("Database initialized with 5 seeded users.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == btnLogin) {
            String user = txtUser.getText().trim();
            String pass = new String(txtPass.getPassword()).trim();

            if (user.isEmpty() || pass.isEmpty()) {
                lblStatus.setText("Status: Fields cannot be empty!");
                return;
            }

            // Perform JDBC Authentication
            String role = authenticate(user, pass);
            if (role != null) {
                lblStatus.setText("Status: Success! Welcome " + user + " (" + role + ")");
                JOptionPane.showMessageDialog(this, "Login Successful!\nRole: " + role, "Auth Success", JOptionPane.INFORMATION_MESSAGE);
            } else {
                lblStatus.setText("Status: Invalid username or password.");
                JOptionPane.showMessageDialog(this, "Access Denied: Invalid Credentials", "Auth Failed", JOptionPane.ERROR_MESSAGE);
            }
        } else if (e.getSource() == btnReset) {
            txtUser.setText("");
            txtPass.setText("");
            lblStatus.setText("Status: Reset complete.");
        }
    }

    private String authenticate(String username, String password) {
        String sql = "SELECT role FROM users WHERE username = ? AND password = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, username);
            pstmt.setString(2, password);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("role");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> new JdbcLoginSystemApp());
    }
}
        

5. Examiner Traps & Scoring Checklist

Requirement	Common Student Mistake	Full Mark Solution
Driver Registration	Forgetting `Class.forName("...")` when using older JDBC drivers.	Include `Class.forName("org.sqlite.JDBC")` or `com.mysql.cj.jdbc.Driver` inside try-catch.
SQL Injection Prevention	Concatenating strings in raw `Statement` query.	Use `PreparedStatement` with `?` placeholders.
ResultSet Iteration	Calling `rs.getString(1)` without calling `rs.next()` first.	Always evaluate `if (rs.next())` before extracting column values!
Resource Cleanup	Leaving Connection, PreparedStatement, or ResultSet open.	Use Try-with-resources `try (Connection c = ...)` or explicit `finally` block with `.close()`.
5-User Seeding	Writing JDBC code without demonstrating database creation or pre-seeding.	Provide explicit SQL DDL/DML table creation & 5-user `INSERT` statements.

⚡ Quick Self-Test

Q: An exam question asks: "Write a Java JDBC method that authenticates a user against a table containing 5 users. Explain what rs.next() returns and why."

Answer:
rs.next() moves the cursor of the ResultSet from its initial position (before the first row) to the first matching row.

- It returns true if a matching user record exists (meaning authentication succeeded).
- It returns false if no matching user record exists (meaning invalid credentials).

Using if (rs.next()) guarantees that we only attempt to read columns (like `role` or `id`) when a valid matching database record is present.