How to Perform CRUD Operations Using JDBC

Introduction

CRUD (Create, Read, Update, Delete) operations are the core functionalities required for interacting with databases in software applications. Java Database Connectivity (JDBC) provides a standardized API to perform these operations in a relational database.

In this guide, we’ll walk you through performing CRUD operations using JDBC, from establishing a database connection to executing SQL queries. Whether you’re new to JDBC or need a refresher, this article provides a practical, step-by-step approach.

What Are CRUD Operations?

CRUD represents the four basic operations performed on a database:

• Create: Insert new records into a database table.
• Read: Retrieve records from a database table.
• Update: Modify existing records in a database table.
• Delete: Remove records from a database table.

Prerequisites for JDBC CRUD Operations

1. Java Development Kit (JDK): Ensure you have JDK installed on your system.
2. Database Server: Use a relational database like MySQL, PostgreSQL, or Oracle.
3. JDBC Driver: Download and include the JDBC driver for your database. For example, MySQL Connector/J for MySQL.
4. IDE or Text Editor: Use an IDE like IntelliJ IDEA, Eclipse, or a text editor with a build tool like Maven or Gradle.

Step-by-Step Guide to Performing CRUD Operations Using JDBC

1. Set Up Your Database

Create a sample database and table to test CRUD operations. For instance, in MySQL:

CREATE DATABASE jdbc_demo;

USE jdbc_demo;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Add JDBC Dependency

• Maven Dependency:
  Add the JDBC driver dependency for your database in pom.xml:

   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>8.0.34</version>
   </dependency>

• Manually Add JAR: Download the JDBC driver JAR and include it in your project’s classpath.

3. Establish a Database Connection

Create a connection to your database using the DriverManager class.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_demo";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

4. Create Operation (Insert Records)

Use PreparedStatement to insert data into the employees table.

import java.sql.Connection;
import java.sql.PreparedStatement;

public class CreateEmployee {
    public static void createEmployee(String name, String position, double salary) {
        String query = "INSERT INTO employees (name, position, salary) VALUES (?, ?, ?)";
        try (Connection con = DatabaseConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(query)) {
            ps.setString(1, name);
            ps.setString(2, position);
            ps.setDouble(3, salary);
            ps.executeUpdate();
            System.out.println("Employee added successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        createEmployee("John Doe", "Software Engineer", 75000);
    }
}

5. Read Operation (Retrieve Records)

Fetch records from the database using a ResultSet.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class ReadEmployees {
    public static void readEmployees() {
        String query = "SELECT * FROM employees";
        try (Connection con = DatabaseConnection.getConnection();
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") +
                                   ", Name: " + rs.getString("name") +
                                   ", Position: " + rs.getString("position") +
                                   ", Salary: $" + rs.getDouble("salary"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        readEmployees();
    }
}

6. Update Operation (Modify Records)

Update existing records using PreparedStatement.

import java.sql.Connection;
import java.sql.PreparedStatement;

public class UpdateEmployee {
    public static void updateEmployee(int id, double newSalary) {
        String query = "UPDATE employees SET salary = ? WHERE id = ?";
        try (Connection con = DatabaseConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(query)) {
            ps.setDouble(1, newSalary);
            ps.setInt(2, id);
            ps.executeUpdate();
            System.out.println("Employee updated successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        updateEmployee(1, 80000);
    }
}

7. Delete Operation (Remove Records)

Delete records using PreparedStatement.

import java.sql.Connection;
import java.sql.PreparedStatement;

public class DeleteEmployee {
    public static void deleteEmployee(int id) {
        String query = "DELETE FROM employees WHERE id = ?";
        try (Connection con = DatabaseConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(query)) {
            ps.setInt(1, id);
            ps.executeUpdate();
            System.out.println("Employee deleted successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        deleteEmployee(1);
    }
}

Best Practices for JDBC CRUD Operations

1. Use PreparedStatement: Prevents SQL injection attacks and enhances performance.
2. Close Resources: Always close Connection, Statement, and ResultSet to avoid memory leaks.
3. Use Connection Pooling: Libraries like HikariCP can optimize performance in production.
4. Log Errors: Log all exceptions for debugging and monitoring.
5. Validate Input Data: Sanitize user inputs before executing queries.

External Resources

• Oracle JDBC Documentation
• Baeldung JDBC Guide
• MySQL Connector/J Documentation

FAQs

1. What is JDBC?
   JDBC (Java Database Connectivity) is an API for connecting Java applications to relational databases.
2. What are CRUD operations?
   CRUD stands for Create, Read, Update, and Delete—basic operations performed on a database.
3. Which JDBC driver is best for CRUD operations?
   Type-4 (Thin Driver) is preferred for modern applications due to its portability and performance.
4. What is PreparedStatement in JDBC?
   PreparedStatement is a precompiled SQL statement that provides better performance and security.
5. Can I use JDBC with NoSQL databases?
   JDBC is primarily for relational databases, but some NoSQL databases offer JDBC-like drivers.
6. What is the difference between Statement and PreparedStatement?
   PreparedStatement is precompiled and prevents SQL injection, whereas Statement is not.
7. How do I handle SQL exceptions in JDBC?
   Use try-catch blocks and log exceptions for debugging.
8. Is connection pooling necessary for JDBC?
   Connection pooling improves performance, especially in applications with high database interaction.
9. What is the role of DriverManager in JDBC?
   DriverManager manages database drivers and establishes connections.
10. How can I secure JDBC applications?
    Use PreparedStatement, validate inputs, and avoid exposing database credentials.

Conclusion

Performing CRUD operations is an essential skill for Java developers working with databases. JDBC provides a robust and flexible API to execute these operations efficiently. By following best practices and using tools like PreparedStatement and connection pooling, developers can build secure, scalable, and high-performance applications.