Java Database Connectivity (JDBC) Step-by-Step: MySQL Connection, Prepared Statements & Swing GUI

Author: Bhuban Subedi | Subject: Object-Oriented Programming in Java (CACS205) | Semester: Third Semester

In professional software development, graphical user interfaces must persist data to relational database management systems (RDBMS) such as MySQL, PostgreSQL, or Oracle.

JDBC (Java Database Connectivity) is the standard Java API (part of java.sql) that allows Java applications to connect to databases, execute SQL queries, process result sets, and commit transactions.

In the Tribhuvan University (TU) BCA Third Semester OOP in Java (CACS205) board exams and final lab examinations, building a Java Swing GUI form connected to a MySQL database using JDBC is one of the most consistent 10-mark practical examination questions.

In this guide, we will break down the 5 steps of JDBC, compare Statement vs PreparedStatement, and build a complete database-driven Student Registration System.

1. The 5 Core Steps of JDBC

Every JDBC program follows a predictable sequence of 5 fundamental steps:

+------------------------------------+-----------------------------------------------------+
| Step                               | Standard Code Pattern                               |
+------------------------------------+-----------------------------------------------------+
| 1. Load the JDBC Driver Class      | `Class.forName("com.mysql.cj.jdbc.Driver");`        |
+------------------------------------+-----------------------------------------------------+
| 2. Establish the Database Connect  | `Connection conn = DriverManager.getConnection(...);|
+------------------------------------+-----------------------------------------------------+
| 3. Create Statement Object         | `PreparedStatement ps = conn.prepareStatement(...);`|
+------------------------------------+-----------------------------------------------------+
| 4. Execute the SQL Query           | `ResultSet rs = ps.executeQuery();` (or `executeUpdate`)|
+------------------------------------+-----------------------------------------------------+
| 5. Process Results & Close Streams | `conn.close(); ps.close();`                         |
+------------------------------------+-----------------------------------------------------+

                        +----------------------------+
                        |      Java Application      |
                        +--------------+-------------+
                                       |
                                       v
                        +----------------------------+
                        |          JDBC API          |
                        | (java.sql.Connection, etc) |
                        +--------------+-------------+
                                       |
                                       v
                        +----------------------------+
                        |    MySQL JDBC Driver       |
                        | (mysql-connector-java.jar) |
                        +--------------+-------------+
                                       |
                                       v
                        +----------------------------+
                        |      MySQL Database        |
                        |      (localhost:3306)      |
                        +----------------------------+

2. Statement vs. PreparedStatement: Why Security Matters

In TU examinations and viva voce, you will always be asked why PreparedStatement is preferred over Statement.

+------------------------------------+------------------------------------+
| Statement                          | PreparedStatement                  |
+------------------------------------+------------------------------------+
| Executes static SQL queries without| Executes parameterized, pre-compiled|
| parameters.                        | SQL queries.                       |
+------------------------------------+------------------------------------+
| Slower for repeated queries because| Faster execution because the DB    |
| database must parse and compile the| parses and compiles the query plan |
| SQL string every single time.      | once and reuses it with new params.|
+------------------------------------+------------------------------------+
| **Vulnerable to SQL Injection!**   | **Immune to SQL Injection!** User  |
| Concatenating strings allows user  | inputs are strictly treated as data|
| input to alter the SQL syntax.     | values, not executable SQL syntax. |
+------------------------------------+------------------------------------+

3. Database Setup: Creating the MySQL Table

Before writing Java code, open phpMyAdmin in XAMPP (or MySQL command line) and run:

CREATE DATABASE IF NOT EXISTS tu_bca_db;
USE tu_bca_db;

CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    roll_number INT UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    semester VARCHAR(20) NOT NULL,
    gpa DECIMAL(3,2) NOT NULL
);

4. Complete Compilable Program: Java Swing GUI with MySQL JDBC

Here is a clean, complete, and fully functional Java Swing GUI application with CRUD database connectivity.

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentRegistrationGUI extends JFrame implements ActionListener {
    // GUI Components
    private JTextField txtRoll, txtName, txtGpa;
    private JComboBox<String> cmbSemester;
    private JButton btnSave, btnSearch, btnClear;

    // Database Connection Parameters
    private static final String DB_URL = "jdbc:mysql://localhost:3306/tu_bca_db";
    private static final String DB_USER = "root";
    private static final String DB_PASS = ""; // Default XAMPP password is empty

    public StudentRegistrationGUI() {
        setTitle("TU BCA Student Registration System (JDBC)");
        setSize(450, 350);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null); // Center on screen
        setLayout(new GridLayout(6, 2, 10, 10));

        // Form Labels and Fields
        add(new JLabel("  Roll Number:"));
        txtRoll = new JTextField();
        add(txtRoll);

        add(new JLabel("  Full Name:"));
        txtName = new JTextField();
        add(txtName);

        add(new JLabel("  Semester:"));
        String[] semesters = {"First", "Second", "Third", "Fourth", "Fifth", "Sixth", "Seventh", "Eighth"};
        cmbSemester = new JComboBox<>(semesters);
        add(cmbSemester);

        add(new JLabel("  GPA (0.0 - 4.0):"));
        txtGpa = new JTextField();
        add(txtGpa);

        // Buttons
        btnSave = new JButton("Save Record");
        btnSearch = new JButton("Search by Roll");
        btnClear = new JButton("Clear Form");

        btnSave.addActionListener(this);
        btnSearch.addActionListener(this);
        btnClear.addActionListener(this);

        add(btnSave);
        add(btnSearch);
        add(btnClear);
        add(new JLabel("")); // Empty cell for grid alignment
    }

    // Helper method to get DB Connection
    private Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == btnSave) {
            saveStudent();
        } else if (e.getSource() == btnSearch) {
            searchStudent();
        } else if (e.getSource() == btnClear) {
            clearFields();
        }
    }

    // Insert Record into MySQL
    private void saveStudent() {
        String sql = "INSERT INTO students (roll_number, full_name, semester, gpa) VALUES (?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, Integer.parseInt(txtRoll.getText().trim()));
            ps.setString(2, txtName.getText().trim());
            ps.setString(3, (String) cmbSemester.getSelectedItem());
            ps.setDouble(4, Double.parseDouble(txtGpa.getText().trim()));

            int rowsInserted = ps.executeUpdate();
            if (rowsInserted > 0) {
                JOptionPane.showMessageDialog(this, "Student record successfully saved into database!");
                clearFields();
            }

        } catch (NumberFormatException ex) {
            JOptionPane.showMessageDialog(this, "Please enter valid numeric values for Roll and GPA!", "Input Error", JOptionPane.ERROR_MESSAGE);
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(this, "Database Error: " + ex.getMessage(), "SQL Error", JOptionPane.ERROR_MESSAGE);
        }
    }

    // Search Record from MySQL
    private void searchStudent() {
        String sql = "SELECT * FROM students WHERE roll_number = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, Integer.parseInt(txtRoll.getText().trim()));
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                txtName.setText(rs.getString("full_name"));
                cmbSemester.setSelectedItem(rs.getString("semester"));
                txtGpa.setText(String.valueOf(rs.getDouble("gpa")));
                JOptionPane.showMessageDialog(this, "Student record loaded successfully!");
            } else {
                JOptionPane.showMessageDialog(this, "No student found with Roll Number: " + txtRoll.getText());
            }

        } catch (Exception ex) {
            JOptionPane.showMessageDialog(this, "Search Error: " + ex.getMessage(), "SQL Error", JOptionPane.ERROR_MESSAGE);
        }
    }

    private void clearFields() {
        txtRoll.setText("");
        txtName.setText("");
        txtGpa.setText("");
        cmbSemester.setSelectedIndex(0);
    }

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

Frequently Asked Questions (FAQ)

Q1: What is the difference between executeQuery() and executeUpdate()?

  • executeQuery(): Used for SQL SELECT statements. It returns a ResultSet containing tabular records.
  • executeUpdate(): Used for DML/DDL statements (INSERT, UPDATE, DELETE, CREATE TABLE). It returns an int indicating the number of affected database rows.

Q2: What is the purpose of ResultSet.next()?

A ResultSet cursor initially points before the first row of data. Calling .next() moves the cursor to the next valid record row and returns true if a row exists, or false when there are no more records.

LEAVE A REPLY

Please enter your comment!
Please enter your name here