Java JDBC MySQL CRUD: Data Manipulation Examples

Posted on Sep 16, 2025 in Computing and Communications

Java JDBC CRUD: MySQL Database Interaction

This document provides practical Java code examples demonstrating basic CRUD (Create, Read, Update, Delete) operations on a MySQL database using JDBC (Java Database Connectivity).

Each example connects to a local MySQL database named mydb with user root and an empty password. It’s important to note that for production environments, using PreparedStatement is highly recommended to prevent SQL injection vulnerabilities, and sensitive credentials should not be hardcoded.

1. Insert Data into MySQL with Java JDBC

The InsertData.java program allows users to input student details (ID, name, address) and insert them as a new record into the student table in the MySQL database.

InsertData.java Code

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

public class InsertData {
    public static void main(String[] args) {
        try {
            // To establish a database connection
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", "");
            Statement s = con.createStatement();

            // To read data for insertion into the student table
            Scanner sc = new Scanner(System.in);
            System.out.println("Inserting Data into student table:");
            System.out.println("________________________________________");
            System.out.print("Enter student id: ");
            int sid = sc.nextInt();
            System.out.print("Enter student name: ");
            String sname = sc.next(); // Consider sc.nextLine() after consuming newline for multi-word names
            System.out.print("Enter student address: ");
            String saddr = sc.next(); // Consider sc.nextLine() for multi-word addresses

            // To execute the insert query
            s.execute("insert into student values("+sid+",'"+sname+"','"+saddr+"')");
            System.out.println("Data inserted successfully into student table");

            s.close();
            con.close();
            sc.close(); // Close the scanner
        } catch (SQLException err) {
            System.out.println("ERROR: " + err);
        } catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
    }
}
Key Steps for Data Insertion:
  • Driver Loading: Class.forName("com.mysql.jdbc.Driver"); loads the MySQL JDBC driver. (Note: This step is often optional with modern JDBC drivers.)
  • Connection Establishment: A connection is made to the mydb database.
  • User Input: A Scanner reads student ID, name, and address from the console.
  • SQL Execution: An INSERT SQL query is constructed and executed using a Statement object.
  • Resource Closure: The Statement, Connection, and Scanner objects are closed to release resources.

2. Update Records in MySQL using Java JDBC

The UpdateData.java program allows users to modify existing student records in the student table based on a provided student ID.

UpdateData.java Code

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

public class UpdateData {
    public static void main(String[] args) {
        try {
            // To establish a database connection
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", "");
            Statement s = con.createStatement();

            // To read data for updating records in the student table
            Scanner sc = new Scanner(System.in);
            System.out.println("Update Data in student table:");
            System.out.println("________________________________________");
            System.out.print("Enter student id: ");
            int sid = sc.nextInt();
            System.out.print("Enter new student name: ");
            String sname = sc.next(); // Consider sc.nextLine()
            System.out.print("Enter new student address: ");
            String saddr = sc.next(); // Consider sc.nextLine()

            // To execute the update query
            s.execute("update student set s_name='"+sname+"',s_address = '"+saddr+"' where s_id = "+sid);
            System.out.println("Data updated successfully");

            s.close();
            con.close();
            sc.close(); // Close the scanner
        } catch (SQLException err) {
            System.out.println("ERROR: " + err);
        } catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
    }
}
Key Steps for Data Update:
  • Connection Setup: Similar to insertion, a database connection is established.
  • Input for Update: The program prompts for the student ID of the record to be updated, along with the new name and address.
  • SQL Execution: An UPDATE SQL query is executed to modify the specified student’s details.
  • Resource Closure: Database resources and the Scanner are properly closed.

3. Delete Data from MySQL with Java JDBC

The DeleteData.java program facilitates the removal of student records from the student table based on a given student ID.

DeleteData.java Code

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

public class DeleteData {
    public static void main(String[] args) {
        try {
            // To establish a database connection
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", "");
            Statement s = con.createStatement();

            // To read data for deleting records from the student table
            Scanner sc = new Scanner(System.in);
            System.out.println("Delete Data from student table:");
            System.out.println("________________________________________");
            System.out.print("Enter student id to delete: ");
            int sid = sc.nextInt();

            // To execute the delete query
            s.execute("delete from student where s_id = "+sid);
            System.out.println("Data deleted successfully");

            s.close();
            con.close();
            sc.close(); // Close the scanner
        } catch (SQLException err) {
            System.out.println("ERROR: " + err);
        } catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
    }
}
Key Steps for Data Deletion:
  • Database Connection: A connection to the MySQL database is established.
  • ID Input: The user provides the student ID of the record to be deleted.
  • SQL Execution: A DELETE SQL query is executed to remove the matching record.
  • Resource Management: All open resources are closed.

4. Display All Records from MySQL with Java JDBC

The DisplayData.java program retrieves and displays all records from the student table in a formatted manner.

DisplayData.java Code

import java.sql.*;
import java.util.Scanner; // Scanner is imported but not used in this specific class

public class DisplayData {
    public static void main(String[] args) {
        try {
            // To establish a database connection
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", "");
            Statement s = con.createStatement();

            // To display the data from the student table
            ResultSet rs = s.executeQuery("select * from student");
            if (rs != null) {
                System.out.println("SID \t STU_NAME \t ADDRESS");
                System.out.println("________________________________________");
                while (rs.next()) {
                    System.out.println(rs.getString(1) +" \t "+ rs.getString(2)+ " \t "+rs.getString(3));
                    // The original code printed a separator after each row, which might be redundant.
                    // Keeping it as per "Do not cut the text or remove content" but noting it.
                    System.out.println("________________________________________");
                }
                rs.close(); // Close ResultSet
            }
            s.close();
            con.close();
            // Scanner sc = new Scanner(System.in); is not used here, so sc.close() is not needed.
        } catch (SQLException err) {
            System.out.println("ERROR: " + err);
        } catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
    }
}
Key Steps for Data Display:
  • Connection: A standard JDBC connection is made to the database.
  • Query Execution: A SELECT * FROM student SQL query is executed using executeQuery(), which returns a ResultSet.
  • Result Processing: The program iterates through the ResultSet, extracting and printing each student’s ID, name, and address.
  • Resource Cleanup: The ResultSet, Statement, and Connection are closed.