Connect Java to MySQL Using JDBC – Full Setup Guide

🖊️ Introduction

After learning how to establish a JDBC connection in Java, the next essential step is mastering CRUD operations:

• Create (Insert data)

• Read (Retrieve data)

• Update (Modify data)

• Delete (Remove data)

This guide explains how to perform CRUD operations on a MySQL database using Java and JDBC step-by-step.

---

🚄 Prerequisites

• Java JDK and MySQL installed

• JDBC MySQL connector .jar file downloaded

• Database: harsh

• Table: students

---

⚖️ SQL: Create the students Table

Run this SQL in MySQL:

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

---

🎓 Step-by-Step CRUD Operations in Java

1. Insert Data (Create)

public static void insertStudent(Connection con, String name, int age, String city) throws SQLException {
    String query = "INSERT INTO students(name, age, city) VALUES (?, ?, ?)";
    try (PreparedStatement pstmt = con.prepareStatement(query)) {
        pstmt.setString(1, name);
        pstmt.setInt(2, age);
        pstmt.setString(3, city);
        int rows = pstmt.executeUpdate();
        System.out.println(rows + " row(s) inserted.");
    }
}

2. Read Data (Retrieve)

public static void readStudents(Connection con) throws SQLException {
    String query = "SELECT * FROM students";
    try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
        while (rs.next()) {
            System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") +
                ", Age: " + rs.getInt("age") + ", City: " + rs.getString("city"));
        }
    }
}

3. Update Data

public static void updateStudentCity(Connection con, int id, String newCity) throws SQLException {
    String query = "UPDATE students SET city = ? WHERE id = ?";
    try (PreparedStatement pstmt = con.prepareStatement(query)) {
        pstmt.setString(1, newCity);
        pstmt.setInt(2, id);
        int rows = pstmt.executeUpdate();
        System.out.println(rows + " row(s) updated.");
    }
}

4. Delete Data

public static void deleteStudent(Connection con, int id) throws SQLException {
    String query = "DELETE FROM students WHERE id = ?";
    try (PreparedStatement pstmt = con.prepareStatement(query)) {
        pstmt.setInt(1, id);
        int rows = pstmt.executeUpdate();
        System.out.println(rows + " row(s) deleted.");
    }
}

---

▶️ Full Working Example

import java.sql.*;

public class CRUDJDBC {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/harsh";
        String user = "root";
        String password = "root";

        try (Connection con = DriverManager.getConnection(url, user, password)) {
            Class.forName("com.mysql.cj.jdbc.Driver");

            insertStudent(con, "Harsh", 22, "Jaipur");
            readStudents(con);
            updateStudentCity(con, 1, "Delhi");
            deleteStudent(con, 1);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Insert
    public static void insertStudent(Connection con, String name, int age, String city) throws SQLException {
        String query = "INSERT INTO students(name, age, city) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = con.prepareStatement(query)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, age);
            pstmt.setString(3, city);
            int rows = pstmt.executeUpdate();
            System.out.println(rows + " row(s) inserted.");
        }
    }

    // Read
    public static void readStudents(Connection con) throws SQLException {
        String query = "SELECT * FROM students";
        try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") +
                        ", Age: " + rs.getInt("age") + ", City: " + rs.getString("city"));
            }
        }
    }

    // Update
    public static void updateStudentCity(Connection con, int id, String newCity) throws SQLException {
        String query = "UPDATE students SET city = ? WHERE id = ?";
        try (PreparedStatement pstmt = con.prepareStatement(query)) {
            pstmt.setString(1, newCity);
            pstmt.setInt(2, id);
            int rows = pstmt.executeUpdate();
            System.out.println(rows + " row(s) updated.");
        }
    }

    // Delete
    public static void deleteStudent(Connection con, int id) throws SQLException {
        String query = "DELETE FROM students WHERE id = ?";
        try (PreparedStatement pstmt = con.prepareStatement(query)) {
            pstmt.setInt(1, id);
            int rows = pstmt.executeUpdate();
            System.out.println(rows + " row(s) deleted.");
        }
    }
}

---

❗ Common Errors and Solutions

---

🚀 Final Tips

• Always close Connection, Statement, ResultSet

• Use try-with-resources to auto-close JDBC resources

• Avoid SQL injection with PreparedStatement

• Catch specific exceptions (SQLException, ClassNotFoundException)

---

This guide gives you a hands-on experience with full CRUD capabilities in JDBC. Next up, you can explore:

• Batch processing

• Transactions

• Connection pooling

• Building mini projects like Employee Management System